Hi,
We have been having some problems with a postgres database recently and would appreciate any advice or pointers.
Environment:
Postgres 8.1.4 running on Windows Server 2003 Enterprise x64 Edition (installed using standard win32 installer).
Quad (2 x dual core) AMD Opteron 870. 8Gb RAM. 8x250Gb SATA Raid 10 array.
Main database is around 30Gb dominated by 2 large tables (one 7m rows using 11Gb, and another 55m rows using 9gb). Encoding is UTF8.
Symptoms:
Two separate symptoms may be completely unconnected?
1) When running backups using pg_dump (from pgAdmin ie using f option for output), about half way through the dumping contents of table x stage I get the message :
pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used
repeated for every table afterwards. When trying to restore this backup I get :
pg_restore: [custom archiver] error during file seek: Invalid argument
and running the pg_dump with the TAR option I get :
pg_dump: [tar archiver] actual file length (591516459) does not match expected (591516459)
I did manage to run an error/warning free pg_dump using the plain option but I havent yet proved this restores cleanly (can someone confirm the proper way to do this since pgAdmin doesnt seem to support it I guess command line psql, but flags etc?).
Im hoping that this problem is isolated to usage of pg_dump/pg_restore in non-plain mode (maybe affected by Win 2003 x64 ?) and not symptomatic of anything fundamental however the next problem makes me worry about that
2) Occasionally (3 times in the last 2 months) the database locks up. When this happens the log file contains a bunch of errors along the lines of :
2006-06-26 14:53:40 LOG: could not fsync segment 0 of relation 1663/16672/50807554: Permission denied
2006-06-26 14:53:40 ERROR: storage sync failed on magnetic disk: Permission denied
Bouncing the postgres service seems to get everything working again? Ive checked that the postgres account has permissions on all the files, and the last couple of times this happened I ran a forced re-index on everything (following a suggestion on a similar problem in another thread).
Any advice on these issues would be greatly appreciated.
Thanks Barry