Re: [ADMIN] postgres pg_basebackup

Hello

I restored the files into some new locations and made the symbolic links like:

pgwb@BLIXSQL0:/data1/pgwb/pg_tblspc> ls -l

total 0

lrwxrwxrwx 1 pgwb users 24 May 10 15:42 16536 -> /data1/tablespaces/16536

lrwxrwxrwx 1 pgwb users 24 May 10 15:43 16537 -> /data1/tablespaces/16537

lrwxrwxrwx 1 pgwb users 24 May 10 15:43 16538 -> /data1/tablespaces/16538

lrwxrwxrwx 1 pgwb users 24 May 10 15:43 16539 -> /data1/tablespaces/16539

lrwxrwxrwx 1 pgwb users 24 May 10 15:43 16540 -> /data1/tablespaces/16540

After the start with:

pg_ctl -D /data1/pgwb -l /logs1/logs/logfile start

I got he following links in pg_tblspc:

pgwb@BLIXSQL0:/data1/pgwb> ls -xal pg_tblspc/

total 8

drwx------  2 pgwb users 4096 May 10 15:45 .

drwx------ 19 pgwb pg    4096 May 10 15:45 ..

lrwxrwxrwx  1 pgwb users   35 May 10 15:45 16536 -> /data1/tablespaces/TS_U_SPACE/tests

lrwxrwxrwx  1 pgwb users   35 May 10 15:45 16537 -> /data1/tablespaces/TS_B_SPACE/tests

lrwxrwxrwx  1 pgwb users   35 May 10 15:45 16538 -> /data1/tablespaces/TS_N_SPACE/tests

lrwxrwxrwx  1 pgwb users   35 May 10 15:45 16539 -> /data1/tablespaces/TS_M_SPACE/tests

lrwxrwxrwx  1 pgwb users   33 May 10 15:45 16540 -> /data1/tablespaces/metadata/tests

where does postgress get the old container paths if they are not stored in the database?

Conclusion: I cannot start postgress and got he error in the log:

LOG:  could not open tablespace directory "pg_tblspc/16537/PG_9.5_201510051": No such file or directory

LOG:  could not open tablespace directory "pg_tblspc/16539/PG_9.5_201510051": No such file or directory

LOG:  could not open tablespace directory "pg_tblspc/16538/PG_9.5_201510051": No such file or directory

LOG:  could not open tablespace directory "pg_tblspc/16536/PG_9.5_201510051": No such file or directory

LOG:  could not open tablespace directory "pg_tblspc/16540/PG_9.5_201510051": No such file or directory

For me it seems I have to note somewhere the whole linking information stored in pg_tblspc together with pg_basebackup.

I can use the olddir newdir options in pg_basebackup. But still I need this information outside of the backup package to restore the database after a crash to PIT.

Regards

Wolfgang

-----Ursprüngliche Nachricht-----
Von: Albe Laurenz [mailto:laurenz.albe@wien.gv.at]
Gesendet: Mittwoch, 10. Mai 2017 15:35
An: Brandl, Wolfgang; pgsql-admin@postgresql.org
Betreff: RE: 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