Waiting for 9.4 – pg_basebackup: Add support for relocating tablespaces

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.

3 thoughts on “Waiting for 9.4 – pg_basebackup: Add support for relocating tablespaces”

  1. Table space mapping is cool.

    Just need someone to knock out xlog dir mapping…

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.