"PgBackRest" PostgreSQL backup and restore solution .

To implement best backup and restore policy for PostgreSQL ,We should use PgBackRest ,As it's provides lot's of features without paying any money .

I am going to cover some basics functionality regarding  PgBackRest as per my knowledge .

Introduction : -

PgBackRest aims to be a simple backup and restore system that can seamlessly scale up to the largest databases and workloads.

Primary PgBackRest features:

• Local or remote backup

• Multi-threaded backup/restore for performance

• Checksums

• Safe backups (checks that logs required for consistency are present before backup completes)

• Full, differential, and incremental backups

• Backup rotation (and minimum retention rules with optional separate retention for archive)

• In-stream compression/decompression

• Archiving and retrieval of logs for replicas/restores built in

• Async archiving for very busy systems (including space limits)

• Backup directories are consistent Postgres clusters (when hardlinks are on and compression is off)

• Tablespace support

• Restore delta option

• Restore using timestamp/size or checksum

• Restore remapping base/tablespaces

Installation : -

Installed some pre-required packages .

yum install perl-DBD-Pg yum install perl-Time-HiRes yum install perl-Test*

Download cpanm from below link .

http://search.cpan.org/dist/App-cpanminus/

Then install it 

wget http://search.cpan.org/CPAN/authors/id/M/MI/MIYAGAWA/App-cpanminus-1.7043.tar.gz tar -xvf App-cpanminus-1.7043.tar.gz cd App-cpanminus-1.7043 perl Makefile.PL make make test make install

After installation of cpanm ,Run execute below commands .

cpanm Net::OpenSSH cpanm IPC::System::Simple cpanm threads (update this package when thread-max > 1) cpanm Thread::Queue (update this package when thread-max > 1) cpan parent


So ,Now we will start installation of PgBackRest

Download Latest code from below link .

https://github.com/pgbackrest/pgbackrest



wget https://github.com/pgbackrest/pgbackrest/archive/master.zip unzip master cd pgbackrest-master #Copy Lib files cp -r /tmp/pgbackrest-master/lib/pgBackRest /usr/share/perl5 #Change Permission sudo find /usr/share/perl5/pgBackRest -type f -exec chmod 644 {} + sudo find /usr/share/perl5/pgBackRest -type d -exec chmod 755 {} + #Copy binary cp /tmp/pgbackrest-master/bin/pgbackrest /usr/bin/pgbackrest #Change Permission sudo chmod 755 /usr/bin/pgbackrest sudo mkdir -m 770 /var/log/pgbackrest sudo chown postgres:postgres /var/log/pgbackrest

You can verify installation .

Configuration : -

Change below paramaters into postgresql.conf .

archive_command = 'pgbackrest --stanza=my_test archive-push %p' archive_mode = on listen_addresses = '*' max_wal_senders = 3 wal_level = hot_standby

Create stanza on backup server 

Add below entry into /etc/pgbackrest.conf

[my_test]
db-path=/opt/PostgreSQL/9.6/data2

[global]
repo-path=/opt/backup_repo/pgbackrest

After that execute below command to create stanza 

pgbackrest --stanza=my_test --log-level-console=info stanza-create --db-host 192.168.213.238 --db-port 5433

Check stanza configuration by executing below command .

pgbackrest --stanza my_test --db-host 192.168.213.238 --db-port 5433 --log-level-console=info check

Now ,Let's take a backup 

1. First time it will take full backup
2. After that it will start taking incremental backup (You can change if you want) 

Use below command to take a backup .

pgbackrest --stanza=my_test --log-level-console=info --db-host 192.168.213.238 --db-port 5433 backup > /tmp/backup.log 2>&1

 

List-out backups :- 

Now ,Try to restore the backup upto some point of of time (PITR).

recovery.conf will get created automatically 

pgbackrest --stanza=my_test --db-path=/opt/PostgrSQL/DB_restore/ --log-level-console=info  --recovery-option recovery_target_time='2017-06-09 04:51:56' restore > /tmp/my_test_restore.log 2>&1