Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1THIlN-000145-Jn for pgsql-admin@postgresql.org; Thu, 27 Sep 2012 18:24:41 +0000 Received: from smtp138.dfw.emailsrvr.com ([67.192.241.138]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1THIlK-00026m-TS for pgsql-admin@postgresql.org; Thu, 27 Sep 2012 18:24:40 +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 8178434899B for ; Thu, 27 Sep 2012 14:24:37 -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 7DAC7348AAE for ; Thu, 27 Sep 2012 14:24:37 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp30.relay.dfw1a.emailsrvr.com (Authenticated sender: spike-AT-ticketevolution.com) with ESMTPSA id 4290034899B for ; Thu, 27 Sep 2012 14:24:37 -0400 (EDT) From: Spike Grobstein Mime-Version: 1.0 (Apple Message framework v1280) Content-Type: multipart/alternative; boundary="Apple-Mail=_1AF854E4-9B1E-4EE0-BC54-D865D9234FF4" Subject: Re: error during pg_dump Date: Thu, 27 Sep 2012 14:24:36 -0400 In-Reply-To: <0BDD66D6-9EF2-4E72-BDF8-93D10FA17376@ticketevolution.com> To: pgsql-admin@postgresql.org References: <0BDD66D6-9EF2-4E72-BDF8-93D10FA17376@ticketevolution.com> Message-Id: X-Mailer: Apple Mail (2.1280) X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201209/160 X-Sequence-Number: 40101 --Apple-Mail=_1AF854E4-9B1E-4EE0-BC54-D865D9234FF4 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii 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, >=20 > I'm running into an issue with our backup process using pg_dump on our = replica that I just noticed. >=20 > When running pg_dump, I get the following error: >=20 > 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 >=20 > This occurs while dumping the contents of tables on the 57th table (we = have 110 tables), so about halfway through. >=20 > I'm using the following command when dumping: >=20 > pg_dump -v -ESQL_ASCII -Upostgres -Fc -fd_1 $MY_DATABASE >=20 > 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). >=20 > 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. >=20 > I stopped and started postgres on the replica, and it stops and starts = without errors or warnings. >=20 > I then stopped postgres on the replica, renamed the data directory to = data.old and followed the instructions on: >=20 > http://wiki.postgresql.org/wiki/Streaming_Replication >=20 > to re-configure streaming replication (using rsync). >=20 > 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. >=20 > Any ideas? >=20 > In the interim, our daily dumps are being moved to the master so we = have backups. >=20 > thanks! >=20 >=20 >=20 > ...spike > Spike Grobstein > Ticket Evolution --Apple-Mail=_1AF854E4-9B1E-4EE0-BC54-D865D9234FF4 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; 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:


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=_1AF854E4-9B1E-4EE0-BC54-D865D9234FF4--