Notes on Streaming Test
https://www.postgresql.org/docs/9.6/static/high-availability.html
Setup
Install
rpm -i https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm rpm -i https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm yum install htop pg_activity mlocate bash-completion bash-completion-extras yum install postgresql96.x86_64 postgresql96-contrib.x86_64 postgresql96-devel.x86_64 postgresql96-libs.x86_64 postgresql96-server.x86_64 yum install postgis2_96 postgis2_96-utils postgis2_96-client
PostgreSQL Setup
Primary Server
Initial DB Population
Initialize the DB and get our schema and data into the new primary server.
# initialize on-disk DB /usr/pgsql-9.6/bin/postgresql96-setup initdb # Get roles pg_dumpall -r --file=./pg_roles.sql -h db0.cloud.votesmart.org -U mike -l pvsadmin # Get schema pg_dump -s -d pvsadmin -n public -n pyadmin -n django -n whitefront -h db0.cloud.votesmart.org -U mike -f pvsadmin_schema-20170417.sql # Get data #pg_dump -a -d pvsadmin -n public -n pyadmin -n django -n whitefront -h db0.cloud.votesmart.org -U mike -f pvsadmin_data-20170417.sql pg_dump -Fc -a -d pvsadmin -n public -n pyadmin -n django -n whitefront -h db0.cloud.votesmart.org -U mike -f pvsadmin_data-20170417.sql # On the new database server, create the DB and extensions necessary psql -c 'CREATE DATABASE pvsadmin' postgres psql -c 'CREATE EXTENSION postgis' pvsadmin # Import roles psql -1f pg_roles.sql # # This shouldn't be necessary after this one time, but leaving it here in case it's needed again # # Remove old user references sed -i 's/FROM adrian/FROM postgres/' pvsadmin_schema-20170417.sql sed -i 's/TO adrian/TO postgres/' pvsadmin_schema-20170417.sql sed -i 's/FROM slon/FROM postgres/' pvsadmin_schema-20170417.sql sed -i 's/TO slon/TO postgres/' pvsadmin_schema-20170417.sql # Remove all GRANTs to old users sed -i '/TO pgsql/d' pvsadmin_schema-20170417.sql sed -i '/TO jason/d' pvsadmin_schema-20170417.sql sed -i '/TO clinton/d' pvsadmin_schema-20170417.sql sed -i '/TO sarkis/d' pvsadmin_schema-20170417.sql sed -i '/TO ecasey/d' pvsadmin_schema-20170417.sql # Remove slony triggers and references sed -i '/_cluster_/d' pvsadmin_schema-20170417.sql # Import schema psql -1f pvsadmin_schema-20170417.sql pvsadmin # Import data psql -1f pvsadmin_data-20170417.sql pvsadmin pg_restore -1 -a -d pvsadmin --disable-triggers pvsadmin_data-20170417.sql
Now you should have a standlone DB setup. Now we need to configure it to be usable as a primary for streaming replication.
System Preparation
Create the WAL archive directory.
mkdir -p /var/data/postgres/wal && chown -R postgres:postgres /var/data/postgres/ && chmod 750 /var/data/postgres
Configuration
Notable config parameters in postgresql.conf below(these do not include any performance tuning, just what is necessary for replication):
listen_addresses = '*' wal_level = replica archive_mode = on archive_command = 'test ! -f /var/data/postgres/wal/%f && cp %p /var/data/postgres/wal/%f' max_wal_senders = 5 max_replication_slots = 1 # This should be equivalent to the amount of standbys
Update pg_hba.conf for replication.
host replication repl 10.1.20.0/24 md5
Standby Server
System Preparation
Create the WAL archive directory.
mkdir -p /var/data/postgres/wal && chown -R postgres:postgres /var/data/postgres/ && chmod 750 /var/data/postgres
Create a Replication Slot
On the primary, make sure this same replication slot has been created.
SELECT * FROM pg_create_physical_replication_slot('testdb9');DB Population
Do a base backup of the new primary to the standby. This basically clones the primary server.
pg_basebackup -P -U repl --xlog-method=stream -R -h or-testdb7.cloud.votesmart.org -D /var/lib/pgsql/9.6/data
Configuration
Since postgresql.conf is included in the basebackup we did, we only need to make some minor changes.
hot_standby = on
pg_basebackup should have created a #recovery.conf but we want to make a few changes. A recovery command is needed, and we should utilize a replication slot. Make sure to add the following:
restore_command = 'test -f /var/data/postgres/wal/%f && cp /var/data/postgres/wal/%f "%p"' primary_slot_name = 'testdb9'
CategoryITMisc
