Database - Postgresql BDR
Внешний вид
postgresql bdr
echo "deb http://packages.2ndquadrant.com/bdr/apt/ wheezy-2ndquadrant main" > /etc/apt/sources.list.d/2ndquadrant.list wget --quiet -O - http://packages.2ndquadrant.com/bdr/apt/AA7A6805.asc | apt-key add - apt-get update apt-get install postgresql-bdr-9.4-bdr-plugin
Edit the postgresql.conf
shared_preload_libraries = 'bdr' wal_level = 'logical' track_commit_timestamp = on max_connections = 100 max_wal_senders = 20 max_replication_slots = 20 # Make sure there are enough background worker slots for BDR to run max_worker_processes = 20 # These aren't required, but are useful for diagnosing problems log_error_verbosity = verbose log_min_messages = debug1 log_line_prefix = '%m d=%d p=%p a=%a%q ' # Useful options for playing with conflicts #bdr.default_apply_delay=2000 # milliseconds #bdr.log_conflicts_to_table=on
Edit pg_hba.conf, grant access from remote host to current node (need make for each host and on each host)
hostssl database_name postgres remotehost/32 trust
hostssl replication postgres remotehost/32 trust
Edit hosts /etc/hosts and add host for local_node_name and node_external_dsn host
Enable Extensions
su - postgres psql database_name
CREATE EXTENSION btree_gist; CREATE EXTENSION bdr;
Create BDR Group
SELECT bdr.bdr_group_create(
local_node_name := 'xen1',
node_external_dsn := 'host=xen1 dbname=database_name'
);
Join BDR Group
SELECT bdr.bdr_group_join(
local_node_name := 'xen2',
node_external_dsn := 'host=xen2 dbname=database_name',
join_using_dsn := 'host=xen1 dbname=database_name'
);
Sequences
BEGIN; SET LOCAL default_sequenceam = 'bdr'; CREATE TABLE example ( id serial primary key, text text ); COMMIT;
Disable BDR
BEGIN; SET LOCAL bdr.skip_ddl_locking = on; SET LOCAL bdr.permit_unsafe_ddl_commands = on; SET LOCAL bdr.skip_ddl_replication = on; SECURITY LABEL FOR bdr ON DATABASE !!!!DATABASENAME!!!! IS NULL; DELETE FROM bdr.bdr_connections; DELETE FROM bdr.bdr_nodes; SELECT bdr.bdr_connections_changed(); COMMIT; SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = current_database() AND application_name LIKE '%): perdb'; DROP EXTENSION bdr CASCADE; select pg_drop_replication_slot(slot_name) FROM pg_replication_slots ;
Enable extensions like POSTGis
For each database !!!!!!!!!!!!!!
set bdr.skip_ddl_replication = on; set bdr.permit_unsafe_commands = on; set bdr.skip_ddl_locking=on; CREATE EXTENSION postgis; CREATE EXTENSION fuzzystrmatch; CREATE EXTENSION pg_trgm; CREATE EXTENSION unaccent; set bdr.skip_ddl_replication = off; set bdr.permit_unsafe_commands = off; set bdr.skip_ddl_locking=off;
restart all postgresql