2

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?

5
  • You might be better off trying the 0.9.0 release of BDR. The node configuration has been rewritten and has a lot more sanity checks and tests. See blog.2ndquadrant.com/… and 2ndquadrant.com/BDR . We do routinely test with different database names, and in fact the regression tests rely on that to work. – Craig Ringer Mar 31 '15 at 3:42
  • Please show the configs for the other two servers, and the exact BDR version. – Craig Ringer Mar 31 '15 at 3:44
  • @CraigRinger please see my EDIT 1 for the config details you've requested. BTW. I'm also looking into updating to latest version. In interim, if you have any suggestions on what I might have done wrong, please advise! Thank you!! – Happydevdays Mar 31 '15 at 13:55
  • Ah. You need to set the local dbname property if the local database name differs from the database name being connected to. All this has been redone in 0.9 mind. – Craig Ringer Mar 31 '15 at 14:14
  • @CraigRinger can you post as an answer with an example of how to do that? Thanks – Happydevdays Mar 31 '15 at 14:37
1

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'
6
  • I added the entry to the config and restarted downstream 2. I changed a record in the database on downstream 2 but its not replicated over. So I checked the logs and I see and error on downstream 2. Please see edit 2 – Happydevdays Apr 1 '15 at 12:12
  • @Happydevdays Because the restore succeeded but it didn't get to the next step, you'll probably want to (a) on upstream1 and on downstream1, SELECT pg_drop_replication_slot('the_slot_id_of_downstream2');; (b) select pg_drop_replication_slot('...'); all slots on downstream2; (c) DROP DATABASE newname; on downstream2; (d) create database newname; on downstream2; (e) Add downstream2 again with the correct config. Again... all this is way easier in 0.9, and a lot of sanity checks were added, as well as useful info functions to get slot IDs etc. Why persist with 0.8? – Craig Ringer Apr 1 '15 at 13:19
  • i wanted to finish this prototype on 0.8 to prove it works. but it seems very involved. Hopefully 0.9 simplifies all this. i was just trying to demo how "easy" it is to bring up a new node and have it "just work". – Happydevdays Apr 1 '15 at 14:29
  • @Happydevdays Heh. Improving that was the main goal of the 0.9 release. Because we knew configuration was fragile, difficult and hard-to-debug. It will be way easier to just make the jump. If you hit issues with 0.9 please open a report on github.com/2ndQuadrant/bdr/issues, post here with the postgresql-bdr tag or post on pgsql-general. – Craig Ringer Apr 1 '15 at 14:33
  • thanks for all the support! BDR seems to be pretty new and I was worried that support would be lacking. But you have been very helpful / responsive. Thanks. – Happydevdays Apr 1 '15 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.