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
