======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