Обсуждение: [ADMIN] WG: 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

Wolfgang Brandl wrote:
> 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.

You are right; after looking at the code, I realize that I got it wrong.

During an online backup, a file "tablespace_map" is created in the
data directory that contains the original locations of the tablespaces.

During recovery, the symbolic links are restored from that directory.

So you have to un-tar the tablespaces into the same location as in the
original database cluster.  The good news is that you can find the locations
in the "tablespace_map" file and don't need to document them somewhere.

Sorry for the misinformation in the original answer.

Yours,
Laurenz Albe

Thanks it is working perfectly.
Even if i choose to redirect the database to new location and adapting the tablespace_map file to this new locations it
isworking.
 
Do you think there are any concerns doing this redirect restore?
Regards
Wolfgang

-----Ursprüngliche Nachricht-----
Von: Albe Laurenz [mailto:laurenz.albe@wien.gv.at] 
Gesendet: Mittwoch, 10. Mai 2017 16:35
An: Brandl, Wolfgang; pgsql-admin@postgresql.org
Betreff: RE: postgres pg_basebackup

Wolfgang Brandl wrote:
> 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.

You are right; after looking at the code, I realize that I got it wrong.

During an online backup, a file "tablespace_map" is created in the data directory that contains the original locations
ofthe tablespaces.
 

During recovery, the symbolic links are restored from that directory.

So you have to un-tar the tablespaces into the same location as in the original database cluster.  The good news is
thatyou can find the locations in the "tablespace_map" file and don't need to document them somewhere.
 

Sorry for the misinformation in the original answer.

Yours,
Laurenz Albe

I wrote:
>> During an online backup, a file "tablespace_map" is created in the data directory that
>> contains the original locations of the tablespaces.
>> 
>> During recovery, the symbolic links are restored from that directory.
>> 
>> So you have to un-tar the tablespaces into the same location as in the original database
>> cluster.  The good news is that you can find the locations in the "tablespace_map" file
>> and don't need to document them somewhere.

Wolfgang Brandl wrote:
> Thanks it is working perfectly.
> Even if i choose to redirect the database to new location and adapting the tablespace_map
> file to this new locations it is working.
> Do you think there are any concerns doing this redirect restore?

That should be fine.

Yours,
Laurenz Albe

Hi