Install BDR on FreeBSD

bdr-freebsd.md

BDR Core (currently a fork from postgres project)

Before start, run:

mkdir -p /opt/resources
cd /opt/resources

Clone the BDR source, using the latest branch:

git clone -b 'bdr-pg/REL9_4_6-1' git://git.postgresql.org/git/2ndquadrant_bdr.git postgresql-bdr

Acesss the new directory and change to the new branch:

git checkout 'bdr-pg/REL9_4_6-1'

Run de ./configure script and install:

./configure --with-libraries=/usr/local/lib --with-includes=/usr/local/include --enable-thread-safety --disable-debug --without-pam --with-openssl --without-gssapi --prefix=/usr/local --localstatedir=/var --mandir=/usr/local/man --infodir=/usr/local/info/ --build=amd64-portbld-freebsd10.2 build_alias=amd64-portbld-freebsd10.2 CC=cc CFLAGS="-O2 -pipe  -fstack-protector -fno-strict-aliasing" LDFLAGS=" -L/usr/local/lib -lpthread -Wl,-rpath,/usr/local/lib -fstack-protector" CPP=cpp
gmake -j 4 install
cd contrib
gmake -j 4 install

BDR Plugin

Before start, run:

cd /opt/resources

Clone BDR git repository, using de latest plugin branch:

git clone -b 'bdr-plugin/0.9.3-2' git://git.postgresql.org/git/2ndquadrant_bdr.git bdr-plugin

Acesss the new directory and change to the new branch:

cd bdr-plugin
git checkout 'bdr-plugin/0.9.3-2'

Run the ./configure script and install:

./configure
gmake install

Tested on FreeBSD 10.2.

configure-bdr.md

BDR Configuration

Before start, install the BDR fork (the branches bdr-pg/REL9_4_6-1 and bdr-plugin/0.9.3-2) in both servers.

Adjust the postgresql.conf parameters bellow:

# BDR dependencies
shared_preload_libraries = 'bdr'
wal_level = 'logical'
track_commit_timestamp = on
max_wal_senders = 10
max_replication_slots = 10
# Make sure there are enough background worker slots for BDR to run
max_worker_processes = 10

wal_keep_segments = 2500 # ~40gb

# Useful options for playing with conflicts
#bdr.default_apply_delay=2000   # milliseconds
bdr.log_conflicts_to_table=on
bdr.synchronous_commit = off

# These aren't required, but are useful for diagnosing problems
log_error_verbosity = verbose
#log_min_messages = debug1
#log_line_prefix = 'd=%d p=%p a=%a%q '

Create the targetdb database, create the replication user and create the required extensions on both PostgreSQL hosts:

CREATE DATABASE targetdb;
CREATE ROLE replica LOGIN REPLICATION SUPERUSER PASSWORD 'testpassword';
\c targetdb
CREATE EXTENSION btree_gist;
CREATE EXTENSION bdr;

On node1 host, create a replication group:

SELECT bdr.bdr_group_create(
  local_node_name := 'node1',
  node_external_dsn := 'hostaddr=192.168.10.60 port=5432 dbname=targetdb user=replica password=testpassword'
);

Then run on node1 host:

SELECT bdr.bdr_node_join_wait_for_ready();

On node2 host, run:

SELECT bdr.bdr_group_join(
  local_node_name := 'node2',
  node_external_dsn := 'hostaddr=192.168.10.61 port=5432 dbname=targetdb user=replica password=testpassword',
  join_using_dsn := 'hostaddr=192.168.10.60 port=5432 dbname=targetdb user=replica password=testpassword'
);
SELECT bdr.bdr_node_join_wait_for_ready();

working-bdr.md

BDR cheat cheet

Resync a node

Remove (or move) a old PGDATA directory, then make a copy with bdr_init_copy:

/usr/local/bin/bdr_init_copy \
-D data/ \
--node-name=node61 \
--remote-dbname='hostaddr=192.168.10.60 port=5432 dbname=targetdb user=replica password=testpassword' \
--local-dbname='hostaddr=192.168.10.61 port=5432 dbname=targetdb user=replica password=testpassword' \
-v

Remove a node:

On psql, using de targetdb database run:

SELECT bdr.bdr_part_by_node_names(ARRAY['node2']);

When you remove a node you MUST recreate a target database. Details here.

Completely remove bdr from a database

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 mydb 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;

Referenced here.

Node status

Value	Description
R	Ready
K	Removed/Killed
I	Initializing
B	Bootstraping
C	Catching up
O	Waiting for the slots creation

More in Documentation

Useful queries

Remove all unactive replication slots

DO $$
DECLARE

	slot RECORD;
BEGIN
	FOR slot in 
		SELECT
			slot_name
		FROM pg_replication_slots
		WHERE active IS FALSE
	LOOP

		RAISE NOTICE 'Removing % slot... ', slot.slot_name;

		PERFORM pg_drop_replication_slot(slot.slot_name);
	END LOOP;
END;
$$;

Show WAL keep usage

    SELECT
      slot_name, database, active,
      pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) AS retained_bytes
    FROM pg_replication_slots
    WHERE plugin = 'bdr';

i made a github account just to say this article was super useful to me. thankyou.