/* * 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 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 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"); }