Re: error during pg_dump

[Spike Grobstein <spike@xxxxxxxxxxxxxxxxxxx>]

SOLVED.

So it turns out that you can't access unlogged tables on the replica:

exchange_prod=# select count(1) from office_imports;

ERROR:  cannot access temporary or unlogged relations during recovery

The solution was to add the --no-unlogged-table-data option to pg_dump and it dumps successfully.

I got really scared when some files were missing, but things look good now.

w00.

...spike

On Sep 27, 2012, at 2:24 PM, Spike Grobstein wrote:

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