Received: from magus.postgresql.org ([87.238.57.229])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <spike@ticketevolution.com>) id 1THHtq-0006Zi-Mu
	for pgsql-admin@postgresql.org; Thu, 27 Sep 2012 17:29:22 +0000
Received: from smtp138.dfw.emailsrvr.com ([67.192.241.138])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <spike@ticketevolution.com>) id 1THHtn-0001I8-PN
	for pgsql-admin@postgresql.org; Thu, 27 Sep 2012 17:29:22 +0000
Received: from smtp30.relay.dfw1a.emailsrvr.com (localhost.localdomain
	[127.0.0.1])
	by smtp30.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id
	7D11E3488BD
	for <pgsql-admin@postgresql.org>; Thu, 27 Sep 2012 13:29:17 -0400 (EDT)
X-SMTPDoctor-Processed: csmtpprox 2.7.4
Received: from localhost (localhost.localdomain [127.0.0.1])
	by smtp30.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id
	48F85348904
	for <pgsql-admin@postgresql.org>; Thu, 27 Sep 2012 13:29:17 -0400 (EDT)
X-Virus-Scanned: OK
Received: by smtp30.relay.dfw1a.emailsrvr.com (Authenticated sender:
	spike-AT-ticketevolution.com) with ESMTPSA id 005D6348A0F
	for <pgsql-admin@postgresql.org>; Thu, 27 Sep 2012 13:29:16 -0400 (EDT)
From: Spike Grobstein <spike@ticketevolution.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_B48484FE-B30E-4142-B7BF-B951385F9153"
Subject: error during pg_dump
Date: Thu, 27 Sep 2012 13:29:16 -0400
Message-Id: <0BDD66D6-9EF2-4E72-BDF8-93D10FA17376@ticketevolution.com>
To: pgsql-admin@postgresql.org
Mime-Version: 1.0 (Apple Message framework v1280)
X-Mailer: Apple Mail (2.1280)
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201209/159
X-Sequence-Number: 40100


--Apple-Mail=_B48484FE-B30E-4142-B7BF-B951385F9153
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

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=

--Apple-Mail=_B48484FE-B30E-4142-B7BF-B951385F9153
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=us-ascii

<html><head></head><body style=3D"word-wrap: break-word; =
-webkit-nbsp-mode: space; -webkit-line-break: after-white-space; =
">Hi,<div><br></div><div>I'm running into an issue with our backup =
process using pg_dump on our replica that I just =
noticed.</div><div><br></div><div>When running pg_dump, I get the =
following error:</div><div><br></div><div><div><span =
class=3D"Apple-tab-span" style=3D"white-space:pre">	</span>pg_dump: =
SQL command failed</div><div><span class=3D"Apple-tab-span" =
style=3D"white-space:pre">	</span>pg_dump: Error message from =
server: ERROR: &nbsp;could not open file "base/3273817/4515672": No such =
file or directory</div><div><span class=3D"Apple-tab-span" =
style=3D"white-space:pre">	</span>pg_dump: The command was: COPY =
public.office_imports (id, created_at, updated_at, office_id, =
import_last_active_at) TO stdout;</div><div><span class=3D"Apple-tab-span"=
 style=3D"white-space:pre">	</span>pg_dump: *** aborted because of =
error</div></div><div><br></div><div>This occurs while dumping the =
contents of tables on the 57th table (we have 110 tables), so about =
halfway through.</div><div><br></div><div>I'm using the following =
command when dumping:</div><div><br></div><div><span =
class=3D"Apple-tab-span" style=3D"white-space:pre">	</span>pg_dump =
-v -ESQL_ASCII -Upostgres -Fc -fd_1 =
$MY_DATABASE</div><div><br></div><div>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).</div><div><br></div><div>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.</div><div><br></div><div>I stopped =
and started postgres on the replica, and it stops and starts without =
errors or warnings.</div><div><br></div><div>I then stopped postgres on =
the replica, renamed the data directory to data.old and followed the =
instructions on:</div><div><br></div><div><a =
href=3D"http://wiki.postgresql.org/wiki/Streaming_Replication">http://wiki=
.postgresql.org/wiki/Streaming_Replication</a></div><div><br></div><div>to=
 re-configure streaming replication (using =
rsync).</div><div><br></div><div>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.</div><div><br></div><div>Any ideas?</div><div><br></div><div>In =
the interim, our daily dumps are being moved to the master so we have =
backups.</div><div><br></div><div>thanks!</div><div><br></div><div><br></d=
iv><div><br></div><div>...spike</div><div>Spike =
Grobstein</div><div>Ticket Evolution</div></body></html>=

--Apple-Mail=_B48484FE-B30E-4142-B7BF-B951385F9153--