Slony
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.
- Configure postgres
- 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
- 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';
- 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
- Move CREATE INDEX statements to other file for later creation.
grep 'CREATE INDEX' pvsadmin_slon_ready.sql | sed 's/--//' > pvsadmin_index.sql
- Grab script for roles.
pg_dumpall -r --file=./pg_roles.sql -h$MASTER_HOST -p$MASTER_PORT -Uslon
- Create database pvsadmin with UTF8 encoding.
/usr/pgsql-<version>/bin/createdb -p$NEW_PORT -EUTF8 pvsadmin
- Create roles.
/usr/pgsql-<version>/bin/psql -p$NEW_PORT -f pg_roles.sql postgres
- Create schema in pvsadmin.
/usr/pgsql-<version>/bin/psql -f pvsadmin_slon_ready.sql -Uslon pvsadmin
- Test connections between servers
- Install slony version matching master and pg version
- Copy slon-<cluster_name>.conf from another server to /etc/slony1-<version>/ on the new slave and make any necessy changes
- 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)
- Start slony service on slave
/etc/init.d/slony1-93-<cluster_name> start
- Make sure preamble.<cluster> is updated with new connection information. Add new slave using slonik scripts.
Currently examples can be found in /var/lib/pgsql/slon/scripts/ and should utilize STORE NODE, STORE PATH, and SUBSCRIBE SET.
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. - 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
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') - 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
- Create indexes
/usr/pgsql-<version>/bin/psql -Uslon -p$NEW_PORT -f pvsadmin_index.sql pvsadmin
- 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
