Restore Backup using BARMAN in PostgreSQL

Jamsher Khan 20 mins2
()

Restore Backup using BARMAN in PostgreSQL

In previous blog, we saw How to Install and Configure BARMAN for Backup Click here to read more. 

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

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


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


PostgreSql Cluster Physical Structure: PostgreSQL Cluster data directory: /var/lib/pgsql/13/data, Created two non-default tablespaces myts01: /u01/psql_tbls & myts02: /u01/psql2_tbls. Created two sample database dvdrental & sample and loaded them with sample data along with configured WAL archiving to copy in BARMAN Server.

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

postgres=#
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=#
postgres=# show data_directory;
     data_directory
------------------------
 /var/lib/pgsql/13/data
(1 row)

postgres=#
postgres=# \db
           List of tablespaces
    Name    |  Owner   |    Location
------------+----------+-----------------
 myts01     | postgres | /u01/psql_tbls
 myts02     | postgres | /u01/psql2_tbls
 pg_default | postgres |
 pg_global  | postgres |
(4 rows)

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
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 sample    | 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
(5 rows)

postgres=# select name,setting,unit from pg_settings where name in ('archive_mode','archive_command','archive_timeout');
      name       |                                                                          setting                                                                           | unit
-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------+------
 archive_command | test ! -f barman@test-machine01:/var/lib/barman/test-machine02/incoming/%f && rsync -a %p barman@test-machine01:/var/lib/barman/test-machine02/incoming/%f |
 archive_mode    | on                                                                                                                                                         |
 archive_timeout | 21600                                                                                                                                                      | s
(3 rows)
postgres=#


Create and Load test table and Switch WAL File: Let’s Create a 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=#
postgres=# \connect dvdrental
You are now connected to database "dvdrental" as user "postgres".
dvdrental=# create table t_Test (slno integer, ts timestamp);
CREATE TABLE
dvdrental=# insert into t_test values (generate_series(1,1000),now());
INSERT 0 1000
dvdrental=#
dvdrental=# \connect sample
You are now connected to database "sample" as user "postgres".
sample=# create table t_Test (slno integer, ts timestamp);
CREATE TABLE
sample=# insert into t_test values (generate_series(1,1000),now());
INSERT 0 1000
sample=#
sample=#
sample=# select pg_switch_wal();
 pg_switch_wal
---------------
 0/3F04D5B0
(1 row)

sample=#


Full DB backup: Perform full cluster-level backup using the barman backup command. Use command barman list-backup to list down all available backups.

-bash-4.2$ whoami
barman
-bash-4.2$ hostname
test-machine01
-bash-4.2$
-bash-4.2$ barman backup test-machine02 --reuse-backup=off
Starting backup using rsync-concurrent method for server test-machine02 in /var/lib/barman/test-machine02/base/20210705T155400
Backup start at LSN: 0/3E000028 (00000001000000000000003E, 00000028)
Starting backup copy via rsync/SSH for 20210705T155400
Copy done (time: 7 seconds)
Asking PostgreSQL server to finalize the backup.
Backup size: 46.8 MiB
Backup end at LSN: 0/3E000138 (00000001000000000000003E, 00000138)
Backup completed (start time: 2021-07-05 15:54:00.342610, elapsed time: 10 seconds)
Processing xlog segments from file archival for test-machine02
        00000001000000000000003E
        00000001000000000000003E.00000028.backup
-bash-4.2$
-bash-4.2$ barman list-backup test-machine02
test-machine02 20210705T155400 - Mon Jul  5 15:54:07 2021 - Size: 46.9 MiB - WAL Size: 0 B (tablespaces: myts01:/u01/psql_tbls, myts02:/u01/psql2_tbls)
test-machine02 20210705T154705 - Mon Jul  5 15:47:07 2021 - Size: 46.9 MiB - WAL Size: 32.3 KiB (tablespaces: myts01:/u01/psql_tbls, myts02:/u01/psql2_tbls)
test-machine02 20210623T154948 - Wed Jun 23 15:49:57 2021 - Size: 46.9 MiB - WAL Size: 96.5 KiB (tablespaces: myts01:/u01/psql_tbls, myts02:/u01/psql2_tbls)
test-machine02 20210623T143656 - Wed Jun 23 14:37:06 2021 - Size: 46.9 MiB - WAL Size: 32.3 KiB (tablespaces: myts01:/u01/psql_tbls, myts02:/u01/psql2_tbls)
-bash-4.2$


Stop PostgreSQL Cluster:  It is not recommended to issue a barman recover command using a target data directory where a PostgreSQL instance is running. At this point recovering from a shutdown or a crash won’t be much different because the recovery will erase your PostgreSQL data directory.

[root@test-machine02 ~]#  systemctl stop postgresql-13
[root@test-machine02 ~]#


Restore in same directory structure as of backup database


Step 1. Issue barman recover command: Command Format: barman recover [server_name] [backup_id] /path/to/recover/dir. barman recover command will copy all required files in tablespace and base directory. Also, WAL files will be copied in pg_wal. Please note we are using the latest backup: 20210705T155400.

remote-ssh-command: This will allow the barman to execute the copy on a remote server using ssh.

-bash-4.2$ whoami
barman
-bash-4.2$ hostname
test-machine01
-bash-4.2$
-bash-4.2$  barman recover --remote-ssh-command "ssh postgres@test-machine02" test-machine02 20210705T155400  /var/lib/pgsql/13/data
Starting remote restore for server test-machine02 using backup 20210705T155400
Destination directory: /var/lib/pgsql/13/data
Remote command: ssh postgres@test-machine02
        23701, myts01, /u01/psql_tbls
        23704, myts02, /u01/psql2_tbls
Copying the base backup.
Copying required WAL segments.
Generating archive status files
Identify dangerous settings in destination directory.

IMPORTANT
These settings have been modified to prevent data losses

postgresql.auto.conf line 3: archive_command = false

Recovery completed (start time: 2021-07-05 16:11:39.228717, elapsed time: 11 seconds)

Your PostgreSQL server has been successfully prepared for recovery!
-bash-4.2$


Step 2. Verify archived WAL copied: Verify WAL files exist in pg_wal which will be required for recovery.

-bash-4.2$ pwd
/var/lib/pgsql/13/data/pg_wal
-bash-4.2$
-bash-4.2$ ls -ltr
total 0
-rw-rw-r--. 1 postgres postgres 0 Jul  5 16:11 000000010000000000000040
-rw-rw-r--. 1 postgres postgres 0 Jul  5 16:11 00000001000000000000003F
-rw-rw-r--. 1 postgres postgres 0 Jul  5 16:11 00000001000000000000003E
-rw-rw-r--. 1 postgres postgres 0 Jul  5 16:11 00000001000000000000003E.00000028.backup.done
-bash-4.2$


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

[root@test-machine02 ~]#  systemctl start postgresql-13
[root@test-machine02 ~]#


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

-bash-4.2$pwd
/var/lib/pgsql/13/data/log
-bash-4.2$ ls -ltr
-bash-4.2$ vi postgresql-Mon.log
2021-07-05 16:45:45.305 +03 [129234] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-07-05 16:45:45.308 +03 [129237] LOG:  database system was interrupted; last known up at 2021-07-05 15:53:58 +03
2021-07-05 16:45:45.325 +03 [129237] LOG:  redo starts at 0/3E000028
2021-07-05 16:45:45.326 +03 [129237] LOG:  consistent recovery state reached at 0/3E000138
2021-07-05 16:45:45.329 +03 [129237] LOG:  redo done at 0/40002670
2021-07-05 16:45:45.369 +03 [129234] LOG:  database system is ready to accept connections


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

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

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

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

sample=#


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. Issue barman recover command: Command Format: barman recover [server_name] [backup_id] /path/to/recover/dir. We need to use option –tablespace to do new tablespace mapping.

-bash-4.2$
-bash-4.2$ barman recover --tablespace myts01:/u01/psql5_tbls  --tablespace myts02:/u01/psql6_tbls --remote-ssh-command "ssh postgres@test-machine02" test-machine02 20210705T155400 /u01/psql-data
Starting remote restore for server test-machine02 using backup 20210705T155400
Destination directory: /u01/psql-data
Remote command: ssh postgres@test-machine02
        23701, myts01, /u01/psql5_tbls
        23704, myts02, /u01/psql6_tbls
Copying the base backup.
Copying required WAL segments.
Generating archive status files
Identify dangerous settings in destination directory.

IMPORTANT
These settings have been modified to prevent data losses

postgresql.auto.conf line 3: archive_command = false

Recovery completed (start time: 2021-07-05 17:07:35.976783, elapsed time: 11 seconds)

Your PostgreSQL server has been successfully prepared for recovery!
-bash-4.2$


Step 2. Check softlink created under pg_tblspc: Once the backup restore is done by BARMAN verify new softlink is created under directory pg_tblspc pointing to a new tablespace location.

-bash-4.2$ pwd
/u01/psql-data/pg_tblspc
-bash-4.2$ ls -ltr
total 0
lrwxrwxrwx. 1 postgres postgres 15 Jul  5 17:07 23701 -> /u01/psql5_tbls
lrwxrwxrwx. 1 postgres postgres 15 Jul  5 17:07 23704 -> /u01/psql6_tbls
-bash-4.2$


Step 3. Verify archived WAL copied: Verify WAL files exist in pg_wal which will be required for recovery.

-bash-4.2$ pwd
/u01/psql-data/pg_wal
-bash-4.2$
-bash-4.2$ ls -ltr
total 0
-rw-rw-r--. 1 postgres postgres 0 Jul  5 16:11 000000010000000000000040
-rw-rw-r--. 1 postgres postgres 0 Jul  5 16:11 00000001000000000000003F
-rw-rw-r--. 1 postgres postgres 0 Jul  5 16:11 00000001000000000000003E
-rw-rw-r--. 1 postgres postgres 0 Jul  5 16:11 00000001000000000000003E.00000028.backup.done
-bash-4.2$


Step 4. Start PostgreSQL Cluster: Once all the above steps are done. Start postgresql cluster using pg_ctl. As we didn’t register the PostgreSQL service under systemctl we cannot use systemctl command.

-bash-4.2$
-bash-4.2$  /usr/pgsql-13/bin/pg_ctl -D /u01/psql-data  start
waiting for server to start....2021-07-05 17:12:11.709 +03 [502] LOG:  redirecting log output to logging collector process
2021-07-05 17:12:11.709 +03 [502] HINT:  Future log output will appear in directory "log".
 stopped waiting
pg_ctl: could not start server
Examine the log output.
-bash-4.2$
-bash-4.2$


Step 5. Verify logfile: Verify logfile to see what causing the above error. As Port 5432 is already in use we are getting the above error.

-bash-4.2$ pwd
/u01/psql-data/log
-bash-4.2$ vi postgresql-Mon.log
2021-07-05 17:12:11.709 +03 [502] 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-05 17:12:11.710 +03 [502] LOG:  could not bind IPv4 address "0.0.0.0": Address already in use
2021-07-05 17:12:11.710 +03 [502] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2021-07-05 17:12:11.710 +03 [502] LOG:  could not bind IPv6 address "::": Address already in use
2021-07-05 17:12:11.710 +03 [502] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2021-07-05 17:12:11.710 +03 [502] WARNING:  could not create listen socket for "*"
2021-07-05 17:12:11.710 +03 [502] FATAL:  could not create any TCP/IP sockets
2021-07-05 17:12:11.711 +03 [502] LOG:  database system is shut down


Step 6. Edit postgresql.conf: Edit postgresql.conf file to update the port to use new port 5433.

-bash-4.2$ pwd
/u01/psql-data
-bash-4.2$ vi postgresql.conf
port = 5433
:wq!


Step 7. Start PostgreSQL Cluster: Once the port is changed try to start the postgreSQL cluster using pg_ctl.

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


Step 8. Verify logfile: Verify logfile to see everything went fine during restore.

-bash-4.2$ pwd
/u01/psql-data/log
-bash-4.2$ vi postgresql-Mon.log
2021-07-05 17:14:38.729 +03 [644] LOG:  database system was interrupted; last known up at 2021-07-05 15:53:58 +03
2021-07-05 17:14:38.746 +03 [644] LOG:  redo starts at 0/3E000028
2021-07-05 17:14:38.747 +03 [644] LOG:  consistent recovery state reached at 0/3E000138
2021-07-05 17:14:38.750 +03 [644] LOG:  redo done at 0/40002670
2021-07-05 17:14:38.774 +03 [642] 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 5433
psql (13.2)
Type "help" for help.

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

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

sample=#


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.

Related Articles

, , , ,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

2 thoughts on “Restore Backup using BARMAN in PostgreSQL”

Comments are closed.

Related Articles