So, we have a ppas 94 database up and running and we have a backup server for backing up and restoring the database. Now it is time to additionally protect the database by setting up a hot standby database. This database could even be used to offload reporting functionality from the primary database as the standby database will be open in read only mode. Again, I’ll use another system for that so that the system overview looks like this:

server ip address purpose
ppas 192.168.56.243 ppas database cluster
ppasbart 192.168.56.245 backup and recovery server
ppasstandby 192.168.56.244 ppas hot standby database

As the standby database will need the ppas binaries just follow the first post for setting this up again. Once the binaries are installed and the database is up and running I’ll completely destroy it but keep the data directory:

[root@oel7 tmp]# service ppas-9.4 stop
Stopping Postgres Plus Advanced Server 9.4: 
waiting for server to shut down.... done
server stopped
[root@oel7 tmp]# rm -rf /opt/PostgresPlus/9.4AS/data/*
[root@oel7 tmp]# 

Ready to go. It is amazingly easy to setup a hot standby server with postgres. In a nutshell, everything that needs to be done is to create a replication user in the database, do a base backup of the primary database, copy that to the standby server, create a recovery.conf file and startup the standby database. Lets start by creating the user which will be used for the recovery in the primary database:

[root@ppas ~]# su - enterprisedb
-bash-4.2$ . ./pgplus_env.sh 
-bash-4.2$ psql
psql.bin (9.4.1.3)
Type "help" for help.

edb=# edb=# create role standby LOGIN REPLICATION UNENCRYPTED PASSWORD 'standby';
CREATE ROLE
edb=# commit;
COMMIT
edb'# 

… and adjust the pg_hba.conf file (the second entry is for the base backup later):

-bash-4.2$ tail -1 data/pg_hba.conf
host    replication     standby         192.168.56.244/24          md5
local   replication     standby                                              md5

… and adjust the wal-level in postgresql.conf

-bash-4.2$ grep wal_level data/postgresql.conf 
wal_level = hot_standby			# minimal, archive, hot_standby, or logical

For the settings in pg_hba.conf and postgresql.conf to take effect either a reload of the main server process or a complete restart is required:

-bash-4.2$ pg_ctl -D data/ restart
waiting for server to shut down..... done
server stopped
server starting

Now it is a good time to test if we can connect to the primary database from the standby node:

[root@oel7 tmp]# /opt/PostgresPlus/9.4AS/bin/psql -h 192.168.56.243 -U standby edb
Password for user standby: 
psql.bin (9.4.1.3)
Type "help" for help.

edb=> 

Ready for the basebackup of the primary database?

mkdir /var/tmp/primary_base_backup/
-bash-4.2$ pg_basebackup -D /var/tmp/primary_base_backup/ -U standby -F t -R -x -z -l for_standby -P
Password: 
56517/56517 kB (100%), 1/1 tablespace
-bash-4.2$ 

Especially notice the “-R” switch of pg_basebackup as this creates a minimal recovery.conf for us which we can use as a template for our standby database. Transfer and extract the file written to the standby server (I again prepared passwordless ssh authentication between the primary and the standby server. check the second post on how to do that).

bash-4.2$ pwd
/opt/PostgresPlus/9.4AS/data
bash-4.2$ scp 192.168.56.243:/var/tmp/primary_base_backup/* .
base.tar.gz                                                                                                  100% 5864KB   5.7MB/s   00:00    
-bash-4.2$ 
-bash-4.2$ tar -axf base.tar.gz 
-bash-4.2$ ls
backup_label  dbms_pipe  pg_dynshmem    pg_log        pg_notify    pg_snapshots  pg_subtrans  PG_VERSION            postgresql.conf
base          global     pg_hba.conf    pg_logical    pg_replslot  pg_stat       pg_tblspc    pg_xlog               recovery.conf
base.tar.gz   pg_clog    pg_ident.conf  pg_multixact  pg_serial    pg_stat_tmp   pg_twophase  postgresql.auto.conf
-bash-4.2$ 

Almost ready. Now we need to adjust the recovery.conf file:

standby_mode = 'on'
primary_conninfo = 'host=192.168.56.243 port=5444 user=standby password=standby'
restore_command = 'scp [email protected]:/opt/backup/ppas94/archived_wals/%f %p'

… and enable hot standby mode in the postgresql.conf file on the standby server and adjust the listen address:

-bash-4.2$ grep hot postgresql.conf 
wal_level = hot_standby			# minimal, archive, hot_standby, or logical
hot_standby = on			# "on" allows queries during recovery
#hot_standby_feedback = off		# send info from standby to prevent
-bash-4.2$ grep listen data/postgresql.conf
listen_addresses = '192.168.56.244'		# what IP address(es) to listen on;

Startup the standby database and if everything is fine messages similar to this should be reported in the postgresql log file (/opt/PostgresPlus/9.4AS/data/pg_log/):

2015-04-29 14:03:36 CEST LOG:  entering standby mode
scp: /opt/backup/ppas94/archived_wals/000000010000000000000017: No such file or directory
2015-04-29 14:03:36 CEST LOG:  consistent recovery state reached at 0/17000090
2015-04-29 14:03:36 CEST LOG:  redo starts at 0/17000090
2015-04-29 14:03:36 CEST LOG:  record with zero length at 0/170000C8
2015-04-29 14:03:36 CEST LOG:  database system is ready to accept read only connections
2015-04-29 14:03:36 CEST LOG:  started streaming WAL from primary at 0/17000000 on timeline 1

To further prove the setup lets create a simple table in the primary database and add some rows to it:

edb=# create table standby_test ( a int ); 
CREATE TABLE
edb=# insert into standby_test values (1);
INSERT 0 1
edb=# insert into standby_test values (2);
INSERT 0 1
edb=# commit;
COMMIT
edb=# ! hostname
ppas.local
edb=#

Lets see if we can query the table on the standby:

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

edb=# select * from standby_test;
 a 
---
 1
 2
(2 rows)

edb=# ! hostname
ppasstandby.local
edb=# 

Cool. Minimal effort for getting a hot standby database up and running. Make yourself familiar with the various settings that influence the behavior of the standby database. I’ll write another post on how to do failovers in near future.