A report from a pgbouncer user revealed that running pg_dump -C/--create does not work through a connection proxy if the virtual database name on the proxy does not match the real database name on the database server. That's because pg_dump looks up the database to be dumped using the information from PQdb(). It should be using current_database() instead. (The code was quite likely written before current_database() was available (PG 7.3)).
See attached patch. There are a few other uses of PQdb() in pg_dump, but I think those are OK because they relate to connection information. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From eaccc63b2f9943633f957f80d67acf54ad70098d Mon Sep 17 00:00:00 2001 From: Peter Eisentraut <pete...@gmx.net> Date: Tue, 10 Apr 2018 12:32:05 -0400 Subject: [PATCH] pg_dump: Use current_database() instead of PQdb() For querying pg_database about information about the database being dumped, look up by using current_database() instead of the value obtained from PQdb(). When using a connection proxy, the value from PQdb() might not be the real name of the database. --- src/bin/pg_dump/pg_dump.c | 52 +++++++++++++++++---------------------- 1 file changed, 22 insertions(+), 30 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index d066f4f00b..52698cb3b6 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -251,7 +251,7 @@ static char *convertRegProcReference(Archive *fout, const char *proc); static char *getFormattedOperatorName(Archive *fout, const char *oproid); static char *convertTSFunction(Archive *fout, Oid funcOid); -static Oid findLastBuiltinOid_V71(Archive *fout, const char *); +static Oid findLastBuiltinOid_V71(Archive *fout); static char *getFormattedTypeName(Archive *fout, Oid oid, OidOptions opts); static void getBlobs(Archive *fout); static void dumpBlob(Archive *fout, BlobInfo *binfo); @@ -735,8 +735,7 @@ main(int argc, char **argv) * With 8.1 and above, we can just use FirstNormalObjectId - 1. */ if (fout->remoteVersion < 80100) - g_last_builtin_oid = findLastBuiltinOid_V71(fout, - PQdb(GetConnection(fout))); + g_last_builtin_oid = findLastBuiltinOid_V71(fout); else g_last_builtin_oid = FirstNormalObjectId - 1; @@ -2538,6 +2537,7 @@ dumpDatabase(Archive *fout) PGresult *res; int i_tableoid, i_oid, + i_datname, i_dba, i_encoding, i_collate, @@ -2565,16 +2565,13 @@ dumpDatabase(Archive *fout) minmxid; char *qdatname; - datname = PQdb(conn); - qdatname = pg_strdup(fmtId(datname)); - if (g_verbose) write_msg(NULL, "saving database definition\n"); /* Fetch the database-level properties for this database */ if (fout->remoteVersion >= 90600) { - appendPQExpBuffer(dbQry, "SELECT tableoid, oid, " + appendPQExpBuffer(dbQry, "SELECT tableoid, oid, datname, " "(%s datdba) AS dba, " "pg_encoding_to_char(encoding) AS encoding, " "datcollate, datctype, datfrozenxid, datminmxid, " @@ -2591,13 +2588,12 @@ dumpDatabase(Archive *fout) "shobj_description(oid, 'pg_database') AS description " "FROM pg_database " - "WHERE datname = ", + "WHERE datname = current_database()", username_subquery); - appendStringLiteralAH(dbQry, datname, fout); } else if (fout->remoteVersion >= 90300) { - appendPQExpBuffer(dbQry, "SELECT tableoid, oid, " + appendPQExpBuffer(dbQry, "SELECT tableoid, oid, datname, " "(%s datdba) AS dba, " "pg_encoding_to_char(encoding) AS encoding, " "datcollate, datctype, datfrozenxid, datminmxid, " @@ -2606,13 +2602,12 @@ dumpDatabase(Archive *fout) "shobj_description(oid, 'pg_database') AS description " "FROM pg_database " - "WHERE datname = ", + "WHERE datname = current_database()", username_subquery); - appendStringLiteralAH(dbQry, datname, fout); } else if (fout->remoteVersion >= 80400) { - appendPQExpBuffer(dbQry, "SELECT tableoid, oid, " + appendPQExpBuffer(dbQry, "SELECT tableoid, oid, datname, " "(%s datdba) AS dba, " "pg_encoding_to_char(encoding) AS encoding, " "datcollate, datctype, datfrozenxid, 0 AS datminmxid, " @@ -2621,13 +2616,12 @@ dumpDatabase(Archive *fout) "shobj_description(oid, 'pg_database') AS description " "FROM pg_database " - "WHERE datname = ", + "WHERE datname = current_database()", username_subquery); - appendStringLiteralAH(dbQry, datname, fout); } else if (fout->remoteVersion >= 80200) { - appendPQExpBuffer(dbQry, "SELECT tableoid, oid, " + appendPQExpBuffer(dbQry, "SELECT tableoid, oid, datname, " "(%s datdba) AS dba, " "pg_encoding_to_char(encoding) AS encoding, " "NULL AS datcollate, NULL AS datctype, datfrozenxid, 0 AS datminmxid, " @@ -2636,13 +2630,12 @@ dumpDatabase(Archive *fout) "shobj_description(oid, 'pg_database') AS description " "FROM pg_database " - "WHERE datname = ", + "WHERE datname = current_database()", username_subquery); - appendStringLiteralAH(dbQry, datname, fout); } else { - appendPQExpBuffer(dbQry, "SELECT tableoid, oid, " + appendPQExpBuffer(dbQry, "SELECT tableoid, oid, datname, " "(%s datdba) AS dba, " "pg_encoding_to_char(encoding) AS encoding, " "NULL AS datcollate, NULL AS datctype, datfrozenxid, 0 AS datminmxid, " @@ -2650,15 +2643,15 @@ dumpDatabase(Archive *fout) "-1 as datconnlimit, " "(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace " "FROM pg_database " - "WHERE datname = ", + "WHERE datname = current_database()", username_subquery); - appendStringLiteralAH(dbQry, datname, fout); } res = ExecuteSqlQueryForSingleRow(fout, dbQry->data); i_tableoid = PQfnumber(res, "tableoid"); i_oid = PQfnumber(res, "oid"); + i_datname = PQfnumber(res, "datname"); i_dba = PQfnumber(res, "dba"); i_encoding = PQfnumber(res, "encoding"); i_collate = PQfnumber(res, "datcollate"); @@ -2673,6 +2666,7 @@ dumpDatabase(Archive *fout) dbCatId.tableoid = atooid(PQgetvalue(res, 0, i_tableoid)); dbCatId.oid = atooid(PQgetvalue(res, 0, i_oid)); + datname = PQgetvalue(res, 0, i_datname); dba = PQgetvalue(res, 0, i_dba); encoding = PQgetvalue(res, 0, i_encoding); collate = PQgetvalue(res, 0, i_collate); @@ -2685,6 +2679,8 @@ dumpDatabase(Archive *fout) datconnlimit = PQgetvalue(res, 0, i_datconnlimit); tablespace = PQgetvalue(res, 0, i_tablespace); + qdatname = pg_strdup(fmtId(datname)); + /* * Prepare the CREATE DATABASE command. We must specify encoding, locale, * and tablespace since those can't be altered later. Other DB properties @@ -16540,23 +16536,19 @@ dumpTableConstraintComment(Archive *fout, ConstraintInfo *coninfo) * find the last built in oid * * For 7.1 through 8.0, we do this by retrieving datlastsysoid from the - * pg_database entry for the current database. + * pg_database entry for the current database. (Note: current_database() + * requires 7.3; pg_dump requires 8.0 now.) */ static Oid -findLastBuiltinOid_V71(Archive *fout, const char *dbname) +findLastBuiltinOid_V71(Archive *fout) { PGresult *res; Oid last_oid; - PQExpBuffer query = createPQExpBuffer(); - resetPQExpBuffer(query); - appendPQExpBufferStr(query, "SELECT datlastsysoid from pg_database where datname = "); - appendStringLiteralAH(query, dbname, fout); - - res = ExecuteSqlQueryForSingleRow(fout, query->data); + res = ExecuteSqlQueryForSingleRow(fout, + "SELECT datlastsysoid FROM pg_database WHERE datname = current_database()"); last_oid = atooid(PQgetvalue(res, 0, PQfnumber(res, "datlastsysoid"))); PQclear(res); - destroyPQExpBuffer(query); return last_oid; } base-commit: 4f813c7203e0c08395833b2bd75ac9ccf63c6264 -- 2.17.0