2

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 and I have to restore from these 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?

Community
  • 1
Wolfgang Brandl
  • 21
  • 3

2 Answers2

1

You will find a mapping file named tablespace_map in your base.tar file.

Here's the documentation:

When tar format mode is used, it is the user's responsibility to unpack each tar file before starting the PostgreSQL server. If there are additional tablespaces, the tar files for them need to be unpacked in the correct locations. In this case the symbolic links for those tablespaces will be created by the server according to the contents of the tablespace_map file that is included in the base.tar file.

Link: https://www.postgresql.org/docs/9.6/static/app-pgbasebackup.html

Arkhena
  • 1,610
  • 10
  • 15
0

I know that you certainly do not need it now but maybe others could use it. Since we faced lately some crashes here is full description of restoring from pg_basebackup tar files - postgresql.freeideas.cz/pg_basebackup-pgbarman-restore-tar-backup/

JosMac
  • 101
  • 1