Restore Backup using pg_basebackup in PostgreSQL

July 5, 2021

Restore Backup using pg_basebackup in PostgreSQL

In this blog, we will see How to perform a restore of a backup taken from pg_basebackup. We will see full steps for the below two scenarios:

1. Restore in the same directory structure of the backup database.
2. Restore in the different directory structures of the backup database.

For the purpose of demonstration, we will be using our second PostgreSQL Cluster created on Port: 5433 Click here to learn How to create a second PostgreSQL Cluster. For pg_basebackup backup commands Click here to read more.

PostgreSql Cluster Physical Structure: PostgreSQL Cluster data directory: /u01/psql-13, Created two non-default tablespaces myts03: /u01/psql3_tbls & myts04: /u01/psql4_tbls. Created two sample database sample3 & sample4 & loaded them with sample data. And configured WAL archiving: /u01/wal_archive.

postgres=# show data_directory;
 data_directory
----------------
 /u01/psql-13

postgres=#  \db+
                                      List of tablespaces
    Name    |  Owner   |    Location     | Access privileges | Options |  Size   | Description
------------+----------+-----------------+-------------------+---------+---------+-------------
 myts03     | postgres | /u01/psql3_tbls |                   |         | 0 bytes |
 myts04     | postgres | /u01/psql4_tbls |                   |         | 0 bytes |
 pg_default | postgres |                 |                   |         | 46 MB   |
 pg_global  | postgres |                 |                   |         | 559 kB  |
(4 rows)


postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 dvdrental | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +| 15 MB   | pg_default |
           |          |          |             |             | postgres=CTc/postgres+|         |            |
           |          |          |             |             | read_only=c/postgres  |         |            |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 15 MB   | pg_default | default administrative connection database
 sample3   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 15 MB   | myts03     |
 sample4   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 15 MB   | myts04     |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7969 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8117 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
(6 rows)

postgres=#

postgres=# select name,setting,unit from pg_settings where name in ('archive_mode','archive_command','archive_timeout');
      name       |                          setting                           | unit
-----------------+------------------------------------------------------------+------
 archive_command | test ! -f /u01/wal_archive/%f && cp %p /u01/wal_archive/%f |
 archive_mode    | on                                                         |

Create and Load test table and Switch WAL File: Let’s Create Table T_TEST and insert 1000 random records. Once the insert operation is done do force the WAL switch to make sure all changes are archived.

postgres=# \connect sample3
You are now connected to database "sample3" as user "postgres".
sample3=#
sample3=# create table t_Test (slno integer, ts timestamp);
CREATE TABLE
sample3=# insert into t_test values (generate_series(1,1000),now());
INSERT 0 1000
sample3=#
sample3=# select count(*) from t_test;
 count
-------
  1000
(1 row)

sample3=# \connect sample4
You are now connected to database "sample4" as user "postgres".
sample4=#
sample4=# create table t_Test (slno integer, ts timestamp);
CREATE TABLE
sample4=# insert into t_test values (generate_series(1,1000),now());
INSERT 0 1000
sample4=# select count(*) from t_test;
 count
-------
  1000
(1 row)

postgres=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/A04DDC8
(1 row)

postgres=#
postgres=#

Full DB backup: Perform full cluster level tar backup using pg_basebackup using the below command.

-bash-4.2$ pg_basebackup -h localhost -p 5433 -U postgres -D /u01/backup/latest1_backup -Ft -z -Xs -P
78382/78382 kB (100%), 3/3 tablespaces
-bash-4.2$

-bash-4.2$ ls -ltr /u01/backup/latest1_backup
total 12900
-rw-------. 1 postgres postgres 2699207 Jul  4 16:16 21913.tar.gz
-rw-------. 1 postgres postgres 2699006 Jul  4 16:16 21914.tar.gz
-rw-------. 1 postgres postgres 7453080 Jul  4 16:16 base.tar.gz
-rw-------. 1 postgres postgres   17655 Jul  4 16:16 pg_wal.tar.gz
-rw-------. 1 postgres postgres  334189 Jul  4 16:16 backup_manifest
-bash-4.2$

Stop PostgreSQL Cluster and remove all datafiles: Let simulate the failure and delete all datafiles we have from the base and tablespaces directory.

[root@test-machine02 ~]# systemctl stop psql-13.service
[root@test-machine02 ~]# rm -rf /u01/psql-13/*
[root@test-machine02 ~]# rm -rf /u01/psql3_tbls/*
[root@test-machine02 ~]# rm -rf /u01/psql4_tbls/*

Restore in same directory structure as of backup database

Step 1. Untar Base backup: Use OS command tar to copy the base backup to the base location: /u01/psql-13

-bash-4.2$ tar xvf /u01/backup/latest1_backup/base.tar.gz -C  /u01/psql-13
backup_label
tablespace_map
pg_wal/
./pg_wal/archive_status/
global/
global/1262

Step 2. View tablespace_map File: pg_basebackup command creates a text file called tablespace_map which has mapping to tablespace OID and directory location.

-bash-4.2$ pwd
/u01/psql-13
-bash-4.2$ cat tablespace_map
21913 /u01/psql3_tbls
21914 /u01/psql4_tbls
-bash-4.2$

Step 3. Untar Tablespace & WAL Backup: Untar both tablespace backup and WAL backup to its respective location.

-bash-4.2$
-bash-4.2$ tar xvf /u01/backup/latest1_backup/21913.tar.gz -C  /u01/psql3_tbls
PG_13_202007201/
PG_13_202007201/21916/
PG_13_202007201/21916/1255

-bash-4.2$
-bash-4.2$ tar xvf /u01/backup/latest1_backup/21914.tar.gz -C  /u01/psql4_tbls
PG_13_202007201/
PG_13_202007201/21917/
PG_13_202007201/21917/1255

-bash-4.2$
-bash-4.2$ tar xvf /u01/backup/latest1_backup/pg_wal.tar.gz -C /u01/psql-13/pg_wal
00000001000000000000000C
archive_status/00000001000000000000000C.done
-bash-4.2$
-bash-4.2$

Step 4. Update restore_command in postgresql.conf: Edit postgresql.conf file and provide WAL archiving location So postgresql service and do recovery based on available WAL archives.

-bash-4.2$ vi postgresql.conf
restore_command = 'cp /u01/wal_archive/%f %p'           # command to use to restore an archived logfile segment
:wq!

-bash-4.2$ grep restore_command postgresql.conf
restore_command = 'cp /u01/wal_archive/%f %p'           # command to use to restore an archived logfile segment
-bash-4.2$

Step 5. Start PostgreSQL Service: Once all the above steps are done. Start postgresql services.

[root@test-machine02 ~]#
[root@test-machine02 ~]# systemctl start psql-13.service
[root@test-machine02 ~]#

Step 6. Verify logfile: Verify logfile to see everything went fine to restore.

-bash-4.2$ pwd
/u01/psql-13/log
-bash-4.2$ vi postgresql-Sun.log
2021-07-04 16:41:44.044 +03 [47578] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
2021-07-04 16:41:44.047 +03 [47582] LOG:  database system was interrupted; last known up at 2021-07-04 16:16:13 +03
2021-07-04 16:41:44.063 +03 [47582] LOG:  redo starts at 0/C000028
2021-07-04 16:41:44.064 +03 [47582] LOG:  consistent recovery state reached at 0/C000100
2021-07-04 16:41:44.064 +03 [47582] LOG:  redo done at 0/C000100
2021-07-04 16:41:44.132 +03 [47578] LOG:  database system is ready to accept connections

Step 7. Verify Data: Verify all data exists after restore and recovery.

-bash-4.2$
-bash-4.2$ psql -p 5433
psql (13.2)
Type "help" for help.

postgres=#
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 dvdrental | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | read_only=c/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 sample3   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 sample4   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(6 rows)

postgres=#


postgres=# \connect sample3
You are now connected to database "sample3" as user "postgres".
sample3=# select count(*) from t_test;
 count
-------
  1000
(1 row)

sample3=# \connect sample4
You are now connected to database "sample4" as user "postgres".
sample4=# select count(*) from t_test;
 count
-------
  1000
(1 row)

sample4=#

Restore in different directory structure as of backup database

Let assume we are doing a restore in a different server having a directory structure as below.

[root@test-machine02 u01]# mkdir psql-data
[root@test-machine02 u01]# mkdir psql5_tbls
[root@test-machine02 u01]# mkdir psql6_tbls
[root@test-machine02 u01]# chown postgres:postgres psql-data
[root@test-machine02 u01]# chown postgres:postgres psql5_tbls
[root@test-machine02 u01]# chown postgres:postgres psql6_tbls
[root@test-machine02 u01]#

Step 1. Untar Base backup: Untar base backup in base location.

-bash-4.2$ tar xvf /u01/backup/latest1_backup/base.tar.gz -C  /u01/psql-data
backup_label
tablespace_map
pg_wal/
./pg_wal/archive_status/
global/

Step 2. Untar Tablespace & WAL backup: Untar backup of tablespace and WAL backup to its respective location.

-bash-4.2$ tar xvf /u01/backup/latest1_backup/21913.tar.gz -C  /u01/psql5_tbls
PG_13_202007201/
PG_13_202007201/21916/
PG_13_202007201/21916/1255

-bash-4.2$
-bash-4.2$ tar xvf /u01/backup/latest1_backup/21914.tar.gz -C  /u01/psql6_tbls
PG_13_202007201/
PG_13_202007201/21917/
PG_13_202007201/21917/1255
PG_13_202007201/21917/1255_fsm

-bash-4.2$
-bash-4.2$ tar xvf /u01/backup/latest1_backup/pg_wal.tar.gz -C /u01/psql-data/pg_wal
00000001000000000000000C
archive_status/00000001000000000000000C.done
-bash-4.2$

Step 3. Edit tablespace_map file: Edit file tablespace_map and update the new location of tablespaces.

-bash-4.2$ pwd
/u01/psql-data
-bash-4.2$ vi tablespace_map
21913 /u01/psql5_tbls
21914 /u01/psql6_tbls
:wq!

Step 4. Update restore_command in postgresql.conf: Edit postgresql.conf file and provide WAL archiving location So PostgresSQL service do recovery based on available WAL archives. We are providing a new Port 5434 as port 5433 is already in use.

-bash-4.2$ cd /u01/psql-data
-bash-4.2$ vi postgresql.conf
port = 5434  
restore_command = 'cp /u01/wal_archive/%f %p'
:wq!

Step 5. Start Cluster with pg_ctl: Once all the above steps are done. Start PostgreSQL cluster using command pg_ctl, We cannot use systemctl command as service is not registered in systemctl.

-bash-4.2$ /usr/pgsql-13/bin/pg_ctl -D /u01/psql-data  start
waiting for server to start....2021-07-04 17:18:01.072 +03 [49561] FATAL:  data directory "/u01/psql-data" has invalid permissions
2021-07-04 17:18:01.072 +03 [49561] DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
 stopped waiting
pg_ctl: could not start server
Examine the log output.
-bash-4.2$

Step 6. Correct permissions: As from the above output we have a folder permission issue. Correct permission with the below commands.

[root@test-machine02 u01]#
[root@test-machine02 u01]# chmod 750 psql-data
[root@test-machine02 u01]#
[root@test-machine02 u01]# ls -ld psql-data
drwxr-x---. 20 postgres postgres 4096 Jul  4 17:12 psql-data
[root@test-machine02 u01]# chmod 750 psql5_tbls
[root@test-machine02 u01]# chmod 750 psql6_tbls
[root@test-machine02 u01]#

Step 7. Start Cluster again with pg_ctl: Let try to start the PostgreSQL cluster once again using pg_ctl utility.

-bash-4.2$ /usr/pgsql-13/bin/pg_ctl -D /u01/psql-data  start
waiting for server to start....2021-07-04 17:20:07.510 +03 [49688] LOG:  redirecting log output to logging collector process
2021-07-04 17:20:07.510 +03 [49688] HINT:  Future log output will appear in directory "log".
 done
server started
-bash-4.2$

Step 8. Verify logfile: Verify logfile to confirm everything is fine.

-bash-4.2$ pwd
/u01/psql-data/log
-bash-4.2$
-bash-4.2$ vi postgresql-Sun.log
2021-07-04 17:20:07.510 +03 [49688] LOG:  starting PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-07-04 17:20:07.512 +03 [49688] LOG:  listening on IPv6 address "::1", port 5434
2021-07-04 17:20:07.512 +03 [49688] LOG:  listening on IPv4 address "127.0.0.1", port 5434
2021-07-04 17:20:07.513 +03 [49688] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5434"
2021-07-04 17:20:07.514 +03 [49688] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5434"
2021-07-04 17:20:07.516 +03 [49690] LOG:  database system was interrupted; last known up at 2021-07-04 16:16:13 +03
2021-07-04 17:20:07.531 +03 [49690] LOG:  redo starts at 0/C000028
2021-07-04 17:20:07.531 +03 [49690] LOG:  consistent recovery state reached at 0/C000100
2021-07-04 17:20:07.531 +03 [49690] LOG:  redo done at 0/C000100
2021-07-04 17:20:07.551 +03 [49688] LOG:  database system is ready to accept connections

Step 9. Verify Data: Verify all data exists after restore and recovery.

-bash-4.2$
-bash-4.2$ psql -p 5434
psql (13.2)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5434".
postgres=#
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 dvdrental | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | read_only=c/postgres
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 sample3   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 sample4   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(6 rows)

postgres=# \connect sample3
You are now connected to database "sample3" as user "postgres".
sample3=# select count(*) from t_test;
 count
-------
  1000
(1 row)

sample3=# \connect sample4
You are now connected to database "sample4" as user "postgres".
sample4=# select count(*) from t_test;
 count
-------
  1000
(1 row)

sample4=#

This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

BACKUP, BACKUP AND RECOVERY, PG_BASEBACKUP, POSTGRESQL, RESTORE

Restore Backup using pg_basebackup in PostgreSQL

Related Articles