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:
- Install the product from scratch
- Create a master database
- Create a hot standby database (on the same host)
- The standby database needs to be open read only
- Create a table containing one record on the master
- 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:
postgres@oel7:/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):
postgres@oel7:/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 🙂