PostgreSQL Incremental Backup

Introduction:

Barman is a management tool for PostgreSQL backup restore. Official website: http://www.pgbarman.org/

This article environment:

System: centos6.6

PostgreSQL 9.3.9

barman-1.4.1-1.rhel6.noarch.rpm

The host is as follows:

192.168.33.30 pgserver192.168.33.31 Backup

Where Pgserver installs the same version of PG as backup

For PostgreSQL installation See:http://my.oschina.net/firxiao/blog/295027

installation and configuration:to install rsync on Pgserver:

Yum Install Rsync-y

Install and configure the barman on backup:

To configure the Epel source:

Curl Firxiao.com/sh/chrepo.sh|bash

Install Barman:

Yum Install-y http://nchc.dl.sourceforge.net/project/pgbarman/1.4.1/barman-1.4.1-1.rhel6.noarch.rpm

Configuration barman:

Mv/etc/barman.conf/etc/barman.conf.bakvim/etc/barman.conf

Add the following content:

[barman]barman_home =/var/lib/barmanbarman_user = Barmanlog_file =/var/log/barman/barman.logconfiguration_files_ Directory =/etc/barman.dcompression = Gzipreuse_backup = Linkbandwidth_limit = 4000basebackup_retry_times = 3basebackup _retry_sleep = 30

To create a configuration file directory

Mkdir/etc/barman.d

Create a backup configuration named Pgserver

Vim/etc/barman.d/pgserver.conf

Add the following content

[Pgserver]description = "The Pgserver PostgreSQL Database" ssh_command = ssh [email protected]conninfo = Host=pgserver Use r=postgresminimum_redundancy = 1retention_policy = RECOVERY WINDOW of 4 WEEKS

The definitions for each parameter are described in

Mans 5 Barman

Configure dual-machine SSH Trust logins:

Using Ssh-copy-id requires the Postgre user password on the pgserver and the barman user password on the backup

You can use passwd to give

Use passwd-d to remove password after configuration

Configure on the Pgserver

[[email protected] ~]# mkdir /var/lib/pgsql/.ssh[[email protected] ~]#  chown postgres /var/lib/pgsql/.ssh[[email protected] ~]# su -  Postgres-bash-4.1$ ssh-keygen generating public/private rsa key pair. enter file in which to save the key  (/VAR/LIB/PGSQL/.SSH/ID_RSA):  Enter passphrase  (empty for no passphrase): enter same  Passphrase again: your identification has been saved in /var/lib/pgsql /.ssh/id_rsa. your public key has been saved in /var/lib/pgsql/.ssh/id_rsa.pub.the  Key fingerprint is:5b:bf:25:02:1c:36:e3:b9:98:42:5a:85:27:d4:03:92 [email protected]the  key ' s randomart image is:+--[ rsa 2048]----+|  .. oo           | |  eo .o          | |     o o.=        | |      + + =       | |     o   s .      | |    +   o = .     | |   . . o o . o .  | |      .     . +   | |             .    |+----------- ------+-bash-4.1$ ssh-copy-id [email protected]the authenticity of host  ' backup  (192.168.33.31) '  can ' t be established. Rsa key fingerprint is f3:48:30:89:03:76:cb:04:19:7a:fe:8d:6c:90:e4:fe. Are you sure you want to continue connecting  (yes/no)  yeswarning: permanently added   ' backup,192.168.33.31 '   (RSA)  to the list of known hosts. [email protected] ' s password: now try logging into the machine,  with  "ssh  ' [email protected] '",  and check in:  .ssh/authorized_keysto  make sure we haven ' T added extra keys that you weren ' t  expecting.-bash-4.1$ ssh [email protected]

Log in to backup without entering a password indicates a successful configuration

Configure on Backup

[[email protected] ~]# su - barman-bash-4.1$ ssh-keygen generating  Public/private rsa key pair. enter file in which to save the key  (/VAR/LIB/BARMAN/.SSH/ID_RSA):  Enter passphrase  (empty for no passphrase): enter same  passphrase again: your identification has been saved in /var/lib/ Barman/.ssh/id_rsa. your public key has been saved in /var/lib/barman/.ssh/id_rsa.pub.the  Key fingerprint is:59:f3:8a:23:56:b5:00:b2:40:e2:a9:fb:ea:95:1d:b8 [email protected]the  key ' s randomart image is:+--[ rsa 2048]----+|. o . .          | | .....  o .         | |  o  .   . +      | |.    .    = +     | |.   . .  s . .    | |  .  +&NBSP, ....  . .     | |.   e .o o .      | | &NBSP, ....   . . .       | | oo.              |+-----------------+- Bash-4.1$ ssh-copy-id [email protected][email protected] ' s password: Now  try logging into the machine, with  "ssh  ' [email protected] '",  and  check in:  .ssh/authorized_keysto make sure we haven ' t added  extra keys that you weren ' t expecting.-bash-4.1$ ssh [email  Protected

Log in Pgserver without entering a password indicates a successful configuration

to configure PostgreSQL in Pgserver:

[Email protected] ~]# vim/var/lib/pgsql/9.3/data/postgresql.conf

Add the following content

Wal_level = ' archive ' # for PostgreSQL >= 9.0archive_mode = Onarchive_command = ' rsync-a%p ' [Email protected]:incoming _wals_directory/%f '

Where incoming_wals_directory can be used on the backup server

#barman show-server pgserver|grep "Incoming_wals_directory" Incoming_wals_directory:/var/lib/barman/pgserver/ Incoming

View and change to that directory

So the replacement should be:

Wal_level = ' archive ' # for PostgreSQL >= 9.0archive_mode = Onarchive_command = ' rsync-a%p ' [Email protected]:/var/lib /barman/pgserver/incoming/%f '

Next configure Backup to password-free login

Vim/var/lib/pgsql/9.3/data/pg_hba.conf

Add to

Host all Postgres 192.168.33.31/32 Trust

Note Where the IP address is the backup address

Verify the configuration:

Check if configuration is successful on backup

#psql  -c  ' select version () '  -U postgres -h pgserver                                                       version                                                       -------------------------------------------- ------------------------------------------------------------------- PostgreSQL 9.3.9 on  x86_64-unknown-linux-gnu, compiled by gcc  (GCC)  4.4.7 20120313  (red  HAT 4.4.7-11), &nbsP;64-bit (1  line record) 

Verifying the Barman configuration

[Email protected] ~]# barman check pgserverserver Pgserver:ssh:OKPostgreSQL:OKarchive_mode:OKarchive_command: Okdirectories:okretention policy Settings:okbackup Maximum Age:ok (no last_backup_maximum_age provided) compression set Tings:okminimum Redundancy requirements:failed (with 0 backups, expected at least 1)

Last behavior check the minimum number of saved backups, because there is no backup so 0, the others are OK

Basic use:

The following operations are performed on backup

Start Backup:

[[email protected] ~]# Barman backup pgserverstarting backup for server Pgserver in/var/lib/barman/pgserver/base/2015091 4t151454backup start at Xlog location:0/6000028 (000000010000000000000006, 00000028) Copying files. Copy done. Backup size:32.1 MiB. Actual size on disk:141.8 KiB ( -99.57% deduplication ratio). Asking PostgreSQL server to finalize the backup. Backup end at Xlog location:0/60000b8 (000000010000000000000006, 000000b8) backup completedprocessing xlog segments for PG Server000000010000000000000005000000010000000000000006000000010000000000000006.00000028.backup

List backups:

[Email protected] ~]# barman list-backup pgserverpgserver 20150914t151454-mon Sep + 15:14:55 2015-size:32.1 mib-w AL size:0 bpgserver 20150914t143210-mon Sep 14:32:11 2015-size:32.1 mib-wal size:32.1 kibpgserver 20150914T142 931-mon Sep 14:29:41 2015-size:32.1 mib-wal size:1.9 mibpgserver 20150914t142547-failed

To Delete a backup:

[[email protected] ~]# Barman delete pgserver 20150914t142547deleting backup 20150914t142547 for server Pgserverdelete ociated WAL Segments:done

To restore a backup:

Database is deleted when it is lost, it needs to be restored.

[[Email protected] ~]# su - barman-bash-4.1$ barman recover pgserver  20150914T142931 /tmp/pgserver_recoverStarting local restore for server  Pgserver using backup 20150914t142931 destination directory: /tmp/pgserver_ Recovercopying the base backup. Copying required wal segments. Generating archive status filesdisabling dangerous settings in destination  directory. 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. Recovery completed successful. 

This operation copies the files required to start the database to the specified directory and then starts with a command

-bash-4.1$  /usr/pgsql-9.3/bin/pg_ctl -d /tmp/pg_recover/ startserver  Starting-bash-4.1$ < 2015-09-14 15:52:06.384 cst >log:  redirecting  log output to logging collector process< 2015-09-14 15:52:06.384  cst >hint:  future log output will appear in directory   "Pg_log" .-bash-4.1$ ps -ef|grep postgresbarman    4547      1  0 15:52 pts/0    00:00:00 /usr/pgsql-9.3/bin /postgres -d /tmp/pg_recoverbarman    4548  4547  0  15:52 ?        00:00:00 postgres: logger process                        barman    4550  4547  0 15:52 ?         00:00:00 postgres: checkpointer process                 barman    4551  4547   0 15:52 ?        00:00:00 postgres: writer  process                       barman    4552  4547  0 15:52  ?        00:00:00 postgres: wal writer  process                   barman    4553  4547  0 15:52 ?         00:00:00 postgres: autovacuum launcher process          barman    4554  4547  0 15:52 ?         00:00:00 postgres: archiver process   failed  on 000000010000000000000009barman    4555  4547  0  15:52 ?        00:00:00 postgres: stats collector  process             barman     4560  4434  0 15:52 pts/0    00:00:00 grep  postgres

Even on this database, find your lost data and restore it to pgserver.

To stop the database:

-bash-4.1$/usr/pgsql-9.3/bin/pg_ctl-d/tmp/pg_recover/stopwaiting for server-Shut down .... Doneserver stopped

Scheduled backups:

Use Cron for scheduled backups

#crontab-E

Add the following information:

*/5 * * * */usr/bin/barman backup Pgserver >/dev/null 2>&1

(5-minute backup)

This completes the basic configuration of the barman and restores the backup.

Reference:

Installation and configuration: http://docs.pgbarman.org/#installation

More barman configuration and use See official documentation: http://docs.pgbarman.org/

PostgreSQL Incremental Backup