pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Spike Grobstein <spike@ticketevolution.com>
To: pgsql-admin@postgresql.org
Subject: Re: error during pg_dump
Date: Thu, 27 Sep 2012 14:42:45 -0400
Message-ID: <DE2DE764-307D-4A23-A9A9-6608AC0977CB@ticketevolution.com> (raw)
In-Reply-To: <DFE66BC7-D582-48B1-B7CF-779BE1A92406@ticketevolution.com>
References: <0BDD66D6-9EF2-4E72-BDF8-93D10FA17376@ticketevolution.com>
	<DFE66BC7-D582-48B1-B7CF-779BE1A92406@ticketevolution.com>

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
> 

view thread (3+ messages)

Message-ID: <DE2DE764-307D-4A23-A9A9-6608AC0977CB@ticketevolution.com>
Permalink:  ../DE2DE764-307D-4A23-A9A9-6608AC0977CB@ticketevolution.com/
Also on:    postgresql.org/message-id/DE2DE764-307D-4A23-A9A9-6608AC0977CB@ticketevolution.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: spike@ticketevolution.com
  Subject: Re: error during pg_dump
  In-Reply-To: <DE2DE764-307D-4A23-A9A9-6608AC0977CB@ticketevolution.com>
  References: <0BDD66D6-9EF2-4E72-BDF8-93D10FA17376@ticketevolution.com> <DFE66BC7-D582-48B1-B7CF-779BE1A92406@ticketevolution.com> <DE2DE764-307D-4A23-A9A9-6608AC0977CB@ticketevolution.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox