4 * server checks and output routines
6 * Copyright (c) 2010-2020, PostgreSQL Global Development Group
7 * src/bin/pg_upgrade/check.c
10 #include "postgres_fe.h"
12 #include "catalog/pg_authid_d.h"
13 #include "fe_utils/string_utils.h"
14 #include "mb/pg_wchar.h"
15 #include "pg_upgrade.h"
17 static void check_new_cluster_is_empty(void);
18 static void check_databases_are_compatible(void);
19 static void check_locale_and_encoding(DbInfo
*olddb
, DbInfo
*newdb
);
20 static bool equivalent_locale(int category
, const char *loca
, const char *locb
);
21 static void check_is_install_user(ClusterInfo
*cluster
);
22 static void check_proper_datallowconn(ClusterInfo
*cluster
);
23 static void check_for_prepared_transactions(ClusterInfo
*cluster
);
24 static void check_for_isn_and_int8_passing_mismatch(ClusterInfo
*cluster
);
25 static void check_for_user_defined_postfix_ops(ClusterInfo
*cluster
);
26 static void check_for_tables_with_oids(ClusterInfo
*cluster
);
27 static void check_for_reg_data_type_usage(ClusterInfo
*cluster
);
28 static void check_for_jsonb_9_4_usage(ClusterInfo
*cluster
);
29 static void check_for_pg_role_prefix(ClusterInfo
*cluster
);
30 static void check_for_new_tablespace_dir(ClusterInfo
*new_cluster
);
31 static char *get_canonical_locale_name(int category
, const char *locale
);
36 * For non-Windows, just return the argument.
37 * For Windows convert any forward slash to a backslash
38 * such as is suitable for arguments to builtin commands
42 fix_path_separator(char *path
)
49 result
= pg_strdup(path
);
51 for (c
= result
; *c
!= '\0'; c
++)
63 output_check_banner(bool live_check
)
65 if (user_opts
.check
&& live_check
)
68 "Performing Consistency Checks on Old Live Server\n"
69 "------------------------------------------------\n");
74 "Performing Consistency Checks\n"
75 "-----------------------------\n");
81 check_and_dump_old_cluster(bool live_check
)
86 start_postmaster(&old_cluster
, true);
88 /* Extract a list of databases and tables from the old cluster */
89 get_db_and_rel_infos(&old_cluster
);
93 get_loadable_libraries();
97 * Check for various failure cases
99 check_is_install_user(&old_cluster
);
100 check_proper_datallowconn(&old_cluster
);
101 check_for_prepared_transactions(&old_cluster
);
102 check_for_reg_data_type_usage(&old_cluster
);
103 check_for_isn_and_int8_passing_mismatch(&old_cluster
);
106 * Pre-PG 14 allowed user defined postfix operators, which are not
107 * supported anymore. Verify there are none, iff applicable.
109 if (GET_MAJOR_VERSION(old_cluster
.major_version
) <= 1300)
110 check_for_user_defined_postfix_ops(&old_cluster
);
113 * Pre-PG 12 allowed tables to be declared WITH OIDS, which is not
114 * supported anymore. Verify there are none, iff applicable.
116 if (GET_MAJOR_VERSION(old_cluster
.major_version
) <= 1100)
117 check_for_tables_with_oids(&old_cluster
);
120 * PG 12 changed the 'sql_identifier' type storage to be based on name,
121 * not varchar, which breaks on-disk format for existing data. So we need
122 * to prevent upgrade when used in user objects (tables, indexes, ...).
124 if (GET_MAJOR_VERSION(old_cluster
.major_version
) <= 1100)
125 old_11_check_for_sql_identifier_data_type_usage(&old_cluster
);
128 * Pre-PG 10 allowed tables with 'unknown' type columns and non WAL logged
131 if (GET_MAJOR_VERSION(old_cluster
.major_version
) <= 906)
133 old_9_6_check_for_unknown_data_type_usage(&old_cluster
);
135 old_9_6_invalidate_hash_indexes(&old_cluster
, true);
138 /* 9.5 and below should not have roles starting with pg_ */
139 if (GET_MAJOR_VERSION(old_cluster
.major_version
) <= 905)
140 check_for_pg_role_prefix(&old_cluster
);
142 if (GET_MAJOR_VERSION(old_cluster
.major_version
) == 904 &&
143 old_cluster
.controldata
.cat_ver
< JSONB_FORMAT_CHANGE_CAT_VER
)
144 check_for_jsonb_9_4_usage(&old_cluster
);
146 /* Pre-PG 9.4 had a different 'line' data type internal format */
147 if (GET_MAJOR_VERSION(old_cluster
.major_version
) <= 903)
148 old_9_3_check_for_line_data_type_usage(&old_cluster
);
150 /* Pre-PG 9.0 had no large object permissions */
151 if (GET_MAJOR_VERSION(old_cluster
.major_version
) <= 804)
152 new_9_0_populate_pg_largeobject_metadata(&old_cluster
, true);
155 * While not a check option, we do this now because this is the only time
156 * the old server is running.
158 if (!user_opts
.check
)
162 stop_postmaster(false);
167 check_new_cluster(void)
169 get_db_and_rel_infos(&new_cluster
);
171 check_new_cluster_is_empty();
172 check_databases_are_compatible();
174 check_loadable_libraries();
176 switch (user_opts
.transfer_mode
)
178 case TRANSFER_MODE_CLONE
:
181 case TRANSFER_MODE_COPY
:
183 case TRANSFER_MODE_LINK
:
188 check_is_install_user(&new_cluster
);
190 check_for_prepared_transactions(&new_cluster
);
192 check_for_new_tablespace_dir(&new_cluster
);
197 report_clusters_compatible(void)
201 pg_log(PG_REPORT
, "\n*Clusters are compatible*\n");
202 /* stops new cluster */
203 stop_postmaster(false);
207 pg_log(PG_REPORT
, "\n"
208 "If pg_upgrade fails after this point, you must re-initdb the\n"
209 "new cluster before continuing.\n");
214 issue_warnings_and_set_wal_level(void)
217 * We unconditionally start/stop the new server because pg_resetwal -o set
218 * wal_level to 'minimum'. If the user is upgrading standby servers using
219 * the rsync instructions, they will need pg_upgrade to write its final
220 * WAL record showing wal_level as 'replica'.
222 start_postmaster(&new_cluster
, true);
224 /* Create dummy large object permissions for old < PG 9.0? */
225 if (GET_MAJOR_VERSION(old_cluster
.major_version
) <= 804)
226 new_9_0_populate_pg_largeobject_metadata(&new_cluster
, false);
228 /* Reindex hash indexes for old < 10.0 */
229 if (GET_MAJOR_VERSION(old_cluster
.major_version
) <= 906)
230 old_9_6_invalidate_hash_indexes(&new_cluster
, false);
232 stop_postmaster(false);
237 output_completion_banner(char *analyze_script_file_name
,
238 char *deletion_script_file_name
)
241 "Optimizer statistics are not transferred by pg_upgrade so,\n"
242 "once you start the new server, consider running:\n"
243 " %s\n\n", analyze_script_file_name
);
245 if (deletion_script_file_name
)
247 "Running this script will delete the old cluster's data files:\n"
249 deletion_script_file_name
);
252 "Could not create a script to delete the old cluster's data files\n"
253 "because user-defined tablespaces or the new cluster's data directory\n"
254 "exist in the old cluster directory. The old cluster's contents must\n"
255 "be deleted manually.\n");
260 check_cluster_versions(void)
262 prep_status("Checking cluster versions");
264 /* cluster versions should already have been obtained */
265 Assert(old_cluster
.major_version
!= 0);
266 Assert(new_cluster
.major_version
!= 0);
269 * We allow upgrades from/to the same major version for alpha/beta
273 if (GET_MAJOR_VERSION(old_cluster
.major_version
) < 804)
274 pg_fatal("This utility can only upgrade from PostgreSQL version 8.4 and later.\n");
276 /* Only current PG version is supported as a target */
277 if (GET_MAJOR_VERSION(new_cluster
.major_version
) != GET_MAJOR_VERSION(PG_VERSION_NUM
))
278 pg_fatal("This utility can only upgrade to PostgreSQL version %s.\n",
282 * We can't allow downgrading because we use the target pg_dump, and
283 * pg_dump cannot operate on newer database versions, only current and
286 if (old_cluster
.major_version
> new_cluster
.major_version
)
287 pg_fatal("This utility cannot be used to downgrade to older major PostgreSQL versions.\n");
289 /* Ensure binaries match the designated data directories */
290 if (GET_MAJOR_VERSION(old_cluster
.major_version
) !=
291 GET_MAJOR_VERSION(old_cluster
.bin_version
))
292 pg_fatal("Old cluster data and binary directories are from different major versions.\n");
293 if (GET_MAJOR_VERSION(new_cluster
.major_version
) !=
294 GET_MAJOR_VERSION(new_cluster
.bin_version
))
295 pg_fatal("New cluster data and binary directories are from different major versions.\n");
302 check_cluster_compatibility(bool live_check
)
304 /* get/check pg_control data of servers */
305 get_control_data(&old_cluster
, live_check
);
306 get_control_data(&new_cluster
, false);
307 check_control_data(&old_cluster
.controldata
, &new_cluster
.controldata
);
309 /* We read the real port number for PG >= 9.1 */
310 if (live_check
&& GET_MAJOR_VERSION(old_cluster
.major_version
) <= 900 &&
311 old_cluster
.port
== DEF_PGUPORT
)
312 pg_fatal("When checking a pre-PG 9.1 live old server, "
313 "you must specify the old server's port number.\n");
315 if (live_check
&& old_cluster
.port
== new_cluster
.port
)
316 pg_fatal("When checking a live server, "
317 "the old and new port numbers must be different.\n");
322 * check_locale_and_encoding()
324 * Check that locale and encoding of a database in the old and new clusters
328 check_locale_and_encoding(DbInfo
*olddb
, DbInfo
*newdb
)
330 if (olddb
->db_encoding
!= newdb
->db_encoding
)
331 pg_fatal("encodings for database \"%s\" do not match: old \"%s\", new \"%s\"\n",
333 pg_encoding_to_char(olddb
->db_encoding
),
334 pg_encoding_to_char(newdb
->db_encoding
));
335 if (!equivalent_locale(LC_COLLATE
, olddb
->db_collate
, newdb
->db_collate
))
336 pg_fatal("lc_collate values for database \"%s\" do not match: old \"%s\", new \"%s\"\n",
337 olddb
->db_name
, olddb
->db_collate
, newdb
->db_collate
);
338 if (!equivalent_locale(LC_CTYPE
, olddb
->db_ctype
, newdb
->db_ctype
))
339 pg_fatal("lc_ctype values for database \"%s\" do not match: old \"%s\", new \"%s\"\n",
340 olddb
->db_name
, olddb
->db_ctype
, newdb
->db_ctype
);
344 * equivalent_locale()
346 * Best effort locale-name comparison. Return false if we are not 100% sure
347 * the locales are equivalent.
349 * Note: The encoding parts of the names are ignored. This function is
350 * currently used to compare locale names stored in pg_database, and
351 * pg_database contains a separate encoding field. That's compared directly
352 * in check_locale_and_encoding().
355 equivalent_locale(int category
, const char *loca
, const char *locb
)
365 * If the names are equal, the locales are equivalent. Checking this first
366 * avoids calling setlocale() in the common case that the names are equal.
367 * That's a good thing, if setlocale() is buggy, for example.
369 if (pg_strcasecmp(loca
, locb
) == 0)
373 * Not identical. Canonicalize both names, remove the encoding parts, and
376 canona
= get_canonical_locale_name(category
, loca
);
377 chara
= strrchr(canona
, '.');
378 lena
= chara
? (chara
- canona
) : strlen(canona
);
380 canonb
= get_canonical_locale_name(category
, locb
);
381 charb
= strrchr(canonb
, '.');
382 lenb
= charb
? (charb
- canonb
) : strlen(canonb
);
384 if (lena
== lenb
&& pg_strncasecmp(canona
, canonb
, lena
) == 0)
398 check_new_cluster_is_empty(void)
402 for (dbnum
= 0; dbnum
< new_cluster
.dbarr
.ndbs
; dbnum
++)
405 RelInfoArr
*rel_arr
= &new_cluster
.dbarr
.dbs
[dbnum
].rel_arr
;
407 for (relnum
= 0; relnum
< rel_arr
->nrels
;
410 /* pg_largeobject and its index should be skipped */
411 if (strcmp(rel_arr
->rels
[relnum
].nspname
, "pg_catalog") != 0)
412 pg_fatal("New cluster database \"%s\" is not empty: found relation \"%s.%s\"\n",
413 new_cluster
.dbarr
.dbs
[dbnum
].db_name
,
414 rel_arr
->rels
[relnum
].nspname
,
415 rel_arr
->rels
[relnum
].relname
);
421 * Check that every database that already exists in the new cluster is
422 * compatible with the corresponding database in the old one.
425 check_databases_are_compatible(void)
432 for (newdbnum
= 0; newdbnum
< new_cluster
.dbarr
.ndbs
; newdbnum
++)
434 newdbinfo
= &new_cluster
.dbarr
.dbs
[newdbnum
];
436 /* Find the corresponding database in the old cluster */
437 for (olddbnum
= 0; olddbnum
< old_cluster
.dbarr
.ndbs
; olddbnum
++)
439 olddbinfo
= &old_cluster
.dbarr
.dbs
[olddbnum
];
440 if (strcmp(newdbinfo
->db_name
, olddbinfo
->db_name
) == 0)
442 check_locale_and_encoding(olddbinfo
, newdbinfo
);
451 * create_script_for_cluster_analyze()
453 * This incrementally generates better optimizer statistics
456 create_script_for_cluster_analyze(char **analyze_script_file_name
)
459 PQExpBufferData user_specification
;
461 prep_status("Creating script to analyze new cluster");
463 initPQExpBuffer(&user_specification
);
464 if (os_info
.user_specified
)
466 appendPQExpBufferStr(&user_specification
, "-U ");
467 appendShellString(&user_specification
, os_info
.user
);
468 appendPQExpBufferChar(&user_specification
, ' ');
471 *analyze_script_file_name
= psprintf("%sanalyze_new_cluster.%s",
472 SCRIPT_PREFIX
, SCRIPT_EXT
);
474 if ((script
= fopen_priv(*analyze_script_file_name
, "w")) == NULL
)
475 pg_fatal("could not open file \"%s\": %s\n",
476 *analyze_script_file_name
, strerror(errno
));
479 /* add shebang header */
480 fprintf(script
, "#!/bin/sh\n\n");
482 /* suppress command echoing */
483 fprintf(script
, "@echo off\n");
486 fprintf(script
, "echo %sThis script will generate minimal optimizer statistics rapidly%s\n",
487 ECHO_QUOTE
, ECHO_QUOTE
);
488 fprintf(script
, "echo %sso your system is usable, and then gather statistics twice more%s\n",
489 ECHO_QUOTE
, ECHO_QUOTE
);
490 fprintf(script
, "echo %swith increasing accuracy. When it is done, your system will%s\n",
491 ECHO_QUOTE
, ECHO_QUOTE
);
492 fprintf(script
, "echo %shave the default level of optimizer statistics.%s\n",
493 ECHO_QUOTE
, ECHO_QUOTE
);
494 fprintf(script
, "echo%s\n\n", ECHO_BLANK
);
496 fprintf(script
, "echo %sIf you have used ALTER TABLE to modify the statistics target for%s\n",
497 ECHO_QUOTE
, ECHO_QUOTE
);
498 fprintf(script
, "echo %sany tables, you might want to remove them and restore them after%s\n",
499 ECHO_QUOTE
, ECHO_QUOTE
);
500 fprintf(script
, "echo %srunning this script because they will delay fast statistics generation.%s\n",
501 ECHO_QUOTE
, ECHO_QUOTE
);
502 fprintf(script
, "echo%s\n\n", ECHO_BLANK
);
504 fprintf(script
, "echo %sIf you would like default statistics as quickly as possible, cancel%s\n",
505 ECHO_QUOTE
, ECHO_QUOTE
);
506 fprintf(script
, "echo %sthis script and run:%s\n",
507 ECHO_QUOTE
, ECHO_QUOTE
);
508 fprintf(script
, "echo %s \"%s/vacuumdb\" %s--all --analyze-only%s\n", ECHO_QUOTE
,
509 new_cluster
.bindir
, user_specification
.data
, ECHO_QUOTE
);
510 fprintf(script
, "echo%s\n\n", ECHO_BLANK
);
512 fprintf(script
, "\"%s/vacuumdb\" %s--all --analyze-in-stages\n",
513 new_cluster
.bindir
, user_specification
.data
);
515 fprintf(script
, "echo%s\n\n", ECHO_BLANK
);
516 fprintf(script
, "echo %sDone%s\n",
517 ECHO_QUOTE
, ECHO_QUOTE
);
522 if (chmod(*analyze_script_file_name
, S_IRWXU
) != 0)
523 pg_fatal("could not add execute permission to file \"%s\": %s\n",
524 *analyze_script_file_name
, strerror(errno
));
527 termPQExpBuffer(&user_specification
);
534 * A previous run of pg_upgrade might have failed and the new cluster
535 * directory recreated, but they might have forgotten to remove
536 * the new cluster's tablespace directories. Therefore, check that
537 * new cluster tablespace directories do not already exist. If
538 * they do, it would cause an error while restoring global objects.
539 * This allows the failure to be detected at check time, rather than
540 * during schema restore.
542 * Note, v8.4 has no tablespace_suffix, which is fine so long as the
543 * version being upgraded *to* has a suffix, since it's not allowed
544 * to pg_upgrade from a version to the same version if tablespaces are
548 check_for_new_tablespace_dir(ClusterInfo
*new_cluster
)
550 char new_tablespace_dir
[MAXPGPATH
];
552 prep_status("Checking for new cluster tablespace directories");
554 for (int tblnum
= 0; tblnum
< os_info
.num_old_tablespaces
; tblnum
++)
558 snprintf(new_tablespace_dir
, MAXPGPATH
, "%s%s",
559 os_info
.old_tablespaces
[tblnum
],
560 new_cluster
->tablespace_suffix
);
562 if (stat(new_tablespace_dir
, &statbuf
) == 0 || errno
!= ENOENT
)
563 pg_fatal("new cluster tablespace directory already exists: \"%s\"\n",
571 * create_script_for_old_cluster_deletion()
573 * This is particularly useful for tablespace deletion.
576 create_script_for_old_cluster_deletion(char **deletion_script_file_name
)
580 char old_cluster_pgdata
[MAXPGPATH
],
581 new_cluster_pgdata
[MAXPGPATH
];
583 *deletion_script_file_name
= psprintf("%sdelete_old_cluster.%s",
584 SCRIPT_PREFIX
, SCRIPT_EXT
);
586 strlcpy(old_cluster_pgdata
, old_cluster
.pgdata
, MAXPGPATH
);
587 canonicalize_path(old_cluster_pgdata
);
589 strlcpy(new_cluster_pgdata
, new_cluster
.pgdata
, MAXPGPATH
);
590 canonicalize_path(new_cluster_pgdata
);
592 /* Some people put the new data directory inside the old one. */
593 if (path_is_prefix_of_path(old_cluster_pgdata
, new_cluster_pgdata
))
596 "\nWARNING: new data directory should not be inside the old data directory, e.g. %s\n", old_cluster_pgdata
);
598 /* Unlink file in case it is left over from a previous run. */
599 unlink(*deletion_script_file_name
);
600 pg_free(*deletion_script_file_name
);
601 *deletion_script_file_name
= NULL
;
606 * Some users (oddly) create tablespaces inside the cluster data
607 * directory. We can't create a proper old cluster delete script in that
610 for (tblnum
= 0; tblnum
< os_info
.num_old_tablespaces
; tblnum
++)
612 char old_tablespace_dir
[MAXPGPATH
];
614 strlcpy(old_tablespace_dir
, os_info
.old_tablespaces
[tblnum
], MAXPGPATH
);
615 canonicalize_path(old_tablespace_dir
);
616 if (path_is_prefix_of_path(old_cluster_pgdata
, old_tablespace_dir
))
618 /* reproduce warning from CREATE TABLESPACE that is in the log */
620 "\nWARNING: user-defined tablespace locations should not be inside the data directory, e.g. %s\n", old_tablespace_dir
);
622 /* Unlink file in case it is left over from a previous run. */
623 unlink(*deletion_script_file_name
);
624 pg_free(*deletion_script_file_name
);
625 *deletion_script_file_name
= NULL
;
630 prep_status("Creating script to delete old cluster");
632 if ((script
= fopen_priv(*deletion_script_file_name
, "w")) == NULL
)
633 pg_fatal("could not open file \"%s\": %s\n",
634 *deletion_script_file_name
, strerror(errno
));
637 /* add shebang header */
638 fprintf(script
, "#!/bin/sh\n\n");
641 /* delete old cluster's default tablespace */
642 fprintf(script
, RMDIR_CMD
" %c%s%c\n", PATH_QUOTE
,
643 fix_path_separator(old_cluster
.pgdata
), PATH_QUOTE
);
645 /* delete old cluster's alternate tablespaces */
646 for (tblnum
= 0; tblnum
< os_info
.num_old_tablespaces
; tblnum
++)
649 * Do the old cluster's per-database directories share a directory
650 * with a new version-specific tablespace?
652 if (strlen(old_cluster
.tablespace_suffix
) == 0)
654 /* delete per-database directories */
657 fprintf(script
, "\n");
658 /* remove PG_VERSION? */
659 if (GET_MAJOR_VERSION(old_cluster
.major_version
) <= 804)
660 fprintf(script
, RM_CMD
" %s%cPG_VERSION\n",
661 fix_path_separator(os_info
.old_tablespaces
[tblnum
]),
664 for (dbnum
= 0; dbnum
< old_cluster
.dbarr
.ndbs
; dbnum
++)
665 fprintf(script
, RMDIR_CMD
" %c%s%c%d%c\n", PATH_QUOTE
,
666 fix_path_separator(os_info
.old_tablespaces
[tblnum
]),
667 PATH_SEPARATOR
, old_cluster
.dbarr
.dbs
[dbnum
].db_oid
,
672 char *suffix_path
= pg_strdup(old_cluster
.tablespace_suffix
);
675 * Simply delete the tablespace directory, which might be ".old"
676 * or a version-specific subdirectory.
678 fprintf(script
, RMDIR_CMD
" %c%s%s%c\n", PATH_QUOTE
,
679 fix_path_separator(os_info
.old_tablespaces
[tblnum
]),
680 fix_path_separator(suffix_path
), PATH_QUOTE
);
688 if (chmod(*deletion_script_file_name
, S_IRWXU
) != 0)
689 pg_fatal("could not add execute permission to file \"%s\": %s\n",
690 *deletion_script_file_name
, strerror(errno
));
698 * check_is_install_user()
700 * Check we are the install user, and that the new cluster
701 * has no other users.
704 check_is_install_user(ClusterInfo
*cluster
)
707 PGconn
*conn
= connectToServer(cluster
, "template1");
709 prep_status("Checking database user is the install user");
711 /* Can't use pg_authid because only superusers can view it. */
712 res
= executeQueryOrDie(conn
,
713 "SELECT rolsuper, oid "
714 "FROM pg_catalog.pg_roles "
715 "WHERE rolname = current_user "
716 "AND rolname !~ '^pg_'");
719 * We only allow the install user in the new cluster (see comment below)
720 * and we preserve pg_authid.oid, so this must be the install user in the
723 if (PQntuples(res
) != 1 ||
724 atooid(PQgetvalue(res
, 0, 1)) != BOOTSTRAP_SUPERUSERID
)
725 pg_fatal("database user \"%s\" is not the install user\n",
730 res
= executeQueryOrDie(conn
,
732 "FROM pg_catalog.pg_roles "
733 "WHERE rolname !~ '^pg_'");
735 if (PQntuples(res
) != 1)
736 pg_fatal("could not determine the number of users\n");
739 * We only allow the install user in the new cluster because other defined
740 * users might match users defined in the old cluster and generate an
741 * error during pg_dump restore.
743 if (cluster
== &new_cluster
&& atooid(PQgetvalue(res
, 0, 0)) != 1)
744 pg_fatal("Only the install user can be defined in the new cluster.\n");
755 check_proper_datallowconn(ClusterInfo
*cluster
)
758 PGconn
*conn_template1
;
764 prep_status("Checking database connection settings");
766 conn_template1
= connectToServer(cluster
, "template1");
768 /* get database names */
769 dbres
= executeQueryOrDie(conn_template1
,
770 "SELECT datname, datallowconn "
771 "FROM pg_catalog.pg_database");
773 i_datname
= PQfnumber(dbres
, "datname");
774 i_datallowconn
= PQfnumber(dbres
, "datallowconn");
776 ntups
= PQntuples(dbres
);
777 for (dbnum
= 0; dbnum
< ntups
; dbnum
++)
779 char *datname
= PQgetvalue(dbres
, dbnum
, i_datname
);
780 char *datallowconn
= PQgetvalue(dbres
, dbnum
, i_datallowconn
);
782 if (strcmp(datname
, "template0") == 0)
784 /* avoid restore failure when pg_dumpall tries to create template0 */
785 if (strcmp(datallowconn
, "t") == 0)
786 pg_fatal("template0 must not allow connections, "
787 "i.e. its pg_database.datallowconn must be false\n");
792 * avoid datallowconn == false databases from being skipped on
795 if (strcmp(datallowconn
, "f") == 0)
796 pg_fatal("All non-template0 databases must allow connections, "
797 "i.e. their pg_database.datallowconn must be true\n");
803 PQfinish(conn_template1
);
810 * check_for_prepared_transactions()
812 * Make sure there are no prepared transactions because the storage format
813 * might have changed.
816 check_for_prepared_transactions(ClusterInfo
*cluster
)
819 PGconn
*conn
= connectToServer(cluster
, "template1");
821 prep_status("Checking for prepared transactions");
823 res
= executeQueryOrDie(conn
,
825 "FROM pg_catalog.pg_prepared_xacts");
827 if (PQntuples(res
) != 0)
829 if (cluster
== &old_cluster
)
830 pg_fatal("The source cluster contains prepared transactions\n");
832 pg_fatal("The target cluster contains prepared transactions\n");
844 * check_for_isn_and_int8_passing_mismatch()
846 * contrib/isn relies on data type int8, and in 8.4 int8 can now be passed
847 * by value. The schema dumps the CREATE TYPE PASSEDBYVALUE setting so
848 * it must match for the old and new servers.
851 check_for_isn_and_int8_passing_mismatch(ClusterInfo
*cluster
)
856 char output_path
[MAXPGPATH
];
858 prep_status("Checking for contrib/isn with bigint-passing mismatch");
860 if (old_cluster
.controldata
.float8_pass_by_value
==
861 new_cluster
.controldata
.float8_pass_by_value
)
868 snprintf(output_path
, sizeof(output_path
),
869 "contrib_isn_and_int8_pass_by_value.txt");
871 for (dbnum
= 0; dbnum
< cluster
->dbarr
.ndbs
; dbnum
++)
874 bool db_used
= false;
879 DbInfo
*active_db
= &cluster
->dbarr
.dbs
[dbnum
];
880 PGconn
*conn
= connectToServer(cluster
, active_db
->db_name
);
882 /* Find any functions coming from contrib/isn */
883 res
= executeQueryOrDie(conn
,
884 "SELECT n.nspname, p.proname "
885 "FROM pg_catalog.pg_proc p, "
886 " pg_catalog.pg_namespace n "
887 "WHERE p.pronamespace = n.oid AND "
888 " p.probin = '$libdir/isn'");
890 ntups
= PQntuples(res
);
891 i_nspname
= PQfnumber(res
, "nspname");
892 i_proname
= PQfnumber(res
, "proname");
893 for (rowno
= 0; rowno
< ntups
; rowno
++)
896 if (script
== NULL
&& (script
= fopen_priv(output_path
, "w")) == NULL
)
897 pg_fatal("could not open file \"%s\": %s\n",
898 output_path
, strerror(errno
));
901 fprintf(script
, "In database: %s\n", active_db
->db_name
);
904 fprintf(script
, " %s.%s\n",
905 PQgetvalue(res
, rowno
, i_nspname
),
906 PQgetvalue(res
, rowno
, i_proname
));
919 pg_log(PG_REPORT
, "fatal\n");
920 pg_fatal("Your installation contains \"contrib/isn\" functions which rely on the\n"
921 "bigint data type. Your old and new clusters pass bigint values\n"
922 "differently so this cluster cannot currently be upgraded. You can\n"
923 "manually dump databases in the old cluster that use \"contrib/isn\"\n"
924 "facilities, drop them, perform the upgrade, and then restore them. A\n"
925 "list of the problem functions is in the file:\n"
926 " %s\n\n", output_path
);
933 * Verify that no user defined postfix operators exist.
936 check_for_user_defined_postfix_ops(ClusterInfo
*cluster
)
941 char output_path
[MAXPGPATH
];
943 prep_status("Checking for user-defined postfix operators");
945 snprintf(output_path
, sizeof(output_path
),
948 /* Find any user defined postfix operators */
949 for (dbnum
= 0; dbnum
< cluster
->dbarr
.ndbs
; dbnum
++)
952 bool db_used
= false;
960 DbInfo
*active_db
= &cluster
->dbarr
.dbs
[dbnum
];
961 PGconn
*conn
= connectToServer(cluster
, active_db
->db_name
);
964 * The query below hardcodes FirstNormalObjectId as 16384 rather than
965 * interpolating that C #define into the query because, if that
966 * #define is ever changed, the cutoff we want to use is the value
967 * used by pre-version 14 servers, not that of some future version.
969 res
= executeQueryOrDie(conn
,
970 "SELECT o.oid AS oproid, "
971 " n.nspname AS oprnsp, "
973 " tn.nspname AS typnsp, "
975 "FROM pg_catalog.pg_operator o, "
976 " pg_catalog.pg_namespace n, "
977 " pg_catalog.pg_type t, "
978 " pg_catalog.pg_namespace tn "
979 "WHERE o.oprnamespace = n.oid AND "
980 " o.oprleft = t.oid AND "
981 " t.typnamespace = tn.oid AND "
982 " o.oprright = 0 AND "
984 ntups
= PQntuples(res
);
985 i_oproid
= PQfnumber(res
, "oproid");
986 i_oprnsp
= PQfnumber(res
, "oprnsp");
987 i_oprname
= PQfnumber(res
, "oprname");
988 i_typnsp
= PQfnumber(res
, "typnsp");
989 i_typname
= PQfnumber(res
, "typname");
990 for (rowno
= 0; rowno
< ntups
; rowno
++)
993 if (script
== NULL
&&
994 (script
= fopen_priv(output_path
, "w")) == NULL
)
995 pg_fatal("could not open file \"%s\": %s\n",
996 output_path
, strerror(errno
));
999 fprintf(script
, "In database: %s\n", active_db
->db_name
);
1002 fprintf(script
, " (oid=%s) %s.%s (%s.%s, NONE)\n",
1003 PQgetvalue(res
, rowno
, i_oproid
),
1004 PQgetvalue(res
, rowno
, i_oprnsp
),
1005 PQgetvalue(res
, rowno
, i_oprname
),
1006 PQgetvalue(res
, rowno
, i_typnsp
),
1007 PQgetvalue(res
, rowno
, i_typname
));
1020 pg_log(PG_REPORT
, "fatal\n");
1021 pg_fatal("Your installation contains user-defined postfix operators, which are not\n"
1022 "supported anymore. Consider dropping the postfix operators and replacing\n"
1023 "them with prefix operators or function calls.\n"
1024 "A list of user-defined postfix operators is in the file:\n"
1025 " %s\n\n", output_path
);
1032 * Verify that no tables are declared WITH OIDS.
1035 check_for_tables_with_oids(ClusterInfo
*cluster
)
1038 FILE *script
= NULL
;
1040 char output_path
[MAXPGPATH
];
1042 prep_status("Checking for tables WITH OIDS");
1044 snprintf(output_path
, sizeof(output_path
),
1045 "tables_with_oids.txt");
1047 /* Find any tables declared WITH OIDS */
1048 for (dbnum
= 0; dbnum
< cluster
->dbarr
.ndbs
; dbnum
++)
1051 bool db_used
= false;
1056 DbInfo
*active_db
= &cluster
->dbarr
.dbs
[dbnum
];
1057 PGconn
*conn
= connectToServer(cluster
, active_db
->db_name
);
1059 res
= executeQueryOrDie(conn
,
1060 "SELECT n.nspname, c.relname "
1061 "FROM pg_catalog.pg_class c, "
1062 " pg_catalog.pg_namespace n "
1063 "WHERE c.relnamespace = n.oid AND "
1065 " n.nspname NOT IN ('pg_catalog')");
1067 ntups
= PQntuples(res
);
1068 i_nspname
= PQfnumber(res
, "nspname");
1069 i_relname
= PQfnumber(res
, "relname");
1070 for (rowno
= 0; rowno
< ntups
; rowno
++)
1073 if (script
== NULL
&& (script
= fopen_priv(output_path
, "w")) == NULL
)
1074 pg_fatal("could not open file \"%s\": %s\n",
1075 output_path
, strerror(errno
));
1078 fprintf(script
, "In database: %s\n", active_db
->db_name
);
1081 fprintf(script
, " %s.%s\n",
1082 PQgetvalue(res
, rowno
, i_nspname
),
1083 PQgetvalue(res
, rowno
, i_relname
));
1096 pg_log(PG_REPORT
, "fatal\n");
1097 pg_fatal("Your installation contains tables declared WITH OIDS, which is not\n"
1098 "supported anymore. Consider removing the oid column using\n"
1099 " ALTER TABLE ... SET WITHOUT OIDS;\n"
1100 "A list of tables with the problem is in the file:\n"
1101 " %s\n\n", output_path
);
1109 * check_for_reg_data_type_usage()
1110 * pg_upgrade only preserves these system values:
1115 * Many of the reg* data types reference system catalog info that is
1116 * not preserved, and hence these data types cannot be used in user
1117 * tables upgraded by pg_upgrade.
1120 check_for_reg_data_type_usage(ClusterInfo
*cluster
)
1123 FILE *script
= NULL
;
1125 char output_path
[MAXPGPATH
];
1127 prep_status("Checking for reg* data types in user tables");
1129 snprintf(output_path
, sizeof(output_path
), "tables_using_reg.txt");
1131 for (dbnum
= 0; dbnum
< cluster
->dbarr
.ndbs
; dbnum
++)
1134 bool db_used
= false;
1140 DbInfo
*active_db
= &cluster
->dbarr
.dbs
[dbnum
];
1141 PGconn
*conn
= connectToServer(cluster
, active_db
->db_name
);
1144 * While several relkinds don't store any data, e.g. views, they can
1145 * be used to define data types of other columns, so we check all
1148 res
= executeQueryOrDie(conn
,
1149 "SELECT n.nspname, c.relname, a.attname "
1150 "FROM pg_catalog.pg_class c, "
1151 " pg_catalog.pg_namespace n, "
1152 " pg_catalog.pg_attribute a, "
1153 " pg_catalog.pg_type t "
1154 "WHERE c.oid = a.attrelid AND "
1155 " NOT a.attisdropped AND "
1156 " a.atttypid = t.oid AND "
1157 " t.typnamespace = "
1158 " (SELECT oid FROM pg_namespace "
1159 " WHERE nspname = 'pg_catalog') AND"
1161 /* regclass.oid is preserved, so 'regclass' is OK */
1164 " 'regdictionary', "
1170 /* regrole.oid is preserved, so 'regrole' is OK */
1171 /* regtype.oid is preserved, so 'regtype' is OK */
1173 " c.relnamespace = n.oid AND "
1174 " n.nspname NOT IN ('pg_catalog', 'information_schema')");
1176 ntups
= PQntuples(res
);
1177 i_nspname
= PQfnumber(res
, "nspname");
1178 i_relname
= PQfnumber(res
, "relname");
1179 i_attname
= PQfnumber(res
, "attname");
1180 for (rowno
= 0; rowno
< ntups
; rowno
++)
1183 if (script
== NULL
&& (script
= fopen_priv(output_path
, "w")) == NULL
)
1184 pg_fatal("could not open file \"%s\": %s\n",
1185 output_path
, strerror(errno
));
1188 fprintf(script
, "In database: %s\n", active_db
->db_name
);
1191 fprintf(script
, " %s.%s.%s\n",
1192 PQgetvalue(res
, rowno
, i_nspname
),
1193 PQgetvalue(res
, rowno
, i_relname
),
1194 PQgetvalue(res
, rowno
, i_attname
));
1207 pg_log(PG_REPORT
, "fatal\n");
1208 pg_fatal("Your installation contains one of the reg* data types in user tables.\n"
1209 "These data types reference system OIDs that are not preserved by\n"
1210 "pg_upgrade, so this cluster cannot currently be upgraded. You can\n"
1211 "remove the problem tables and restart the upgrade. A list of the\n"
1212 "problem columns is in the file:\n"
1213 " %s\n\n", output_path
);
1221 * check_for_jsonb_9_4_usage()
1223 * JSONB changed its storage format during 9.4 beta, so check for it.
1226 check_for_jsonb_9_4_usage(ClusterInfo
*cluster
)
1229 FILE *script
= NULL
;
1231 char output_path
[MAXPGPATH
];
1233 prep_status("Checking for incompatible \"jsonb\" data type");
1235 snprintf(output_path
, sizeof(output_path
), "tables_using_jsonb.txt");
1237 for (dbnum
= 0; dbnum
< cluster
->dbarr
.ndbs
; dbnum
++)
1240 bool db_used
= false;
1246 DbInfo
*active_db
= &cluster
->dbarr
.dbs
[dbnum
];
1247 PGconn
*conn
= connectToServer(cluster
, active_db
->db_name
);
1250 * While several relkinds don't store any data, e.g. views, they can
1251 * be used to define data types of other columns, so we check all
1254 res
= executeQueryOrDie(conn
,
1255 "SELECT n.nspname, c.relname, a.attname "
1256 "FROM pg_catalog.pg_class c, "
1257 " pg_catalog.pg_namespace n, "
1258 " pg_catalog.pg_attribute a "
1259 "WHERE c.oid = a.attrelid AND "
1260 " NOT a.attisdropped AND "
1261 " a.atttypid = 'pg_catalog.jsonb'::pg_catalog.regtype AND "
1262 " c.relnamespace = n.oid AND "
1263 /* exclude possible orphaned temp tables */
1264 " n.nspname !~ '^pg_temp_' AND "
1265 " n.nspname NOT IN ('pg_catalog', 'information_schema')");
1267 ntups
= PQntuples(res
);
1268 i_nspname
= PQfnumber(res
, "nspname");
1269 i_relname
= PQfnumber(res
, "relname");
1270 i_attname
= PQfnumber(res
, "attname");
1271 for (rowno
= 0; rowno
< ntups
; rowno
++)
1274 if (script
== NULL
&& (script
= fopen_priv(output_path
, "w")) == NULL
)
1275 pg_fatal("could not open file \"%s\": %s\n",
1276 output_path
, strerror(errno
));
1279 fprintf(script
, "In database: %s\n", active_db
->db_name
);
1282 fprintf(script
, " %s.%s.%s\n",
1283 PQgetvalue(res
, rowno
, i_nspname
),
1284 PQgetvalue(res
, rowno
, i_relname
),
1285 PQgetvalue(res
, rowno
, i_attname
));
1298 pg_log(PG_REPORT
, "fatal\n");
1299 pg_fatal("Your installation contains the \"jsonb\" data type in user tables.\n"
1300 "The internal format of \"jsonb\" changed during 9.4 beta so this\n"
1301 "cluster cannot currently be upgraded. You can remove the problem\n"
1302 "tables and restart the upgrade. A list of the problem columns is\n"
1304 " %s\n\n", output_path
);
1311 * check_for_pg_role_prefix()
1313 * Versions older than 9.6 should not have any pg_* roles
1316 check_for_pg_role_prefix(ClusterInfo
*cluster
)
1319 PGconn
*conn
= connectToServer(cluster
, "template1");
1321 prep_status("Checking for roles starting with \"pg_\"");
1323 res
= executeQueryOrDie(conn
,
1325 "FROM pg_catalog.pg_roles "
1326 "WHERE rolname ~ '^pg_'");
1328 if (PQntuples(res
) != 0)
1330 if (cluster
== &old_cluster
)
1331 pg_fatal("The source cluster contains roles starting with \"pg_\"\n");
1333 pg_fatal("The target cluster contains roles starting with \"pg_\"\n");
1345 * get_canonical_locale_name
1347 * Send the locale name to the system, and hope we get back a canonical
1348 * version. This should match the backend's check_locale() function.
1351 get_canonical_locale_name(int category
, const char *locale
)
1356 /* get the current setting, so we can restore it. */
1357 save
= setlocale(category
, NULL
);
1359 pg_fatal("failed to get the current locale\n");
1361 /* 'save' may be pointing at a modifiable scratch variable, so copy it. */
1362 save
= pg_strdup(save
);
1364 /* set the locale with setlocale, to see if it accepts it. */
1365 res
= setlocale(category
, locale
);
1368 pg_fatal("failed to get system locale name for \"%s\"\n", locale
);
1370 res
= pg_strdup(res
);
1372 /* restore old value. */
1373 if (!setlocale(category
, save
))
1374 pg_fatal("failed to restore old locale \"%s\"\n", save
);