Infrastructure at your Service

Daniel Westermann

How fast can you setup a standby database with PostgreSQL?

While setting up a PostgreSQL infrastructure at customer today my colleague Pierre Sicot and I discussed on how time consuming it is to setup standby databases if you compare different vendors. Lets make a challenge out of this. This is the baseline:

  1. Install the product from scratch
  2. Create a master database
  3. Create a hot standby database (on the same host)
  4. The standby database needs to be open read only
  5. Create a table containing one record on the master
  6. Verify that the table and the record is there on the standby

For this PostgreSQL demo I used the just released PostgreSQL 9.5 RC1.

This is the script:

[email protected]:/home/postgres/ [dummy] cat create_standby_from_scratch.sh 
#!/bin/bash

BASE=$HOME
SOURCE="${HOME}/postgresql-9.5rc1.tar.bz2"
PGHHOME="${HOME}/pg95rc"

MASTERDATADIR="${HOME}/pgmaster"
STANDBYDATADIR="${HOME}/pgstandby"

PATH=${PHOME}/bin:$PATH
export PATH

pg_ctl stop -D ${HOME}/pgstandby -m fast
pg_ctl stop -D ${HOME}/pgmaster -m fast

rm -rf ${PGHHOME}
rm -rf ${HOME}/postgresql-9.5rc1}
rm -rf ${MASTERDATADIR}
rm -rf ${STANDBYDATADIR}

# configure, compile and install
cd ${HOME}
tar -axf ${SOURCE}
cd ${HOME}/postgresql-9.5rc1
./configure --prefix=${PGHHOME} 
make world
make install

PATH=${PHOME}/bin:$PATH
export PATH

# initialze the master postgresql cluster
initdb -D ${MASTERDATADIR}
echo "listen_addresses = '*'" > ${MASTERDATADIR}/postgresql.conf
echo "port=7777" >> ${MASTERDATADIR}/postgresql.conf
echo "wal_level='hot_standby'" >> ${MASTERDATADIR}/postgresql.conf
echo "max_replication_slots=3" >> ${MASTERDATADIR}/postgresql.conf
echo "max_wal_senders=3" >> ${MASTERDATADIR}/postgresql.conf
pg_ctl start -D ${MASTERDATADIR}
sleep 2
psql -p 7777 -c "select * from pg_create_physical_replication_slot('test');" postgres
pg_ctl stop -D ${MASTERDATADIR} -m fast

# prepare the primary
mkdir ${STANDBYDATADIR}
chmod 700 ${STANDBYDATADIR}
cp -pr ${MASTERDATADIR}/* ${STANDBYDATADIR}/
echo "standby_mode = 'on'" > ${STANDBYDATADIR}/recovery.conf
echo "primary_slot_name = 'test' " >> ${STANDBYDATADIR}/recovery.conf 
echo "primary_conninfo = 'user=postgres port=7777 host=localhost'" >> ${STANDBYDATADIR}/recovery.conf
echo "recovery_target_timeline = 'latest'" >> ${STANDBYDATADIR}/recovery.conf
echo "port=7778" >> ${STANDBYDATADIR}/postgresql.conf
echo "hot_standby='on'" >> ${STANDBYDATADIR}/postgresql.conf
echo "host    replication     postgres        ::1/128            trust" >> ${MASTERDATADIR}/pg_hba.conf

pg_ctl start -D ${MASTERDATADIR}
sleep 2
pg_ctl start -D ${STANDBYDATADIR}

## create a sample table on the master
psql -p 7777 -c "create table t1 ( a int );" postgres
psql -p 7777 -c "insert into t1 values (1);" postgres
sleep 1
# check the table on the standby
psql -p 7778 -c "select * from t1;" postgres

And this is the result (in a VirtualBox VM on my notebook, not a big server):

[email protected]:/home/postgres/ [dummy] time ./create_standby_from_scratch.sh
...
LOG:  autovacuum launcher started
server starting
CREATE TABLE
INSERT 0 1
LOG:  database system was shut down at 2015-12-22 19:41:20 GMT
LOG:  entering standby mode
LOG:  consistent recovery state reached at 0/1718740
LOG:  record with zero length at 0/1718740
LOG:  database system is ready to accept read only connections
LOG:  started streaming WAL from primary at 0/1000000 on timeline 1
LOG:  redo starts at 0/1718740
 a 
---
 1
(1 row)
...
real	0m50.061s
user	0m8.908s
sys	0m4.796s

Less than a minute. The battle is open 🙂

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