I have set up a 2-node PG 9.4 system with BDR. Replication works great. Now, let's suppose that one of the two nodes needs to be replaced. In fact, let's be more specific. Let's say that the node on which I ran the command to create the group : SELECT bdr.bdr_group_create() has to be replaced. What is the procedure to do this? Thanks

I previously used the following to install BDR (9.4) on Ubuntu: echo "deb http://packages.2ndquadrant.com/bdr/apt/ trusty-2ndquadrant main" > "/etc/apt/sources.list.d/2ndquadrant.list" wget --quiet -O - http://packages.2ndquadrant.com/bdr/apt/AA7A6805.asc | sudo apt-key add - echo "deb http:// apt.postgresql.org/pub/repos/apt/ trusty-pgdg main" > "/etc/apt/sources.list.d/pgdg.list" wget --quiet -O - https:// www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - apt-get update apt-get install bdr-9.4 Since the end of May when I make use of this, it only installs normal PostgreSQL. I've tried this on Ubuntu 14.04, 15.10 and 16.04. Any help in this regard would be highly appreciated.

Looks like BDR doesn't support exclusion constraints ... However, I'm using exclusion constraints to achieve the same result as a unique constraint (but using a hash index instead of a B-Tree): alter table my_table add constraint my_table_unique_hash exclude using hash (my_unique_field with =); Can I tell BDR to consider the exclusion constraint only for the master and ignore it in the read replicas?

My understanding, reading between the lines, is that BDR replicates row by row, starting after the COMMIT. I have a requirement to be able to then wait until some nodes have actually replicated my latest commit. If not, I will report the problem upstream. And maybe mark the transaction as dubious on the local db, which will hopefully also get replicated. bdr.bdr_node_slots seems to tell me a pg_lsn of the latest replication of each node. But I need to know the pg_lsn of my last transaction. Knowing the current state of the db now that transaction committed would be close enough. I could only find pg_current_xlog_insert_location(), but it looks different.

I'm trying to build deb packages for postgresql BDR plugin, but I'm getting the following error: gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Werror=format-security -I/usr/include/mit-krb5 -fPIC -pie -DLINUX_OOM_SCORE_ADJ=0 -fno-omit-frame-pointer -fpic -I/usr/include/postgresql -I. -I./ -I/usr/include/postgresql/9.4/server -I/usr/include/postgresql/internal -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/tcl8.6 -c -o pg_resetxlog.o pg_resetxlog.c gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Werror=format-security -I/usr/include/mit-krb5 -fPIC -pie -DLINUX_OOM_SCORE_ADJ=0 -fno-omit-frame-pointer -fpic pg_resetxlog.o -L/usr/lib/x86_64-linux-gnu -Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-z,now -Wl,--as-needed -L/usr/lib/mit-krb5 -L/usr/lib/x86_64-linux-gnu/mit-krb5 -Wl,--as-needed -L/usr/lib/x86_64-linux-gnu -lpgcommon -lpgport -L/usr/lib/x86_64-linux-gnu -lpq -lpgcommon -lpgport -lselinux -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -ledit -lrt -lcrypt -ldl -lm -o bdr_resetxlog pg_resetxlog.o: In function `memcpy': /usr/include/x86_64-linux-gnu/bits/string3.h:51: undefined reference to `pg_crc32_table' pg_resetxlog.o: In function `RewriteControlFile': /var/lib/postgresql/src/bdr-plugin/pg_resetxlog.c:955: undefined reference to `pg_crc32_table' pg_resetxlog.o: In function `PrintControlValues': /var/lib/postgresql/src/bdr-plugin/pg_resetxlog.c:749: undefined reference to `pg_crc32_table' collect2: error: ld returned 1 exit status make: *** [bdr_resetxlog] Error 1 To build this deb package I'm using the following commands: cd ~/src/ git clone -b bdr-plugin/next git://git.postgresql.org/git/2ndquadrant_bdr.git bdr-plugin git clone -b debian/bdr https://github.com/2ndQuadrant/bdr-packaging bdr-packaging cp -rfv bdr-packaging/debian bdr-plugin/debian cd bdr-plugin dpkg-buildpackage -b -rfakeroot -us -uc About the dependencies, all the postgresql-BDR server are installed, and all postgresql looks ok: # dpkg -l | grep postgresq | grep dev ii postgresql-bdr-server-dev-9.4 9.4.5-2trusty amd64 development files for PostgreSQL-BDR 9.4 server-side programming ii postgresql-server-dev-9.1 9.1.21-1.pgdg14.04+1 amd64 development files for PostgreSQL 9.1 server-side programming ii postgresql-server-dev-9.2 9.2.16-1.pgdg14.04+1 amd64 development files for PostgreSQL 9.2 server-side programming ii postgresql-server-dev-9.3 9.3.12-1.pgdg14.04+1 amd64 development files for PostgreSQL 9.3 server-side programming ii postgresql-server-dev-9.5 9.5.2-1.pgdg14.04+1 amd64 development files for PostgreSQL 9.5 server-side programming ii postgresql-server-dev-all 173.pgdg14.04+1 all extension build tool for multiple PostgreSQL versions Any idea why I'm getting this error? Regards

BDR error : when making changes to the postgresql.conf for shared_preload_libraries = ’pglogical,bdr’ ``FATAL: could not access file "pglogical": No such file or directory

also cannot make extension

postgres=# create extension BDR; ERROR: could not open extension control file "/usr/share/postgresql/10/extension/bdr.control": No such file or directory``` Its Postgresql 10 and BDR3

I'm planning to use BDR (by 2ndQuadrant) to setup a multi master cluster for my PostgreSQL Databases. how can i achieve real loadbalancing for read and write Operations? I dont want to do it on the application side. Is it a good idea to combine pgpool and a BDR Cluster, given that pgpool seems to be able to Balance only Select-Queries or is there a better way to distribute load over the whole cluster?

I'm trying to hunt down what causes replication slots to go inactive. Might seem like a remedial question but I'm noticing that on my master database server, I have many inactive replication slots that are taking up some space on the disk. I know I can just drop inactive slots using the pg_drop_replication_slot(slot_name name) command. But I'd also like to be able review how we are doing our work / code to see if we are doing something wrong. If a child server goes offline... from what I've read, it's supposed to just reconnect to the same slot when it comes back up. So not too sure what causes a slot to go inactive. Here's the query I'm running to see what's what: widgets=# SELECT slot_name, database, active, pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) AS retained_bytes widgets-# FROM pg_replication_slots widgets-# WHERE plugin = 'bdr'; slot_name | database | active | retained_bytes --------------------------+--=-------+--------+------------------------ bdr_16385_12345_1_1111__ | widgets | f | 2431628304 bdr_16385_11111_1_1638__ | widgets | f | 2432065824 bdr_16385_22222_1_1638__ | widgets | t | 56 bdr_16385_33333_1_2222__ | widgets | t | 56 bdr_16385_44441_1_1111__ | widgets | f | 2432185336 bdr_16385_55551_1_1111__ | widgets | f | 1133538200 bdr_16385_66661_1_1111__ | widgets | f | 2431950952 bdr_16385_77771_1_1111__ | widgets | f | 2432057320 bdr_16385_88881_1_1111__ | widgets | f | 2331233432 (9 rows) widgets=# Any comments would be appreciated.

Apparently only the new values of rows are sent between nodes on update. So how can there ever be *detectable* conflict? Node A receives an update from Node B, and a different update from node C. How can A know that there was a conflict? That the B and C updates were not conducted in some well defined order? And what does "last-update-wins" mean? Are the updates timestamped? In which case does it rely on synchronized clocks? Or does it just mean whichever update happens to get to Node A last wins? SUMMARY * Last Update Wins means based on commit time (requiring fairly synchronized clocks), and not the time a node sees an update. So should be globally consitant. * There aren't any Update/Update conflicts that are detectable (except constraint violations). And they would be difficult to resolve if they were. * Conflict resolution is row by row, not transaction by transaction. Also see http://sdf.org/~riley/blog/2016/01/04/is-bi-directional-replication-bdr-in-postgres-transactional/

I am implementing a multi tenant web app using postgres. I want to use Row Level Security (RLS ) for data separation. RLS is available from PG version 9.5. However, at the same time, the HA / DR teams plan to use BDR ( Bi directional replication) for the HA solution. It seems that BDR is not yet supported in 9.5. - What can be done in such a scenario ? - Is it possible to get BDR on 9.5 ? - What other replication strategies provide the same benefits as BDR? Yes, schemas is also an approach, but I am already using that for tables which have less data. I want RLS for tables that will eventually get enormous, so replicating them in different schemas wont be ideal.

I'm trying to get postgresql 9.4 and BDR working. Following this tutorial: https://wiki.postgresql.org/wiki/BDR_Quick_Start Actually, my sysadmin provided me with a package to test with. He's taken care of the compilation "stuff". I have two instances running, one on port 5598 and the other on 5599. What I'm noticing is that when I leave the following line in the postgresql.conf file, I can't start the service: # Load BDR shared_preload_libraries = 'bdr' the error message i get is: > FATAL: could not access file "bdr": No such file or directory When I remove the above line, it I can start the service on the specific port needed. According to the BDR docs, if you get that message, you're missing the bdr contrib package: https://wiki.postgresql.org/wiki/BDR_Reference#Could_not_access_file_.22bdr.22:_No_such_file_or_directory But I have it installed. I just don't know how to verify it because I don't exactly know what it does. When i check my package manager to see everything that's been installed, I have the following items listed: postgresql-bdr postgresql-bdr-contrib postgresql-bdr-dbg My apologies in advance for being such a noob. But i'm not a sysadmin, linux is relatively new to me... and it looks like BDR is pretty new too. I'm just trying to collect as much information as possible for my sysadmins to be able to fix whatever is wrong with this package... Thanks.

I am trying to setup 2-node BDR replication and i am getting the following error while running bdr_init_copy. > /usr/pgsql-9.4/bin/bdr_init_copy -d "host=10.10.145.111 > dbname=apimgtdb port=5432" -U postgres > --local-dbname="host=10.10.145.110 dbname=apimgtdb port=5432" -n nodeawswest -D /postgres/pgsql-9.4/data LOG: database system was interrupted; last known up at 2015-06-05 14:49:09 PDT LOG: entering standby mode LOG: starting up replication identifier with ckpt at 9/F5000060 LOG: redo starts at 9/F5000060 LOG: consistent recovery state reached at 9/F5000128 LOG: database system is ready to accept read only connections LOG: started streaming WAL from primary at 9/F6000000 on timeline 1 LOG: recovery stopping at restore point "bdr_6157013497737678047", time 2015-06-05 14:56:57.137408-07 LOG: recovery has paused HINT: Execute pg_xlog_replay_resume() to continue. ERROR: cannot execute DELETE in a read-only transaction STATEMENT: DELETE FROM pg_catalog.pg_shseclabel WHERE provider = 'bdr'; LOG: received smart shutdown request FATAL: terminating walreceiver process due to administrator command LOG: shutting down LOG: database system is shut down

I have the following postgresql 9.4 bdr setup: - upstream server with db called "bdrdemo" running on 10.1.1.1 - downstream server(1) with db called bdrdemo running on 10.2.2.2 (replicates with 10.1.1.1) - downstream server(2) with db called "newname" running on 10.3.3.3 (replicates with 10.1.1.1) When i set up downstream server 2, i purposely used a different database name to test whether database names matter. It looks like all the data from bdrdemo running on 10.1.1.1 copied over properly, but when I make new changes from the upstream, or from the downstream2, nothing is replicated between the two. I see an error on in the logs on the upstream server that says: > Mar 30 19:44:38 testbox postgres: [339-1] d= p=2745 a=FATAL: 3D000: database "newname" does not exist **What I've checked so far:** 1. I checked the bdr.bdr_nodes table and it shows 3 entries now instead of the two before i created the new downstream server. select * from bdr.bdr_nodes node_sysid | node_timeline | node_dboid | node_status ---------------------+---------------+------------+------------- 6127254639323810674 | 1 | 16385 | r 6127254604756301413 | 1 | 16384 | r 6132048976759969713 | 1 | 16385 | r (3 rows) bdrdemo=# 2. the postgresql.conf file on the upstream server has the following settings: #------------------------------------------- # BDR connection configuration for upstream #------------------------------------------- bdr.connections = 'bdrdownstream,bdrdownstream2' bdr.bdrdownstream_dsn = 'dbname=bdrdemo host=10.2.2.2 user=postgres port=5432' bdr.bdrdownstream2_dsn='dbname=newname host=10.3.3.3 user=postgres port=5432' **Edit 1** Downstream server 1's configuration (this server/node is working) # BDR connection configuration for upstream node. #------------------------------------------- bdr.connections = 'bdrupstream' bdr.bdrupstream_dsn = 'dbname=bdrdemo host=10.1.1.1 user=postgres port=5432' bdr.bdrupstream_init_replica = on bdr.bdrupstream_replica_local_dsn = 'dbname=bdrdemo user=postgres port=5432' Downstream server 2's configuration (this server/node is NOT working) # BDR connection configuration for upstream node. #------------------------------------------------- bdr.connections = 'bdrupstream' bdr.bdrupstream_dsn = 'dbname=bdrdemo host=10.1.1.1 user=postgres port=5432' bdr.bdrupstream_init_replica = on bdr.bdrupstream_replica_local_dsn = 'dbname=newname user=postgres port=5432' **EDIT 2** After adding the local database name to downstream 2's confguration, I restarted the database on downstream 2. Replication was not working. So I restarted the upstream server. Still a no go. Then I checked the logs on the downstream 2 and I see this: d=newname p=16791 a=pg_restore NOTICE: 42710: extension "btree_gist" already exists, skipping d=newname p=16791 a=pg_restore LOCATION: CreateExtension, extension.c:1208 d=newname p=16791 a=pg_restore NOTICE: 42710: extension "bdr" already exists, skipping d=newname p=16791 a=pg_restore LOCATION: CreateExtension, extension.c:1208 d=newname p=16791 a=pg_restore NOTICE: 42710: extension "plpgsql" already exists, skipping d=newname p=16791 a=pg_restore LOCATION: CreateExtension, extension.c:1208 d=newname p=16791 a=pg_restore ERROR: 42P07: relation "newtable" already exists d=newname p=16791 a=pg_restore LOCATION: heap_create_with_catalog, heap.c:1056 d=newname p=16791 a=pg_restore STATEMENT: CREATE TABLE newtable ( id integer NOT NULL, fname character varying(60), lname character varying(60) ); pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 191; 1259 17130 TABLE newtable postgres pg_restore: [archiver (db)] could not execute query: ERROR: relation "newtable" already exists Command was: CREATE TABLE newtable ( id integer NOT NULL, fname character varying(60), lname character varying(60) ); pg_restore to dbname=newname user=postgres port=5432 fallback_application_name='bdr (6132048976759969713,1,16384,): bdrupstream: init_replica restore' options='-c bdr.do_not_replicate=on -c bdr.permit_unsafe_ddl_commands=on -c bdr.skip_ddl_replication=on -c bdr.skip_ddl_locking=on' failed, aborting d= p=16780 a=FATAL: XX000: bdr: /usr/bin/bdr_initial_load exited with exit code 2 When i initially set up downstream2, it did copy over all the data from upstream, but it just wasn't participating in the replication of new data / new changes. So I guess I can understand why it's failing while trying to create objects that already exist. But do I have to delete the data within the subscriber database and restart to get the replication working?

I have installed the new BDR-Solution with PostgreSQL 9.4 and it works like a charm. **My Problem:** I want to add more databases to the replication but every time if I added the other database and restarted the servers one server is crashing. pg_hba.conf and postgresql.conf are correct. So I read on [this site](https://wiki.postgresql.org/wiki/BDR_User_Guide) that > "You can replicate just one database from a server" But on [this site](https://wiki.postgresql.org/wiki/BDR_Comparisons) it sounds like an advantage: > "You must explicitly configure each database you want to replicate." > and "BDR replicates per-database, not per-cluster. So you don't have > to split your databases up into different PostgreSQL instances just to > control how they replicate." this sounds to me like I *could* vsync more databases if I want...