2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Craig's PlanetPostgreSQL3 / Dynamic SQL-level configuration for BDR 0.9.0
craig.ringer

Dynamic SQL-level configuration for BDR 0.9.0

March 20, 2015/1 Comment/in Craig's PlanetPostgreSQL /by craig.ringer

The BDR team has recently introduced support for dynamically adding new nodes to a BDR group from SQL into the current development builds. Now no configuration file changes are required to add nodes and there’s no need to restart the existing or newly joining nodes.

This change does not appear in the current 0.8.0 stable release; it’ll land in 0.9.0 when that’s released, and can be found in the bdr-plugin/next branch in the mean time.

New nodes negotiate with the existing nodes for permission to join. Soon they’ll be able to the group without disrupting any DDL locking, global sequence voting, etc.

There’s also an easy node removal process so you don’t need to modify internal catalog tables and manually remove slots to drop a node anymore.

New node join process

With this change, the long-standing GUC-based configuration for BDR has been removed. bdr.connections no longer exists and you no longer configure connections with bdr.[conname]_dsn etc.

Instead, node addition is accomplished with the bdr.bdr_group_join(...) function. Because this is a function in the bdr extension, you must first CREATE EXTENSION bdr;. PostgreSQL doesn’t have extension dependencies and the bdr extension requires the btree_gist extension so you’ll have to CREATE EXTENSION btree_gist first.

Creating the first node

Creation of the first node must now be done explicitly using bdr.bdr_group_create. This promotes a standalone PostgreSQL database to a single-node BDR group, allowing other nodes to then be joined to it.

You must pass a node name and a valid externally-reachable connection string for the dsn parameter, e.g.:

CREATE EXTENSION btree_gist;

CREATE EXTENSION bdr;

SELECT bdr.bdr_group_join(
  local_node_name = 'node1',
  node_external_dsn := 'host=node1 dbname=mydb'
);

Note that the dsn is not used by the root node its self. It’s used by other nodes to connect to the root node, so you can’t use a dsn like host=localhost dbname=mydb if you intend to have nodes on multiple machines.

Adding other nodes

You can now join other nodes to form a fully functional BDR group by calling bdr.bdr_group_join and specifying a connection string that points to an existing node for the join_using_dsn. e.g.:

CREATE EXTENSION btree_gist;

CREATE EXTENSION bdr;

SELECT bdr.bdr_node_join(
    local_node_name := 'node2',
    node_external_dsn := 'host=node2 dbname=mydb',
    join_using_dsn := 'host=node1 dbname=mydb'
);

Here, node_external_dsn is an externally reachable connection string that can be used to establish connection to the new node, just like you supplied for the root node.

The join_using_dsn specifies the node that this new node should connect to when joining the group and establishing its membership. It won’t be used after joining.

Waiting until a node is ready

It’s now possible to tell when a new node has finished joining by calling bdr.node_join_wait(). This function blocks until the local node reports that it’s successfully joined a BDR group and is ready to execute commands.

Database name “bdr” now reserved

Additionally, the database name bdr is now reserved. It may not be used for BDR nodes, as BDR requires it for internal management. Hopefully this requirement can be removed later once a patch to the BGWorkers API has been applied to core.

Documentation moving into the source tree

The documentation on the PostgreSQL wiki sections for BDR is being converted into the same format as is used for PostgreSQL its self. It’s being added to the BDR extension source tree and will be available as part of the 0.9.0 release.

Trying it out

If you’d like to test out bdr-plugin/next, which is due to become BDR 0.9.0, take a look at the source install instructions and the quick-start guide.

There are no packages for BDR 0.9.0 yet, so if you try to install from packages you’ll just get 0.8.0.

Comments? Questions

Please feel free to leave comments and questions here, or post to pgsql-general with BDR-related questions.

We’re also now using GitHub to host a mirror of the BDR repository. We’re using the issue tracker there, so if you’ve found a bug and can supply a detailed report with the exact version and steps to reproduce, please file it there.

Tags: BDR, Postgres-BDR, PostgreSQL, release
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
1 reply
  1. jtorral
    jtorral says:
    June 16, 2015 at 8:29 pm

    A much too simple question but I need to ask it.

    Does one need to create the extension and a group for node1 in every database you wish to replicate? For example ..

    Server A and B

    Create database mydb
    create extensions
    create group (local_node_name := ‘node1’ , node_external_dsn := ‘host=hosta dbname=mydb’)

    Then on Server B
    Create database mydb
    create extensions
    group_join( local_node_name := ‘node2’, node_external_dsn := ‘host=hostb port=5432 dbname=mydb’,
    join_using_dsn := ‘host=hosta port=5432 dbname=mydb’ )

    The above would replicate database mydb

    If I also wanted to replicated myotherdb would I go through the exact same steps above and just change
    the dsn to point to myotherdb

    Create database myotherdb
    create extensions
    create group (local_node_name := ‘node1’ , node_external_dsn := ‘host=hosta dbname=myotherdb’)

    Then on Server B
    Create database myotherdb
    create extensions
    group_join( local_node_name := ‘node2’, node_external_dsn := ‘host=hostb port=5432 dbname=myotherdb’,
    join_using_dsn := ‘host=hosta port=5432 dbname=myotherdb’ )

    or would I modify the existing group created for mydb and somehow add myotherdb to it in addition to mydb ?

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Automating Barman with Puppet: it2ndq/barman (part one) Automating Barman with Puppet: it2ndq/barman (part two)
Scroll to top
×