Перейти к содержанию

Database - Postgresql BDR

Материал из Solipse Wiki
Версия от 11:19, 19 апреля 2016; Glorin (обсуждение | вклад)
(разн.) ← Предыдущая версия | Текущая версия (разн.) | Следующая версия → (разн.)

postgresql bdr

echo "deb http://packages.2ndquadrant.com/bdr/apt/ jessie-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