10000 pg_dump, pg_upgrade: allow postgres/template1 tablespace moves · percona/postgres@52b0777 · GitHub
[go: up one dir, main page]
Skip to content

Commit 52b0777

Browse files
bmomjianbmomjian
committed
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
1 parent 6d36d7b commit 52b0777

File tree

2 files changed

+44
-5
lines changed

2 files changed

+44
-5
lines changed

‎contrib/pg_upgrade/info.c

Lines changed: 22 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -27,7 +27,7 @@ static void map_rel(migratorContext *ctx, const RelInfo *oldrel,
2727
static void map_rel_by_id(migratorContext *ctx, Oid oldid, Oid newid,
2828
const char *old_nspname, const char *old_relname,
2929
const char *new_nspname, const char *new_relname,
30-
const char *old_tablespace, const DbInfo *old_db,
30+
const char *old_tablespace, const char *new_tablespace, const DbInfo *old_db,
3131
const DbInfo *new_db, const char *olddata,
3232
const char *newdata, FileNameMap *map);
3333
static RelInfo *relarr_lookup_reloid(migratorContext *ctx,
@@ -140,7 +140,7 @@ map_rel(migratorContext *ctx, const RelInfo *oldrel, const RelInfo *newrel,
140140
const char *newdata, FileNameMap *map)
141141
{
142142
map_rel_by_id(ctx, oldrel->relfilenode, newrel->relfilenode, oldrel->nspname,
143-
oldrel->relname, newrel->nspname, newrel->relname, oldrel->tablespace, old_db,
143+
oldrel->relname, newrel->nspname, newrel->relname, oldrel->tablespace, newrel->tablespace, old_db,
144144
new_db, olddata, newdata, map);
145145
}
146146

@@ -154,7 +154,7 @@ static void
154154
map_rel_by_id(migratorContext *ctx, Oid oldid, Oid newid,
155155
const char *old_nspname, const char *old_relname,
156156
const char *new_nspname, const char *new_relname,
157-
const char *old_tablespace, const DbInfo *old_db,
157+
const char *old_tablespace, const char *new_tablespace, const DbInfo *old_db,
158158
const DbInfo *new_db, const char *olddata,
159159
const char *newdata, FileNameMap *map)
160160
{
@@ -166,14 +166,14 @@ map_rel_by_id(migratorContext *ctx, Oid oldid, Oid newid,
166166
snprintf(map->new_nspname, sizeof(map->new_nspname), "%s", new_nspname);
167167
snprintf(map->new_relname, sizeof(map->new_relname), "%s", new_relname);
168168

169+
/* In case old/new tablespaces don't match, do them separately. */
169170
if (strlen(old_tablespace) == 0)
170171
{
171172
/*
172173
* relation belongs to the default tablespace, hence relfiles would
173174
* exist in the data directories.
174175
*/
175176
snprintf(map->old_file, sizeof(map->old_file), "%s/base/%u", olddata, old_db->db_oid);
176-
snprintf(map->new_file, sizeof(map->new_file), "%s/base/%u", newdata, new_db->db_oid);
177177
}
178178
else
179179
{
@@ -183,7 +183,24 @@ map_rel_by_id(migratorContext *ctx, Oid oldid, Oid newid,
183183
*/
184184
snprintf(map->old_file, sizeof(map->old_file), "%s%s/%u", old_tablespace,
185185
ctx->old.tablespace_suffix, old_db->db_oid);
186-
snprintf(map->new_file, sizeof(map->new_file), "%s%s/%u", old_tablespace,
186+
}
187+
188+
/* Do the same for new tablespaces */
189+
if (strlen(new_tablespace) == 0)
190+
{
191+
/*
192+
* relation belongs to the default tablespace, hence relfiles would
193+
* exist in the data directories.
194+
*/
195+
snprintf(map->new_file, sizeof(map->new_file), "%s/base/%u", newdata, new_db->db_oid);
196+
}
197+
else
198+
{
199+
/*
200+
* relation belongs to some tablespace, hence copy its physical
201+
* location
202+
*/
203+
snprintf(map->new_file, sizeof(map->new_file), "%s%s/%u", new_tablespace,
187204
ctx->new.tablespace_suffix, new_db->db_oid);
188205
}
189206
}

‎src/bin/pg_dump/pg_dumpall.c

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1303,6 +1303,28 @@ dumpCreateDB(PGconn *conn)
13031303
appendPQExpBuffer(buf, ";\n");
13041304
}
13051305
}
1306+
else if (strcmp(dbtablespace, "pg_default") != 0 && !no_tablespaces)
1307+
{
1308+
/*
1309+
* Cannot change tablespace of the database we're connected to,
1310+
* so to move "postgres" to another tablespace, we connect to
1311+
* "template1", and vice versa.
1312+
*/
1313+
if (strcmp(dbname, "postgres") == 0)
1314+
appendPQExpBuffer(buf, "%s\\connect template1\n",
1315+
/* Add a space before \\connect so pg_upgrade can split */
1316+
binary_upgrade ? " " : "");
1317+
else
1318+
appendPQExpBuffer(buf, "%s\\connect postgres\n",
1319+
binary_upgrade ? " " : "");
1320+
1321+
appendPQExpBuffer(buf, "ALTER DATABASE %s SET TABLESPACE %s;\n",
1322+
fdbname, fmtId(dbtablespace));
1323+
1324+
/* connect to original database */
1325+
appendPQExpBuffer(buf, "%s\\connect %s\n",
1326+
binary_upgrade ? " " : "", fdbname);
1327+
}
13061328

13071329
if (binary_upgrade)
13081330
{

0 commit comments

Comments
 (0)
0