Skip to content

Commit 12ee6ec

Browse files
bmomjianbmomjian
committed
In pg_upgrade, dump each database separately and use
--single-transaction to restore each database schema. This yields performance improvements for databases with many tables. Also, remove split_old_dump() as it is no longer needed.
1 parent bd9c8e7 commit 12ee6ec

File tree

8 files changed

+85
-106
lines changed

8 files changed

+85
-106
lines changed

‎contrib/pg_upgrade/check.c

Lines changed: 1 addition & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -72,7 +72,7 @@ output_check_banner(bool *live_check)
7272

7373

7474
void
75-
check_old_cluster(bool live_check, char **sequence_script_file_name)
75+
check_and_dump_old_cluster(bool live_check, char **sequence_script_file_name)
7676
{
7777
/* -- OLD -- */
7878

@@ -131,10 +131,7 @@ check_old_cluster(bool live_check, char **sequence_script_file_name)
131131
* the old server is running.
132132
*/
133133
if (!user_opts.check)
134-
{
135134
generate_old_dump();
136-
split_old_dump();
137-
}
138135

139136
if (!live_check)
140137
stop_postmaster(false);

‎contrib/pg_upgrade/dump.c

Lines changed: 20 additions & 81 deletions
Original file line numberDiff line numberDiff line change
@@ -16,95 +16,34 @@
1616
void
1717
generate_old_dump(void)
1818
{
19-
/* run new pg_dumpall binary */
20-
prep_status("Creating catalog dump");
19+
int dbnum;
2120

22-
/*
23-
* --binary-upgrade records the width of dropped columns in pg_class, and
24-
* restores the frozenid's for databases and relations.
25-
*/
21+
prep_status("Creating catalog dump\n");
22+
23+
pg_log(PG_REPORT, OVERWRITE_MESSAGE, "global objects");
24+
25+
/* run new pg_dumpall binary for globals */
2626
exec_prog(UTILITY_LOG_FILE, NULL, true,
27-
"\"%s/pg_dumpall\" %s --schema-only --binary-upgrade %s -f %s",
27+
"\"%s/pg_dumpall\" %s --schema-only --globals-only --binary-upgrade %s -f %s",
2828
new_cluster.bindir, cluster_conn_opts(&old_cluster),
2929
log_opts.verbose ? "--verbose" : "",
30-
ALL_DUMP_FILE);
31-
check_ok();
32-
}
33-
34-
35-
/*
36-
* split_old_dump
37-
*
38-
* This function splits pg_dumpall output into global values and
39-
* database creation, and per-db schemas. This allows us to create
40-
* the support functions between restoring these two parts of the
41-
* dump. We split on the first "\connect " after a CREATE ROLE
42-
* username match; this is where the per-db restore starts.
43-
*
44-
* We suppress recreation of our own username so we don't generate
45-
* an error during restore
46-
*/
47-
void
48-
split_old_dump(void)
49-
{
50-
FILE *all_dump,
51-
*globals_dump,
52-
*db_dump;
53-
FILE *current_output;
54-
char line[LINE_ALLOC];
55-
bool start_of_line = true;
56-
char create_role_str[MAX_STRING];
57-
char create_role_str_quote[MAX_STRING];
58-
char filename[MAXPGPATH];
59-
bool suppressed_username = false;
60-
61-
62-
/*
63-
* Open all files in binary mode to avoid line end translation on Windows,
64-
* both for input and output.
65-
*/
30+
GLOBALS_DUMP_FILE);
6631

67-
snprintf(filename, sizeof(filename), "%s", ALL_DUMP_FILE);
68-
if ((all_dump = fopen(filename, PG_BINARY_R)) == NULL)
69-
pg_log(PG_FATAL, "Could not open dump file \"%s\": %s\n", filename, getErrorText(errno));
70-
snprintf(filename, sizeof(filename), "%s", GLOBALS_DUMP_FILE);
71-
if ((globals_dump = fopen_priv(filename, PG_BINARY_W)) == NULL)
72-
pg_log(PG_FATAL, "Could not write to dump file \"%s\": %s\n", filename, getErrorText(errno));
73-
snprintf(filename, sizeof(filename), "%s", DB_DUMP_FILE);
74-
if ((db_dump = fopen_priv(filename, PG_BINARY_W)) == NULL)
75-
pg_log(PG_FATAL, "Could not write to dump file \"%s\": %s\n", filename, getErrorText(errno));
76-
77-
current_output = globals_dump;
78-
79-
/* patterns used to prevent our own username from being recreated */
80-
snprintf(create_role_str, sizeof(create_role_str),
81-
"CREATE ROLE %s;", os_info.user);
82-
snprintf(create_role_str_quote, sizeof(create_role_str_quote),
83-
"CREATE ROLE %s;", quote_identifier(os_info.user));
84-
85-
while (fgets(line, sizeof(line), all_dump) != NULL)
32+
/* create per-db dump files */
33+
for (dbnum = 0; dbnum < old_cluster.dbarr.ndbs; dbnum++)
8634
{
87-
/* switch to db_dump file output? */
88-
if (current_output == globals_dump && start_of_line &&
89-
suppressed_username &&
90-
strncmp(line, "\\connect ", strlen("\\connect ")) == 0)
91-
current_output = db_dump;
35+
char file_name[MAXPGPATH];
36+
DbInfo *old_db = &old_cluster.dbarr.dbs[dbnum];
9237

93-
/* output unless we are recreating our own username */
94-
if (current_output != globals_dump || !start_of_line ||
95-
(strncmp(line, create_role_str, strlen(create_role_str)) != 0 &&
96-
strncmp(line, create_role_str_quote, strlen(create_role_str_quote)) != 0))
97-
fputs(line, current_output);
98-
else
99-
suppressed_username = true;
38+
pg_log(PG_REPORT, OVERWRITE_MESSAGE, old_db->db_name);
39+
snprintf(file_name, sizeof(file_name), DB_DUMP_FILE_MASK, old_db->db_oid);
10040

101-
if (strlen(line) > 0 && line[strlen(line) - 1] == '\n')
102-
start_of_line = true;
103-
else
104-
start_of_line = false;
41+
exec_prog(RESTORE_LOG_FILE, NULL, true,
42+
"\"%s/pg_dump\" %s --schema-only --binary-upgrade --format=custom %s --file=\"%s\" \"%s\"",
43+
new_cluster.bindir, cluster_conn_opts(&old_cluster),
44+
log_opts.verbose ? "--verbose" : "", file_name, old_db->db_name);
10545
}
10646

107-
fclose(all_dump);
108-
fclose(globals_dump);
109-
fclose(db_dump);
47+
end_progress_output();
48+
check_ok();
11049
}

‎contrib/pg_upgrade/exec.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -104,8 +104,10 @@ exec_prog(const char *log_file, const char *opt_log_file,
104104

105105
if (result != 0)
106106
{
107-
report_status(PG_REPORT, "*failure*");
107+
/* we might be in on a progress status line, so go to the next line */
108+
report_status(PG_REPORT, "\n*failure*");
108109
fflush(stdout);
110+
109111
pg_log(PG_VERBOSE, "There were problems executing \"%s\"\n", cmd);
110112
if (opt_log_file)
111113
pg_log(throw_error ? PG_FATAL : PG_REPORT,

‎contrib/pg_upgrade/pg_upgrade.c

Lines changed: 39 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -92,7 +92,7 @@ main(int argc, char **argv)
9292

9393
check_cluster_compatibility(live_check);
9494

95-
check_old_cluster(live_check, &sequence_script_file_name);
95+
check_and_dump_old_cluster(live_check, &sequence_script_file_name);
9696

9797

9898
/* -- NEW -- */
@@ -282,6 +282,11 @@ create_new_objects(void)
282282

283283
prep_status("Adding support functions to new cluster");
284284

285+
/*
286+
* Technically, we only need to install these support functions in new
287+
* databases that also exist in the old cluster, but for completeness
288+
* we process all new databases.
289+
*/
285290
for (dbnum = 0; dbnum < new_cluster.dbarr.ndbs; dbnum++)
286291
{
287292
DbInfo *new_db = &new_cluster.dbarr.dbs[dbnum];
@@ -292,11 +297,27 @@ create_new_objects(void)
292297
}
293298
check_ok();
294299

295-
prep_status("Restoring database schema to new cluster");
296-
exec_prog(RESTORE_LOG_FILE, NULL, true,
297-
"\"%s/psql\" " EXEC_PSQL_ARGS " %s -f \"%s\"",
298-
new_cluster.bindir, cluster_conn_opts(&new_cluster),
299-
DB_DUMP_FILE);
300+
prep_status("Restoring database schema to new cluster\n");
301+
302+
for (dbnum = 0; dbnum < old_cluster.dbarr.ndbs; dbnum++)
303+
{
304+
char file_name[MAXPGPATH];
305+
DbInfo *old_db = &old_cluster.dbarr.dbs[dbnum];
306+
307+
pg_log(PG_REPORT, OVERWRITE_MESSAGE, old_db->db_name);
308+
snprintf(file_name, sizeof(file_name), DB_DUMP_FILE_MASK, old_db->db_oid);
309+
310+
/*
311+
* Using pg_restore --single-transaction is faster than other
312+
* methods, like --jobs. pg_dump only produces its output at the
313+
* end, so there is little parallelism using the pipe.
314+
*/
315+
exec_prog(RESTORE_LOG_FILE, NULL, true,
316+
"\"%s/pg_restore\" %s --exit-on-error --single-transaction --verbose --dbname \"%s\" \"%s\"",
317+
new_cluster.bindir, cluster_conn_opts(&new_cluster),
318+
old_db->db_name, file_name);
319+
}
320+
end_progress_output();
300321
check_ok();
301322

302323
/* regenerate now that we have objects in the databases */
@@ -455,14 +476,23 @@ cleanup(void)
455476
/* Remove dump and log files? */
456477
if (!log_opts.retain)
457478
{
479+
int dbnum;
458480
char **filename;
459481

460482
for (filename = output_files; *filename != NULL; filename++)
461483
unlink(*filename);
462484

463-
/* remove SQL files */
464-
unlink(ALL_DUMP_FILE);
485+
/* remove dump files */
465486
unlink(GLOBALS_DUMP_FILE);
466-
unlink(DB_DUMP_FILE);
487+
488+
if (old_cluster.dbarr.dbs)
489+
for (dbnum = 0; dbnum < old_cluster.dbarr.ndbs; dbnum++)
490+
{
491+
char file_name[MAXPGPATH];
492+
DbInfo *old_db = &old_cluster.dbarr.dbs[dbnum];
493+
494+
snprintf(file_name, sizeof(file_name), DB_DUMP_FILE_MASK, old_db->db_oid);
495+
unlink(file_name);
496+
}
467497
}
468498
}

‎contrib/pg_upgrade/pg_upgrade.h

Lines changed: 4 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -29,10 +29,9 @@
2929
#define OVERWRITE_MESSAGE " %-" MESSAGE_WIDTH "." MESSAGE_WIDTH "s\r"
3030
#define GET_MAJOR_VERSION(v) ((v) / 100)
3131

32-
#define ALL_DUMP_FILE "pg_upgrade_dump_all.sql"
3332
/* contains both global db information and CREATE DATABASE commands */
3433
#define GLOBALS_DUMP_FILE "pg_upgrade_dump_globals.sql"
35-
#define DB_DUMP_FILE "pg_upgrade_dump_db.sql"
34+
#define DB_DUMP_FILE_MASK "pg_upgrade_dump_%u.custom"
3635

3736
#define SERVER_LOG_FILE "pg_upgrade_server.log"
3837
#define RESTORE_LOG_FILE "pg_upgrade_restore.log"
@@ -296,12 +295,12 @@ extern OSInfo os_info;
296295
/* check.c */
297296

298297
void output_check_banner(bool *live_check);
299-
void check_old_cluster(bool live_check,
298+
void check_and_dump_old_cluster(bool live_check,
300299
char **sequence_script_file_name);
301300
void check_new_cluster(void);
302301
void report_clusters_compatible(void);
303302
void issue_warnings(char *sequence_script_file_name);
304-
void output_completion_banner(char *analyze_script_file_name,
303+
void output_completion_banner(char *analyze_script_file_name,
305304
char *deletion_script_file_name);
306305
void check_cluster_versions(void);
307306
void check_cluster_compatibility(bool live_check);
@@ -319,7 +318,6 @@ void disable_old_cluster(void);
319318
/* dump.c */
320319

321320
void generate_old_dump(void);
322-
void split_old_dump(void);
323321

324322

325323
/* exec.c */
@@ -433,6 +431,7 @@ __attribute__((format(PG_PRINTF_ATTRIBUTE, 2, 3)));
433431
void
434432
pg_log(eLogType type, char *fmt,...)
435433
__attribute__((format(PG_PRINTF_ATTRIBUTE, 2, 3)));
434+
void end_progress_output(void);
436435
void
437436
prep_status(const char *fmt,...)
438437
__attribute__((format(PG_PRINTF_ATTRIBUTE, 1, 2)));

‎contrib/pg_upgrade/relfilenode.c

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -82,9 +82,7 @@ transfer_all_new_dbs(DbInfoArr *old_db_arr,
8282
}
8383
}
8484

85-
prep_status(" "); /* in case nothing printed; pass a space so
86-
* gcc doesn't complain about empty format
87-
* string */
85+
end_progress_output();
8886
check_ok();
8987

9088
return msg;

‎contrib/pg_upgrade/util.c

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,18 @@ report_status(eLogType type, const char *fmt,...)
3535
}
3636

3737

38+
/* force blank output for progress display */
39+
void
40+
end_progress_output(void)
41+
{
42+
/*
43+
* In case nothing printed; pass a space so gcc doesn't complain about
44+
* empty format string.
45+
*/
46+
prep_status(" ");
47+
}
48+
49+
3850
/*
3951
* prep_status
4052
*

‎src/bin/pg_dump/pg_dumpall.c

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -502,7 +502,7 @@ main(int argc, char *argv[])
502502
}
503503

504504
/* Dump CREATE DATABASE commands */
505-
if (!globals_only && !roles_only && !tablespaces_only)
505+
if (binary_upgrade || (!globals_only && !roles_only && !tablespaces_only))
506506
dumpCreateDB(conn);
507507

508508
/* Dump role/database settings */
@@ -745,9 +745,11 @@ dumpRoles(PGconn *conn)
745745
* will acquire the right properties even if it already exists (ie, it
746746
* won't hurt for the CREATE to fail). This is particularly important
747747
* for the role we are connected as, since even with --clean we will
748-
* have failed to drop it.
748+
* have failed to drop it. binary_upgrade cannot generate any errors,
749+
* so we assume the role is already created.
749750
*/
750-
appendPQExpBuffer(buf, "CREATE ROLE %s;\n", fmtId(rolename));
751+
if (!binary_upgrade)
752+
appendPQExpBuffer(buf, "CREATE ROLE %s;\n", fmtId(rolename));
751753
appendPQExpBuffer(buf, "ALTER ROLE %s WITH", fmtId(rolename));
752754

753755
if (strcmp(PQgetvalue(res, i, i_rolsuper), "t") == 0)

0 commit comments

Comments
 (0)