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

On Tue, Jan 15, 2013 at 10:35 PM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> On Tue, Jan 15, 2013 at 12:13 AM, <joe(at)tanga(dot)com> wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference: 7809
>> Logged by: Joe Van Dyk
>> Email address: joe(at)tanga(dot)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/