"Reprint" Using Barman backup PostgreSQL

What is Barman

Barman (Backup and Recovery Manager) is a convenient backup and recovery tool in the PostgreSQL database server that allows remote backup of multiple servers, allowing a single command from a single backup set to restore the database. Multiple backup sets can also be easily managed.

Environment description

The environment has two machines:

• 192.168.1.21 Main Database Host
• 192.168.1.22 for barman backup machine

Installing barman

To Barman's official website http://www.pgbarman.org/Download the barman installation package, copy the installation package to the 192.168.1.22 machine, unzip:

tar xvf barman-1.2.1.tar.gz

Then go to the directory and install:

cd barman-1.2.1python setup.py buildsudo python setup.py install

Install the additional packages required for barman:

sudo aptitude install python-arghsudo aptitude install python-psycopg2sudo aptitude install python-dateutilsudo pip install argcomplete

Detecting the version of Argh

[email protected]:~$ pythonPython 2.7.4 (default, Apr 19 2013, 18:28:01)[GCC 4.7.3] on linux2Type "help", "copyright", "credits" or "license" for more information.>>> import argh>>> argh.__version__‘0.17.2‘

Barman requires Argh >= 0.21.2, version too low, upgrade version:

[email protected]:~$ sudo pip install --upgrade arghDownloading/unpacking argh from https://pypi.python.org/packages/source/a/argh/argh-0.23.2.tar.gz#md5=0d026a85bdfb5dd895138b274a3fc665 Downloading argh-0.23.2.tar.gz Running setup.py egg_info for package arghInstalling collected packages: argh Found existing installation: argh 0.17.2   Uninstalling argh:     Successfully uninstalled argh Running setup.py install for arghSuccessfully installed arghCleaning up...[email protected]:~$ pythonPython 2.7.4 (default, Apr 19 2013, 18:28:01)[GCC 4.7.3] on linux2Type "help", "copyright", "credits" or "license" for more information.>>> import argh>>> argh.__version__‘0.23.2‘

Configure barman to configure the main library to be backed up

To 192.168.1.21 Configure the postgresql.conf file for the main library, open the archive mode of the main library, and modify the configuration of the Archive_command as follows:

wal_level = archivearchive_mode = onarchive_command = ‘rsync -a %p [email protected]:/home/osdba/barman/main/incoming/%f‘

Note that the archive command above is to copy the Wal log to the/home/osdba/barman/main/incoming/directory of the 192.168.1.22 machine, so build this directory on the backup machine first:

Of course you can also use other directories. After the configuration is complete, restart the main library is required.

Access the SSH channel between the backup machine and the database machine

Barman need to get through the SSH channel between the two machines, assuming our user is OSDBA, the method is as follows: First enter the 192.168.1.21 machine:

[email protected]:~/$ cd .ssh[email protected]:~/.ssh$ cat id_rsa.pub >> authorized_keysscp authorized_keys 192.168.1.22:`pwd`

Re-enter the 192.168.1.22 machine:

[email protected]:~/$ cd .ssh[email protected]:~/.ssh$ cat id_rsa.pub >> authorized_keysscp authorized_keys 192.168.1.21:`pwd`/.

In fact, the above operation is to generate a Authroized_keys file containing the contents of the Id_rsa.pub file on the 192.168.1.21 and 192.168.1.22 two machines.

Add the following to the/etc/hosts file for both machines:

192.168.1.21 pg192.168.1.22 backup

Test if you can ssh to each other: on 192.168.1.21:

[email protected]:~/.ssh$ ssh [email protected]Welcome to Ubuntu 13.04 (GNU/Linux 3.8.0-25-generic x86_64)Documentation: https://help.ubuntu.com/Last login: Sat Jun 22 16:02:07 2013 from pg [email protected]:~$ exitlogout Connection to backup closed.[email protected]:~/.ssh$ ssh [email protected]Welcome to Ubuntu 13.04 (GNU/Linux 3.8.0-25-generic x86_64)Documentation: https://help.ubuntu.com/45 packages can be updated. 13 updates are security updates.Last login: Sat Jun 22 16:01:53 2013 from pg[email protected]:~$ exitlogout Connection to pg closed.

On the 192.168.1.22:

[email protected]:~$ ssh [email protected]Welcome to Ubuntu 13.04 (GNU/Linux 3.8.0-25-generic x86_64)Documentation: https://help.ubuntu.com/45 packages can be updated. 13 updates are security updates.Last login: Sat Jun 22 16:02:21 2013 from pg[email protected]:~$ exitlogout Connection to pg closed. [email protected]:~$ ssh [email protected]Welcome to Ubuntu 13.04 (GNU/Linux 3.8.0-25-generic x86_64)Documentation: https://help.ubuntu.com/Last login: Sat Jun 22 16:02:12 2013 from pg[email protected]:~$ exitlogout Connection to backup closed.

To set the configuration file for barman

In 192.168.1.22 copy a barman profile from the template and modify it on the basis of this template:

[email protected]:~/src/barman-1.2.1/doc$ cp barman.conf ~/.barman.conf [email protected]:~/src/barman-1.2.1/doc$ vi ~/.barman.conf

Modify the contents of the. barman.conf file to:

 [barman] ;  Main directory barman_home = /home/osdba/barman; System user barman_user = osdba; Log locationlog_file = /home/osdba/barman/log[main] ; ; Human readable description description = "Main PostgreSQL Database"; SSH options ; ssh_command = ssh [email protected] ssh_command = ssh [email protected]; PostgreSQL connection stringconninfo = host=pg user=osdba dbname=master; Minimum number of required backups (redundancy)minimum_redundancy = 1

After configuration, barman needs to connect to the database on the 192.168.1.22 machine on the 192.168.1.21, the connection string configured in the configuration file is: Conninfo = Host=pg user=osdba dbname=master, Generally we also have the password, in order not to enter the password, we configure the password to the ~/.pgpass file:

The above content "XXXXXX" is the password of the database user Osdba, of course, you can also use other super users to connect PostgreSQL, here is the "osdba" user.

Backup Database View the database to be backed up

Use barman Show-server main to view the database to be backed up: [email protected]:~/barman$ barman Show-server main

Server main:active:truedescription:Main PostgreSQL databasessh_command:ssh [EMAIL PROTECTED]CONNINFO:HOST=PG USER=OSDBA dbname=masterbackup_directory:/home/osdba/barman/mainbasebackups_directory:/home/osdba/barman/main/ Basewals_directory:/home/osdba/barman/main/walsincoming_wals_directory:/home/osdba/barman/main/incominglock_ File:/home/osdba/barman/main/main.lockcompression:nonecustom_compression_filter:nonecustom_decompression_ Filter:Noneretention_policy_mode:autoretention_policy:Nonewal_retention_policy:mainpre_backup_script:Nonepost_ Backup_script:noneminimum_redundancy:1bandwidth_limit:nonetablespace_bandwidth_limit:nonecurrent_xlog: 000000010000000000000005last_shipped_wal:nonearchive_command:rsync-a%p [email protected]:/home/osdba/ Barman/main/incoming/%fserver_txt_version:9.2.3data_directory:/home/osdba/pgdataarchive_mode:onconfig_file:/ Home/osdba/pgdata/postgresql.confhba_file:/home/osdba/pgdata/pg_hba.confident_file:/home/osdba/pgdata/pg_iDent.conf 

Perform a backup

Starting backup for server main in /home/osdba/barman/main/base/20130622T120619Backup start at xlog location: 0/B000020 (00000001000000000000000B, 00000020)Copying files.Copy done.Asking PostgreSQL server to finalize the backup.Backup end at xlog location: 0/B0000A8 (00000001000000000000000B, 000000A8)Backup completed

View Backup Results

Recovering a database to perform recovery

Do the recovery test and restore the database to the/HOME/OSDBA/PGD2 directory:

[email protected]:~$ barman recover main 20130622T120619 /home/osdba/pgd2Processing xlog segments for main000000010000000000000005000000010000000000000006000000010000000000000006.00000020.backup000000010000000000000007000000010000000000000008000000010000000000000008.00000020.backup00000001000000000000000900000001000000000000000A00000001000000000000000A.00000020.backup00000001000000000000000B00000001000000000000000B.00000020.backupStarting local restore for server main using backup 20130622T120619Destination directory: /home/osdba/pgd2Copying the base backup.Copying required wal segments.The archive_command was set to ‘false‘ to prevent data losses.Your PostgreSQL server has been successfully prepared for recovery!Please review network and archive related settings in the PostgreSQLconfiguration file before starting the just recovered instance.

Start a recovered database

[email protected]:~$ pg_ctl -D /home/osdba/pgd2 start server starting[email protected]:~$

Check the recovered database:

[email protected]:~$ psql postgrespsql (9.2.3)Type "help" for help.postgres=# \l                             List of databases  Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges-----------+-------+----------+-------------+-------------+-------------------master    | slony | UTF8     | en_US.UTF-8 | en_US.UTF-8 |postgres  | osdba | UTF8     | en_US.UTF-8 | en_US.UTF-8 |template0 | osdba | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/osdba         +          |       |          |             |             | osdba=CTc/osdbatemplate1 | osdba | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/osdba         +          |       |          |             |             | osdba=CTc/osdba(4 rows)

Stop recovering a database

[email protected]:~$ pg_ctl -D /home/osdba/pgd2 stopwaiting for server to shut down....... doneserver stopped

"Reprint" Using Barman backup PostgreSQL