Re: [ADMIN] postgres pg_basebackup

Wolfgang Brandl wrote:
> I have a database with the following tablespaces:
> 
> pgwb=# \db
>                    List of tablespaces
>     Name    | Owner |              Location
> ------------+-------+-------------------------------------
>  bspace     | pgwb  | /data1/tablespaces/TS_B_SPACE/tests
>  meta       | pgwb  | /data1/tablespaces/metadata/tests
>  mspace     | pgwb  | /data1/tablespaces/TS_M_SPACE/tests
>  nspace     | pgwb  | /data1/tablespaces/TS_N_SPACE/tests
>  pg_default | pgwb  |
>  pg_global  | pgwb  |
>  uspace     | pgwb  | /data1/tablespaces/TS_U_SPACE/tests
> (7 rows)
> 
> They are referenced in as symbolic links in:
> 
> pgwb@BLIXSQL0:/data1/pgwb/pg_tblspc> ls -l /data1/pgwb/pg_tblspc
> total 0
> lrwxrwxrwx 1 pgwb users 35 May  9 09:17 16536 -> /data1/tablespaces/TS_U_SPACE/tests
> lrwxrwxrwx 1 pgwb users 35 May  9 09:17 16537 -> /data1/tablespaces/TS_B_SPACE/tests
> lrwxrwxrwx 1 pgwb users 35 May  9 09:17 16538 -> /data1/tablespaces/TS_N_SPACE/tests
> lrwxrwxrwx 1 pgwb users 35 May  9 09:17 16539 -> /data1/tablespaces/TS_M_SPACE/tests
> lrwxrwxrwx 1 pgwb users 33 May  9 09:17 16540 -> /data1/tablespaces/metadata/tests
> 
> I have setup the database for PIT recovery.
> 
> To make a base backup I use pg_basbackup like:
> pg_basebackup --xlog --format=t -D /data1/backup/`date +%Y%m%d`
> 
> In the backup folder I have the following files:
> pgwb@BLIXSQL0:/data1/backup/20170510> ls -l
> total 62772
> -rw-r--r-- 1 pgwb users   289792 May 10 13:51 16536.tar
> -rw-r--r-- 1 pgwb users  2821120 May 10 13:51 16537.tar
> -rw-r--r-- 1 pgwb users  2821120 May 10 13:51 16538.tar
> -rw-r--r-- 1 pgwb users   371712 May 10 13:51 16539.tar
> -rw-r--r-- 1 pgwb users   716800 May 10 13:51 16540.tar
> -rw-r--r-- 1 pgwb users 57170432 May 10 13:51 base.tar
> 
> 
> Now a crash happens I have to restore from this tar files, but I don’t know the symbolic
> link destination.
> 
> How can I solve this?
> 
> Do I need a pg_dump from the tablespaces?

You need to unpack the TAR files to directories and create symbolic links
from the "pg_tblspc" subdirectory of the PostgreSQL data directory
to these directories.

The name of the symbolic link has to be the object ID of the tablespace,
which is the number that is the name of the TAR file.

So the only information you might need for a restore is where you want
each tablespace to reside.  This is not stored inside the database
since release 9.2, and you'd have to document is somewhere.

Yours,
Laurenz Albe