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:
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