Обсуждение: 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 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
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 dumpingcontents 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/ > 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
bunchof 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