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:
/messages/by-id/DE2DE764-307D-4A23-A9A9-6608AC0977CB@ticketevolution.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.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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:
/messages/by-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 Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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:
/messages/by-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/
Attachments:
pg_dump_unlogged.patchapplication/octet-stream; name=pg_dump_unlogged.patchDownload
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 6295b5b..0a64f21 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -606,6 +606,24 @@ main(int argc, char **argv)
no_security_labels = 1;
/*
+ * When running against 9.0 or later, check if we are in recovery mode,
+ * which means we are on a hot standby.
+ */
+ if (fout->remoteVersion >= 90000)
+ {
+ PGresult *res = ExecuteSqlQueryForSingleRow(fout, "SELECT pg_is_in_recovery()");
+ if (strcmp(PQgetvalue(res, 0, 0), "t") == 0)
+ {
+ /*
+ * On hot standby slaves, never try to dump unlogged table data,
+ * since it will just throw an error.
+ */
+ no_unlogged_table_data = true;
+ }
+ PQclear(res);
+ }
+
+ /*
* Start transaction-snapshot mode transaction to dump consistent data.
*/
ExecuteSqlStatement(fout, "BEGIN");
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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 Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs