Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1THJ2w-0003pV-TF for pgsql-admin@postgresql.org; Thu, 27 Sep 2012 18:42:51 +0000 Received: from smtp138.dfw.emailsrvr.com ([67.192.241.138]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1THJ2u-0004OH-TG for pgsql-admin@postgresql.org; Thu, 27 Sep 2012 18:42:49 +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 213433489B1 for ; Thu, 27 Sep 2012 14:42:47 -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 1E827348904 for ; Thu, 27 Sep 2012 14:42:47 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp30.relay.dfw1a.emailsrvr.com (Authenticated sender: spike-AT-ticketevolution.com) with ESMTPSA id D5410348914 for ; Thu, 27 Sep 2012 14:42:46 -0400 (EDT) From: Spike Grobstein Mime-Version: 1.0 (Apple Message framework v1280) Content-Type: multipart/alternative; boundary="Apple-Mail=_62B3729A-25D1-4F4F-A1A4-95AB9D178FD5" Subject: Re: error during pg_dump Date: Thu, 27 Sep 2012 14:42:45 -0400 In-Reply-To: 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/161 X-Sequence-Number: 40102 --Apple-Mail=_62B3729A-25D1-4F4F-A1A4-95AB9D178FD5 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii SOLVED. So it turns out that you can't access unlogged tables on the replica: exchange_prod=3D# 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. >=20 > Thanks! >=20 >=20 > ...spike >=20 >=20 > On Sep 27, 2012, at 1:29 PM, Spike Grobstein wrote: >=20 >> 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 >=20 --Apple-Mail=_62B3729A-25D1-4F4F-A1A4-95AB9D178FD5 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii = exchange_prod=3D# 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:


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


<= /div>= --Apple-Mail=_62B3729A-25D1-4F4F-A1A4-95AB9D178FD5--