The attached patch adds to pg_dumpall --binary-upgrade by restoring
information about frozen xids for relations and databases. I think this
is the last patch I need to complete my TODO items for the pg_migrator
binary upgrade utility.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.523
diff -c -c -r1.523 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 17 Feb 2009 22:32:54 -0000 1.523
--- src/bin/pg_dump/pg_dump.c 17 Feb 2009 22:34:08 -0000
***************
*** 1585,1590 ****
--- 1585,1591 ----
i_encoding,
i_collate,
i_ctype,
+ i_frozenxid,
i_tablespace;
CatalogId dbCatId;
DumpId dbDumpId;
***************
*** 1594,1599 ****
--- 1595,1601 ----
*collate,
*ctype,
*tablespace;
+ uint32 frozenxid;
datname = PQdb(g_conn);
***************
*** 1609,1615 ****
appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
"(%s datdba) AS dba, "
"pg_encoding_to_char(encoding) AS encoding, "
! "datcollate, datctype, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
"shobj_description(oid, 'pg_database') AS description "
--- 1611,1617 ----
appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
"(%s datdba) AS dba, "
"pg_encoding_to_char(encoding) AS encoding, "
! "datcollate, datctype, datfrozenxid, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
"shobj_description(oid, 'pg_database') AS description "
***************
*** 1623,1629 ****
appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
"(%s datdba) AS dba, "
"pg_encoding_to_char(encoding) AS encoding, "
! "NULL AS datcollate, NULL AS datctype, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
"shobj_description(oid, 'pg_database') AS description "
--- 1625,1631 ----
appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
"(%s datdba) AS dba, "
"pg_encoding_to_char(encoding) AS encoding, "
! "NULL AS datcollate, NULL AS datctype, datfrozenxid, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace, "
"shobj_description(oid, 'pg_database') AS description "
***************
*** 1637,1643 ****
appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
"(%s datdba) AS dba, "
"pg_encoding_to_char(encoding) AS encoding, "
! "NULL AS datcollate, NULL AS datctype, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace "
"FROM pg_database "
"WHERE datname = ",
--- 1639,1645 ----
appendPQExpBuffer(dbQry, "SELECT tableoid, oid, "
"(%s datdba) AS dba, "
"pg_encoding_to_char(encoding) AS encoding, "
! "NULL AS datcollate, NULL AS datctype, datfrozenxid, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = dattablespace) AS tablespace "
"FROM pg_database "
"WHERE datname = ",
***************
*** 1650,1655 ****
--- 1652,1658 ----
"(%s datdba) AS dba, "
"pg_encoding_to_char(encoding) AS encoding, "
"NULL AS datcollate, NULL AS datctype, "
+ "0 AS datfrozenxid, "
"NULL AS tablespace "
"FROM pg_database "
"WHERE datname = ",
***************
*** 1664,1669 ****
--- 1667,1673 ----
"(%s datdba) AS dba, "
"pg_encoding_to_char(encoding) AS encoding, "
"NULL AS datcollate, NULL AS datctype, "
+ "0 AS datfrozenxid, "
"NULL AS tablespace "
"FROM pg_database "
"WHERE datname = ",
***************
*** 1696,1701 ****
--- 1700,1706 ----
i_encoding = PQfnumber(res, "encoding");
i_collate = PQfnumber(res, "datcollate");
i_ctype = PQfnumber(res, "datctype");
+ i_frozenxid = PQfnumber(res, "datfrozenxid");
i_tablespace = PQfnumber(res, "tablespace");
dbCatId.tableoid = atooid(PQgetvalue(res, 0, i_tableoid));
***************
*** 1704,1709 ****
--- 1709,1715 ----
encoding = PQgetvalue(res, 0, i_encoding);
collate = PQgetvalue(res, 0, i_collate);
ctype = PQgetvalue(res, 0, i_ctype);
+ frozenxid = atooid(PQgetvalue(res, 0, i_frozenxid));
tablespace = PQgetvalue(res, 0, i_tablespace);
appendPQExpBuffer(creaQry, "CREATE DATABASE %s WITH TEMPLATE = template0",
***************
*** 1728,1733 ****
--- 1734,1748 ----
fmtId(tablespace));
appendPQExpBuffer(creaQry, ";\n");
+ if (binary_upgrade)
+ {
+ appendPQExpBuffer(creaQry, "\n-- For binary upgrade, set datfrozenxid.\n");
+ appendPQExpBuffer(creaQry, "UPDATE pg_database\n"
+ "SET datfrozenxid = '%u'\n"
+ "WHERE datname = '%s';\n",
+ frozenxid, datname);
+ }
+
appendPQExpBuffer(delQry, "DROP DATABASE %s;\n",
fmtId(datname));
***************
*** 3114,3119 ****
--- 3129,3135 ----
int i_relhasindex;
int i_relhasrules;
int i_relhasoids;
+ int i_relfrozenxid;
int i_owning_tab;
int i_owning_col;
int i_reltablespace;
***************
*** 3155,3160 ****
--- 3171,3177 ----
"(%s c.relowner) AS rolname, "
"c.relchecks, c.relhastriggers, "
"c.relhasindex, c.relhasrules, c.relhasoids, "
+ "c.relfrozenxid, "
"d.refobjid AS owning_tab, "
"d.refobjsubid AS owning_col, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
***************
*** 3186,3191 ****
--- 3203,3209 ----
"(%s relowner) AS rolname, "
"relchecks, (reltriggers <> 0) AS relhastriggers, "
"relhasindex, relhasrules, relhasoids, "
+ "relfrozenxid, "
"d.refobjid AS owning_tab, "
"d.refobjsubid AS owning_col, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
***************
*** 3216,3221 ****
--- 3234,3240 ----
"(%s relowner) AS rolname, "
"relchecks, (reltriggers <> 0) AS relhastriggers, "
"relhasindex, relhasrules, relhasoids, "
+ "0 AS relfrozenxid, "
"d.refobjid AS owning_tab, "
"d.refobjsubid AS owning_col, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
***************
*** 3246,3251 ****
--- 3265,3271 ----
"(%s relowner) AS rolname, "
"relchecks, (reltriggers <> 0) AS relhastriggers, "
"relhasindex, relhasrules, relhasoids, "
+ "0 AS relfrozenxid, "
"d.refobjid AS owning_tab, "
"d.refobjsubid AS owning_col, "
"NULL AS reltablespace, "
***************
*** 3272,3277 ****
--- 3292,3298 ----
"(%s relowner) AS rolname, "
"relchecks, (reltriggers <> 0) AS relhastriggers, "
"relhasindex, relhasrules, relhasoids, "
+ "0 AS relfrozenxid, "
"NULL::oid AS owning_tab, "
"NULL::int4 AS owning_col, "
"NULL AS reltablespace, "
***************
*** 3293,3298 ****
--- 3314,3320 ----
"relchecks, (reltriggers <> 0) AS relhastriggers, "
"relhasindex, relhasrules, "
"'t'::bool AS relhasoids, "
+ "0 AS relfrozenxid, "
"NULL::oid AS owning_tab, "
"NULL::int4 AS owning_col, "
"NULL AS reltablespace, "
***************
*** 3324,3329 ****
--- 3346,3352 ----
"relchecks, (reltriggers <> 0) AS relhastriggers, "
"relhasindex, relhasrules, "
"'t'::bool AS relhasoids, "
+ "0 as relfrozenxid, "
"NULL::oid AS owning_tab, "
"NULL::int4 AS owning_col, "
"NULL AS reltablespace, "
***************
*** 3367,3372 ****
--- 3390,3396 ----
i_relhasindex = PQfnumber(res, "relhasindex");
i_relhasrules = PQfnumber(res, "relhasrules");
i_relhasoids = PQfnumber(res, "relhasoids");
+ i_relfrozenxid = PQfnumber(res, "relfrozenxid");
i_owning_tab = PQfnumber(res, "owning_tab");
i_owning_col = PQfnumber(res, "owning_col");
i_reltablespace = PQfnumber(res, "reltablespace");
***************
*** 3404,3409 ****
--- 3428,3434 ----
tblinfo[i].hasrules = (strcmp(PQgetvalue(res, i, i_relhasrules), "t") == 0);
tblinfo[i].hastriggers = (strcmp(PQgetvalue(res, i, i_relhastriggers), "t") == 0);
tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0);
+ tblinfo[i].frozenxid = atooid(PQgetvalue(res, i, i_relfrozenxid));
tblinfo[i].ncheck = atoi(PQgetvalue(res, i, i_relchecks));
if (PQgetisnull(res, i, i_owning_tab))
{
***************
*** 9860,9865 ****
--- 9885,9899 ----
tbinfo->dobj.name);
}
}
+ appendPQExpBuffer(q, "\n-- For binary upgrade, set relfrozenxid.\n");
+ appendPQExpBuffer(q, "UPDATE pg_class\n"
+ "SET relfrozenxid = '%u'\n"
+ "WHERE relname = '%s'\n"
+ " AND relnamespace = "
+ "(SELECT oid FROM pg_namespace "
+ "WHERE nspname = CURRENT_SCHEMA);\n",
+ tbinfo->frozenxid,
+ tbinfo->dobj.name);
}
/* Loop dumping statistics and storage statements */
Index: src/bin/pg_dump/pg_dump.h
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.h,v
retrieving revision 1.151
diff -c -c -r1.151 pg_dump.h
*** src/bin/pg_dump/pg_dump.h 17 Feb 2009 15:41:50 -0000 1.151
--- src/bin/pg_dump/pg_dump.h 17 Feb 2009 22:34:08 -0000
***************
*** 226,231 ****
--- 226,232 ----
bool hasrules; /* does it have any rules? */
bool hastriggers; /* does it have any triggers? */
bool hasoids; /* does it have OIDs? */
+ uint32 frozenxid; /* for restore frozen xid */
int ncheck; /* # of CHECK expressions */
/* these two are set only if table is a sequence owned by a column: */
Oid owning_tab; /* OID of table owning sequence */
Index: src/bin/pg_dump/pg_dumpall.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.114
diff -c -c -r1.114 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c 17 Feb 2009 15:41:50 -0000 1.114
--- src/bin/pg_dump/pg_dumpall.c 17 Feb 2009 22:34:08 -0000
***************
*** 27,33 ****
#endif
#include "dumputils.h"
!
/* version string we expect back from pg_dump */
#define PGDUMP_VERSIONSTR "pg_dump (PostgreSQL) " PG_VERSION "\n"
--- 27,33 ----
#endif
#include "dumputils.h"
! #include "pg_backup.h"
/* version string we expect back from pg_dump */
#define PGDUMP_VERSIONSTR "pg_dump (PostgreSQL) " PG_VERSION "\n"
***************
*** 71,76 ****
--- 71,78 ----
static FILE *OPF;
static char *filename = NULL;
+ static int binary_upgrade = 0;
+
int
main(int argc, char *argv[])
{
***************
*** 90,96 ****
const char *std_strings;
int c,
ret;
- int binary_upgrade = 0;
struct option long_options[] = {
{"binary-upgrade", no_argument, &binary_upgrade, 1}, /* not documented */
--- 92,97 ----
***************
*** 937,943 ****
"SELECT datname, "
"coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from
pg_databasewhere datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
! "datcollate, datctype, "
"datistemplate, datacl, datconnlimit, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
"FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
--- 938,944 ----
"SELECT datname, "
"coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from
pg_databasewhere datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
! "datcollate, datctype, datfrozenxid, "
"datistemplate, datacl, datconnlimit, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
"FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
***************
*** 947,953 ****
"SELECT datname, "
"coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from
pg_databasewhere datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
! "null::text AS datcollate, null::text AS datctype, "
"datistemplate, datacl, datconnlimit, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
"FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
--- 948,954 ----
"SELECT datname, "
"coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from
pg_databasewhere datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
! "null::text AS datcollate, null::text AS datctype, datfrozenxid, "
"datistemplate, datacl, datconnlimit, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
"FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
***************
*** 957,963 ****
"SELECT datname, "
"coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from
pg_databasewhere datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
! "null::text AS datcollate, null::text AS datctype, "
"datistemplate, datacl, -1 as datconnlimit, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
"FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
--- 958,964 ----
"SELECT datname, "
"coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from
pg_databasewhere datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
! "null::text AS datcollate, null::text AS datctype, datfrozenxid, "
"datistemplate, datacl, -1 as datconnlimit, "
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
"FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
***************
*** 967,973 ****
"SELECT datname, "
"coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from
pg_databasewhere datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
! "null::text AS datcollate, null::text AS datctype, "
"datistemplate, datacl, -1 as datconnlimit, "
"'pg_default' AS dattablespace "
"FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
--- 968,974 ----
"SELECT datname, "
"coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from
pg_databasewhere datname='template0'))), "
"pg_encoding_to_char(d.encoding), "
! "null::text AS datcollate, null::text AS datctype, datfrozenxid, "
"datistemplate, datacl, -1 as datconnlimit, "
"'pg_default' AS dattablespace "
"FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
***************
*** 979,985 ****
"(select usename from pg_shadow where usesysid=datdba), "
"(select usename from pg_shadow where usesysid=(select datdba from pg_database where
datname='template0'))),"
"pg_encoding_to_char(d.encoding), "
! "null::text AS datcollate, null::text AS datctype, "
"datistemplate, '' as datacl, -1 as datconnlimit, "
"'pg_default' AS dattablespace "
"FROM pg_database d "
--- 980,986 ----
"(select usename from pg_shadow where usesysid=datdba), "
"(select usename from pg_shadow where usesysid=(select datdba from pg_database where
datname='template0'))),"
"pg_encoding_to_char(d.encoding), "
! "null::text AS datcollate, null::text AS datctype, 0 AS datfrozenxid"
"datistemplate, '' as datacl, -1 as datconnlimit, "
"'pg_default' AS dattablespace "
"FROM pg_database d "
***************
*** 994,1000 ****
"SELECT datname, "
"(select usename from pg_shadow where usesysid=datdba), "
"pg_encoding_to_char(d.encoding), "
! "null::text AS datcollate, null::text AS datctype, "
"'f' as datistemplate, "
"'' as datacl, -1 as datconnlimit, "
"'pg_default' AS dattablespace "
--- 995,1001 ----
"SELECT datname, "
"(select usename from pg_shadow where usesysid=datdba), "
"pg_encoding_to_char(d.encoding), "
! "null::text AS datcollate, null::text AS datctype, 0 AS datfrozenxid"
"'f' as datistemplate, "
"'' as datacl, -1 as datconnlimit, "
"'pg_default' AS dattablespace "
***************
*** 1009,1018 ****
char *dbencoding = PQgetvalue(res, i, 2);
char *dbcollate = PQgetvalue(res, i, 3);
char *dbctype = PQgetvalue(res, i, 4);
! char *dbistemplate = PQgetvalue(res, i, 5);
! char *dbacl = PQgetvalue(res, i, 6);
! char *dbconnlimit = PQgetvalue(res, i, 7);
! char *dbtablespace = PQgetvalue(res, i, 8);
char *fdbname;
fdbname = strdup(fmtId(dbname));
--- 1010,1020 ----
char *dbencoding = PQgetvalue(res, i, 2);
char *dbcollate = PQgetvalue(res, i, 3);
char *dbctype = PQgetvalue(res, i, 4);
! uint32 dbfrozenxid = atooid(PQgetvalue(res, i, 5));
! char *dbistemplate = PQgetvalue(res, i, 6);
! char *dbacl = PQgetvalue(res, i, 7);
! char *dbconnlimit = PQgetvalue(res, i, 8);
! char *dbtablespace = PQgetvalue(res, i, 9);
char *fdbname;
fdbname = strdup(fmtId(dbname));
***************
*** 1076,1081 ****
--- 1078,1092 ----
appendStringLiteralConn(buf, dbname, conn);
appendPQExpBuffer(buf, ";\n");
}
+
+ if (binary_upgrade)
+ {
+ appendPQExpBuffer(buf, "\n-- For binary upgrade, set datfrozenxid.\n");
+ appendPQExpBuffer(buf, "UPDATE pg_database\n"
+ "SET datfrozenxid = '%u'\n"
+ "WHERE datname = '%s';\n",
+ dbfrozenxid, fdbname);
+ }
}
if (!skip_acls &&