btw, I just realized... this table that it's failing on is unlogged. I'm beginning to do some googling based around that tidbit of information, but I wanted to get that clarification on here sooner rather than later.
Thanks! ...spike On Sep 27, 2012, at 1:29 PM, Spike Grobstein wrote: > 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
