Thread: [ADMIN] WG: postgres pg_basebackup
Hello
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?
Regards
Wolfgang
Telefon: +43 1 711 23 - 883332
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
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
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