/*
* Build ALTER DEFAULT PRIVILEGES command(s) for single pg_default_acl entry.
*
* type: the object type (TABLES, FUNCTIONS, etc)
* nspname: schema name, or NULL for global default privileges
* acls: the ACL string fetched from the database
* owner: username of privileges owner (will be passed through fmtId)
* remoteVersion: version of database
*
* Returns TRUE if okay, FALSE if could not parse the acl string.
* The resulting commands (if any) are appended to the contents of 'sql'.
*/
bool
buildDefaultACLCommands(const char *type, const char *nspname,
const char *acls, const char *owner,
int remoteVersion,
PQExpBuffer sql)
{
bool result;
PQExpBuffer prefix;
prefix = createPQExpBuffer();
/*
* We incorporate the target role directly into the command, rather than
* playing around with SET ROLE or anything like that. This is so that a
* permissions error leads to nothing happening, rather than changing
* default privileges for the wrong user.
*/
appendPQExpBuffer(prefix, "ALTER DEFAULT PRIVILEGES FOR ROLE %s ",
fmtId(owner));
if (nspname)
appendPQExpBuffer(prefix, "IN SCHEMA %s ", fmtId(nspname));
result = buildACLCommands("", NULL,
type, acls, owner,
prefix->data, remoteVersion,
sql);
destroyPQExpBuffer(prefix);
return result;
}
/*
* Build ALTER DEFAULT PRIVILEGES command(s) for single pg_default_acl entry.
*
* type: the object type (TABLES, FUNCTIONS, etc)
* nspname: schema name, or NULL for global default privileges
* acls: the ACL string fetched from the database
* owner: username of privileges owner (will be passed through fmtId)
* remoteVersion: version of database
*
* Returns TRUE if okay, FALSE if could not parse the acl string.
* The resulting commands (if any) are appended to the contents of 'sql'.
*/
bool
buildDefaultACLCommands(const char *type, const char *nspname,
const char *acls, const char *racls,
const char *initacls, const char *initracls,
const char *owner,
int remoteVersion,
PQExpBuffer sql)
{
PQExpBuffer prefix;
prefix = createPQExpBuffer();
/*
* We incorporate the target role directly into the command, rather than
* playing around with SET ROLE or anything like that. This is so that a
* permissions error leads to nothing happening, rather than changing
* default privileges for the wrong user.
*/
appendPQExpBuffer(prefix, "ALTER DEFAULT PRIVILEGES FOR ROLE %s ",
fmtId(owner));
if (nspname)
appendPQExpBuffer(prefix, "IN SCHEMA %s ", fmtId(nspname));
if (strlen(initacls) != 0 || strlen(initracls) != 0)
{
appendPQExpBuffer(sql, "SELECT pg_catalog.binary_upgrade_set_record_init_privs(true);\n");
if (!buildACLCommands("", NULL, type, initacls, initracls, owner,
prefix->data, remoteVersion, sql))
return false;
appendPQExpBuffer(sql, "SELECT pg_catalog.binary_upgrade_set_record_init_privs(false);\n");
}
if (!buildACLCommands("", NULL, type, acls, racls, owner,
prefix->data, remoteVersion, sql))
return false;
destroyPQExpBuffer(prefix);
return true;
}
/*
* Dump commands to create each database.
*
* To minimize the number of reconnections (and possibly ensuing
* password prompts) required by the output script, we emit all CREATE
* DATABASE commands during the initial phase of the script, and then
* run pg_dump for each database to dump the contents of that
* database. We skip databases marked not datallowconn, since we'd be
* unable to connect to them anyway (and besides, we don't want to
* dump template0).
*/
static void
dumpCreateDB(PGconn *conn)
{
PQExpBuffer buf = createPQExpBuffer();
PGresult *res;
int i;
printf("--\n-- Database creation\n--\n\n");
if (server_version >= 80100)
res = executeQuery(conn,
"SELECT datname, "
"coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
"datistemplate, datacl, datconnlimit, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
"FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
"WHERE datallowconn ORDER BY 1");
else if (server_version >= 80000)
res = executeQuery(conn,
"SELECT datname, "
"coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
"datistemplate, datacl, -1 as datconnlimit, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
"FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
"WHERE datallowconn ORDER BY 1");
else if (server_version >= 70300)
res = executeQuery(conn,
"SELECT datname, "
"coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
"datistemplate, datacl, -1 as datconnlimit, "
"'pg_default' AS dattablespace "
"FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
"WHERE datallowconn ORDER BY 1");
else if (server_version >= 70100)
res = executeQuery(conn,
"SELECT datname, "
"coalesce("
"(select usename from pg_shadow where usesysid=datdba), "
"(select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
"datistemplate, '' as datacl, -1 as datconnlimit, "
"'pg_default' AS dattablespace "
"FROM pg_database d "
"WHERE datallowconn ORDER BY 1");
else
{
/*
* Note: 7.0 fails to cope with sub-select in COALESCE, so just deal
* with getting a NULL by not printing any OWNER clause.
*/
res = executeQuery(conn,
"SELECT datname, "
"(select usename from pg_shadow where usesysid=datdba), "
"pg_encoding_to_char(d.encoding), "
"'f' as datistemplate, "
"'' as datacl, -1 as datconnlimit, "
"'pg_default' AS dattablespace "
"FROM pg_database d "
"ORDER BY 1");
}
for (i = 0; i < PQntuples(res); i++)
{
char *dbname = PQgetvalue(res, i, 0);
char *dbowner = PQgetvalue(res, i, 1);
char *dbencoding = PQgetvalue(res, i, 2);
char *dbistemplate = PQgetvalue(res, i, 3);
char *dbacl = PQgetvalue(res, i, 4);
char *dbconnlimit = PQgetvalue(res, i, 5);
char *dbtablespace = PQgetvalue(res, i, 6);
char *fdbname;
fdbname = strdup(fmtId(dbname));
resetPQExpBuffer(buf);
/*
* Skip the CREATE DATABASE commands for "template1" and "postgres",
* since they are presumably already there in the destination cluster.
* We do want to emit their ACLs and config options if any, however.
*/
if (strcmp(dbname, "template1") != 0 &&
strcmp(dbname, "postgres") != 0)
{
if (output_clean)
appendPQExpBuffer(buf, "DROP DATABASE %s;\n", fdbname);
appendPQExpBuffer(buf, "CREATE DATABASE %s", fdbname);
appendPQExpBuffer(buf, " WITH TEMPLATE = template0");
if (strlen(dbowner) != 0)
appendPQExpBuffer(buf, " OWNER = %s", fmtId(dbowner));
appendPQExpBuffer(buf, " ENCODING = ");
appendStringLiteral(buf, dbencoding, true);
/* Output tablespace if it isn't default */
if (strcmp(dbtablespace, "pg_default") != 0)
appendPQExpBuffer(buf, " TABLESPACE = %s",
fmtId(dbtablespace));
if (strcmp(dbconnlimit, "-1") != 0)
appendPQExpBuffer(buf, " CONNECTION LIMIT = %s",
dbconnlimit);
appendPQExpBuffer(buf, ";\n");
if (strcmp(dbistemplate, "t") == 0)
{
appendPQExpBuffer(buf, "UPDATE pg_database SET datistemplate = 't' WHERE datname = ");
appendStringLiteral(buf, dbname, true);
appendPQExpBuffer(buf, ";\n");
}
}
if (!skip_acls &&
!buildACLCommands(fdbname, "DATABASE", dbacl, dbowner,
server_version, buf))
{
fprintf(stderr, _("%s: could not parse ACL list (%s) for database \"%s\"\n"),
progname, dbacl, fdbname);
PQfinish(conn);
exit(1);
}
printf("%s", buf->data);
if (server_version >= 70300)
dumpDatabaseConfig(conn, dbname);
free(fdbname);
}
PQclear(res);
destroyPQExpBuffer(buf);
printf("\n\n");
}
/*
* Dump tablespaces.
*/
static void
dumpTablespaces(PGconn *conn)
{
PGresult *res;
int i;
/*
* Get all tablespaces except built-in ones (which we assume are named
* pg_xxx)
*/
res = executeQuery(conn, "SELECT spcname, "
"pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
"spclocation, spcacl "
"FROM pg_catalog.pg_tablespace "
"WHERE spcname !~ '^pg_' "
"ORDER BY 1");
if (PQntuples(res) > 0)
printf("--\n-- Tablespaces\n--\n\n");
for (i = 0; i < PQntuples(res); i++)
{
PQExpBuffer buf = createPQExpBuffer();
char *spcname = PQgetvalue(res, i, 0);
char *spcowner = PQgetvalue(res, i, 1);
char *spclocation = PQgetvalue(res, i, 2);
char *spcacl = PQgetvalue(res, i, 3);
char *fspcname;
/* needed for buildACLCommands() */
fspcname = strdup(fmtId(spcname));
if (output_clean)
appendPQExpBuffer(buf, "DROP TABLESPACE %s;\n", fspcname);
appendPQExpBuffer(buf, "CREATE TABLESPACE %s", fspcname);
appendPQExpBuffer(buf, " OWNER %s", fmtId(spcowner));
appendPQExpBuffer(buf, " LOCATION ");
appendStringLiteral(buf, spclocation, true);
appendPQExpBuffer(buf, ";\n");
if (!skip_acls &&
!buildACLCommands(fspcname, "TABLESPACE", spcacl, spcowner,
server_version, buf))
{
fprintf(stderr, _("%s: could not parse ACL list (%s) for tablespace \"%s\"\n"),
progname, spcacl, fspcname);
PQfinish(conn);
exit(1);
}
printf("%s", buf->data);
free(fspcname);
destroyPQExpBuffer(buf);
}
PQclear(res);
printf("\n\n");
}
/*
* Dump commands to create each database.
*
* To minimize the number of reconnections (and possibly ensuing
* password prompts) required by the output script, we emit all CREATE
* DATABASE commands during the initial phase of the script, and then
* run pg_dump for each database to dump the contents of that
* database. We skip databases marked not datallowconn, since we'd be
* unable to connect to them anyway (and besides, we don't want to
* dump template0).
*/
static void
dumpCreateDB(PGconn *conn)
{
PQExpBuffer buf = createPQExpBuffer();
PGresult *res;
int i;
fprintf(OPF, "--\n-- Database creation\n--\n\n");
res = executeQuery(conn,
"SELECT datname, "
"coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
"datistemplate, datacl, datconnlimit, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
"FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
"WHERE datallowconn ORDER BY 1");
for (i = 0; i < PQntuples(res); i++)
{
char *dbname = PQgetvalue(res, i, 0);
char *dbowner = PQgetvalue(res, i, 1);
char *dbencoding = PQgetvalue(res, i, 2);
char *dbistemplate = PQgetvalue(res, i, 3);
char *dbacl = PQgetvalue(res, i, 4);
char *dbconnlimit = PQgetvalue(res, i, 5);
char *dbtablespace = PQgetvalue(res, i, 6);
char *fdbname;
fdbname = strdup(fmtId(dbname));
resetPQExpBuffer(buf);
/*
* Skip the CREATE DATABASE commands for "template1" and "postgres",
* since they are presumably already there in the destination cluster.
* We do want to emit their ACLs and config options if any, however.
*/
if (strcmp(dbname, "template1") != 0 &&
strcmp(dbname, "postgres") != 0)
{
if (output_clean)
appendPQExpBuffer(buf, "DROP DATABASE %s;\n", fdbname);
appendPQExpBuffer(buf, "CREATE DATABASE %s", fdbname);
appendPQExpBuffer(buf, " WITH TEMPLATE = template0");
if (strlen(dbowner) != 0)
appendPQExpBuffer(buf, " OWNER = %s", fmtId(dbowner));
appendPQExpBuffer(buf, " ENCODING = ");
appendStringLiteralConn(buf, dbencoding, conn);
/*
* Output tablespace if it isn't the default. For default, it
* uses the default from the template database. If tablespace is
* specified and tablespace creation failed earlier, (e.g. no such
* directory), the database creation will fail too. One solution
* would be to use 'SET default_tablespace' like we do in pg_dump
* for setting non-default database locations.
*/
if (strcmp(dbtablespace, "pg_default") != 0)
appendPQExpBuffer(buf, " TABLESPACE = %s",
fmtId(dbtablespace));
if (strcmp(dbconnlimit, "-1") != 0)
appendPQExpBuffer(buf, " CONNECTION LIMIT = %s",
dbconnlimit);
appendPQExpBuffer(buf, ";\n");
if (strcmp(dbistemplate, "t") == 0)
{
appendPQExpBuffer(buf, "UPDATE pg_database SET datistemplate = 't' WHERE datname = ");
appendStringLiteralConn(buf, dbname, conn);
appendPQExpBuffer(buf, ";\n");
}
}
if (!skip_acls &&
!buildACLCommands(fdbname, "DATABASE", dbacl, dbowner,
server_version, buf))
{
fprintf(stderr, _("%s: could not parse ACL list (%s) for database \"%s\"\n"),
progname, dbacl, fdbname);
PQfinish(conn);
exit(1);
}
fprintf(OPF, "%s", buf->data);
dumpDatabaseConfig(conn, dbname);
free(fdbname);
}
PQclear(res);
destroyPQExpBuffer(buf);
fprintf(OPF, "\n\n");
}
/*
* Dump tablespaces.
*/
static void
dumpTablespaces(PGconn *conn)
{
PGresult *res;
int i;
/*
* Get all tablespaces execpt built-in ones (named pg_xxx)
*
* [FIXME] the queries need to be slightly different if the backend isn't
* Greenplum, and the dump format should vary depending on if the dump is
* --gp-syntax or --no-gp-syntax.
*/
if (server_version < 80214)
{
/* Filespaces were introduced in GP 4.0 (server_version 8.2.14) */
return;
}
else
{
res = executeQuery(conn, "SELECT spcname, "
"pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
"fsname, spcacl, "
"pg_catalog.shobj_description(t.oid, 'pg_tablespace') "
"FROM pg_catalog.pg_tablespace t, "
"pg_catalog.pg_filespace fs "
"WHERE t.spcfsoid = fs.oid AND spcname !~ '^pg_' "
"ORDER BY 1");
}
if (PQntuples(res) > 0)
fprintf(OPF, "--\n-- Tablespaces\n--\n\n");
for (i = 0; i < PQntuples(res); i++)
{
PQExpBuffer buf = createPQExpBuffer();
char *spcname = PQgetvalue(res, i, 0);
char *spcowner = PQgetvalue(res, i, 1);
char *fsname = PQgetvalue(res, i, 2);
char *spcacl = PQgetvalue(res, i, 3);
char *spccomment = PQgetvalue(res, i, 4);
/* needed for buildACLCommands() */
spcname = strdup(fmtId(spcname));
if (output_clean)
appendPQExpBuffer(buf, "DROP TABLESPACE %s;\n", spcname);
appendPQExpBuffer(buf, "CREATE TABLESPACE %s", spcname);
appendPQExpBuffer(buf, " OWNER %s", fmtId(spcowner));
appendPQExpBuffer(buf, " FILESPACE %s;\n", fmtId(fsname));
/* Build Acls */
if (!skip_acls &&
!buildACLCommands(spcname, "TABLESPACE", spcacl, spcowner,
server_version, buf))
{
fprintf(stderr, _("%s: could not parse ACL list (%s) for tablespace \"%s\"\n"),
progname, spcacl, spcname);
PQfinish(conn);
exit(1);
}
/* Set comments */
if (spccomment && strlen(spccomment))
{
appendPQExpBuffer(buf, "COMMENT ON TABLESPACE %s IS ", spcname);
appendStringLiteralConn(buf, spccomment, conn);
appendPQExpBuffer(buf, ";\n");
}
fprintf(OPF, "%s", buf->data);
free(spcname);
destroyPQExpBuffer(buf);
}
PQclear(res);
fprintf(OPF, "\n\n");
}
/*
* Dump tablespaces.
*/
static void
dumpTablespaces(PGconn *conn)
{
PGresult *res;
int i;
/*
* Get all tablespaces except built-in ones (which we assume are named
* pg_xxx)
*/
if (server_version >= 90000)
res = executeQuery(conn, "SELECT spcname, "
"pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
"spclocation, spcacl, "
"array_to_string(spcoptions, ', '),"
"pg_catalog.shobj_description(oid, 'pg_tablespace') "
"FROM pg_catalog.pg_tablespace "
"WHERE spcname !~ '^pg_' "
"ORDER BY 1");
else if (server_version >= 80200)
res = executeQuery(conn, "SELECT spcname, "
"pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
"spclocation, spcacl, null, "
"pg_catalog.shobj_description(oid, 'pg_tablespace') "
"FROM pg_catalog.pg_tablespace "
"WHERE spcname !~ '^pg_' "
"ORDER BY 1");
else
res = executeQuery(conn, "SELECT spcname, "
"pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
"spclocation, spcacl, "
"null, null "
"FROM pg_catalog.pg_tablespace "
"WHERE spcname !~ '^pg_' "
"ORDER BY 1");
if (PQntuples(res) > 0)
fprintf(OPF, "--\n-- Tablespaces\n--\n\n");
for (i = 0; i < PQntuples(res); i++)
{
PQExpBuffer buf = createPQExpBuffer();
char *spcname = PQgetvalue(res, i, 0);
char *spcowner = PQgetvalue(res, i, 1);
char *spclocation = PQgetvalue(res, i, 2);
char *spcacl = PQgetvalue(res, i, 3);
char *spcoptions = PQgetvalue(res, i, 4);
char *spccomment = PQgetvalue(res, i, 5);
char *fspcname;
/* needed for buildACLCommands() */
fspcname = strdup(fmtId(spcname));
appendPQExpBuffer(buf, "CREATE TABLESPACE %s", fspcname);
appendPQExpBuffer(buf, " OWNER %s", fmtId(spcowner));
appendPQExpBuffer(buf, " LOCATION ");
appendStringLiteralConn(buf, spclocation, conn);
appendPQExpBuffer(buf, ";\n");
if (spcoptions && spcoptions[0] != '\0')
appendPQExpBuffer(buf, "ALTER TABLESPACE %s SET (%s);\n",
fspcname, spcoptions);
if (!skip_acls &&
!buildACLCommands(fspcname, NULL, "TABLESPACE", spcacl, spcowner,
"", server_version, buf))
{
fprintf(stderr, _("%s: could not parse ACL list (%s) for tablespace \"%s\"\n"),
progname, spcacl, fspcname);
PQfinish(conn);
exit(1);
}
if (spccomment && strlen(spccomment))
{
appendPQExpBuffer(buf, "COMMENT ON TABLESPACE %s IS ", fspcname);
appendStringLiteralConn(buf, spccomment, conn);
appendPQExpBuffer(buf, ";\n");
}
fprintf(OPF, "%s", buf->data);
free(fspcname);
destroyPQExpBuffer(buf);
}
PQclear(res);
fprintf(OPF, "\n\n");
}
/*
* Dump commands to create each database.
*
* To minimize the number of reconnections (and possibly ensuing
* password prompts) required by the output script, we emit all CREATE
* DATABASE commands during the initial phase of the script, and then
* run pg_dump for each database to dump the contents of that
* database. We skip databases marked not datallowconn, since we'd be
* unable to connect to them anyway (and besides, we don't want to
* dump template0).
*/
static void
dumpCreateDB(PGconn *conn)
{
PQExpBuffer buf = createPQExpBuffer();
char *default_encoding = NULL;
char *default_collate = NULL;
char *default_ctype = NULL;
PGresult *res;
int i;
fprintf(OPF, "--\n-- Database creation\n--\n\n");
/*
* First, get the installation's default encoding and locale information.
* We will dump encoding and locale specifications in the CREATE DATABASE
* commands for just those databases with values different from defaults.
*
* We consider template0's encoding and locale (or, pre-7.1, template1's)
* to define the installation default. Pre-8.4 installations do not have
* per-database locale settings; for them, every database must necessarily
* be using the installation default, so there's no need to do anything
* (which is good, since in very old versions there is no good way to find
* out what the installation locale is anyway...)
*/
if (server_version >= 80400)
res = executeQuery(conn,
"SELECT pg_encoding_to_char(encoding), "
"datcollate, datctype "
"FROM pg_database "
"WHERE datname = 'template0'");
else if (server_version >= 70100)
res = executeQuery(conn,
"SELECT pg_encoding_to_char(encoding), "
"null::text AS datcollate, null::text AS datctype "
"FROM pg_database "
"WHERE datname = 'template0'");
else
res = executeQuery(conn,
"SELECT pg_encoding_to_char(encoding), "
"null::text AS datcollate, null::text AS datctype "
"FROM pg_database "
"WHERE datname = 'template1'");
/* If for some reason the template DB isn't there, treat as unknown */
if (PQntuples(res) > 0)
{
if (!PQgetisnull(res, 0, 0))
default_encoding = strdup(PQgetvalue(res, 0, 0));
if (!PQgetisnull(res, 0, 1))
default_collate = strdup(PQgetvalue(res, 0, 1));
if (!PQgetisnull(res, 0, 2))
default_ctype = strdup(PQgetvalue(res, 0, 2));
}
PQclear(res);
/* Now collect all the information about databases to dump */
if (server_version >= 80400)
res = executeQuery(conn,
"SELECT datname, "
"coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
"datcollate, datctype, datfrozenxid, "
"datistemplate, datacl, datconnlimit, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
"FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
"WHERE datallowconn ORDER BY 1");
else if (server_version >= 80100)
res = executeQuery(conn,
"SELECT datname, "
"coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
"null::text AS datcollate, null::text AS datctype, datfrozenxid, "
"datistemplate, datacl, datconnlimit, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
"FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
"WHERE datallowconn ORDER BY 1");
else if (server_version >= 80000)
res = executeQuery(conn,
"SELECT datname, "
"coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
"null::text AS datcollate, null::text AS datctype, datfrozenxid, "
"datistemplate, datacl, -1 as datconnlimit, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
"FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
"WHERE datallowconn ORDER BY 1");
else if (server_version >= 70300)
res = executeQuery(conn,
"SELECT datname, "
"coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
"null::text AS datcollate, null::text AS datctype, datfrozenxid, "
"datistemplate, datacl, -1 as datconnlimit, "
"'pg_default' AS dattablespace "
"FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
"WHERE datallowconn ORDER BY 1");
else if (server_version >= 70100)
res = executeQuery(conn,
"SELECT datname, "
"coalesce("
"(select usename from pg_shadow where usesysid=datdba), "
"(select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
"null::text AS datcollate, null::text AS datctype, 0 AS datfrozenxid, "
"datistemplate, '' as datacl, -1 as datconnlimit, "
"'pg_default' AS dattablespace "
"FROM pg_database d "
"WHERE datallowconn ORDER BY 1");
else
{
/*
* Note: 7.0 fails to cope with sub-select in COALESCE, so just deal
* with getting a NULL by not printing any OWNER clause.
*/
res = executeQuery(conn,
"SELECT datname, "
"(select usename from pg_shadow where usesysid=datdba), "
"pg_encoding_to_char(d.encoding), "
"null::text AS datcollate, null::text AS datctype, 0 AS datfrozenxid, "
"'f' as datistemplate, "
"'' as datacl, -1 as datconnlimit, "
"'pg_default' AS dattablespace "
"FROM pg_database d "
"ORDER BY 1");
}
for (i = 0; i < PQntuples(res); i++)
{
char *dbname = PQgetvalue(res, i, 0);
char *dbowner = PQgetvalue(res, i, 1);
char *dbencoding = PQgetvalue(res, i, 2);
char *dbcollate = PQgetvalue(res, i, 3);
char *dbctype = PQgetvalue(res, i, 4);
uint32 dbfrozenxid = atooid(PQgetvalue(res, i, 5));
char *dbistemplate = PQgetvalue(res, i, 6);
char *dbacl = PQgetvalue(res, i, 7);
char *dbconnlimit = PQgetvalue(res, i, 8);
char *dbtablespace = PQgetvalue(res, i, 9);
char *fdbname;
fdbname = strdup(fmtId(dbname));
resetPQExpBuffer(buf);
/*
* Skip the CREATE DATABASE commands for "template1" and "postgres",
* since they are presumably already there in the destination cluster.
* We do want to emit their ACLs and config options if any, however.
*/
if (strcmp(dbname, "template1") != 0 &&
strcmp(dbname, "postgres") != 0)
{
appendPQExpBuffer(buf, "CREATE DATABASE %s", fdbname);
appendPQExpBuffer(buf, " WITH TEMPLATE = template0");
if (strlen(dbowner) != 0)
appendPQExpBuffer(buf, " OWNER = %s", fmtId(dbowner));
if (default_encoding && strcmp(dbencoding, default_encoding) != 0)
{
appendPQExpBuffer(buf, " ENCODING = ");
appendStringLiteralConn(buf, dbencoding, conn);
}
if (default_collate && strcmp(dbcollate, default_collate) != 0)
{
appendPQExpBuffer(buf, " LC_COLLATE = ");
appendStringLiteralConn(buf, dbcollate, conn);
}
if (default_ctype && strcmp(dbctype, default_ctype) != 0)
{
appendPQExpBuffer(buf, " LC_CTYPE = ");
appendStringLiteralConn(buf, dbctype, conn);
}
/*
* Output tablespace if it isn't the default. For default, it
* uses the default from the template database. If tablespace is
* specified and tablespace creation failed earlier, (e.g. no such
* directory), the database creation will fail too. One solution
* would be to use 'SET default_tablespace' like we do in pg_dump
* for setting non-default database locations.
*/
if (strcmp(dbtablespace, "pg_default") != 0 && !no_tablespaces)
appendPQExpBuffer(buf, " TABLESPACE = %s",
fmtId(dbtablespace));
if (strcmp(dbconnlimit, "-1") != 0)
appendPQExpBuffer(buf, " CONNECTION LIMIT = %s",
dbconnlimit);
appendPQExpBuffer(buf, ";\n");
if (strcmp(dbistemplate, "t") == 0)
{
appendPQExpBuffer(buf, "UPDATE pg_catalog.pg_database SET datistemplate = 't' WHERE datname = ");
appendStringLiteralConn(buf, dbname, conn);
appendPQExpBuffer(buf, ";\n");
}
if (binary_upgrade)
{
appendPQExpBuffer(buf, "-- For binary upgrade, set datfrozenxid.\n");
appendPQExpBuffer(buf, "UPDATE pg_catalog.pg_database "
"SET datfrozenxid = '%u' "
"WHERE datname = ",
dbfrozenxid);
appendStringLiteralConn(buf, dbname, conn);
appendPQExpBuffer(buf, ";\n");
}
}
if (!skip_acls &&
!buildACLCommands(fdbname, NULL, "DATABASE", dbacl, dbowner,
"", server_version, buf))
{
fprintf(stderr, _("%s: could not parse ACL list (%s) for database \"%s\"\n"),
progname, dbacl, fdbname);
PQfinish(conn);
exit(1);
}
fprintf(OPF, "%s", buf->data);
if (server_version >= 70300)
dumpDatabaseConfig(conn, dbname);
free(fdbname);
}
PQclear(res);
destroyPQExpBuffer(buf);
fprintf(OPF, "\n\n");
}
