Thread: BUG #7809: Running pg_dump on slave w/ streaming replication fails if there are unlogged tables

The following bug has been logged on the website:

Bug reference:      7809
Logged by:          Joe Van Dyk
Email address:      joe@tanga.com
PostgreSQL version: 9.2.2
Operating system:   Ubuntu
Description:        =


Running pg_dump on a streaming replication slave with a database that has
unlogged_tables will fail unless you provide the "--no-unlogged-table-data"
option with the following (scary) error:

pg_dump: Dumping the contents of table "tracking_import_data" failed:
PQgetResult() failed.
pg_dump: Error message from server: ERROR:  could not open file
"base/16388/190326": No such file or directory
pg_dump: The command was: COPY public.tracking_import_data (uuid,
tracking_number) TO stdout;

(this guy  encountered the error as well:
http://www.postgresql.org/message-id/DE2DE764-307D-4A23-A9A9-6608AC0977CB@t=
icketevolution.com
)

Could running pg_dump against a slave always use the
"--no-unlogged-table-data" option?

In my case, I had automatic scripts that run pg_dump (without the unlogged
option) against the slave, and they failed right after I added an unlogged
table for the first time.

On Tue, Jan 15, 2013 at 12:13 AM,  <joe@tanga.com> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      7809
> Logged by:          Joe Van Dyk
> Email address:      joe@tanga.com
> PostgreSQL version: 9.2.2
> Operating system:   Ubuntu
> Description:
>
> Running pg_dump on a streaming replication slave with a database that has
> unlogged_tables will fail unless you provide the "--no-unlogged-table-data"
> option with the following (scary) error:
>
> pg_dump: Dumping the contents of table "tracking_import_data" failed:
> PQgetResult() failed.
> pg_dump: Error message from server: ERROR:  could not open file
> "base/16388/190326": No such file or directory
> pg_dump: The command was: COPY public.tracking_import_data (uuid,
> tracking_number) TO stdout;
>
> (this guy  encountered the error as well:
> http://www.postgresql.org/message-id/DE2DE764-307D-4A23-A9A9-6608AC0977CB@ticketevolution.com
> )
>
> Could running pg_dump against a slave always use the
> "--no-unlogged-table-data" option?

That sounds like a pretty reasonable idea, I think. Should be easy
enough to figure out at an early stage, too.

That said, it wouldn't hurt if we could make that error a little less
scary. Instead of saying "could not open file", could we find a way to
say "this is an unlogged table on a slave, it's not going to work"?

We can fix pg_dump the easy way, but what about custom tools...


--Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/

On Tue, Jan 15, 2013 at 10:35 PM, Magnus Hagander <magnus@hagander.net> wrote:
> On Tue, Jan 15, 2013 at 12:13 AM,  <joe@tanga.com> wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference:      7809
>> Logged by:          Joe Van Dyk
>> Email address:      joe@tanga.com
>> PostgreSQL version: 9.2.2
>> Operating system:   Ubuntu
>> Description:
>>
>> Running pg_dump on a streaming replication slave with a database that has
>> unlogged_tables will fail unless you provide the "--no-unlogged-table-data"
>> option with the following (scary) error:
>>
>> pg_dump: Dumping the contents of table "tracking_import_data" failed:
>> PQgetResult() failed.
>> pg_dump: Error message from server: ERROR:  could not open file
>> "base/16388/190326": No such file or directory
>> pg_dump: The command was: COPY public.tracking_import_data (uuid,
>> tracking_number) TO stdout;
>>
>> (this guy  encountered the error as well:
>> http://www.postgresql.org/message-id/DE2DE764-307D-4A23-A9A9-6608AC0977CB@ticketevolution.com
>> )
>>
>> Could running pg_dump against a slave always use the
>> "--no-unlogged-table-data" option?
>
> That sounds like a pretty reasonable idea, I think. Should be easy
> enough to figure out at an early stage, too.
>
> That said, it wouldn't hurt if we could make that error a little less
> scary. Instead of saying "could not open file", could we find a way to
> say "this is an unlogged table on a slave, it's not going to work"?
>
> We can fix pg_dump the easy way, but what about custom tools...

Here's a patch to fix this in pg_dump. I intentionally made the check
for pg_is_in_recovery() on everything since 9.0, since we might well
end up with other things we want to do different in hot standby (in
theory. but not likely). And since we're not going to end up with any
unlogged tables on 9.0 anyway, it doesn't hurt to turn them off.

I'm thinking we can consider this a bug and it should be backpatched
(to 9.1 where we added unlogged tables). Comments?


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus@hagander.net> writes:
> +        PGresult *res = ExecuteSqlQueryForSingleRow(fout, "SELECT pg_is_in_recovery()");

That function call needs to be schema-qualified for security.
        regards, tom lane

On Sun, Jan 20, 2013 at 4:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> +             PGresult *res = ExecuteSqlQueryForSingleRow(fout, "SELECT pg_is_in_recovery()");
>
> That function call needs to be schema-qualified for security.

Ha! I wonder if I can set up an autoresponder to *myself* with that
review whenever I commit to pgdump :) Thanks!

--Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/

On Sun, Jan 20, 2013 at 4:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> +             PGresult *res = ExecuteSqlQueryForSingleRow(fout, "SELECT pg_is_in_recovery()");
>
> That function call needs to be schema-qualified for security.

Applied and backpatched, with that fix and a sentence in the
documentation added.

--Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/