Postgresql bdr 0.8.x – adding another downstream server only partially works

multi-masterpostgresqlpostgresql-9.4postgresql-bdrreplication

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[2745]: [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?

Best Answer

This information applies to BDR 0.8 and older; in BDR 0.9 the configuration method has changed.

If the local database name is not the same as the database name specified in the _dsn parameter for a node's connection to its upstream, you must specify it with the _local_dbname connection option.

See the wiki entry I just added for this parameter, which wasn't previously in the docs.

In your case, on downstream 2, add:

bdr.bdrupstream_local_dbname = 'newname'
Related Question