From ca445043e78ef7b2bbb911739f60b7a4726702b1 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Fri, 11 Sep 2015 15:51:10 -0400 Subject: [PATCH] pg_dump, pg_upgrade: allow postgres/template1 tablespace moves Modify pg_dump to restore postgres/template1 databases to non-default tablespaces by switching out of the database to be moved, then switching back. Also, to fix potentially cases where the old/new tablespaces might not match, fix pg_upgrade to process new/old tablespaces separately in all cases. Report by Marti Raudsepp Patch by Marti Raudsepp, me Backpatch through 9.0 --- contrib/pg_upgrade/info.c | 20 +++++++++++++++++--- src/bin/pg_dump/pg_dumpall.c | 18 ++++++++++++++++++ 2 files changed, 35 insertions(+), 3 deletions(-) diff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.c index 7dd810a72a8..034aa71add2 100644 --- a/contrib/pg_upgrade/info.c +++ b/contrib/pg_upgrade/info.c @@ -140,6 +140,7 @@ create_rel_filename_map(const char *old_data, const char *new_data, const RelInfo *old_rel, const RelInfo *new_rel, FileNameMap *map) { + /* In case old/new tablespaces don't match, do them separately. */ if (strlen(old_rel->tablespace) == 0) { /* @@ -147,17 +148,30 @@ create_rel_filename_map(const char *old_data, const char *new_data, * exist in the data directories. */ strlcpy(map->old_tablespace, old_data, sizeof(map->old_tablespace)); - strlcpy(map->new_tablespace, new_data, sizeof(map->new_tablespace)); strlcpy(map->old_tablespace_suffix, "/base", sizeof(map->old_tablespace_suffix)); - strlcpy(map->new_tablespace_suffix, "/base", sizeof(map->new_tablespace_suffix)); } else { /* relation belongs to a tablespace, so use the tablespace location */ strlcpy(map->old_tablespace, old_rel->tablespace, sizeof(map->old_tablespace)); - strlcpy(map->new_tablespace, new_rel->tablespace, sizeof(map->new_tablespace)); strlcpy(map->old_tablespace_suffix, old_cluster.tablespace_suffix, sizeof(map->old_tablespace_suffix)); + } + + /* Do the same for new tablespaces */ + if (strlen(new_rel->tablespace) == 0) + { + /* + * relation belongs to the default tablespace, hence relfiles should + * exist in the data directories. + */ + strlcpy(map->new_tablespace, new_data, sizeof(map->new_tablespace)); + strlcpy(map->new_tablespace_suffix, "/base", sizeof(map->new_tablespace_suffix)); + } + else + { + /* relation belongs to a tablespace, so use the tablespace location */ + strlcpy(map->new_tablespace, new_rel->tablespace, sizeof(map->new_tablespace)); strlcpy(map->new_tablespace_suffix, new_cluster.tablespace_suffix, sizeof(map->new_tablespace_suffix)); } diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c index e988958e1b7..3b8e5350856 100644 --- a/src/bin/pg_dump/pg_dumpall.c +++ b/src/bin/pg_dump/pg_dumpall.c @@ -1383,6 +1383,24 @@ dumpCreateDB(PGconn *conn) appendPQExpBuffer(buf, ";\n"); } } + else if (strcmp(dbtablespace, "pg_default") != 0 && !no_tablespaces) + { + /* + * Cannot change tablespace of the database we're connected to, + * so to move "postgres" to another tablespace, we connect to + * "template1", and vice versa. + */ + if (strcmp(dbname, "postgres") == 0) + appendPQExpBuffer(buf, "\\connect template1\n"); + else + appendPQExpBuffer(buf, "\\connect postgres\n"); + + appendPQExpBuffer(buf, "ALTER DATABASE %s SET TABLESPACE %s;\n", + fdbname, fmtId(dbtablespace)); + + /* connect to original database */ + appendPQExpBuffer(buf, "\\connect %s\n", fdbname); + } if (binary_upgrade) { -- 2.39.5