Revision history for PostgresStreamingNotes


Revision [19655]

Last edited on 2017-04-19 11:00:59 by MikeShultz
Additions:
==Create a Replication Slot==

Revision [19654]

Edited on 2017-04-19 10:42:49 by MikeShultz

No Differences

Revision [19651]

Edited on 2017-04-19 10:40:14 by MikeShultz
Deletions:
##recovery.conf## contains sensitive information. Make sure it isn't publicly readable.
chmod 640 /var/lib/pgsql/9.6/data/recovery.conf

Revision [19650]

Edited on 2017-04-19 10:38:33 by MikeShultz
Additions:
##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'
On the primary, make sure this same replication slot has been created.
SELECT * FROM pg_create_physical_replication_slot('testdb9');

Revision [19646]

Edited on 2017-04-19 10:33:43 by MikeShultz
Additions:
max_replication_slots = 1 # This should be equivalent to the amount of standbys
Since ##postgresql.conf## is included in the basebackup we did, we only need to make some minor changes.
hot_standby = on
##recovery.conf## contains sensitive information. Make sure it isn't publicly readable.
chmod 640 /var/lib/pgsql/9.6/data/recovery.conf

Revision [19637]

Edited on 2017-04-19 10:13:43 by MikeShultz
Additions:
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
Deletions:
Do a base backup of the primary **from the standby**.
pg_basebackup

Revision [19595]

Edited on 2017-04-18 16:57:53 by MikeShultz
Additions:
===Primary Server===
===Standby Server===
==DB Population==
Do a base backup of the primary **from the standby**.
pg_basebackup
Deletions:
===Primary===

Revision [19591]

Edited on 2017-04-18 16:52:24 by MikeShultz
Additions:
Notable config parameters in ##postgresql.conf## below(these do not include any performance tuning, just what is necessary for replication):
Deletions:
Notable config parameters below(these do not include any performance tuning, just what is necessary for replication):

Revision [19590]

Edited on 2017-04-18 16:51:56 by MikeShultz
Additions:
Update ##pg_hba.conf## for replication.
host replication repl 10.1.20.0/24 md5

Revision [19589]

Edited on 2017-04-18 16:50:18 by MikeShultz
Additions:
==Initial DB Population==
==System Preparation==
==Configuration==

Revision [19588]

Edited on 2017-04-18 16:49:17 by MikeShultz
Additions:
===Primary===
Now you should have a standlone DB setup. Now we need to configure it to be usable as a primary for streaming replication.
Notable config parameters 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
Create the WAL archive directory.
mkdir -p /var/data/postgres/wal && chown -R postgres:postgres /var/data/postgres/ && chmod 750 /var/data/postgres
Deletions:
#pg_restore -1 -a -d pvsadmin -h db0.cloud.votesmart.org -U mike --disable-triggers pvsadmin_data-20170417.sql

Revision [19532]

Edited on 2017-04-18 12:29:51 by MikeShultz
Additions:
#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
#pg_restore -1 -a -d pvsadmin -h db0.cloud.votesmart.org -U mike --disable-triggers pvsadmin_data-20170417.sql
pg_restore -1 -a -d pvsadmin --disable-triggers pvsadmin_data-20170417.sql
Deletions:
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

Revision [19497]

Edited on 2017-04-18 10:34:01 by MikeShultz
Additions:
# 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
# Import data
psql -1f pvsadmin_data-20170417.sql pvsadmin

Revision [19439]

Edited on 2017-04-17 17:02:46 by MikeShultz
Additions:
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

Revision [19438]

Edited on 2017-04-17 16:58:27 by MikeShultz
Additions:
# Remove all GRANTs to old users
Deletions:
# Remove all GRANTs to pgsql(deleted)

Revision [19436]

Edited on 2017-04-17 16:57:47 by MikeShultz
Additions:
# Remove all GRANTs to pgsql(deleted)
sed -i '/TO pgsql/d' pvsadmin_schema-20170417.sql

Revision [19434]

Edited on 2017-04-17 16:51:25 by MikeShultz
Additions:
psql -1f pvsadmin_schema-20170417.sql pvsadmin

Revision [19433]

Edited on 2017-04-17 16:50:46 by MikeShultz
Additions:
#
# 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 slony triggers and references
sed -i '/_cluster_/d' pvsadmin_schema-20170417.sql

Revision [19430]

Edited on 2017-04-17 16:41:16 by MikeShultz
Additions:
yum install postgis2_96 postgis2_96-utils postgis2_96-client
# On the new database server, create the DB and extensions necessary
psql -c 'CREATE DATABASE pvsadmin' postgres
psql -c 'CREATE EXTENSION postgis' pvsadmin

Revision [19423]

Edited on 2017-04-17 16:20:49 by MikeShultz
Additions:
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
Deletions:
pg_dump -s -n public -n pyadmin -n django -n whitefront -h db0.votesmart.org -U mike -f pvsadmin_schema-20170417.sql

Revision [19422]

Edited on 2017-04-17 16:17:04 by MikeShultz
Additions:
pg_dump -s -n public -n pyadmin -n django -n whitefront -h db0.votesmart.org -U mike -f pvsadmin_schema-20170417.sql
psql -1f pg_roles.sql
Deletions:
pg_dump -s -h db0.votesmart.org -U mike -f pvsadmin_schema-20170417.sql

Revision [19383]

Edited on 2017-04-17 14:04:22 by MikeShultz
Additions:
yum install htop pg_activity mlocate bash-completion bash-completion-extras
====PostgreSQL Setup====
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 -h db0.votesmart.org -U mike -f pvsadmin_schema-20170417.sql
# Import roles
# Import schema
Deletions:
yum install htop pg_activity mlocate

Revision [19382]

Edited on 2017-04-17 13:24:00 by MikeShultz
Additions:
%%
%%

Revision [19381]

The oldest known version of this page was created on 2017-04-17 13:23:45 by MikeShultz
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki