How serious are these problems?
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 haven�t yet proved this restores cleanly (can someone confirm the proper way to do this since pgAdmin doesn�t seem to support it � I guess command line psql, but flags etc?).
I�m 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? I�ve 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
On Mon, Jul 03, 2006 at 06:57:03AM -0700, Steven Ridgway wrote:
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)
This appears to be some kind of 64-bit issue. ftell is probably not
returning a 64-bit offset. The print is obviously truncating the length
also.
Not sure what can be done about this, any Windows hackers about?
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
Steven Ridgway <admin@heatsinksoftware.com> writes:
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
We've seen reports of this sort of symptom due to anti-virus software
deciding that the postmaster is a virus :-(. What add-on AV code do
you have on that machine?
regards, tom lane