public inbox for [email protected]help / color / mirror / Atom feed
Re: Non-text mode for pg_dumpall 3+ messages / 2 participants [nested] [flat]
* Re: Non-text mode for pg_dumpall @ 2025-04-03 19:47 Andrew Dunstan <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Andrew Dunstan @ 2025-04-03 19:47 UTC (permalink / raw) To: Mahendra Singh Thalor <[email protected]>; Álvaro Herrera <[email protected]>; +Cc: jian he <[email protected]>; Srinath Reddy <[email protected]>; [email protected] On 2025-04-01 Tu 1:59 AM, Mahendra Singh Thalor wrote: > On Mon, 31 Mar 2025 at 23:43, Álvaro Herrera <[email protected]> wrote: >> Hi >> >> FWIW I don't think the on_exit_nicely business is in final shape just >> yet. We're doing something super strange and novel about keeping track >> of an array index, so that we can modify it later. Or something like >> that, I think? That doesn't sound all that nice to me. Elsewhere it >> was suggested that we need some way to keep track of the list of things >> that need cleanup (a list of connections IIRC?) -- perhaps in a >> thread-local variable or a global or something -- and we install the >> cleanup function once, and that reads from the variable. The program >> can add things to the list, or remove them, at will; and we don't need >> to modify the cleanup function in any way. >> >> -- >> Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ > Thanks Álvaro for the feedback. > > I removed the old handling of on_exit_nicely_list from the last patch > set and added one simple function to just update the archive handle in > shutdown_info. (shutdown_info.AHX = AHX;) > > For first database, we will add entry into on_exit_nicely_list array > and for rest database, we will update only shutdown_info as we already > closed connection for previous database.With this fix, we will not > touch entry of on_exit_nicely_list for each database. > > Here, I am attaching updated patches. > OK, looks good. here's my latest. I'm currently working on tidying up docco and comments. cheers andrew -- Andrew Dunstan EDB: https://www.enterprisedb.com Attachments: [text/x-patch] v20250403-0001-Move-common-pg_dump-code-related-to-connec.patch (26.2K, 2-v20250403-0001-Move-common-pg_dump-code-related-to-connec.patch) download | inline diff: From aea4ab40f4d461141ba92b50986b68f036d044cb Mon Sep 17 00:00:00 2001 From: Mahendra Singh Thalor <[email protected]> Date: Wed, 19 Mar 2025 01:18:46 +0530 Subject: [PATCH v20250403 1/4] Move common pg_dump code related to connections to a new file ConnectDatabase is used by pg_dumpall, pg_restore and pg_dump so move common code to new file. new file name: connectdb.c Author: Mahendra Singh Thalor <[email protected]> --- src/bin/pg_dump/Makefile | 5 +- src/bin/pg_dump/connectdb.c | 294 +++++++++++++++++++++++++++ src/bin/pg_dump/connectdb.h | 26 +++ src/bin/pg_dump/meson.build | 1 + src/bin/pg_dump/pg_backup.h | 6 +- src/bin/pg_dump/pg_backup_archiver.c | 6 +- src/bin/pg_dump/pg_backup_db.c | 79 +------ src/bin/pg_dump/pg_dump.c | 2 +- src/bin/pg_dump/pg_dumpall.c | 278 +------------------------ 9 files changed, 352 insertions(+), 345 deletions(-) create mode 100644 src/bin/pg_dump/connectdb.c create mode 100644 src/bin/pg_dump/connectdb.h diff --git a/src/bin/pg_dump/Makefile b/src/bin/pg_dump/Makefile index 233ad15ca75..fa795883e9f 100644 --- a/src/bin/pg_dump/Makefile +++ b/src/bin/pg_dump/Makefile @@ -31,6 +31,7 @@ OBJS = \ compress_lz4.o \ compress_none.o \ compress_zstd.o \ + connectdb.o \ dumputils.o \ filter.o \ parallel.o \ @@ -50,8 +51,8 @@ pg_dump: pg_dump.o common.o pg_dump_sort.o $(OBJS) | submake-libpq submake-libpg pg_restore: pg_restore.o $(OBJS) | submake-libpq submake-libpgport submake-libpgfeutils $(CC) $(CFLAGS) pg_restore.o $(OBJS) $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X) -pg_dumpall: pg_dumpall.o dumputils.o filter.o $(WIN32RES) | submake-libpq submake-libpgport submake-libpgfeutils - $(CC) $(CFLAGS) pg_dumpall.o dumputils.o filter.o $(WIN32RES) $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X) +pg_dumpall: pg_dumpall.o $(OBJS) | submake-libpq submake-libpgport submake-libpgfeutils + $(CC) $(CFLAGS) pg_dumpall.o $(OBJS) $(LDFLAGS) $(LDFLAGS_EX) $(LIBS) -o $@$(X) install: all installdirs $(INSTALL_PROGRAM) pg_dump$(X) '$(DESTDIR)$(bindir)'/pg_dump$(X) diff --git a/src/bin/pg_dump/connectdb.c b/src/bin/pg_dump/connectdb.c new file mode 100644 index 00000000000..9e593b70e81 --- /dev/null +++ b/src/bin/pg_dump/connectdb.c @@ -0,0 +1,294 @@ +/*------------------------------------------------------------------------- + * + * connectdb.c + * This is a common file connection to the database. + * + * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * IDENTIFICATION + * src/bin/pg_dump/connectdb.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres_fe.h" + +#include "common/connect.h" +#include "common/logging.h" +#include "common/string.h" +#include "connectdb.h" +#include "dumputils.h" +#include "fe_utils/string_utils.h" + +static char *constructConnStr(const char **keywords, const char **values); + +/* + * ConnectDatabase + * + * Make a database connection with the given parameters. An + * interactive password prompt is automatically issued if required. + * + * If fail_on_error is false, we return NULL without printing any message + * on failure, but preserve any prompted password for the next try. + * + * On success, the 'connstr' is set to a connection string containing + * the options used and 'server_version' is set to version so that caller + * can use them. + */ +PGconn * +ConnectDatabase(const char *dbname, const char *connection_string, + const char *pghost, const char *pgport, const char *pguser, + trivalue prompt_password, bool fail_on_error, const char *progname, + const char **connstr, int *server_version, char *password, + char *override_dbname) +{ + PGconn *conn; + bool new_pass; + const char *remoteversion_str; + int my_version; + const char **keywords = NULL; + const char **values = NULL; + PQconninfoOption *conn_opts = NULL; + int server_version_temp; + + if (prompt_password == TRI_YES && !password) + password = simple_prompt("Password: ", false); + + /* + * Start the connection. Loop until we have a password if requested by + * backend. + */ + do + { + int argcount = 8; + PQconninfoOption *conn_opt; + char *err_msg = NULL; + int i = 0; + + free(keywords); + free(values); + PQconninfoFree(conn_opts); + + /* + * Merge the connection info inputs given in form of connection string + * and other options. Explicitly discard any dbname value in the + * connection string; otherwise, PQconnectdbParams() would interpret + * that value as being itself a connection string. + */ + if (connection_string) + { + conn_opts = PQconninfoParse(connection_string, &err_msg); + if (conn_opts == NULL) + pg_fatal("%s", err_msg); + + for (conn_opt = conn_opts; conn_opt->keyword != NULL; conn_opt++) + { + if (conn_opt->val != NULL && conn_opt->val[0] != '\0' && + strcmp(conn_opt->keyword, "dbname") != 0) + argcount++; + } + + keywords = pg_malloc0((argcount + 1) * sizeof(*keywords)); + values = pg_malloc0((argcount + 1) * sizeof(*values)); + + for (conn_opt = conn_opts; conn_opt->keyword != NULL; conn_opt++) + { + if (conn_opt->val != NULL && conn_opt->val[0] != '\0' && + strcmp(conn_opt->keyword, "dbname") != 0) + { + keywords[i] = conn_opt->keyword; + values[i] = conn_opt->val; + i++; + } + } + } + else + { + keywords = pg_malloc0((argcount + 1) * sizeof(*keywords)); + values = pg_malloc0((argcount + 1) * sizeof(*values)); + } + + if (pghost) + { + keywords[i] = "host"; + values[i] = pghost; + i++; + } + if (pgport) + { + keywords[i] = "port"; + values[i] = pgport; + i++; + } + if (pguser) + { + keywords[i] = "user"; + values[i] = pguser; + i++; + } + if (password) + { + keywords[i] = "password"; + values[i] = password; + i++; + } + if (dbname) + { + keywords[i] = "dbname"; + values[i] = dbname; + i++; + } + if (override_dbname) + { + keywords[i] = "dbname"; + values[i++] = override_dbname; + } + + keywords[i] = "fallback_application_name"; + values[i] = progname; + i++; + + new_pass = false; + conn = PQconnectdbParams(keywords, values, true); + + if (!conn) + pg_fatal("could not connect to database \"%s\"", dbname); + + if (PQstatus(conn) == CONNECTION_BAD && + PQconnectionNeedsPassword(conn) && + !password && + prompt_password != TRI_NO) + { + PQfinish(conn); + password = simple_prompt("Password: ", false); + new_pass = true; + } + } while (new_pass); + + /* check to see that the backend connection was successfully made */ + if (PQstatus(conn) == CONNECTION_BAD) + { + if (fail_on_error) + pg_fatal("%s", PQerrorMessage(conn)); + else + { + PQfinish(conn); + + free(keywords); + free(values); + PQconninfoFree(conn_opts); + + return NULL; + } + } + + /* + * Ok, connected successfully. If requested, remember the options used, in + * the form of a connection string. + */ + if (connstr) + *connstr = constructConnStr(keywords, values); + + free(keywords); + free(values); + PQconninfoFree(conn_opts); + + /* Check version */ + remoteversion_str = PQparameterStatus(conn, "server_version"); + if (!remoteversion_str) + pg_fatal("could not get server version"); + + server_version_temp = PQserverVersion(conn); + if (server_version_temp == 0) + pg_fatal("could not parse server version \"%s\"", + remoteversion_str); + + /* If requested, then copy server version to out variable. */ + if (server_version) + *server_version = server_version_temp; + + my_version = PG_VERSION_NUM; + + /* + * We allow the server to be back to 9.2, and up to any minor release of + * our own major version. (See also version check in pg_dump.c.) + */ + if (my_version != server_version_temp + && (server_version_temp < 90200 || + (server_version_temp / 100) > (my_version / 100))) + { + pg_log_error("aborting because of server version mismatch"); + pg_log_error_detail("server version: %s; %s version: %s", + remoteversion_str, progname, PG_VERSION); + exit_nicely(1); + } + + PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL)); + + return conn; +} + +/* + * constructConnStr + * + * Construct a connection string from the given keyword/value pairs. It is + * used to pass the connection options to the pg_dump subprocess. + * + * The following parameters are excluded: + * dbname - varies in each pg_dump invocation + * password - it's not secure to pass a password on the command line + * fallback_application_name - we'll let pg_dump set it + */ +static char * +constructConnStr(const char **keywords, const char **values) +{ + PQExpBuffer buf = createPQExpBuffer(); + char *connstr; + int i; + bool firstkeyword = true; + + /* Construct a new connection string in key='value' format. */ + for (i = 0; keywords[i] != NULL; i++) + { + if (strcmp(keywords[i], "dbname") == 0 || + strcmp(keywords[i], "password") == 0 || + strcmp(keywords[i], "fallback_application_name") == 0) + continue; + + if (!firstkeyword) + appendPQExpBufferChar(buf, ' '); + firstkeyword = false; + appendPQExpBuffer(buf, "%s=", keywords[i]); + appendConnStrVal(buf, values[i]); + } + + connstr = pg_strdup(buf->data); + destroyPQExpBuffer(buf); + return connstr; +} + +/* + * executeQuery + * + * Run a query, return the results, exit program on failure. + */ +PGresult * +executeQuery(PGconn *conn, const char *query) +{ + PGresult *res; + + pg_log_info("executing %s", query); + + res = PQexec(conn, query); + if (!res || + PQresultStatus(res) != PGRES_TUPLES_OK) + { + pg_log_error("query failed: %s", PQerrorMessage(conn)); + pg_log_error_detail("Query was: %s", query); + PQfinish(conn); + exit_nicely(1); + } + + return res; +} diff --git a/src/bin/pg_dump/connectdb.h b/src/bin/pg_dump/connectdb.h new file mode 100644 index 00000000000..6c1e1954769 --- /dev/null +++ b/src/bin/pg_dump/connectdb.h @@ -0,0 +1,26 @@ +/*------------------------------------------------------------------------- + * + * connectdb.h + * Common header file for connection to the database. + * + * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * IDENTIFICATION + * src/bin/pg_dump/connectdb.h + * + *------------------------------------------------------------------------- + */ +#ifndef CONNECTDB_H +#define CONNECTDB_H + +#include "pg_backup.h" +#include "pg_backup_utils.h" + +extern PGconn *ConnectDatabase(const char *dbname, const char *connection_string, const char *pghost, + const char *pgport, const char *pguser, + trivalue prompt_password, bool fail_on_error, + const char *progname, const char **connstr, int *server_version, + char *password, char *override_dbname); +extern PGresult *executeQuery(PGconn *conn, const char *query); +#endif /* CONNECTDB_H */ diff --git a/src/bin/pg_dump/meson.build b/src/bin/pg_dump/meson.build index 603ba6cfbf0..25989e8f16b 100644 --- a/src/bin/pg_dump/meson.build +++ b/src/bin/pg_dump/meson.build @@ -6,6 +6,7 @@ pg_dump_common_sources = files( 'compress_lz4.c', 'compress_none.c', 'compress_zstd.c', + 'connectdb.c', 'dumputils.c', 'filter.c', 'parallel.c', diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h index 658986de6f8..49bc1ee71ef 100644 --- a/src/bin/pg_dump/pg_backup.h +++ b/src/bin/pg_dump/pg_backup.h @@ -293,9 +293,9 @@ typedef void (*SetupWorkerPtrType) (Archive *AH); * Main archiver interface. */ -extern void ConnectDatabase(Archive *AHX, - const ConnParams *cparams, - bool isReconnect); +extern void ConnectDatabaseAhx(Archive *AHX, + const ConnParams *cparams, + bool isReconnect); extern void DisconnectDatabase(Archive *AHX); extern PGconn *GetConnection(Archive *AHX); diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index 1d131e5a57d..3f59f8f9d9d 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -415,7 +415,7 @@ RestoreArchive(Archive *AHX) AHX->minRemoteVersion = 0; AHX->maxRemoteVersion = 9999999; - ConnectDatabase(AHX, &ropt->cparams, false); + ConnectDatabaseAhx(AHX, &ropt->cparams, false); /* * If we're talking to the DB directly, don't send comments since they @@ -4458,7 +4458,7 @@ restore_toc_entries_postfork(ArchiveHandle *AH, TocEntry *pending_list) /* * Now reconnect the single parent connection. */ - ConnectDatabase((Archive *) AH, &ropt->cparams, true); + ConnectDatabaseAhx((Archive *) AH, &ropt->cparams, true); /* re-establish fixed state */ _doSetFixedOutputState(AH); @@ -5076,7 +5076,7 @@ CloneArchive(ArchiveHandle *AH) * Connect our new clone object to the database, using the same connection * parameters used for the original connection. */ - ConnectDatabase((Archive *) clone, &clone->public.ropt->cparams, true); + ConnectDatabaseAhx((Archive *) clone, &clone->public.ropt->cparams, true); /* re-establish fixed state */ if (AH->mode == archModeRead) diff --git a/src/bin/pg_dump/pg_backup_db.c b/src/bin/pg_dump/pg_backup_db.c index 71c55d2466a..5c349279beb 100644 --- a/src/bin/pg_dump/pg_backup_db.c +++ b/src/bin/pg_dump/pg_backup_db.c @@ -19,6 +19,7 @@ #include "common/connect.h" #include "common/string.h" +#include "connectdb.h" #include "parallel.h" #include "pg_backup_archiver.h" #include "pg_backup_db.h" @@ -86,9 +87,9 @@ ReconnectToServer(ArchiveHandle *AH, const char *dbname) * ArchiveHandle's connCancel, before closing old connection. Otherwise * an ill-timed SIGINT could try to access a dead connection. */ - AH->connection = NULL; /* dodge error check in ConnectDatabase */ + AH->connection = NULL; /* dodge error check in ConnectDatabaseAhx */ - ConnectDatabase((Archive *) AH, &ropt->cparams, true); + ConnectDatabaseAhx((Archive *) AH, &ropt->cparams, true); PQfinish(oldConn); } @@ -105,14 +106,13 @@ ReconnectToServer(ArchiveHandle *AH, const char *dbname) * username never does change, so one savedPassword is sufficient. */ void -ConnectDatabase(Archive *AHX, - const ConnParams *cparams, - bool isReconnect) +ConnectDatabaseAhx(Archive *AHX, + const ConnParams *cparams, + bool isReconnect) { ArchiveHandle *AH = (ArchiveHandle *) AHX; trivalue prompt_password; char *password; - bool new_pass; if (AH->connection) pg_fatal("already connected to a database"); @@ -125,69 +125,10 @@ ConnectDatabase(Archive *AHX, if (prompt_password == TRI_YES && password == NULL) password = simple_prompt("Password: ", false); - /* - * Start the connection. Loop until we have a password if requested by - * backend. - */ - do - { - const char *keywords[8]; - const char *values[8]; - int i = 0; - - /* - * If dbname is a connstring, its entries can override the other - * values obtained from cparams; but in turn, override_dbname can - * override the dbname component of it. - */ - keywords[i] = "host"; - values[i++] = cparams->pghost; - keywords[i] = "port"; - values[i++] = cparams->pgport; - keywords[i] = "user"; - values[i++] = cparams->username; - keywords[i] = "password"; - values[i++] = password; - keywords[i] = "dbname"; - values[i++] = cparams->dbname; - if (cparams->override_dbname) - { - keywords[i] = "dbname"; - values[i++] = cparams->override_dbname; - } - keywords[i] = "fallback_application_name"; - values[i++] = progname; - keywords[i] = NULL; - values[i++] = NULL; - Assert(i <= lengthof(keywords)); - - new_pass = false; - AH->connection = PQconnectdbParams(keywords, values, true); - - if (!AH->connection) - pg_fatal("could not connect to database"); - - if (PQstatus(AH->connection) == CONNECTION_BAD && - PQconnectionNeedsPassword(AH->connection) && - password == NULL && - prompt_password != TRI_NO) - { - PQfinish(AH->connection); - password = simple_prompt("Password: ", false); - new_pass = true; - } - } while (new_pass); - - /* check to see that the backend connection was successfully made */ - if (PQstatus(AH->connection) == CONNECTION_BAD) - { - if (isReconnect) - pg_fatal("reconnection failed: %s", - PQerrorMessage(AH->connection)); - else - pg_fatal("%s", - PQerrorMessage(AH->connection)); - } + AH->connection = ConnectDatabase(cparams->dbname, NULL, cparams->pghost, + cparams->pgport, cparams->username, + prompt_password, true, + progname, NULL, NULL, password, cparams->override_dbname); /* Start strict; later phases may override this. */ PQclear(ExecuteSqlQueryForSingleRow((Archive *) AH, diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 04c87ba8854..d90b6183792 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -966,7 +966,7 @@ main(int argc, char **argv) * Open the database using the Archiver, so it knows about it. Errors mean * death. */ - ConnectDatabase(fout, &dopt.cparams, false); + ConnectDatabaseAhx(fout, &dopt.cparams, false); setup_connection(fout, dumpencoding, dumpsnapshot, use_role); /* diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c index 2ea574b0f06..573a8b61a45 100644 --- a/src/bin/pg_dump/pg_dumpall.c +++ b/src/bin/pg_dump/pg_dumpall.c @@ -24,11 +24,11 @@ #include "common/hashfn_unstable.h" #include "common/logging.h" #include "common/string.h" +#include "connectdb.h" #include "dumputils.h" #include "fe_utils/string_utils.h" #include "filter.h" #include "getopt_long.h" -#include "pg_backup.h" /* version string we expect back from pg_dump */ #define PGDUMP_VERSIONSTR "pg_dump (PostgreSQL) " PG_VERSION "\n" @@ -71,21 +71,14 @@ static void buildShSecLabels(PGconn *conn, const char *catalog_name, Oid objectId, const char *objtype, const char *objname, PQExpBuffer buffer); -static PGconn *connectDatabase(const char *dbname, - const char *connection_string, const char *pghost, - const char *pgport, const char *pguser, - trivalue prompt_password, bool fail_on_error); -static char *constructConnStr(const char **keywords, const char **values); -static PGresult *executeQuery(PGconn *conn, const char *query); static void executeCommand(PGconn *conn, const char *query); static void expand_dbname_patterns(PGconn *conn, SimpleStringList *patterns, SimpleStringList *names); static void read_dumpall_filters(const char *filename, SimpleStringList *pattern); static char pg_dump_bin[MAXPGPATH]; -static const char *progname; static PQExpBuffer pgdumpopts; -static char *connstr = ""; +static const char *connstr = ""; static bool output_clean = false; static bool skip_acls = false; static bool verbose = false; @@ -129,8 +122,6 @@ static char *filename = NULL; static SimpleStringList database_exclude_patterns = {NULL, NULL}; static SimpleStringList database_exclude_names = {NULL, NULL}; -#define exit_nicely(code) exit(code) - int main(int argc, char *argv[]) { @@ -499,19 +490,22 @@ main(int argc, char *argv[]) */ if (pgdb) { - conn = connectDatabase(pgdb, connstr, pghost, pgport, pguser, - prompt_password, false); + conn = ConnectDatabase(pgdb, connstr, pghost, pgport, pguser, + prompt_password, false, + progname, &connstr, &server_version, NULL, NULL); if (!conn) pg_fatal("could not connect to database \"%s\"", pgdb); } else { - conn = connectDatabase("postgres", connstr, pghost, pgport, pguser, - prompt_password, false); + conn = ConnectDatabase("postgres", connstr, pghost, pgport, pguser, + prompt_password, false, + progname, &connstr, &server_version, NULL, NULL); if (!conn) - conn = connectDatabase("template1", connstr, pghost, pgport, pguser, - prompt_password, true); + conn = ConnectDatabase("template1", connstr, pghost, pgport, pguser, + prompt_password, true, + progname, &connstr, &server_version, NULL, NULL); if (!conn) { @@ -1738,256 +1732,6 @@ buildShSecLabels(PGconn *conn, const char *catalog_name, Oid objectId, destroyPQExpBuffer(sql); } -/* - * Make a database connection with the given parameters. An - * interactive password prompt is automatically issued if required. - * - * If fail_on_error is false, we return NULL without printing any message - * on failure, but preserve any prompted password for the next try. - * - * On success, the global variable 'connstr' is set to a connection string - * containing the options used. - */ -static PGconn * -connectDatabase(const char *dbname, const char *connection_string, - const char *pghost, const char *pgport, const char *pguser, - trivalue prompt_password, bool fail_on_error) -{ - PGconn *conn; - bool new_pass; - const char *remoteversion_str; - int my_version; - const char **keywords = NULL; - const char **values = NULL; - PQconninfoOption *conn_opts = NULL; - static char *password = NULL; - - if (prompt_password == TRI_YES && !password) - password = simple_prompt("Password: ", false); - - /* - * Start the connection. Loop until we have a password if requested by - * backend. - */ - do - { - int argcount = 6; - PQconninfoOption *conn_opt; - char *err_msg = NULL; - int i = 0; - - free(keywords); - free(values); - PQconninfoFree(conn_opts); - - /* - * Merge the connection info inputs given in form of connection string - * and other options. Explicitly discard any dbname value in the - * connection string; otherwise, PQconnectdbParams() would interpret - * that value as being itself a connection string. - */ - if (connection_string) - { - conn_opts = PQconninfoParse(connection_string, &err_msg); - if (conn_opts == NULL) - pg_fatal("%s", err_msg); - - for (conn_opt = conn_opts; conn_opt->keyword != NULL; conn_opt++) - { - if (conn_opt->val != NULL && conn_opt->val[0] != '\0' && - strcmp(conn_opt->keyword, "dbname") != 0) - argcount++; - } - - keywords = pg_malloc0((argcount + 1) * sizeof(*keywords)); - values = pg_malloc0((argcount + 1) * sizeof(*values)); - - for (conn_opt = conn_opts; conn_opt->keyword != NULL; conn_opt++) - { - if (conn_opt->val != NULL && conn_opt->val[0] != '\0' && - strcmp(conn_opt->keyword, "dbname") != 0) - { - keywords[i] = conn_opt->keyword; - values[i] = conn_opt->val; - i++; - } - } - } - else - { - keywords = pg_malloc0((argcount + 1) * sizeof(*keywords)); - values = pg_malloc0((argcount + 1) * sizeof(*values)); - } - - if (pghost) - { - keywords[i] = "host"; - values[i] = pghost; - i++; - } - if (pgport) - { - keywords[i] = "port"; - values[i] = pgport; - i++; - } - if (pguser) - { - keywords[i] = "user"; - values[i] = pguser; - i++; - } - if (password) - { - keywords[i] = "password"; - values[i] = password; - i++; - } - if (dbname) - { - keywords[i] = "dbname"; - values[i] = dbname; - i++; - } - keywords[i] = "fallback_application_name"; - values[i] = progname; - i++; - - new_pass = false; - conn = PQconnectdbParams(keywords, values, true); - - if (!conn) - pg_fatal("could not connect to database \"%s\"", dbname); - - if (PQstatus(conn) == CONNECTION_BAD && - PQconnectionNeedsPassword(conn) && - !password && - prompt_password != TRI_NO) - { - PQfinish(conn); - password = simple_prompt("Password: ", false); - new_pass = true; - } - } while (new_pass); - - /* check to see that the backend connection was successfully made */ - if (PQstatus(conn) == CONNECTION_BAD) - { - if (fail_on_error) - pg_fatal("%s", PQerrorMessage(conn)); - else - { - PQfinish(conn); - - free(keywords); - free(values); - PQconninfoFree(conn_opts); - - return NULL; - } - } - - /* - * Ok, connected successfully. Remember the options used, in the form of a - * connection string. - */ - connstr = constructConnStr(keywords, values); - - free(keywords); - free(values); - PQconninfoFree(conn_opts); - - /* Check version */ - remoteversion_str = PQparameterStatus(conn, "server_version"); - if (!remoteversion_str) - pg_fatal("could not get server version"); - server_version = PQserverVersion(conn); - if (server_version == 0) - pg_fatal("could not parse server version \"%s\"", - remoteversion_str); - - my_version = PG_VERSION_NUM; - - /* - * We allow the server to be back to 9.2, and up to any minor release of - * our own major version. (See also version check in pg_dump.c.) - */ - if (my_version != server_version - && (server_version < 90200 || - (server_version / 100) > (my_version / 100))) - { - pg_log_error("aborting because of server version mismatch"); - pg_log_error_detail("server version: %s; %s version: %s", - remoteversion_str, progname, PG_VERSION); - exit_nicely(1); - } - - PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL)); - - return conn; -} - -/* ---------- - * Construct a connection string from the given keyword/value pairs. It is - * used to pass the connection options to the pg_dump subprocess. - * - * The following parameters are excluded: - * dbname - varies in each pg_dump invocation - * password - it's not secure to pass a password on the command line - * fallback_application_name - we'll let pg_dump set it - * ---------- - */ -static char * -constructConnStr(const char **keywords, const char **values) -{ - PQExpBuffer buf = createPQExpBuffer(); - char *connstr; - int i; - bool firstkeyword = true; - - /* Construct a new connection string in key='value' format. */ - for (i = 0; keywords[i] != NULL; i++) - { - if (strcmp(keywords[i], "dbname") == 0 || - strcmp(keywords[i], "password") == 0 || - strcmp(keywords[i], "fallback_application_name") == 0) - continue; - - if (!firstkeyword) - appendPQExpBufferChar(buf, ' '); - firstkeyword = false; - appendPQExpBuffer(buf, "%s=", keywords[i]); - appendConnStrVal(buf, values[i]); - } - - connstr = pg_strdup(buf->data); - destroyPQExpBuffer(buf); - return connstr; -} - -/* - * Run a query, return the results, exit program on failure. - */ -static PGresult * -executeQuery(PGconn *conn, const char *query) -{ - PGresult *res; - - pg_log_info("executing %s", query); - - res = PQexec(conn, query); - if (!res || - PQresultStatus(res) != PGRES_TUPLES_OK) - { - pg_log_error("query failed: %s", PQerrorMessage(conn)); - pg_log_error_detail("Query was: %s", query); - PQfinish(conn); - exit_nicely(1); - } - - return res; -} - /* * As above for a SQL command (which returns nothing). */ -- 2.34.1 [text/x-patch] v20250403-0002-add-new-list-type-simple_oid_string_list-t.patch (3.2K, 3-v20250403-0002-add-new-list-type-simple_oid_string_list-t.patch) download | inline diff: From 367ecd4f6870f9402a2254152bbf7e97ff1f2a23 Mon Sep 17 00:00:00 2001 From: Andrew Dunstan <[email protected]> Date: Fri, 28 Mar 2025 18:10:24 -0400 Subject: [PATCH v20250403 2/4] add new list type simple_oid_string_list to fe-utils/simple_list This type contains both an oid and a string. This will be used in forthcoming changes to pg_restore. Author: Andrew Dunstan <[email protected]> --- src/fe_utils/simple_list.c | 41 ++++++++++++++++++++++++++++++ src/include/fe_utils/simple_list.h | 16 ++++++++++++ src/tools/pgindent/typedefs.list | 2 ++ 3 files changed, 59 insertions(+) diff --git a/src/fe_utils/simple_list.c b/src/fe_utils/simple_list.c index 483d5455594..b0686e57c4a 100644 --- a/src/fe_utils/simple_list.c +++ b/src/fe_utils/simple_list.c @@ -192,3 +192,44 @@ simple_ptr_list_destroy(SimplePtrList *list) cell = next; } } + +/* + * Add to an oid_string list + */ +void +simple_oid_string_list_append(SimpleOidStringList *list, Oid oid, const char *str) +{ + SimpleOidStringListCell *cell; + + cell = (SimpleOidStringListCell *) + pg_malloc(offsetof(SimpleOidStringListCell, str) + strlen(str) + 1); + + cell->next = NULL; + cell->oid = oid; + strcpy(cell->str, str); + + if (list->tail) + list->tail->next = cell; + else + list->head = cell; + list->tail = cell; +} + +/* + * Destroy an oid_string list + */ +void +simple_oid_string_list_destroy(SimpleOidStringList *list) +{ + SimpleOidStringListCell *cell; + + cell = list->head; + while (cell != NULL) + { + SimpleOidStringListCell *next; + + next = cell->next; + pg_free(cell); + cell = next; + } +} diff --git a/src/include/fe_utils/simple_list.h b/src/include/fe_utils/simple_list.h index 3b8e38414ec..a5373932555 100644 --- a/src/include/fe_utils/simple_list.h +++ b/src/include/fe_utils/simple_list.h @@ -55,6 +55,19 @@ typedef struct SimplePtrList SimplePtrListCell *tail; } SimplePtrList; +typedef struct SimpleOidStringListCell +{ + struct SimpleOidStringListCell *next; + Oid oid; + char str[FLEXIBLE_ARRAY_MEMBER]; /* null-terminated string here */ +} SimpleOidStringListCell; + +typedef struct SimpleOidStringList +{ + SimpleOidStringListCell *head; + SimpleOidStringListCell *tail; +} SimpleOidStringList; + extern void simple_oid_list_append(SimpleOidList *list, Oid val); extern bool simple_oid_list_member(SimpleOidList *list, Oid val); extern void simple_oid_list_destroy(SimpleOidList *list); @@ -68,4 +81,7 @@ extern const char *simple_string_list_not_touched(SimpleStringList *list); extern void simple_ptr_list_append(SimplePtrList *list, void *ptr); extern void simple_ptr_list_destroy(SimplePtrList *list); +extern void simple_oid_string_list_append(SimpleOidStringList *list, Oid oid, const char *str); +extern void simple_oid_string_list_destroy(SimpleOidStringList *list); + #endif /* SIMPLE_LIST_H */ diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 8f28d8ff28e..8be62c9216a 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -2744,6 +2744,8 @@ SimpleActionListCell SimpleEcontextStackEntry SimpleOidList SimpleOidListCell +SimpleOidStringList +SimpleOidListStringCell SimplePtrList SimplePtrListCell SimpleStats -- 2.34.1 [text/x-patch] v20250403-0003-Non-text-modes-for-pg_dumpall-correspondin.patch (60.7K, 4-v20250403-0003-Non-text-modes-for-pg_dumpall-correspondin.patch) download | inline diff: From 76214ce8a2a767a68f30e72f4f79f85880241e52 Mon Sep 17 00:00:00 2001 From: Mahendra Singh Thalor <[email protected]> Date: Tue, 1 Apr 2025 10:48:52 +0530 Subject: [PATCH v20250403 3/4] Non text modes for pg_dumpall, correspondingly change pg_restore MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit pg_dumpall acquires a new -F/--format option, with the same meanings as pg_dump. The default is p, meaning plain text. For any other value, a directory is created containing two files, globals.data and map.dat. The first contains SQL for restoring the global data, and the second contains a map from oids to database names. It will also contain a subdirectory called databases, inside which it will create archives in the specified format, named using the database oids. In these casess the -f argument is required. If pg_restore encounters a directory containing globals.dat, and no toc.dat, it restores the global settings and then restores each database. pg_restore acquires two new options: -g/--globals-only which suppresses restoration of any databases, and --exclude-database which inhibits restoration of particualr database(s) in the same way the same option works in pg_dumpall. Author: Mahendra Singh Thalor <[email protected]> Co-authored-by: Andrew Dunstan <[email protected]> Reviewed-by: jian he <[email protected]> Reviewed-by: Srinath Reddy <[email protected]> Reviewed-by: Álvaro Herrera <[email protected]> Discussion: https://postgr.es/m/[email protected] --- doc/src/sgml/ref/pg_dumpall.sgml | 86 ++- doc/src/sgml/ref/pg_restore.sgml | 66 ++- src/bin/pg_dump/parallel.c | 10 + src/bin/pg_dump/pg_backup.h | 2 +- src/bin/pg_dump/pg_backup_archiver.c | 20 +- src/bin/pg_dump/pg_backup_archiver.h | 1 + src/bin/pg_dump/pg_backup_tar.c | 2 +- src/bin/pg_dump/pg_dump.c | 2 +- src/bin/pg_dump/pg_dumpall.c | 295 ++++++++-- src/bin/pg_dump/pg_restore.c | 800 ++++++++++++++++++++++++++- src/bin/pg_dump/t/001_basic.pl | 9 + 11 files changed, 1208 insertions(+), 85 deletions(-) diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml index 765b30a3a66..43fdab2d77e 100644 --- a/doc/src/sgml/ref/pg_dumpall.sgml +++ b/doc/src/sgml/ref/pg_dumpall.sgml @@ -16,7 +16,7 @@ PostgreSQL documentation <refnamediv> <refname>pg_dumpall</refname> - <refpurpose>extract a <productname>PostgreSQL</productname> database cluster into a script file</refpurpose> + <refpurpose>extract a <productname>PostgreSQL</productname> database cluster using a specified dump format</refpurpose> </refnamediv> <refsynopsisdiv> @@ -33,7 +33,7 @@ PostgreSQL documentation <para> <application>pg_dumpall</application> is a utility for writing out (<quote>dumping</quote>) all <productname>PostgreSQL</productname> databases - of a cluster into one script file. The script file contains + of a cluster into an archive. The archive contains <acronym>SQL</acronym> commands that can be used as input to <xref linkend="app-psql"/> to restore the databases. It does this by calling <xref linkend="app-pgdump"/> for each database in the cluster. @@ -52,11 +52,16 @@ PostgreSQL documentation </para> <para> - The SQL script will be written to the standard output. Use the + Plain text SQL scripts will be written to the standard output. Use the <option>-f</option>/<option>--file</option> option or shell operators to redirect it into a file. </para> + <para> + Archives in other formats will be placed in a directory named using the + <option>-f</option>/<option>--file</option>, which is required in this case. + </para> + <para> <application>pg_dumpall</application> needs to connect several times to the <productname>PostgreSQL</productname> server (once per @@ -121,10 +126,85 @@ PostgreSQL documentation <para> Send output to the specified file. If this is omitted, the standard output is used. + Note: This option can only be omitted when <option>--format</option> is plain </para> </listitem> </varlistentry> + <varlistentry> + <term><option>-F <replaceable class="parameter">format</replaceable></option></term> + <term><option>--format=<replaceable class="parameter">format</replaceable></option></term> + <listitem> + <para> + Specify the format of dump files. In plain format, all the dump data is + sent in a single text stream. This is the default. + + In all other modes, <application>pg_dumpall</application> first creates two files: + <filename>global.dat</filename> and <filename>map.dat</filename>, in the directory + specified by <option>--file</option>. + The first file contains global data, such as roles and tablespaces. The second + contains a mapping between database oids and names. These files are used by + <application>pg_restore</application>. Data for individual databases is placed in + <filename>databases</filename> subdirectory, named using the database's <type>oid</type>. + + <variablelist> + <varlistentry> + <term><literal>d</literal></term> + <term><literal>directory</literal></term> + <listitem> + <para> + Output directory-format archives for each database, + suitable for input into pg_restore. The directory + will have database <type>oid</type> as its name. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>p</literal></term> + <term><literal>plain</literal></term> + <listitem> + <para> + Output a plain-text SQL script file (the default). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>c</literal></term> + <term><literal>custom</literal></term> + <listitem> + <para> + Output a custom-format archive for each database, + suitable for input into pg_restore. The archive + will be named <filename>dboid.dmp</filename> where <type>dboid</type> is the + <type>oid</type> of the database. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>t</literal></term> + <term><literal>tar</literal></term> + <listitem> + <para> + Output a tar-format archive for each database, + suitable for input into pg_restore. The archive + will be named <filename>dboid.tar</filename> where <type>dboid</type> is the + <type>oid</type> of the database. + </para> + </listitem> + </varlistentry> + + </variablelist> + + Note: see <xref linkend="app-pgdump"/> for details + of how the various non plain text archives work. + + </para> + </listitem> + </varlistentry> + <varlistentry> <term><option>--filter=<replaceable class="parameter">filename</replaceable></option></term> <listitem> diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml index c840a807ae9..f14e5866f6c 100644 --- a/doc/src/sgml/ref/pg_restore.sgml +++ b/doc/src/sgml/ref/pg_restore.sgml @@ -18,8 +18,9 @@ PostgreSQL documentation <refname>pg_restore</refname> <refpurpose> - restore a <productname>PostgreSQL</productname> database from an - archive file created by <application>pg_dump</application> + restore a <productname>PostgreSQL</productname> database or cluster + from an archive created by <application>pg_dump</application> or + <application>pg_dumpall</application> </refpurpose> </refnamediv> @@ -38,13 +39,14 @@ PostgreSQL documentation <para> <application>pg_restore</application> is a utility for restoring a - <productname>PostgreSQL</productname> database from an archive - created by <xref linkend="app-pgdump"/> in one of the non-plain-text + <productname>PostgreSQL</productname> database or cluster from an archive + created by <xref linkend="app-pgdump"/> or + <xref linkend="app-pg-dumpall"/> in one of the non-plain-text formats. It will issue the commands necessary to reconstruct the - database to the state it was in at the time it was saved. The - archive files also allow <application>pg_restore</application> to + database or cluster to the state it was in at the time it was saved. The + archives also allow <application>pg_restore</application> to be selective about what is restored, or even to reorder the items - prior to being restored. The archive files are designed to be + prior to being restored. The archive formats are designed to be portable across architectures. </para> @@ -52,10 +54,17 @@ PostgreSQL documentation <application>pg_restore</application> can operate in two modes. If a database name is specified, <application>pg_restore</application> connects to that database and restores archive contents directly into - the database. Otherwise, a script containing the SQL - commands necessary to rebuild the database is created and written + the database. + When restoring from a dump made by<application>pg_dumpall</application>, + each database will be created and then the restoration will be run in that + database. + + Otherwise, when a database name is not specified, a script containing the SQL + commands necessary to rebuild the database or cluster is created and written to a file or standard output. This script output is equivalent to - the plain text output format of <application>pg_dump</application>. + the plain text output format of <application>pg_dump</application> or + <application>pg_dumpall</application>. + Some of the options controlling the output are therefore analogous to <application>pg_dump</application> options. </para> @@ -140,6 +149,8 @@ PostgreSQL documentation commands that mention this database. Access privileges for the database itself are also restored, unless <option>--no-acl</option> is specified. + <option>--create</option> is required when restoring multiple databases + from an archive created by <application>pg_dumpall</application>. </para> <para> @@ -166,6 +177,28 @@ PostgreSQL documentation </listitem> </varlistentry> + <varlistentry> + <term><option>--exclude-database=<replaceable class="parameter">pattern</replaceable></option></term> + <listitem> + <para> + Do not restore databases whose name matches + <replaceable class="parameter">pattern</replaceable>. + Multiple patterns can be excluded by writing multiple + <option>--exclude-database</option> switches. The + <replaceable class="parameter">pattern</replaceable> parameter is + interpreted as a pattern according to the same rules used by + <application>psql</application>'s <literal>\d</literal> + commands (see <xref linkend="app-psql-patterns"/>), + so multiple databases can also be excluded by writing wildcard + characters in the pattern. When using wildcards, be careful to + quote the pattern if needed to prevent shell wildcard expansion. + </para> + <para> + This option is only relevant when restoring from an archive made using <application>pg_dumpall</application>. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><option>-e</option></term> <term><option>--exit-on-error</option></term> @@ -315,6 +348,19 @@ PostgreSQL documentation </listitem> </varlistentry> + <varlistentry> + <term><option>-g</option></term> + <term><option>--globals-only</option></term> + <listitem> + <para> + Restore only global objects (roles and tablespaces), no databases. + </para> + <para> + This option is only relevant when restoring from an archive made using <application>pg_dumpall</application>. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><option>-I <replaceable class="parameter">index</replaceable></option></term> <term><option>--index=<replaceable class="parameter">index</replaceable></option></term> diff --git a/src/bin/pg_dump/parallel.c b/src/bin/pg_dump/parallel.c index 086adcdc502..5974d6706fd 100644 --- a/src/bin/pg_dump/parallel.c +++ b/src/bin/pg_dump/parallel.c @@ -333,6 +333,16 @@ on_exit_close_archive(Archive *AHX) on_exit_nicely(archive_close_connection, &shutdown_info); } +/* + * When pg_restore restores multiple databases, then update already added entry + * into array for cleanup. + */ +void +replace_on_exit_close_archive(Archive *AHX) +{ + shutdown_info.AHX = AHX; +} + /* * on_exit_nicely handler for shutting down database connections and * worker processes cleanly. diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h index 49bc1ee71ef..17d6e06ec25 100644 --- a/src/bin/pg_dump/pg_backup.h +++ b/src/bin/pg_dump/pg_backup.h @@ -311,7 +311,7 @@ extern void SetArchiveOptions(Archive *AH, DumpOptions *dopt, RestoreOptions *ro extern void ProcessArchiveRestoreOptions(Archive *AHX); -extern void RestoreArchive(Archive *AHX); +extern void RestoreArchive(Archive *AHX, bool append_data); /* Open an existing archive */ extern Archive *OpenArchive(const char *FileSpec, const ArchiveFormat fmt); diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index 3f59f8f9d9d..54eb4728928 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -85,7 +85,7 @@ static int RestoringToDB(ArchiveHandle *AH); static void dump_lo_buf(ArchiveHandle *AH); static void dumpTimestamp(ArchiveHandle *AH, const char *msg, time_t tim); static void SetOutput(ArchiveHandle *AH, const char *filename, - const pg_compress_specification compression_spec); + const pg_compress_specification compression_spec, bool append_data); static CompressFileHandle *SaveOutput(ArchiveHandle *AH); static void RestoreOutput(ArchiveHandle *AH, CompressFileHandle *savedOutput); @@ -338,9 +338,14 @@ ProcessArchiveRestoreOptions(Archive *AHX) StrictNamesCheck(ropt); } -/* Public */ +/* + * RestoreArchive + * + * If append_data is set, then append data into file as we are restoring dump + * of multiple databases which was taken by pg_dumpall. + */ void -RestoreArchive(Archive *AHX) +RestoreArchive(Archive *AHX, bool append_data) { ArchiveHandle *AH = (ArchiveHandle *) AHX; RestoreOptions *ropt = AH->public.ropt; @@ -457,7 +462,7 @@ RestoreArchive(Archive *AHX) */ sav = SaveOutput(AH); if (ropt->filename || ropt->compression_spec.algorithm != PG_COMPRESSION_NONE) - SetOutput(AH, ropt->filename, ropt->compression_spec); + SetOutput(AH, ropt->filename, ropt->compression_spec, append_data); ahprintf(AH, "--\n-- PostgreSQL database dump\n--\n\n"); @@ -1293,7 +1298,7 @@ PrintTOCSummary(Archive *AHX) sav = SaveOutput(AH); if (ropt->filename) - SetOutput(AH, ropt->filename, out_compression_spec); + SetOutput(AH, ropt->filename, out_compression_spec, false); if (strftime(stamp_str, sizeof(stamp_str), PGDUMP_STRFTIME_FMT, localtime(&AH->createDate)) == 0) @@ -1672,7 +1677,8 @@ archprintf(Archive *AH, const char *fmt,...) static void SetOutput(ArchiveHandle *AH, const char *filename, - const pg_compress_specification compression_spec) + const pg_compress_specification compression_spec, + bool append_data) { CompressFileHandle *CFH; const char *mode; @@ -1692,7 +1698,7 @@ SetOutput(ArchiveHandle *AH, const char *filename, else fn = fileno(stdout); - if (AH->mode == archModeAppend) + if (append_data || AH->mode == archModeAppend) mode = PG_BINARY_A; else mode = PG_BINARY_W; diff --git a/src/bin/pg_dump/pg_backup_archiver.h b/src/bin/pg_dump/pg_backup_archiver.h index a2064f471ed..ed0238cca47 100644 --- a/src/bin/pg_dump/pg_backup_archiver.h +++ b/src/bin/pg_dump/pg_backup_archiver.h @@ -386,6 +386,7 @@ struct _tocEntry extern int parallel_restore(ArchiveHandle *AH, TocEntry *te); extern void on_exit_close_archive(Archive *AHX); +extern void replace_on_exit_close_archive(Archive *AHX); extern void warn_or_exit_horribly(ArchiveHandle *AH, const char *fmt,...) pg_attribute_printf(2, 3); diff --git a/src/bin/pg_dump/pg_backup_tar.c b/src/bin/pg_dump/pg_backup_tar.c index b5ba3b46dd9..d94d0de2a5d 100644 --- a/src/bin/pg_dump/pg_backup_tar.c +++ b/src/bin/pg_dump/pg_backup_tar.c @@ -826,7 +826,7 @@ _CloseArchive(ArchiveHandle *AH) savVerbose = AH->public.verbose; AH->public.verbose = 0; - RestoreArchive((Archive *) AH); + RestoreArchive((Archive *) AH, false); SetArchiveOptions((Archive *) AH, savDopt, savRopt); diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index d90b6183792..9dcda63b4b8 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -1219,7 +1219,7 @@ main(int argc, char **argv) * right now. */ if (plainText) - RestoreArchive(fout); + RestoreArchive(fout, false); CloseArchive(fout); diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c index 573a8b61a45..248afc4be28 100644 --- a/src/bin/pg_dump/pg_dumpall.c +++ b/src/bin/pg_dump/pg_dumpall.c @@ -15,6 +15,7 @@ #include "postgres_fe.h" +#include <sys/stat.h> #include <time.h> #include <unistd.h> @@ -64,9 +65,10 @@ static void dropTablespaces(PGconn *conn); static void dumpTablespaces(PGconn *conn); static void dropDBs(PGconn *conn); static void dumpUserConfig(PGconn *conn, const char *username); -static void dumpDatabases(PGconn *conn); +static void dumpDatabases(PGconn *conn, ArchiveFormat archDumpFormat); static void dumpTimestamp(const char *msg); -static int runPgDump(const char *dbname, const char *create_opts); +static int runPgDump(const char *dbname, const char *create_opts, + char *dbfile, ArchiveFormat archDumpFormat); static void buildShSecLabels(PGconn *conn, const char *catalog_name, Oid objectId, const char *objtype, const char *objname, @@ -75,6 +77,8 @@ static void executeCommand(PGconn *conn, const char *query); static void expand_dbname_patterns(PGconn *conn, SimpleStringList *patterns, SimpleStringList *names); static void read_dumpall_filters(const char *filename, SimpleStringList *pattern); +static void create_or_open_dir(const char *dirname); +static ArchiveFormat parseDumpFormat(const char *format); static char pg_dump_bin[MAXPGPATH]; static PQExpBuffer pgdumpopts; @@ -146,6 +150,7 @@ main(int argc, char *argv[]) {"password", no_argument, NULL, 'W'}, {"no-privileges", no_argument, NULL, 'x'}, {"no-acl", no_argument, NULL, 'x'}, + {"format", required_argument, NULL, 'F'}, /* * the following options don't have an equivalent short option letter @@ -195,6 +200,8 @@ main(int argc, char *argv[]) char *pgdb = NULL; char *use_role = NULL; const char *dumpencoding = NULL; + ArchiveFormat archDumpFormat = archNull; + const char *formatName = "p"; trivalue prompt_password = TRI_DEFAULT; bool data_only = false; bool globals_only = false; @@ -244,7 +251,7 @@ main(int argc, char *argv[]) pgdumpopts = createPQExpBuffer(); - while ((c = getopt_long(argc, argv, "acd:E:f:gh:l:Op:rsS:tU:vwWx", long_options, &optindex)) != -1) + while ((c = getopt_long(argc, argv, "acd:E:f:F:gh:l:Op:rsS:tU:vwWx", long_options, &optindex)) != -1) { switch (c) { @@ -272,7 +279,9 @@ main(int argc, char *argv[]) appendPQExpBufferStr(pgdumpopts, " -f "); appendShellString(pgdumpopts, filename); break; - + case 'F': + formatName = pg_strdup(optarg); + break; case 'g': globals_only = true; break; @@ -421,6 +430,21 @@ main(int argc, char *argv[]) exit_nicely(1); } + /* Get format for dump. */ + archDumpFormat = parseDumpFormat(formatName); + + /* + * If a non-plain format is specified, a file name is also required as the + * path to the main directory. + */ + if (archDumpFormat != archNull && + (!filename || strcmp(filename, "") == 0)) + { + pg_log_error("option -F/--format=d|c|t requires option -f/--file"); + pg_log_error_hint("Try \"%s --help\" for more information.", progname); + exit_nicely(1); + } + /* * If password values are not required in the dump, switch to using * pg_roles which is equally useful, just more likely to have unrestricted @@ -483,6 +507,33 @@ main(int argc, char *argv[]) if (statistics_only) appendPQExpBufferStr(pgdumpopts, " --statistics-only"); + /* + * Open the output file if required, otherwise use stdout. If required, + * then create new directory and global.dat file. + */ + if (archDumpFormat != archNull) + { + char global_path[MAXPGPATH]; + + /* Create new directory or accept the empty existing directory. */ + create_or_open_dir(filename); + + snprintf(global_path, MAXPGPATH, "%s/global.dat", filename); + + OPF = fopen(global_path, PG_BINARY_W); + if (!OPF) + pg_fatal("could not open global.dat file: %s", strerror(errno)); + } + else if (filename) + { + OPF = fopen(filename, PG_BINARY_W); + if (!OPF) + pg_fatal("could not open output file \"%s\": %m", + filename); + } + else + OPF = stdout; + /* * If there was a database specified on the command line, use that, * otherwise try to connect to database "postgres", and failing that @@ -522,19 +573,6 @@ main(int argc, char *argv[]) expand_dbname_patterns(conn, &database_exclude_patterns, &database_exclude_names); - /* - * Open the output file if required, otherwise use stdout - */ - if (filename) - { - OPF = fopen(filename, PG_BINARY_W); - if (!OPF) - pg_fatal("could not open output file \"%s\": %m", - filename); - } - else - OPF = stdout; - /* * Set the client encoding if requested. */ @@ -634,7 +672,7 @@ main(int argc, char *argv[]) } if (!globals_only && !roles_only && !tablespaces_only) - dumpDatabases(conn); + dumpDatabases(conn, archDumpFormat); PQfinish(conn); @@ -647,7 +685,7 @@ main(int argc, char *argv[]) fclose(OPF); /* sync the resulting file, errors are not fatal */ - if (dosync) + if (dosync && (archDumpFormat == archNull)) (void) fsync_fname(filename, false); } @@ -658,12 +696,14 @@ main(int argc, char *argv[]) static void help(void) { - printf(_("%s extracts a PostgreSQL database cluster into an SQL script file.\n\n"), progname); + printf(_("%s extracts a PostgreSQL database cluster based on specified dump format.\n\n"), progname); printf(_("Usage:\n")); printf(_(" %s [OPTION]...\n"), progname); printf(_("\nGeneral options:\n")); printf(_(" -f, --file=FILENAME output file name\n")); + printf(_(" -F, --format=c|d|t|p output file format (custom, directory, tar,\n" + " plain text (default))\n")); printf(_(" -v, --verbose verbose mode\n")); printf(_(" -V, --version output version information, then exit\n")); printf(_(" --lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock\n")); @@ -969,9 +1009,6 @@ dumpRoles(PGconn *conn) * We do it this way because config settings for roles could mention the * names of other roles. */ - if (PQntuples(res) > 0) - fprintf(OPF, "\n--\n-- User Configurations\n--\n"); - for (i = 0; i < PQntuples(res); i++) dumpUserConfig(conn, PQgetvalue(res, i, i_rolname)); @@ -1485,6 +1522,7 @@ dumpUserConfig(PGconn *conn, const char *username) { PQExpBuffer buf = createPQExpBuffer(); PGresult *res; + static bool header_done = false; printfPQExpBuffer(buf, "SELECT unnest(setconfig) FROM pg_db_role_setting " "WHERE setdatabase = 0 AND setrole = " @@ -1496,7 +1534,13 @@ dumpUserConfig(PGconn *conn, const char *username) res = executeQuery(conn, buf->data); if (PQntuples(res) > 0) + { + if (!header_done) + fprintf(OPF, "\n--\n-- User Configurations\n--\n"); + header_done = true; + fprintf(OPF, "\n--\n-- User Config \"%s\"\n--\n\n", username); + } for (int i = 0; i < PQntuples(res); i++) { @@ -1570,10 +1614,13 @@ expand_dbname_patterns(PGconn *conn, * Dump contents of databases. */ static void -dumpDatabases(PGconn *conn) +dumpDatabases(PGconn *conn, ArchiveFormat archDumpFormat) { PGresult *res; int i; + char db_subdir[MAXPGPATH]; + char dbfilepath[MAXPGPATH]; + FILE *map_file = NULL; /* * Skip databases marked not datallowconn, since we'd be unable to connect @@ -1587,18 +1634,42 @@ dumpDatabases(PGconn *conn) * doesn't have some failure mode with --clean. */ res = executeQuery(conn, - "SELECT datname " + "SELECT datname, oid " "FROM pg_database d " "WHERE datallowconn AND datconnlimit != -2 " "ORDER BY (datname <> 'template1'), datname"); - if (PQntuples(res) > 0) + if (archDumpFormat == archNull && PQntuples(res) > 0) fprintf(OPF, "--\n-- Databases\n--\n\n"); + /* + * If directory/tar/custom format is specified, create a subdirectory + * under the main directory and each database dump file or subdirectory + * will be created in that subdirectory by pg_dump. + */ + if (archDumpFormat != archNull) + { + char map_file_path[MAXPGPATH]; + + snprintf(db_subdir, MAXPGPATH, "%s/databases", filename); + + /* Create a subdirectory with 'databases' name under main directory. */ + if (mkdir(db_subdir, 0755) != 0) + pg_fatal("could not create subdirectory \"%s\": %m", db_subdir); + + snprintf(map_file_path, MAXPGPATH, "%s/map.dat", filename); + + /* Create a map file (to store dboid and dbname) */ + map_file = fopen(map_file_path, PG_BINARY_W); + if (!map_file) + pg_fatal("could not open map file: %s", strerror(errno)); + } + for (i = 0; i < PQntuples(res); i++) { char *dbname = PQgetvalue(res, i, 0); - const char *create_opts; + char *oid = PQgetvalue(res, i, 1); + const char *create_opts = ""; int ret; /* Skip template0, even if it's not marked !datallowconn. */ @@ -1612,9 +1683,27 @@ dumpDatabases(PGconn *conn) continue; } + /* + * If this is not a plain format dump, then append dboid and dbname to + * the map.dat file. + */ + if (archDumpFormat != archNull) + { + if (archDumpFormat == archCustom) + snprintf(dbfilepath, MAXPGPATH, "\"%s\"/\"%s\".dmp", db_subdir, oid); + else if (archDumpFormat == archTar) + snprintf(dbfilepath, MAXPGPATH, "\"%s\"/\"%s\".tar", db_subdir, oid); + else + snprintf(dbfilepath, MAXPGPATH, "\"%s\"/\"%s\"", db_subdir, oid); + + /* Put one line entry for dboid and dbname in map file. */ + fprintf(map_file, "%s %s\n", oid, dbname); + } + pg_log_info("dumping database \"%s\"", dbname); - fprintf(OPF, "--\n-- Database \"%s\" dump\n--\n\n", dbname); + if (archDumpFormat == archNull) + fprintf(OPF, "--\n-- Database \"%s\" dump\n--\n\n", dbname); /* * We assume that "template1" and "postgres" already exist in the @@ -1628,12 +1717,9 @@ dumpDatabases(PGconn *conn) { if (output_clean) create_opts = "--clean --create"; - else - { - create_opts = ""; - /* Since pg_dump won't emit a \connect command, we must */ + /* Since pg_dump won't emit a \connect command, we must */ + else if (archDumpFormat == archNull) fprintf(OPF, "\\connect %s\n\n", dbname); - } } else create_opts = "--create"; @@ -1641,19 +1727,30 @@ dumpDatabases(PGconn *conn) if (filename) fclose(OPF); - ret = runPgDump(dbname, create_opts); + ret = runPgDump(dbname, create_opts, dbfilepath, archDumpFormat); if (ret != 0) pg_fatal("pg_dump failed on database \"%s\", exiting", dbname); if (filename) { - OPF = fopen(filename, PG_BINARY_A); + char global_path[MAXPGPATH]; + + if (archDumpFormat != archNull) + snprintf(global_path, MAXPGPATH, "%s/global.dat", filename); + else + snprintf(global_path, MAXPGPATH, "%s", filename); + + OPF = fopen(global_path, PG_BINARY_A); if (!OPF) pg_fatal("could not re-open the output file \"%s\": %m", - filename); + global_path); } } + /* Close map file */ + if (archDumpFormat != archNull) + fclose(map_file); + PQclear(res); } @@ -1663,7 +1760,8 @@ dumpDatabases(PGconn *conn) * Run pg_dump on dbname, with specified options. */ static int -runPgDump(const char *dbname, const char *create_opts) +runPgDump(const char *dbname, const char *create_opts, char *dbfile, + ArchiveFormat archDumpFormat) { PQExpBufferData connstrbuf; PQExpBufferData cmd; @@ -1672,17 +1770,36 @@ runPgDump(const char *dbname, const char *create_opts) initPQExpBuffer(&connstrbuf); initPQExpBuffer(&cmd); - printfPQExpBuffer(&cmd, "\"%s\" %s %s", pg_dump_bin, - pgdumpopts->data, create_opts); - /* - * If we have a filename, use the undocumented plain-append pg_dump - * format. + * If this is not a plain format dump, then append file name and dump + * format to the pg_dump command to get archive dump. */ - if (filename) - appendPQExpBufferStr(&cmd, " -Fa "); + if (archDumpFormat != archNull) + { + printfPQExpBuffer(&cmd, "\"%s\" -f %s %s", pg_dump_bin, + dbfile, create_opts); + + if (archDumpFormat == archDirectory) + appendPQExpBufferStr(&cmd, " --format=directory "); + else if (archDumpFormat == archCustom) + appendPQExpBufferStr(&cmd, " --format=custom "); + else if (archDumpFormat == archTar) + appendPQExpBufferStr(&cmd, " --format=tar "); + } else - appendPQExpBufferStr(&cmd, " -Fp "); + { + printfPQExpBuffer(&cmd, "\"%s\" %s %s", pg_dump_bin, + pgdumpopts->data, create_opts); + + /* + * If we have a filename, use the undocumented plain-append pg_dump + * format. + */ + if (filename) + appendPQExpBufferStr(&cmd, " -Fa "); + else + appendPQExpBufferStr(&cmd, " -Fp "); + } /* * Append the database name to the already-constructed stem of connection @@ -1827,3 +1944,91 @@ read_dumpall_filters(const char *filename, SimpleStringList *pattern) filter_free(&fstate); } + +/* + * create_or_open_dir + * + * This will create a new directory with given name. If there is already same + * empty directory exist, then use it. + */ +static void +create_or_open_dir(const char *dirname) +{ + struct stat st; + bool is_empty = false; + + /* we accept an empty existing directory */ + if (stat(dirname, &st) == 0 && S_ISDIR(st.st_mode)) + { + DIR *dir = opendir(dirname); + + if (dir) + { + struct dirent *d; + + is_empty = true; + + while (errno = 0, (d = readdir(dir))) + { + if (strcmp(d->d_name, ".") != 0 && strcmp(d->d_name, "..") != 0) + { + is_empty = false; + break; + } + } + + if (errno) + pg_fatal("could not read directory \"%s\": %m", + dirname); + + if (closedir(dir)) + pg_fatal("could not close directory \"%s\": %m", + dirname); + } + + if (!is_empty) + { + pg_log_error("directory \"%s\" exists but is not empty", dirname); + pg_log_error_hint("If you want to dump data on this directory, either remove or empty " + "this directory \"%s\" or run %s " + "with an argument other than \"%s\".", + dirname, progname, dirname); + exit_nicely(1); + } + } + else if (mkdir(dirname, 0700) < 0) + pg_fatal("could not create directory \"%s\": %m", dirname); +} + +/* + * parseDumpFormat + * + * This will validate dump formats. + */ +static ArchiveFormat +parseDumpFormat(const char *format) +{ + ArchiveFormat archDumpFormat; + + if (pg_strcasecmp(format, "c") == 0) + archDumpFormat = archCustom; + else if (pg_strcasecmp(format, "custom") == 0) + archDumpFormat = archCustom; + else if (pg_strcasecmp(format, "d") == 0) + archDumpFormat = archDirectory; + else if (pg_strcasecmp(format, "directory") == 0) + archDumpFormat = archDirectory; + else if (pg_strcasecmp(format, "p") == 0) + archDumpFormat = archNull; + else if (pg_strcasecmp(format, "plain") == 0) + archDumpFormat = archNull; + else if (pg_strcasecmp(format, "t") == 0) + archDumpFormat = archTar; + else if (pg_strcasecmp(format, "tar") == 0) + archDumpFormat = archTar; + else + pg_fatal("unrecognized archive format \"%s\"; please specify \"c\", \"d\", \"p\", or \"t\"", + format); + + return archDumpFormat; +} diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c index 47f7b0dd3a1..175f28a7421 100644 --- a/src/bin/pg_dump/pg_restore.c +++ b/src/bin/pg_dump/pg_restore.c @@ -2,7 +2,7 @@ * * pg_restore.c * pg_restore is an utility extracting postgres database definitions - * from a backup archive created by pg_dump using the archiver + * from a backup archive created by pg_dump/pg_dumpall using the archiver * interface. * * pg_restore will read the backup archive and @@ -41,11 +41,15 @@ #include "postgres_fe.h" #include <ctype.h> +#include <sys/stat.h> #ifdef HAVE_TERMIOS_H #include <termios.h> #endif +#include "common/string.h" +#include "connectdb.h" #include "fe_utils/option_utils.h" +#include "fe_utils/string_utils.h" #include "filter.h" #include "getopt_long.h" #include "parallel.h" @@ -53,18 +57,35 @@ static void usage(const char *progname); static void read_restore_filters(const char *filename, RestoreOptions *opts); +static bool file_exists_in_directory(const char *dir, const char *filename); +static int restore_one_database(const char *inputFileSpec, RestoreOptions *opts, + int numWorkers, bool append_data, int num); +static int read_one_statement(StringInfo inBuf, FILE *pfile); +static int restore_all_databases(PGconn *conn, const char *dumpdirpath, + SimpleStringList db_exclude_patterns, RestoreOptions *opts, int numWorkers); +static int process_global_sql_commands(PGconn *conn, const char *dumpdirpath, + const char *outfile); +static void copy_or_print_global_file(const char *outfile, FILE *pfile); +static int get_dbnames_list_to_restore(PGconn *conn, + SimpleOidStringList *dbname_oid_list, + SimpleStringList db_exclude_patterns); +static int get_dbname_oid_list_from_mfile(const char *dumpdirpath, + SimpleOidStringList *dbname_oid_list); +static size_t quote_literal_internal(char *dst, const char *src, size_t len); +static char *quote_literal_cstr(const char *rawstr); int main(int argc, char **argv) { RestoreOptions *opts; int c; - int exit_code; int numWorkers = 1; - Archive *AH; char *inputFileSpec; bool data_only = false; bool schema_only = false; + int n_errors = 0; + bool globals_only = false; + SimpleStringList db_exclude_patterns = {NULL, NULL}; static int disable_triggers = 0; static int enable_row_security = 0; static int if_exists = 0; @@ -90,6 +111,7 @@ main(int argc, char **argv) {"clean", 0, NULL, 'c'}, {"create", 0, NULL, 'C'}, {"data-only", 0, NULL, 'a'}, + {"globals-only", 0, NULL, 'g'}, {"dbname", 1, NULL, 'd'}, {"exit-on-error", 0, NULL, 'e'}, {"exclude-schema", 1, NULL, 'N'}, @@ -144,6 +166,7 @@ main(int argc, char **argv) {"with-statistics", no_argument, &with_statistics, 1}, {"statistics-only", no_argument, &statistics_only, 1}, {"filter", required_argument, NULL, 4}, + {"exclude-database", required_argument, NULL, 6}, {NULL, 0, NULL, 0} }; @@ -172,7 +195,7 @@ main(int argc, char **argv) } } - while ((c = getopt_long(argc, argv, "acCd:ef:F:h:I:j:lL:n:N:Op:P:RsS:t:T:U:vwWx1", + while ((c = getopt_long(argc, argv, "acCd:ef:F:gh:I:j:lL:n:N:Op:P:RsS:t:T:U:vwWx1", cmdopts, NULL)) != -1) { switch (c) @@ -199,11 +222,14 @@ main(int argc, char **argv) if (strlen(optarg) != 0) opts->formatName = pg_strdup(optarg); break; + case 'g': + /* restore only global.dat file from directory */ + globals_only = true; + break; case 'h': if (strlen(optarg) != 0) opts->cparams.pghost = pg_strdup(optarg); break; - case 'j': /* number of restore jobs */ if (!option_parse_int(optarg, "-j/--jobs", 1, PG_MAX_JOBS, @@ -318,6 +344,9 @@ main(int argc, char **argv) exit(1); opts->exit_on_error = true; break; + case 6: /* database patterns to skip */ + simple_string_list_append(&db_exclude_patterns, optarg); + break; default: /* getopt_long already emitted a complaint */ @@ -345,6 +374,13 @@ main(int argc, char **argv) if (!opts->cparams.dbname && !opts->filename && !opts->tocSummary) pg_fatal("one of -d/--dbname and -f/--file must be specified"); + if (db_exclude_patterns.head != NULL && globals_only) + { + pg_log_error("option --exclude-database cannot be used together with -g/--globals-only"); + pg_log_error_hint("Try \"%s --help\" for more information.", progname); + exit_nicely(1); + } + /* Should get at most one of -d and -f, else user is confused */ if (opts->cparams.dbname) { @@ -452,6 +488,113 @@ main(int argc, char **argv) opts->formatName); } + /* + * If toc.dat file does not present in current path, then check for + * global.dat. If global.dat file is present, then restore all the + * databases from map.dat(if exist) file list and skip restoring for + * --exclude-database patterns. + */ + if (inputFileSpec != NULL && !file_exists_in_directory(inputFileSpec, "toc.dat") && + file_exists_in_directory(inputFileSpec, "global.dat")) + { + PGconn *conn = NULL; /* Connection to restore global sql + * commands. */ + + /* + * User is suggested to use single database dump for --list option. + */ + if (opts->tocSummary) + pg_fatal("option -l/--list cannot be used when restoring multiple databases by archive of pg_dumpall"); + + /* + * To restore multiple databases, -C (create database) option should + * be specified. Even there is single database in dump, report error + * because it might be possible that database hasn't created so better + * we report error. + */ + if (!globals_only && opts->createDB != 1) + { + pg_log_error("-C/--create option should be specified when restoring multiple databases by archive of pg_dumpall"); + pg_log_error_hint("Try \"%s --help\" for more information.", progname); + pg_log_error_hint("If db is already created and dump has single db dump, then use particular dump file."); + exit_nicely(1); + } + + /* + * Connect to database to execute global sql commands from global.dat + * file. + */ + if (opts->cparams.dbname) + { + conn = ConnectDatabase(opts->cparams.dbname, NULL, opts->cparams.pghost, + opts->cparams.pgport, opts->cparams.username, TRI_DEFAULT, + false, progname, NULL, NULL, NULL, NULL); + + + if (!conn) + pg_fatal("could not connect to database \"%s\"", opts->cparams.dbname); + } + + /* If globals-only, then return from here. */ + if (globals_only) + { + /* + * Open global.dat file and execute/append all the global sql + * commands. + */ + n_errors = process_global_sql_commands(conn, inputFileSpec, + opts->filename); + + if (conn) + PQfinish(conn); + + pg_log_info("databases restoring is skipped as -g/--globals-only option is specified"); + } + else + { + /* Now restore all the databases from map.dat file. */ + n_errors = restore_all_databases(conn, inputFileSpec, db_exclude_patterns, + opts, numWorkers); + } + + /* Free db pattern list. */ + simple_string_list_destroy(&db_exclude_patterns); + } + else /* process if global.dat file does not exist. */ + { + if (db_exclude_patterns.head != NULL) + pg_fatal("option --exclude-database can be used only when restoring multiple databases by archive of pg_dumpall"); + + if (globals_only) + pg_fatal("option -g/--globals-only can be used only when restoring multiple databases by archive of pg_dumpall"); + + n_errors = restore_one_database(inputFileSpec, opts, numWorkers, false, 0); + } + + /* Done, print a summary of ignored errors during restore. */ + if (n_errors) + { + pg_log_warning("errors ignored on restore: %d", n_errors); + return 1; + } + + return 0; +} + +/* + * restore_one_database + * + * This will restore one database using toc.dat file. + * + * returns the number of errors while doing restore. + */ +static int +restore_one_database(const char *inputFileSpec, RestoreOptions *opts, + int numWorkers, bool append_data, int num) +{ + Archive *AH; + int n_errors; + AH = OpenArchive(inputFileSpec, opts->format); SetArchiveOptions(AH, NULL, opts); @@ -459,9 +602,15 @@ main(int argc, char **argv) /* * We don't have a connection yet but that doesn't matter. The connection * is initialized to NULL and if we terminate through exit_nicely() while - * it's still NULL, the cleanup function will just be a no-op. + * it's still NULL, the cleanup function will just be a no-op. If we are + * restoring multiple databases, then only update AX handle for cleanup as + * previous entry was already in array and we had closed previous + * connection so we can use same slot from array. */ - on_exit_close_archive(AH); + if (!append_data || num == 0) + on_exit_close_archive(AH); + else + replace_on_exit_close_archive(AH); /* Let the archiver know how noisy to be */ AH->verbose = opts->verbose; @@ -481,25 +630,22 @@ main(int argc, char **argv) else { ProcessArchiveRestoreOptions(AH); - RestoreArchive(AH); + RestoreArchive(AH, append_data); } - /* done, print a summary of ignored errors */ - if (AH->n_errors) - pg_log_warning("errors ignored on restore: %d", AH->n_errors); + n_errors = AH->n_errors; /* AH may be freed in CloseArchive? */ - exit_code = AH->n_errors ? 1 : 0; - CloseArchive(AH); - return exit_code; + return n_errors; } static void usage(const char *progname) { - printf(_("%s restores a PostgreSQL database from an archive created by pg_dump.\n\n"), progname); + printf(_("%s restores a PostgreSQL database from an archive created by pg_dump.\n" + "If archive is created by pg_dumpall, then restores multiple databases also. \n\n"), progname); printf(_("Usage:\n")); printf(_(" %s [OPTION]... [FILE]\n"), progname); @@ -517,6 +663,7 @@ usage(const char *progname) printf(_(" -c, --clean clean (drop) database objects before recreating\n")); printf(_(" -C, --create create the target database\n")); printf(_(" -e, --exit-on-error exit on error, default is to continue\n")); + printf(_(" -g, --globals-only restore only global objects, no databases\n")); printf(_(" -I, --index=NAME restore named index\n")); printf(_(" -j, --jobs=NUM use this many parallel jobs to restore\n")); printf(_(" -L, --use-list=FILENAME use table of contents from this file for\n" @@ -529,6 +676,7 @@ usage(const char *progname) printf(_(" -S, --superuser=NAME superuser user name to use for disabling triggers\n")); printf(_(" -t, --table=NAME restore named relation (table, view, etc.)\n")); printf(_(" -T, --trigger=NAME restore named trigger\n")); + printf(_(" --exclude-database=PATTERN exclude databases whose name matches with pattern\n")); printf(_(" -x, --no-privileges skip restoration of access privileges (grant/revoke)\n")); printf(_(" -1, --single-transaction restore as a single transaction\n")); printf(_(" --disable-triggers disable triggers during data-only restore\n")); @@ -569,8 +717,8 @@ usage(const char *progname) printf(_(" --role=ROLENAME do SET ROLE before restore\n")); printf(_("\n" - "The options -I, -n, -N, -P, -t, -T, and --section can be combined and specified\n" - "multiple times to select multiple objects.\n")); + "The options -I, -n, -N, -P, -t, -T, --section, and --exclude-database can be combined\n" + "and specified multiple times to select multiple objects.\n")); printf(_("\nIf no input file name is supplied, then standard input is used.\n\n")); printf(_("Report bugs to <%s>.\n"), PACKAGE_BUGREPORT); printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL); @@ -675,3 +823,621 @@ read_restore_filters(const char *filename, RestoreOptions *opts) filter_free(&fstate); } + +/* + * file_exists_in_directory + * + * Returns true if file exist in current directory. + */ +static bool +file_exists_in_directory(const char *dir, const char *filename) +{ + struct stat st; + char buf[MAXPGPATH]; + + if (snprintf(buf, MAXPGPATH, "%s/%s", dir, filename) >= MAXPGPATH) + pg_fatal("directory name too long: \"%s\"", dir); + + return (stat(buf, &st) == 0 && S_ISREG(st.st_mode)); +} + +/* + * read_one_statement + * + * This will start reading from passed file pointer using fgetc and read till + * semicolon(sql statement terminator for global.dat file) + * + * EOF is returned if end-of-file input is seen; time to shut down. + */ + +static int +read_one_statement(StringInfo inBuf, FILE *pfile) +{ + int c; /* character read from getc() */ + int m; + + StringInfoData q; + + initStringInfo(&q); + + resetStringInfo(inBuf); + + /* + * Read characters until EOF or the appropriate delimiter is seen. + */ + while ((c = fgetc(pfile)) != EOF) + { + if (c != '\'' && c != '"' && c != '\n' && c != ';') + { + appendStringInfoChar(inBuf, (char) c); + while ((c = fgetc(pfile)) != EOF) + { + if (c != '\'' && c != '"' && c != ';' && c != '\n') + appendStringInfoChar(inBuf, (char) c); + else + break; + } + } + + if (c == '\'' || c == '"') + { + appendStringInfoChar(&q, (char) c); + m = c; + + while ((c = fgetc(pfile)) != EOF) + { + appendStringInfoChar(&q, (char) c); + + if (c == m) + { + appendStringInfoString(inBuf, q.data); + resetStringInfo(&q); + break; + } + } + } + + if (c == ';') + { + appendStringInfoChar(inBuf, (char) ';'); + break; + } + + if (c == '\n') + appendStringInfoChar(inBuf, (char) '\n'); + } + + /* No input before EOF signal means time to quit. */ + if (c == EOF && inBuf->len == 0) + return EOF; + + /* Add '\0' to make it look the same as message case. */ + appendStringInfoChar(inBuf, (char) '\0'); + + return 'Q'; +} + +/* + * get_dbnames_list_to_restore + * + * This will mark for skipping any entries from dbname_oid_list that pattern match an + * entry in the db_exclude_patterns list. + * + * Returns the number of database to be restored. + * + */ +static int +get_dbnames_list_to_restore(PGconn *conn, + SimpleOidStringList *dbname_oid_list, + SimpleStringList db_exclude_patterns) +{ + int count_db = 0; + PQExpBuffer query; + PGresult *res; + + query = createPQExpBuffer(); + + if (!conn) + pg_log_info("considering PATTERN as NAME for --exclude-database option as no db connection while doing pg_restore."); + + /* + * Process one by one all dbnames and if specified to skip restoring, then + * remove dbname from list. + */ + for (SimpleOidStringListCell * db_cell = dbname_oid_list->head; + db_cell; db_cell = db_cell->next) + { + bool skip_db_restore = false; + + for (SimpleStringListCell *pat_cell = db_exclude_patterns.head; pat_cell; pat_cell = pat_cell->next) + { + /* + * the construct pattern matching query: SELECT 1 WHERE XXX + * OPERATOR(pg_catalog.~) '^(PATTERN)$' COLLATE pg_catalog.default + * + * XXX represents the string literal database name derived from + * the dbname_oid_list, which is initially extracted from the + * map.dat file located in the backup directory. that's why we + * need quote_literal_cstr. + * + * If no db connection, then consider PATTERN as NAME. + */ + if (pg_strcasecmp(db_cell->str, pat_cell->val) == 0) + skip_db_restore = true; + else if (conn) + { + int dotcnt; + + appendPQExpBufferStr(query, "SELECT 1 "); + processSQLNamePattern(conn, query, pat_cell->val, false, + false, NULL, quote_literal_cstr(db_cell->str), + NULL, NULL, NULL, &dotcnt); + + if (dotcnt > 0) + { + pg_log_error("improper qualified name (too many dotted names): %s", + db_cell->str); + PQfinish(conn); + exit_nicely(1); + } + + res = executeQuery(conn, query->data); + + if ((PQresultStatus(res) == PGRES_TUPLES_OK) && PQntuples(res)) + { + skip_db_restore = true; + pg_log_info("database \"%s\" matches exclude pattern: \"%s\"", db_cell->str, pat_cell->val); + } + + PQclear(res); + resetPQExpBuffer(query); + } + + if (skip_db_restore) + break; + } + + /* Increment count if database needs to be restored. */ + if (skip_db_restore) + { + pg_log_info("excluding database \"%s\"", db_cell->str); + db_cell->oid = InvalidOid; + } + else + { + count_db++; + } + } + + return count_db; +} + +/* + * get_dbname_oid_list_from_mfile + * + * Open map.dat file and read line by line and then prepare a list of database + * names and corresponding db_oid. + * + * Returns, total number of database names in map.dat file. + */ +static int +get_dbname_oid_list_from_mfile(const char *dumpdirpath, SimpleOidStringList *dbname_oid_list) +{ + FILE *pfile; + char map_file_path[MAXPGPATH]; + char line[MAXPGPATH]; + int count = 0; + + /* + * If there is only global.dat file in dump, then return from here as + * there is no database to restore. + */ + if (!file_exists_in_directory(dumpdirpath, "map.dat")) + { + pg_log_info("databases restoring is skipped as map.dat file is not present in \"%s\"", dumpdirpath); + return 0; + } + + snprintf(map_file_path, MAXPGPATH, "%s/map.dat", dumpdirpath); + + /* Open map.dat file. */ + pfile = fopen(map_file_path, PG_BINARY_R); + + if (pfile == NULL) + pg_fatal("could not open map.dat file: \"%s\"", map_file_path); + + /* Append all the dbname and db_oid to the list. */ + while ((fgets(line, MAXPGPATH, pfile)) != NULL) + { + Oid db_oid = InvalidOid; + char db_oid_str[MAXPGPATH + 1] = {'\0'}; + char dbname[MAXPGPATH + 1] = {'\0'}; + + /* Extract dboid. */ + sscanf(line, "%u", &db_oid); + sscanf(line, "%20s", db_oid_str); + + /* Now copy dbname. */ + strcpy(dbname, line + strlen(db_oid_str) + 1); + + /* Remove \n from dbanme. */ + dbname[strlen(dbname) - 1] = '\0'; + + pg_log_info("found database \"%s\" (OID: %u) in map.dat file while restoring.", dbname, db_oid); + + /* Report error and exit if the file has any corrupted data. */ + if (!OidIsValid(db_oid) || strlen(dbname) == 0) + pg_fatal("invalid entry in map.dat file at line : %d", count + 1); + + /* + * XXX : before adding dbname into list, we can verify that this db + * needs to skipped for restore or not but as of now, we are making a + * list of all the databases. + */ + simple_oid_string_list_append(dbname_oid_list, db_oid, dbname); + count++; + } + + /* Close map.dat file. */ + fclose(pfile); + + return count; +} + +/* + * restore_all_databases + * + * This will restore databases those dumps are present in + * directory based on map.dat file mapping. + * + * This will skip restoring for databases that are specified with + * exclude-database option. + * + * returns, number of errors while doing restore. + */ +static int +restore_all_databases(PGconn *conn, const char *dumpdirpath, + SimpleStringList db_exclude_patterns, RestoreOptions *opts, + int numWorkers) +{ + SimpleOidStringList dbname_oid_list = {NULL, NULL}; + int num_db_restore = 0; + int num_total_db; + int n_errors_total; + int count = 0; + char *connected_db = NULL; + bool dumpData = opts->dumpData; + bool dumpSchema = opts->dumpSchema; + bool dumpStatistics = opts->dumpSchema; + + /* Save db name to reuse it for all the database. */ + if (opts->cparams.dbname) + connected_db = opts->cparams.dbname; + + num_total_db = get_dbname_oid_list_from_mfile(dumpdirpath, &dbname_oid_list); + + /* + * If map.dat has no entry, return from here after processing global.dat + * file. + */ + if (dbname_oid_list.head == NULL) + return process_global_sql_commands(conn, dumpdirpath, opts->filename); + + pg_log_info("found total %d database names in map.dat file", num_total_db); + + if (!conn) + { + pg_log_info("trying to connect database \"postgres\" to dump into out file"); + + conn = ConnectDatabase("postgres", NULL, opts->cparams.pghost, + opts->cparams.pgport, opts->cparams.username, TRI_DEFAULT, + false, progname, NULL, NULL, NULL, NULL); + + /* Try with template1. */ + if (!conn) + { + pg_log_info("trying to connect database \"template1\" as failed to connect to database \"postgres\" to dump into out file"); + + conn = ConnectDatabase("template1", NULL, opts->cparams.pghost, + opts->cparams.pgport, opts->cparams.username, TRI_DEFAULT, + false, progname, NULL, NULL, NULL, NULL); + } + } + + /* + * processing pg_restore --exclude-database=PATTERN/NAME if no connection. + */ + num_db_restore = get_dbnames_list_to_restore(conn, &dbname_oid_list, + db_exclude_patterns); + + /* Open global.dat file and execute/append all the global sql commands. */ + n_errors_total = process_global_sql_commands(conn, dumpdirpath, opts->filename); + + /* Close the db connection as we are done with globals and patterns. */ + if (conn) + PQfinish(conn); + + /* Exit if no db needs to be restored. */ + if (dbname_oid_list.head == NULL || num_db_restore == 0) + { + pg_log_info("no database needs to restore out of %d databases", num_total_db); + return n_errors_total; + } + + pg_log_info("needs to restore %d databases out of %d databases", num_db_restore, num_total_db); + + /* + * Till now, we made a list of databases, those needs to be restored after + * skipping names of exclude-database. Now we can launch parallel workers + * to restore these databases. + */ + for (SimpleOidStringListCell * db_cell = dbname_oid_list.head; + db_cell; db_cell = db_cell->next) + { + char subdirpath[MAXPGPATH]; + char subdirdbpath[MAXPGPATH]; + char dbfilename[MAXPGPATH]; + int n_errors; + + /* ignore dbs marked for skipping */ + if (db_cell->oid == InvalidOid) + continue; + + /* + * We need to reset override_dbname so that objects can be restored + * into already created database. (used with -d/--dbname option) + */ + if (opts->cparams.override_dbname) + { + pfree(opts->cparams.override_dbname); + opts->cparams.override_dbname = NULL; + } + + snprintf(subdirdbpath, MAXPGPATH, "%s/databases", dumpdirpath); + + /* + * Validate database dump file. If there is .tar or .dmp file exist + * then consider particular file, otherwise just append dboid to the + * databases folder. + */ + snprintf(dbfilename, MAXPGPATH, "%u.tar", db_cell->oid); + if (file_exists_in_directory(subdirdbpath, dbfilename)) + snprintf(subdirpath, MAXPGPATH, "%s/databases/%u.tar", dumpdirpath, db_cell->oid); + else + { + snprintf(dbfilename, MAXPGPATH, "%u.dmp", db_cell->oid); + + if (file_exists_in_directory(subdirdbpath, dbfilename)) + snprintf(subdirpath, MAXPGPATH, "%s/databases/%u.dmp", dumpdirpath, db_cell->oid); + else + snprintf(subdirpath, MAXPGPATH, "%s/databases/%u", dumpdirpath, db_cell->oid); + } + + pg_log_info("restoring database \"%s\"", db_cell->str); + + /* If database is already created, then don't set createDB flag. */ + if (opts->cparams.dbname) + { + PGconn *test_conn; + + test_conn = ConnectDatabase(db_cell->str, NULL, opts->cparams.pghost, + opts->cparams.pgport, opts->cparams.username, TRI_DEFAULT, + false, progname, NULL, NULL, NULL, NULL); + if (test_conn) + { + PQfinish(test_conn); + + /* Use already created database for connection. */ + opts->createDB = 0; + opts->cparams.dbname = db_cell->str; + } + else + { + /* we'll have to create it */ + opts->createDB = 1; + opts->cparams.dbname = connected_db; + } + } + + /* + * Reset flags - might have been reset in pg_backup_archiver.c by the + * previous restore. + */ + opts->dumpData = dumpData; + opts->dumpSchema = dumpSchema; + opts->dumpStatistics = dumpStatistics; + + /* Restore single database. */ + n_errors = restore_one_database(subdirpath, opts, numWorkers, true, count); + + /* Print a summary of ignored errors during single database restore. */ + if (n_errors) + { + n_errors_total += n_errors; + pg_log_warning("errors ignored on database \"%s\" restore: %d", db_cell->str, n_errors); + } + + count++; + } + + /* Log number of processed databases. */ + pg_log_info("number of restored databases are %d", num_db_restore); + + /* Free dbname and dboid list. */ + simple_oid_string_list_destroy(&dbname_oid_list); + + return n_errors_total; +} + +/* + * process_global_sql_commands + * + * This will open global.dat file and will execute all global sql commands one + * by one statement. + * Semicolon is considered as statement terminator. If outfile is passed, then + * this will copy all sql commands into outfile rather then executing them. + * + * returns the number of errors while processing global.dat + */ +static int +process_global_sql_commands(PGconn *conn, const char *dumpdirpath, const char *outfile) +{ + char global_file_path[MAXPGPATH]; + PGresult *result; + StringInfoData sqlstatement, + user_create; + FILE *pfile; + int n_errors = 0; + + snprintf(global_file_path, MAXPGPATH, "%s/global.dat", dumpdirpath); + + /* Open global.dat file. */ + pfile = fopen(global_file_path, PG_BINARY_R); + + if (pfile == NULL) + pg_fatal("could not open global.dat file: \"%s\"", global_file_path); + + /* + * If outfile is given, then just copy all global.dat file data into + * outfile. + */ + if (outfile) + { + copy_or_print_global_file(outfile, pfile); + return 0; + } + + /* Init sqlstatement to append commands. */ + initStringInfo(&sqlstatement); + + /* creation statement for our current role */ + initStringInfo(&user_create); + appendStringInfoString(&user_create, "CREATE ROLE "); + /* should use fmtId here, but we don't know the encoding */ + appendStringInfoString(&user_create, PQuser(conn)); + appendStringInfoString(&user_create, ";"); + + /* Process file till EOF and execute sql statements. */ + while (read_one_statement(&sqlstatement, pfile) != EOF) + { + /* don't try to create the role we are connected as */ + if (strstr(sqlstatement.data, user_create.data)) + continue; + + pg_log_info("executing query: %s", sqlstatement.data); + result = PQexec(conn, sqlstatement.data); + + switch (PQresultStatus(result)) + { + case PGRES_COMMAND_OK: + case PGRES_TUPLES_OK: + case PGRES_EMPTY_QUERY: + break; + default: + n_errors++; + pg_log_error("could not execute query: \"%s\" \nCommand was: \"%s\"", PQerrorMessage(conn), sqlstatement.data); + } + PQclear(result); + } + + /* Print a summary of ignored errors during global.dat. */ + if (n_errors) + pg_log_warning("errors ignored on global.dat file restore: %d", n_errors); + + fclose(pfile); + + return n_errors; +} + +/* + * copy_or_print_global_file + * + * This will copy global.dat file into out file. If "-" is used as outfile, + * then print commands to the stdout. + */ +static void +copy_or_print_global_file(const char *outfile, FILE *pfile) +{ + char out_file_path[MAXPGPATH]; + FILE *OPF; + int c; + + /* "-" is used for stdout. */ + if (strcmp(outfile, "-") == 0) + OPF = stdout; + else + { + snprintf(out_file_path, MAXPGPATH, "%s", outfile); + OPF = fopen(out_file_path, PG_BINARY_W); + + if (OPF == NULL) + { + fclose(pfile); + pg_fatal("could not open file: \"%s\"", outfile); + } + } + + /* Append global.dat into out file or print to the stdout. */ + while ((c = fgetc(pfile)) != EOF) + fputc(c, OPF); + + fclose(pfile); + + /* Close out file. */ + if (strcmp(outfile, "-") != 0) + fclose(OPF); +} + +/* + * quote_literal_internal + */ +static size_t +quote_literal_internal(char *dst, const char *src, size_t len) +{ + const char *s; + char *savedst = dst; + + for (s = src; s < src + len; s++) + { + if (*s == '\\') + { + *dst++ = ESCAPE_STRING_SYNTAX; + break; + } + } + + *dst++ = '\''; + while (len-- > 0) + { + if (SQL_STR_DOUBLE(*src, true)) + *dst++ = *src; + *dst++ = *src++; + } + *dst++ = '\''; + + return dst - savedst; +} + +/* + * quote_literal_cstr + * + * returns a properly quoted literal + * copied from src/backend/utils/adt/quote.c + */ +static char * +quote_literal_cstr(const char *rawstr) +{ + char *result; + int len; + int newlen; + + len = strlen(rawstr); + + /* We make a worst-case result area; wasting a little space is OK */ + result = pg_malloc(len * 2 + 3 + 1); + + newlen = quote_literal_internal(result, rawstr, len); + result[newlen] = '\0'; + + return result; +} diff --git a/src/bin/pg_dump/t/001_basic.pl b/src/bin/pg_dump/t/001_basic.pl index 37d893d5e6a..0bbcdbe84a7 100644 --- a/src/bin/pg_dump/t/001_basic.pl +++ b/src/bin/pg_dump/t/001_basic.pl @@ -237,6 +237,11 @@ command_fails_like( 'pg_restore: options -C\/--create and -1\/--single-transaction cannot be used together' ); +command_fails_like( + [ 'pg_restore', '--exclude-database=foo', '--globals-only', '-d', 'xxx' ], + qr/\Qpg_restore: error: option --exclude-database cannot be used together with -g\/--globals-only\E/, + 'pg_restore: option --exclude-database cannot be used together with -g/--globals-only'); + # also fails for -r and -t, but it seems pointless to add more tests for those. command_fails_like( [ 'pg_dumpall', '--exclude-database=foo', '--globals-only' ], @@ -244,4 +249,8 @@ command_fails_like( 'pg_dumpall: option --exclude-database cannot be used together with -g/--globals-only' ); +command_fails_like( + [ 'pg_dumpall', '--format', 'x' ], + qr/\Qpg_dumpall: error: unrecognized archive format "x";\E/, + 'pg_dumpall: unrecognized archive format'); done_testing(); -- 2.34.1 [text/x-patch] v20250403-0004-Add-more-TAP-tests-for-pg_dumpall.patch (10.2K, 5-v20250403-0004-Add-more-TAP-tests-for-pg_dumpall.patch) download | inline diff: From ddf415212e67d450d00e7357e38c5e784d62eeb3 Mon Sep 17 00:00:00 2001 From: Andrew Dunstan <[email protected]> Date: Thu, 3 Apr 2025 14:45:52 -0400 Subject: [PATCH v20250403 4/4] Add more TAP tests for pg_dumpall Author: Matheus Alcantara <[email protected]> --- src/bin/pg_dump/meson.build | 1 + src/bin/pg_dump/t/006_pg_dumpall.pl | 331 ++++++++++++++++++++++++++++ 2 files changed, 332 insertions(+) create mode 100644 src/bin/pg_dump/t/006_pg_dumpall.pl diff --git a/src/bin/pg_dump/meson.build b/src/bin/pg_dump/meson.build index 25989e8f16b..d8e9e101254 100644 --- a/src/bin/pg_dump/meson.build +++ b/src/bin/pg_dump/meson.build @@ -102,6 +102,7 @@ tests += { 't/003_pg_dump_with_server.pl', 't/004_pg_dump_parallel.pl', 't/005_pg_dump_filterfile.pl', + 't/006_pg_dumpall.pl', 't/010_dump_connstr.pl', ], }, diff --git a/src/bin/pg_dump/t/006_pg_dumpall.pl b/src/bin/pg_dump/t/006_pg_dumpall.pl new file mode 100644 index 00000000000..fdfd1ae990b --- /dev/null +++ b/src/bin/pg_dump/t/006_pg_dumpall.pl @@ -0,0 +1,331 @@ +# Copyright (c) 2021-2025, PostgreSQL Global Development Group + +use strict; +use warnings FATAL => 'all'; + +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +my $tempdir = PostgreSQL::Test::Utils::tempdir; +my $run_db = 'postgres'; +my $sep = $windows_os ? "\\" : "/"; + +# Tablespace locations used by "restore_tablespace" test case. +my $tablespace1 = "${tempdir}${sep}tbl1"; +my $tablespace2 = "${tempdir}${sep}tbl2"; +mkdir($tablespace1) || die "mkdir $tablespace1 $!"; +mkdir($tablespace2) || die "mkdir $tablespace2 $!"; + +# Scape tablespace locations on Windows. +$tablespace1 = $windows_os ? ($tablespace1 =~ s/\\/\\\\/gr) : $tablespace1; +$tablespace2 = $windows_os ? ($tablespace2 =~ s/\\/\\\\/gr) : $tablespace2; + +# Where pg_dumpall will be executed. +my $node = PostgreSQL::Test::Cluster->new('node'); +$node->init; +$node->start; + + +############################################################### +# Definition of the pg_dumpall test cases to run. +# +# Each of these test cases are named and those names are used for fail +# reporting and also to save the dump and restore information needed for the +# test to assert. +# +# The "setup_sql" is a psql valid script that contains SQL commands to execute +# before of actually execute the tests. The setups are all executed before of +# any test execution. +# +# The "dump_cmd" and "restore_cmd" are the commands that will be executed. The +# "restore_cmd" must have the --file flag to save the restore output so that we +# can assert on it. +# +# The "like" and "unlike" is a regexp that is used to match the pg_restore +# output. It must have at least one of then filled per test cases but it also +# can have both. See "excluding_databases" test case for example. +my %pgdumpall_runs = ( + restore_roles => { + setup_sql => ' + CREATE ROLE dumpall WITH ENCRYPTED PASSWORD \'admin\' SUPERUSER; + CREATE ROLE dumpall2 WITH REPLICATION CONNECTION LIMIT 10;', + dump_cmd => [ + 'pg_dumpall', + '--format' => 'directory', + '--file' => "$tempdir/restore_roles", + ], + restore_cmd => [ + 'pg_restore', '-C', + '--format' => 'directory', + '--file' => "$tempdir/restore_roles.sql", + "$tempdir/restore_roles", + ], + like => qr/ + ^\s*\QCREATE ROLE dumpall;\E\s*\n + \s*\QALTER ROLE dumpall WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'SCRAM-SHA-256\E + [^']+';\s*\n + \s*\QCREATE ROLE dumpall2;\E + \s*\QALTER ROLE dumpall2 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN REPLICATION NOBYPASSRLS CONNECTION LIMIT 10;\E + /xm + }, + + restore_tablespace => { + setup_sql => " + CREATE ROLE tap; + CREATE TABLESPACE tbl1 OWNER tap LOCATION '$tablespace1'; + CREATE TABLESPACE tbl2 OWNER tap LOCATION '$tablespace2' WITH (seq_page_cost=1.0);", + dump_cmd => [ + 'pg_dumpall', + '--format' => 'directory', + '--file' => "$tempdir/restore_tablespace", + ], + restore_cmd => [ + 'pg_restore', '-C', + '--format' => 'directory', + '--file' => "$tempdir/restore_tablespace.sql", + "$tempdir/restore_tablespace", + ], + # Match "E" as optional since it is added on LOCATION when running on + # Windows. + like => qr/^ + \n\QCREATE TABLESPACE tbl1 OWNER tap LOCATION \E(?:E)?\Q'$tablespace1';\E + \n\QCREATE TABLESPACE tbl2 OWNER tap LOCATION \E(?:E)?\Q'$tablespace2';\E + \n\QALTER TABLESPACE tbl2 SET (seq_page_cost=1.0);\E + /xm, + }, + + restore_grants => { + setup_sql => " + CREATE DATABASE tapgrantsdb; + CREATE SCHEMA private; + CREATE SEQUENCE serial START 101; + CREATE FUNCTION fn() RETURNS void AS \$\$ + BEGIN + END; + \$\$ LANGUAGE plpgsql; + CREATE ROLE super; + CREATE ROLE grant1; + CREATE ROLE grant2; + CREATE ROLE grant3; + CREATE ROLE grant4; + CREATE ROLE grant5; + CREATE ROLE grant6; + CREATE ROLE grant7; + CREATE ROLE grant8; + + CREATE TABLE t (id int); + + GRANT SELECT ON TABLE t TO grant1; + GRANT INSERT ON TABLE t TO grant2; + GRANT ALL PRIVILEGES ON TABLE t to grant3; + GRANT CONNECT, CREATE ON DATABASE tapgrantsdb TO grant4; + GRANT USAGE, CREATE ON SCHEMA private TO grant5; + GRANT USAGE, SELECT, UPDATE ON SEQUENCE serial TO grant6; + GRANT super TO grant7; + GRANT EXECUTE ON FUNCTION fn() TO grant8; + ", + dump_cmd => [ + 'pg_dumpall', + '--format' => 'directory', + '--file' => "$tempdir/restore_grants", + ], + restore_cmd => [ + 'pg_restore', '-C', + '--format' => 'directory', + '--file' => "$tempdir/restore_grants.sql", + "$tempdir/restore_grants", + ], + like => qr/^ + \n\QGRANT super TO grant7 WITH INHERIT TRUE GRANTED BY\E + (.*\n)* + \n\QGRANT ALL ON SCHEMA private TO grant5;\E + (.*\n)* + \n\QGRANT ALL ON FUNCTION public.fn() TO grant8;\E + (.*\n)* + \n\QGRANT ALL ON SEQUENCE public.serial TO grant6;\E + (.*\n)* + \n\QGRANT SELECT ON TABLE public.t TO grant1;\E + \n\QGRANT INSERT ON TABLE public.t TO grant2;\E + \n\QGRANT ALL ON TABLE public.t TO grant3;\E + (.*\n)* + \n\QGRANT CREATE,CONNECT ON DATABASE tapgrantsdb TO grant4;\E + /xm, + }, + + excluding_databases => { + setup_sql => 'CREATE DATABASE db1; + \c db1 + CREATE TABLE t1 (id int); + CREATE TABLE t2 (id int); + + CREATE DATABASE db2; + \c db2 + CREATE TABLE t3 (id int); + CREATE TABLE t4 (id int); + + CREATE DATABASE dbex3; + \c dbex3 + CREATE TABLE t5 (id int); + CREATE TABLE t6 (id int); + + CREATE DATABASE dbex4; + \c dbex4 + CREATE TABLE t7 (id int); + CREATE TABLE t8 (id int); + + CREATE DATABASE db5; + \c db5 + CREATE TABLE t9 (id int); + CREATE TABLE t10 (id int); + ', + dump_cmd => [ + 'pg_dumpall', + '--format' => 'directory', + '--file' => "$tempdir/excluding_databases", + '--exclude-database' => 'dbex*', + ], + restore_cmd => [ + 'pg_restore', '-C', + '--format' => 'directory', + '--file' => "$tempdir/excluding_databases.sql", + '--exclude-database' => 'db5', + "$tempdir/excluding_databases", + ], + like => qr/^ + \n\QCREATE DATABASE db1\E + (.*\n)* + \n\QCREATE TABLE public.t1 (\E + (.*\n)* + \n\QCREATE TABLE public.t2 (\E + (.*\n)* + \n\QCREATE DATABASE db2\E + (.*\n)* + \n\QCREATE TABLE public.t3 (\E + (.*\n)* + \n\QCREATE TABLE public.t4 (/xm, + unlike => qr/^ + \n\QCREATE DATABASE db3\E + (.*\n)* + \n\QCREATE TABLE public.t5 (\E + (.*\n)* + \n\QCREATE TABLE public.t6 (\E + (.*\n)* + \n\QCREATE DATABASE db4\E + (.*\n)* + \n\QCREATE TABLE public.t7 (\E + (.*\n)* + \n\QCREATE TABLE public.t8 (\E + \n\QCREATE DATABASE db5\E + (.*\n)* + \n\QCREATE TABLE public.t9 (\E + (.*\n)* + \n\QCREATE TABLE public.t10 (\E + /xm, + }, + + format_directory => { + setup_sql => + 'CREATE TABLE format_directory(a int, b boolean, c text);', + dump_cmd => [ + 'pg_dumpall', + '--format' => 'directory', + '--file' => "$tempdir/format_directory", + ], + restore_cmd => [ + 'pg_restore', '-C', + '--format' => 'directory', + '--file' => "$tempdir/format_directory.sql", + "$tempdir/format_directory", + ], + like => qr/^\n\QCREATE TABLE public.format_directory (/xm + }, + + format_tar => { + setup_sql => 'CREATE TABLE format_tar(id int);', + dump_cmd => [ + 'pg_dumpall', + '--format' => 'tar', + '--file' => "$tempdir/format_tar", + ], + restore_cmd => [ + 'pg_restore', '-C', + '--format' => 'tar', + '--file' => "$tempdir/format_tar.sql", + "$tempdir/format_tar", + ], + like => qr/^\n\QCREATE TABLE public.format_tar (/xm + }, + + format_custom => { + setup_sql => 'CREATE TABLE format_custom(a int, b boolean, c text);', + dump_cmd => [ + 'pg_dumpall', + '--format' => 'custom', + '--file' => "$tempdir/format_custom", + ], + restore_cmd => [ + 'pg_restore', '-C', + '--format' => 'custom', + '--file' => "$tempdir/format_custom.sql", + "$tempdir/format_custom", + ], + like => qr/^ \n\QCREATE TABLE public.format_custom (/xm + },); + + +# First execute the setup_sql +foreach my $run (sort keys %pgdumpall_runs) +{ + if ($pgdumpall_runs{$run}->{setup_sql}) + { + $node->safe_psql($run_db, $pgdumpall_runs{$run}->{setup_sql}); + } +} + +# Execute the tests +foreach my $run (sort keys %pgdumpall_runs) +{ + # Create a new target cluster to pg_restore each test case run so that we + # don't need to take care of the cleanup from the target cluster after each + # run. + my $target_node = PostgreSQL::Test::Cluster->new("target_$run"); + $target_node->init; + $target_node->start; + + # Dumpall from node cluster. + $node->command_ok(\@{ $pgdumpall_runs{$run}->{dump_cmd} }, + "$run: pg_dumpall runs"); + + # Restore the dump on "target_node" cluster. + my @restore_cmd = ( + @{ $pgdumpall_runs{$run}->{restore_cmd} }, + '--host', $target_node->host, '--port', $target_node->port); + + my ($stdout, $stderr) = run_command(\@restore_cmd); + + # pg_restore --file output file. + my $output_file = slurp_file("$tempdir/${run}.sql"); + + if (!($pgdumpall_runs{$run}->{like}) && !($pgdumpall_runs{$run}->{unlike})) + { + die "missing \"like\" or \"unlike\" in test \"$run\""; + } + + if ($pgdumpall_runs{$run}->{like}) + { + like($output_file, $pgdumpall_runs{$run}->{like}, "should dump $run"); + } + + if ($pgdumpall_runs{$run}->{unlike}) + { + unlike( + $output_file, + $pgdumpall_runs{$run}->{unlike}, + "should not dump $run"); + } +} + +$node->stop('fast'); + +done_testing(); -- 2.34.1 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Non-text mode for pg_dumpall @ 2025-04-04 08:22 Mahendra Singh Thalor <[email protected]> parent: Andrew Dunstan <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Mahendra Singh Thalor @ 2025-04-04 08:22 UTC (permalink / raw) To: Andrew Dunstan <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; jian he <[email protected]>; Srinath Reddy <[email protected]>; [email protected] On Fri, 4 Apr 2025 at 01:17, Andrew Dunstan <[email protected]> wrote: > > > On 2025-04-01 Tu 1:59 AM, Mahendra Singh Thalor wrote: > > On Mon, 31 Mar 2025 at 23:43, Álvaro Herrera <[email protected]> wrote: > >> Hi > >> > >> FWIW I don't think the on_exit_nicely business is in final shape just > >> yet. We're doing something super strange and novel about keeping track > >> of an array index, so that we can modify it later. Or something like > >> that, I think? That doesn't sound all that nice to me. Elsewhere it > >> was suggested that we need some way to keep track of the list of things > >> that need cleanup (a list of connections IIRC?) -- perhaps in a > >> thread-local variable or a global or something -- and we install the > >> cleanup function once, and that reads from the variable. The program > >> can add things to the list, or remove them, at will; and we don't need > >> to modify the cleanup function in any way. > >> > >> -- > >> Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ > > Thanks Álvaro for the feedback. > > > > I removed the old handling of on_exit_nicely_list from the last patch > > set and added one simple function to just update the archive handle in > > shutdown_info. (shutdown_info.AHX = AHX;) > > > > For first database, we will add entry into on_exit_nicely_list array > > and for rest database, we will update only shutdown_info as we already > > closed connection for previous database.With this fix, we will not > > touch entry of on_exit_nicely_list for each database. > > > > Here, I am attaching updated patches. > > > > > OK, looks good. here's my latest. I'm currently working on tidying up > docco and comments. > > > cheers > > > andrew > > > > > -- > Andrew Dunstan > EDB: https://www.enterprisedb.com Thanks Andrew for the updated patches. Here, I am attaching a delta patch with some more TAP-test cases. -- Thanks and Regards Mahendra Singh Thalor EnterpriseDB: http://www.enterprisedb.com Attachments: [application/octet-stream] delta_0004-some-negative-TAP-test-case-for-pg_restore-when-dump.noci (5.6K, 2-delta_0004-some-negative-TAP-test-case-for-pg_restore-when-dump.noci) download ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Non-text mode for pg_dumpall @ 2025-04-04 09:12 Mahendra Singh Thalor <[email protected]> parent: Mahendra Singh Thalor <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Mahendra Singh Thalor @ 2025-04-04 09:12 UTC (permalink / raw) To: Andrew Dunstan <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; jian he <[email protected]>; Srinath Reddy <[email protected]>; [email protected] On Fri, 4 Apr 2025 at 13:52, Mahendra Singh Thalor <[email protected]> wrote: > > On Fri, 4 Apr 2025 at 01:17, Andrew Dunstan <[email protected]> wrote: > > > > > > On 2025-04-01 Tu 1:59 AM, Mahendra Singh Thalor wrote: > > > On Mon, 31 Mar 2025 at 23:43, Álvaro Herrera <[email protected]> wrote: > > >> Hi > > >> > > >> FWIW I don't think the on_exit_nicely business is in final shape just > > >> yet. We're doing something super strange and novel about keeping track > > >> of an array index, so that we can modify it later. Or something like > > >> that, I think? That doesn't sound all that nice to me. Elsewhere it > > >> was suggested that we need some way to keep track of the list of things > > >> that need cleanup (a list of connections IIRC?) -- perhaps in a > > >> thread-local variable or a global or something -- and we install the > > >> cleanup function once, and that reads from the variable. The program > > >> can add things to the list, or remove them, at will; and we don't need > > >> to modify the cleanup function in any way. > > >> > > >> -- > > >> Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ > > > Thanks Álvaro for the feedback. > > > > > > I removed the old handling of on_exit_nicely_list from the last patch > > > set and added one simple function to just update the archive handle in > > > shutdown_info. (shutdown_info.AHX = AHX;) > > > > > > For first database, we will add entry into on_exit_nicely_list array > > > and for rest database, we will update only shutdown_info as we already > > > closed connection for previous database.With this fix, we will not > > > touch entry of on_exit_nicely_list for each database. > > > > > > Here, I am attaching updated patches. > > > > > > > > > OK, looks good. here's my latest. I'm currently working on tidying up > > docco and comments. > > > > > > cheers > > > > > > andrew > > > > > > > > > > -- > > Andrew Dunstan > > EDB: https://www.enterprisedb.com > > Thanks Andrew for the updated patches. > > Here, I am attaching a delta patch with some more TAP-test cases. > Here, I am attaching an updated delta patch which has some more TAP tests. Please include these tests also. This patch can be applied on v20250403_0004* patch. -- Thanks and Regards Mahendra Singh Thalor EnterpriseDB: http://www.enterprisedb.com Attachments: [application/octet-stream] delta_20250403-add-some-more-TAP-test-for-pg_restore-and-pg_dumpall.noci (6.9K, 2-delta_20250403-add-some-more-TAP-test-for-pg_restore-and-pg_dumpall.noci) download ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-04-04 09:12 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-04-03 19:47 Re: Non-text mode for pg_dumpall Andrew Dunstan <[email protected]> 2025-04-04 08:22 ` Mahendra Singh Thalor <[email protected]> 2025-04-04 09:12 ` Mahendra Singh Thalor <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox