On 22nd of February, Peter Eisentraut committed patch:
pg_basebackup: Add support for relocating tablespaces Tablespaces can be relocated in plain backup mode by specifying one or more -T olddir=newdir options. Author: Steeve Lennmark Reviewed-by: Peter Eisentraut
Tablespaces are not among the most commonly used features of PostgreSQL, but they have their uses. Especially “in the cloud" where you can easily add new, fast, “disks" to machine, and might want to move some tables/indexes there.
So far, when you made pg_basebackup – you got your tablespaces exactly like it was on master.
Now, you can get backups (or new slaves) setup with tablespaces pointing to another directories. All without manual work and risk of damaging anything. Let's see how that works…
I have simple database, with one extra tablespace:
\db List OF tablespaces Name | Owner | Location ------------+-------+---------- pg_default | pgdba | pg_global | pgdba | t | pgdba | /tmp/pg (3 ROWS)
I also created two tables, one in default tablespace, and the other in the new, special, one:
\d t1 TABLE "public.t1" COLUMN | TYPE | Modifiers --------+---------+----------- i | INTEGER | \d t2 TABLE "public.t2" COLUMN | TYPE | Modifiers --------+---------+----------- i | INTEGER | Tablespace: "t"
Now, if I'll make normal pg_basebackup (with output to directory, as you can't have tablespace mapping with tarballs):
=$ pg_basebackup --checkpoint=fast --pgdata=back1/ pg_basebackup: directory "/tmp/pg" exists but is not empty
This happened because I actually ran this on the same machine that the server is running, so pg_basebackup correctly aborted not to overwrite real server data.
Now, with new pg_basebackup I can map /tmp/pg to new dir:
=$ pg_basebackup --checkpoint=fast --pgdata=back2/ --tablespace-mapping=/tmp/pg=/var/tmp/new NOTICE: pg_stop_backup complete, all required WAL segments have been archived
This worked out fine, and it did create all the dirs:
=$ find /var/tmp/new/ /var/tmp/new/ /var/tmp/new/PG_9.4_201403032 /var/tmp/new/PG_9.4_201403032/16393 /var/tmp/new/PG_9.4_201403032/16393/35246
and it did change symlink in the new PGDATA:
=$ ls -l back2/pg_tblspc/ total 0 lrwxrwxrwx 1 pgdba pgdba 12 Mar 4 20:26 35239 -> /var/tmp/new/
Nice. I'm not entirely sure why tablespace mapping isn't working with tarballs, but that's not all that important – you can always change the symlink yourself after unpacking tar.
It's a very nice addition, and it will definitely make life easier for some dbas. Thanks guys.