====Slony====

{{image url="http://wiki.votesmart.org/ITImages/files.xml?action=download&file=replication-20150526-2.png" title="Slony replication" alt="Replication diagram"}}
As of 4/20/2015

===Setting Up a Slave===

NOTE: These steps may change slightly between distributions or whether or not there are multiple instances. These are based on RedHat based distributions(RHEL, CentOS, Fedora, etc).

NOTE: These should all be run from the new slave.

~1) Configure postgres
~1) Make sure proper entries are in ~/.pgpass and /var/lib/pgsql/<version>/data/pg_hba.conf to allow for connections between the slave and master
~1) Set some helpful environmental variables. %% export MASTER_HOST='127.0.0.1'; export MASTER_PORT='5432'; export NEW_HOST='127.0.0.1'; export NEW_PORT='5433'; %%
~1) Transfer DB schema to new DB, without indexes and triggers to speed import.%% pg_dump -Uslon -h$MASTER_HOST -p$MASTER_PORT -s -npublic -ndjango -O pvsadmin | sed 's/CREATE INDEX/--CREATE INDEX/' | sed '/CREATE TRIGGER/,/FK CONSTRAINT/d' > pvsadmin_slon_ready.sql %%
~1) Move CREATE INDEX statements to other file for later creation. %% grep 'CREATE INDEX' pvsadmin_slon_ready.sql | sed 's/--//' > pvsadmin_index.sql %%
~1) Grab script for roles. %% pg_dumpall -r --file=./pg_roles.sql -h$MASTER_HOST -p$MASTER_PORT -Uslon %%
~1) Create database pvsadmin with UTF8 encoding. %% /usr/pgsql-<version>/bin/createdb -p$NEW_PORT -EUTF8 pvsadmin %%
~1) Create roles. %% /usr/pgsql-<version>/bin/psql -p$NEW_PORT -f pg_roles.sql postgres %%
~1) Create schema in pvsadmin. %% /usr/pgsql-<version>/bin/psql -f pvsadmin_slon_ready.sql -Uslon pvsadmin %%
~1) Test connections between servers
~1) Install slony version matching master and pg version
~1) Copy slon-<cluster_name>.conf from another server to /etc/slony1-<version>/ on the new slave and make any necessy changes
~1) Copy init script or systemd unit file from similar slave or from default startup script. name it according to cluster(e.g. /etc/init.d/slony1-93-admin) and make any necessary edits(e.g. point to new conf, separate pid file, etc)
~1) Start slony service on slave %% /etc/init.d/slony1-93-<cluster_name> start %%
~1) Make sure preamble.<cluster> is updated with new connection information. Add new slave using slonik scripts. ""<br><br>"" Currently examples can be found in /var/lib/pgsql/slon/scripts/ and should utilize STORE NODE, STORE PATH, and SUBSCRIBE SET.""<br><br>""Make sure to check against _<cluster_name>_cluster.sl_node and sl_status tables, as well as logs and pg_activity to see if replication has begun.
~1) Populate non-replicated tables that are used by website %%/usr/pgsql-<version>/bin/pg_dump -Uslon -h$MASTER_HOST -p$MASTER_PORT -t county -t ctystate -t zip2district -t zip2redistrict -t addr2zip -t mv_rating_party_average -t leadership_state_office -t npatoptiongroup -t category_released -t electionoffice -a pvsadmin | /usr/pgsql-<version>/bin/psql -1 -Uslon pvsadmin%% ""<br><br>"" There are likely more non-replicated tables. You can use the following query to find them. Django tables should not be transferred(TODO: django replication). %%SELECT pg_class.OID::regclass as table_name FROM pg_class JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.OID WHERE relkind = 'r' AND pg_class.OID NOT IN (SELECT tab_reloid FROM _admin_cluster.sl_table) AND pg_namespace.nspname NOT IN ('information_schema', 'pg_catalog', '_admin_cluster')%%
~1) Populate any non-replicated admin tables, if admin will be using this database %%/usr/pgsql-<version>/bin/pg_dump -Uslon -h$MASTER_HOST -t role -t user -t user_role -a pvsadmin | /usr/pgsql-<version>/bin/psql -1 -p$NEW_PORT pvsadmin
/usr/pgsql-<version>/bin/pg_dump -Uslon -h$MASTER_HOST -t contactoutcome -t contacttype -t contactmode -t contact_candidate -a pvsadmin | /usr/pgsql-<version>/bin/psql -1 $NEW_PORT pvsadmin%%
~1) Create indexes%%/usr/pgsql-<version>/bin/psql -Uslon -p$NEW_PORT -f pvsadmin_index.sql pvsadmin %%
~1) ANALYZE to update statistics %%/usr/pgsql-<version>/bin/psql -Uslon -p$NEW_PORT -c 'ANALYZE' pvsadmin %%

===Setting Up a Master===

Setting up a slave to take over for a master is pretty much the same, but keep the following in mind.

- Non-slony triggers need to be moved after master has been changed
- Sequences will need to be updated to reflect current values

----
CategoryITMisc