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.