Hi,
I'm running into an issue with our backup process using pg_dump on our replica
that I just noticed.
When running pg_dump, I get the following error:
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: could not open file
"base/3273817/4515672": No such file or directory
pg_dump: The command was: COPY public.office_imports (id, created_at,
updated_at, office_id, import_last_active_at) TO stdout;
pg_dump: *** aborted because of error
This occurs while dumping the contents of tables on the 57th table (we have 110
tables), so about halfway through.
I'm using the following command when dumping:
pg_dump -v -ESQL_ASCII -Upostgres -Fc -fd_1 $MY_DATABASE
We're running postgresql 9.1.4 on Ubuntu Linux 64-bit on bare-metal hardware.
We've got over 100GB free on the filesystem that we're dumping to and the
average size of our dumps is around 3.2GB. When this fails, the dump is ~2.3GB
(it fluctuates because the size of the first 57 tables changes).
The dumps are done from our replica which is replicated to using streaming
replication. When I do a dump from the master database server (identical
hardware and configuration), it runs to completion without error.
I stopped and started postgres on the replica, and it stops and starts without
errors or warnings.
I then stopped postgres on the replica, renamed the data directory to data.old
and followed the instructions on:
http://wiki.postgresql.org/wiki/Streaming_Replication
to re-configure streaming replication (using rsync).
After that was done, I moved my recovery.conf file back into place and started
postgres and it replication is working, but when I do pg_dump again, it fails
with the same error. That doesn't really seem to make sense.
Any ideas?
In the interim, our daily dumps are being moved to the master so we have
backups.
thanks!
...spike
Spike Grobstein
Ticket Evolution
