Infrastructure at your Service

Daniel Westermann

getting started with postgres plus advanced server (2) – setting up a backup and recovery server

The first post in this series explained how to get ppas installed on a linux system. Now that the database cluster is up and running we should take care immediately about backup and recovery. For this I’ll use another system where I’ll install and configure bart. So, the system overview for now is:

server ip address purpose
ppas 192.168.56.243 ppas database cluster
ppasbart 192.168.56.245 backup and recovery server

As bart requires the postgres binaries I’ll just repeat the ppas installation on the bart server. Check the first poston how to do that.
tip: there is a “–extract-only” switch which only extracts the binaries without bringing up a database cluster.
After that just install the bart rpm:

yum localinstall edb-bart-1.0.2-1.rhel6.x86_64.rpm

All the files will be installed under:

ls -la /usr/edb-bart-1.0/
total 20
drwxr-xr-x.  4 root root    44 Apr 23 13:41 .
drwxr-xr-x. 14 root root  4096 Apr 23 13:41 ..
drwxr-xr-x.  2 root root    17 Apr 23 13:41 bin
drwxr-xr-x.  2 root root    21 Apr 23 13:41 etc
-rw-r--r--.  1 root root 15225 Jan 27 15:24 license.txt

Having a dedicated user for bart is a good idea:

# groupadd bart
# useradd -g bart bart
# passwd bart
Changing password for user bart.
New password: 
Retype new password: 
$passwd: all authentication tokens updated successfully.

As backups need some space a top level directory for all the bart backups needs to be created:

# mkdir /opt/backup
chown bart:bart /opt/backup
chmod 700 /opt/backup
mkdir -p /opt/backup/ppas94/archived_wals

Now everything is in place to start the bart configuration. A minimal configuration file would look like this:

cat /usr/edb-bart-1.0/etc/bart.cfg
[BART]
bart-host = [email protected]
backup_path = /opt/backup
pg_basebackup_path = /opt/PostgresPlus/9.4AS/bin/pg_basebackup
logfile = /var/tmp/bart.log
xlog-method = fetch

[PPAS94]
host = 192.168.56.243
port = 5444
user = enterprisedb
description = "PPAS 94 server"

The BART section is the global section while the next sections are specific to the database clusters to backup and restore. As bart requires passwordless ssh authentication between the bart host and the database host to be backup up lets setup this. On the bart bart host ( ppasbart ):

su - bart
ssh-keygen -t rsa
On the host where database runs ( ppas ):
su -
cd /opt/PostgresPlus/9.4AS
mkdir .ssh
chown enterprisedb:enterprisedb .ssh/
chmod 700 .ssh/
su - enterprisedb
ssh-keygen -t rsa

As the public keys are now available we’ll need to make them available on each host. On the ppas host:

cat .ssh/id_rsa.pub > .ssh/authorized_keys
chmod 600 .ssh/authorized_keys

Add the public key from the barthost to the authorized keys file above. Example: get the public key from the bart host:

[[email protected] ~]$ id
uid=1001(bart) gid=1001(bart) groups=1001(bart) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[[email protected] ~]$ cat .ssh/id_rsa.pub 
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQCn+DN//ia+BocR6kTfHkPoXfx3/HRU5KM1Bqy1nDeGnUn98CSl3kbRkUkiyumDfj4XOIoxOxnVJw6Invyi2VjzeQ12XMMILBFRBAoePDpy4kOQWY+SaS215G72DKzNYY8nGPUwjaQdFpFt3eQhwLP4D5uqomPIi9Dmv7Gp8ZHU0DBgJfrDaqrg8oF3GrzF50ZRjZTAkF3pDxJnrzIEEme+QQFKVxBnSU2ClS5XHdjMBWg+oSx3XSEBHZefP9NgX22ru52lTWmvTscUQbIbDo8SaWucIZC7uhvljteN4AuAdMv+OUblOm9ZUtO2Y8vX8hNMJvqRBlYh9RGl+m6wUZLN document.write(['bart','ppasbart.loca'].join('@'))l

Copy/paste this key into the authorized_keys file for the enterprisedb user on the database host, so that the file looks similar to this:

cat .ssh/id_rsa.pub 
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQCn+DN//ia+BocR6kTfHkPoXfx3/HRU5KM1Bqy1nDeGnUn98CSl3kbRkUkiyumDfj4XOIoxOxnVJw6Invyi2VjzeQ12XMMILBFRBAoePDpy4kOQWY+SaS215G72DKzNYY8nGPUwjaQdFpFt3eQhwLP4D5uqomPIi9Dmv7Gp8ZHU0DBgJfrDaqrg8oF3GrzF50ZRjZTAkF3pDxJnrzIEEme+QQFKVxBnSU2ClS5XHdjMBWg+oSx3XSEBHZefP9NgX22ru52lTWmvTscUQbIbDo8SaWucIZC7uhvljteN4AuAdMv+OUblOm9ZUtO2Y8vX8hNMJvqRBlYh9RGl+m6wUZLN l
[[email protected] ~]$ cat .ssh/authorized_keys
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDAQZWeegLpqVB20c3cIN0Bc7pN6OjFM5pBsunDbO6SQ0+UYxZGScwjnX9FSOlmYzqrlz62jxV2dOJBHgaJj/mbFs5XbmvFw6Z4Zj224aBOXAfej4nHqVnn1Tpuum4HIrbsau3rI+jLCNP+MKnumwM7JiG06dsoG4PeUOghCLyFrItq2/uCIDHWoeQCqqnLD/lLG5y1YXQCSR4VkiQm62tU0aTUBQdZWnvtgskKkHWyVRERfLOmlz2puvmmc5YxmQ5XBVMN5dIcIZntTfx3JC3imjrUl10L3hkiPkV0eAt3KtC1M0n9DDao3SfHFfKfEfp5p69vvpZM2uGFbcpkQrtN l
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQCn+DN//ia+BocR6kTfHkPoXfx3/HRU5KM1Bqy1nDeGnUn98CSl3kbRkUkiyumDfj4XOIoxOxnVJw6Invyi2VjzeQ12XMMILBFRBAoePDpy4kOQWY+SaS215G72DKzNYY8nGPUwjaQdFpFt3eQhwLP4D5uqomPIi9Dmv7Gp8ZHU0DBgJfrDaqrg8oF3GrzF50ZRjZTAkF3pDxJnrzIEEme+QQFKVxBnSU2ClS5XHdjMBWg+oSx3XSEBHZefP9NgX22ru52lTWmvTscUQbIbDo8SaWucIZC7uhvljteN4AuAdMv+OUblOm9ZUtO2Y8vX8hNMJvqRBlYh9RGl+m6wUZLN

Make the file the same on the bart host and test if you can connect without passwords:

[[email protected] ~]$ hostname
ppasbart.local
[[email protected] ~]$ ssh [email protected]
Last login: Thu Apr 23 14:24:39 2015 from ppas
[[email protected] ~]$ logout
Connection to ppasbart closed.
[[email protected] ~]$ ssh [email protected]
Last login: Thu Apr 23 14:24:47 2015 from ppas
-bash-4.2$ logout
Connection to ppas closed.

Do the same test on the ppas host:

bash-4.2$ hostname
ppas.local
-bash-4.2$ ssh [email protected]
Last login: Thu Apr 23 14:22:07 2015 from ppasbart
[[email protected] ~]$ logout
Connection to ppasbart closed.
-bash-4.2$ ssh [email protected]
Last login: Thu Apr 23 14:22:18 2015 from ppasbart
-bash-4.2$ logout
Connection to ppas closed.
-bash-4.2$

Once this works we need to setup a replication user in the database being backed up. So create the user in the database which runs on the ppas host (I’ll do that with enterprise user instead of the postgres user as we’ll need to adjust pg_hba.conf file right after creating the user):

[[email protected] 9.4AS]# su - enterprisedb
Last login: Thu Apr 23 14:25:50 CEST 2015 from ppasbart on pts/1
-bash-4.2$ . pgplus_env.sh
-bash-4.2$ psql -U enterprisedb
psql.bin (9.4.1.3)
Type "help" for help.

edb=# CREATE ROLE bart WITH LOGIN REPLICATION PASSWORD 'bart';       
CREATE ROLE
edb=# exit
-bash-4.2$ echo "host    all     bart         192.168.56.245/32          md5" >> data/pg_hba.conf

Make sure that the IP matches your bart host. Then adjust the bart.cfg file on the bart host to match your configuration:

cat /usr/edb-bart-1.0/etc/bart.cfg
[BART]
bart-host = [email protected]
backup_path = /opt/backup
pg_basebackup_path = /opt/PostgresPlus/9.4AS/bin/pg_basebackup
logfile = /var/tmp/bart.log
xlog-method = fetch

[PPAS94]
host = 192.168.56.243
port = 5444
user = bart
remote-host = [email protected]
description = "PPAS 94 remote server"

Another requirement is that the bart database user must be able to connect to the database without prompting for a password. Thus we create the .pgpass file on the bart host which is used for reading the password:

[[email protected] ~]$ cat .pgpass 
192.168.56.243:5444:*:bart:bart
[[email protected] ~]$ chmod 600 .pgpass

As a last step we need to enable wal archiving on the database that should be backed up. The following parameters need to be set in the postgresql.conf file:

wal_level = archive  # or higher
archive_mode = on
archive_command = 'scp %p [email protected]:/opt/backup/ppas94/archived_wals/%f'
max_wal_senders = 1  # or higher

Once done restart the database cluster:

su -
service ppas-9.4 restart

Lets see if bart can see anything on the bart server:

[[email protected] ~]$ /usr/edb-bart-1.0/bin/bart -c /usr/edb-bart-1.0/etc/bart.cfg SHOW-SERVERS -s PPAS94 
Server name         : ppas94
Host name           : 192.168.56.243
User name           : bart
Port                : 5444
Remote host         : [email protected]
Archive path        : /opt/backup/ppas94/archived_wals
WARNING: xlog-method is empty, defaulting to global policy
Xlog Method         : fetch
Tablespace path(s)  : 
Description         : "PPAS 94 remote server"

Looks fine. So lets do a backup:

[[email protected] ~]$ /usr/edb-bart-1.0/bin/bart -c /usr/edb-bart-1.0/etc/bart.cfg BACKUP -s PPAS94

INFO:  creating backup for server 'ppas94'
INFO:  backup identifier: '1429795268774'
WARNING: xlog-method is empty, defaulting to global policy
56357/56357 kB (100%), 1/1 tablespace

INFO:  backup checksum: 6e614f981902c99326a7625a9c262d98
INFO:  backup completed successfully

Cool. Lets see what is in the backup catalog:

[[email protected] tmp]# ls -la /opt/backup/
total 0
drwx------. 3 bart bart 19 Apr 23 15:02 .
drwxr-xr-x. 4 root root 38 Apr 23 13:49 ..
drwx------. 4 bart bart 46 Apr 23 15:21 ppas94
[[email protected] tmp]# ls -la /opt/backup/ppas94/
total 4
drwx------. 4 bart bart   46 Apr 23 15:21 .
drwx------. 3 bart bart   19 Apr 23 15:02 ..
drwx------. 2 bart bart   36 Apr 23 15:21 1429795268774
drwx------. 2 bart bart 4096 Apr 23 15:21 archived_wals
[[email protected] tmp]# ls -la /opt/backup/ppas94/1429795268774/
total 56364
drwx------. 2 bart bart       36 Apr 23 15:21 .
drwx------. 4 bart bart       46 Apr 23 15:21 ..
-rw-rw-r--. 1 bart bart       33 Apr 23 15:21 base.md5
-rw-rw-r--. 1 bart bart 57710592 Apr 23 15:21 base.tar
[[email protected] tmp]# ls -la /opt/backup/ppas94/archived_wals/
total 81928
drwx------. 2 bart bart     4096 Apr 23 15:21 .
drwx------. 4 bart bart       46 Apr 23 15:21 ..
-rw-------. 1 bart bart 16777216 Apr 23 15:10 000000010000000000000002
-rw-------. 1 bart bart 16777216 Apr 23 15:13 000000010000000000000003
-rw-------. 1 bart bart 16777216 Apr 23 15:20 000000010000000000000004
-rw-------. 1 bart bart 16777216 Apr 23 15:21 000000010000000000000005
-rw-------. 1 bart bart 16777216 Apr 23 15:21 000000010000000000000006
-rw-------. 1 bart bart      304 Apr 23 15:21 000000010000000000000006.00000028.backup

Use the SHOW-BACKUPS switch to get on overview of the backups available:

[[email protected] ~]$ /usr/edb-bart-1.0/bin/bart -c /usr/edb-bart-1.0/etc/bart.cfg SHOW-BACKUPS 
 Server Name   Backup ID       Backup Time           Backup Size  
                                                                  
 ppas94        1429795268774   2015-04-23 15:21:23   55.0371 MB   
 ppas94        1429795515326   2015-04-23 15:25:18   5.72567 MB   
 ppas94        1429795614916   2015-04-23 15:26:58   5.72567 MB   
                                                                  

A backup without a restore proves nothing so lets try to restore one of the backups to the ppas server to a different directory:

[[email protected] 9.4AS]# mkdir /opt/PostgresPlus/9.4AS/data2
[[email protected] 9.4AS]# chown enterprisedb:enterprisedb /opt/PostgresPlus/9.4AS/data2

On the ppasbart host do the restore:

[[email protected]rt ~]$ /usr/edb-bart-1.0/bin/bart -c /usr/edb-bart-1.0/etc/bart.cfg RESTORE -s PPAS94 -i 1429795614916 -r [email protected] -p /opt/PostgresPlus/9.4AS/data2
INFO:  restoring backup '1429795614916' of server 'ppas94'
INFO:  restoring backup to [email protected]:/opt/PostgresPlus/9.4AS/data2
INFO:  base backup restored
INFO:  archiving is disabled
INFO:  backup restored successfully at [email protected]:/opt/PostgresPlus/9.4AS/data2

Looks good. Lets see what is in the data2 directory on the ppas host:

[[email protected] 9.4AS]# ls /opt/PostgresPlus/9.4AS/data2
backup_label  dbms_pipe  pg_clog      pg_hba.conf    pg_log      pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION  postgresql.auto.conf
base          global     pg_dynshmem  pg_ident.conf  pg_logical  pg_notify     pg_serial    pg_stat       pg_subtrans  pg_twophase  pg_xlog     postgresql.conf
[[email protected] 9.4AS]# ls /opt/PostgresPlus/9.4AS/data2/pg_xlog
000000010000000000000008  archive_status

Looks good, too. As this is all on the same server we need to change the port before bringing up the database:

-bash-4.2$ grep port postgresql.conf  | head  -1
port = 5445				# (change requires restart)
-bash-4.2$ pg_ctl start -D data2/
server starting
-bash-4.2$ 2015-04-23 16:01:30 CEST FATAL:  data directory "/opt/PostgresPlus/9.4AS/data2" has group or world access
2015-04-23 16:01:30 CEST DETAIL:  Permissions should be u=rwx (0700).

Ok, fine. Change it:

-bash-4.2$ chmod 700 /opt/PostgresPlus/9.4AS/data2
-bash-4.2$ pg_ctl start -D data2/
server starting
-bash-4.2$ 2015-04-23 16:02:00 CEST LOG:  redirecting log output to logging collector process
2015-04-23 16:02:00 CEST HINT:  Future log output will appear in directory "pg_log".

Seems ok, lets connect:

-bash-4.2$ psql -p 5445 -U bart
Password for user bart: 
psql.bin (9.4.1.3)
Type "help" for help.

edb=> l
                                           List of databases
   Name    |    Owner     | Encoding |   Collate   |    Ctype    | ICU |       Access privileges       
-----------+--------------+----------+-------------+-------------+-----+-------------------------------
 edb       | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | 
 postgres  | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | 
 template0 | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | =c/enterprisedb              +
           |              |          |             |             |     | enterprisedb=CTc/enterprisedb
 template1 | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | =c/enterprisedb              +
           |              |          |             |             |     | enterprisedb=CTc/enterprisedb
(4 rows)

Cool. Works. But: archiving is disabled and you’ll need to enable it again. This is the default behavior of bart as it adds “archive_mode=off” to the end of the postgressql.conf. But take care that you adjust the archive_command parameter as all archived wals will be scp’ed to the same directory on the ppasbart server as the original database did. Can we do a point in time recovery? Let’s try (I’ll destroy the restored database cluster and will use the same data2 directory ):

-bash-4.2$ pg_ctl -D data2 stop -m fast
waiting for server to shut down.... done
server stopped
-bash-4.2$ rm -rf data2/*
-bash-4.2$ 

Lets try the restore to a specific point in time:

[[email protected] ~]$ /usr/edb-bart-1.0/bin/bart -c /usr/edb-bart-1.0/etc/bart.cfg RESTORE -s PPAS94 -i 1429795614916 -r [email protected] -p /opt/PostgresPlus/9.4AS/data2 -g '2015-04-03 15:23:00'
INFO:  restoring backup '1429795614916' of server 'ppas94'
INFO:  restoring backup to [email protected]:/opt/PostgresPlus/9.4AS/data2
INFO:  base backup restored
INFO:  creating recovery.conf file
INFO:  archiving is disabled
INFO:  backup restored successfully at [email protected]:/opt/PostgresPlus/9.4AS/data2

Seems ok, but what is the difference? When specifying a point in time a recovery.conf file will be created for the restored database cluster:

-bash-4.2$ cat data2/recovery.conf
restore_command = 'scp -o BatchMode=yes -o PasswordAuthentication=no [email protected]:/opt/backup/ppas94/archived_wals/%f %p'
recovery_target_time = '2015-04-03 15:23:00'

Lets start the database (after changing the port again in postgresql.conf):

-bash-4.2$ pg_ctl -D data2 start
server starting
-bash-4.2$ 2015-04-23 16:16:12 CEST LOG:  redirecting log output to logging collector process
2015-04-23 16:16:12 CEST HINT:  Future log output will appear in directory "pg_log".

Are we able to connect?

-bash-4.2$ psql -U bart -p 5445 
Password for user bart: 
psql.bin (9.4.1.3)
Type "help" for help.

edb=>

Works, too. So now we have a central backup server for our postgresql infrastructure from which backups and restores can be executed. Combine this with a backup software (like netbackup, etc) which picks up the backups from the bartserver and you should be fine. in the next post we’ll setup a hot standby database server.

5 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure