From PostgreSQL wiki
BDR administration tasks
Tasks a BDR admin might need to do.
This section of the BDR documentation is in ongoing development and has incomplete portions. Please feel free to contribute.
To install BDR from sources you need to check out the sources, then and install PostgreSQL like normal. The only difference from a normal PostgreSQL install is that you'll need to install the contrib/btree_gist and contrib/bdr extensions.
Assuming that you want to install it in your home directory for testing purposes, you would install the development tools for your operating system/distro, then run:
- git clone git://git.postgresql.org/git/2ndquadrant_bdr.git
- cd 2ndquadrant_bdr
- git checkout bdr/0.6
- ./configure --prefix=$HOME/bdr
- make install
To install the BDR extension and all the other "contrib" modules, you'd then run:
- (cd contrib/btree_gist && make && make install)
- (cd contrib/bdr && make && make install)
or to instead install all contribs (useful for widely used extensions things like hstore) instead just:
- (cd contrib && make all)
- (cd contrib && make install)
Adjusting your environment
Once you have BDR installed you will want to make sure that the BDR versions of PostgreSQL's binaries like pg_ctl, postgres and initdb are on your PATH.
You can do this per-command by writing (eg):
PATH=/path/to/bdr/bin:$PATH pg_ctl ...
but it's generally more convenient to add the path to your .bash_profile. BDR's psql is unchanged from that in regular 9.4, as is the network protocol, so there won't be compatibility issues.
Just edit $HOME/.bash_profile and append:
Replace /path/to/bdr with the path you specified as --prefix to configure when you compiled BDR.
To test, close and re-open your terminal or run bash -l then run:
and it'll print:
psql (PostgreSQL) 9.4_bdr0601
or similar if you're using a version with the BDR patches.
Creating the first node
You can make your first BDR node by creating a new data directory (a new PostgreSQL "cluster") or upgrading an existing 9.4 install for use with BDR.
New data directory
On RPM distros like RHEL/CentOS/Fedora you will need to run service postgresql-9.4bdr initdb to actually create the data directory. Your data directory will be /var/lib/pgsql/9.4bdr/data. You can skip to the next step after running that command.
On Ubuntu/Debian your data directory will be in /var/lib/postgresql/9.4bdr/main/data (Ubuntu, Debian). It has already been created for you so you can skip to the next step.
If you installed from source you need to initialize a new PostgreSQL data directory yourself. Run:
to check that it prints a version with "bdr" in it. If it doesn't, re-check the steps in adjusting your environment, above.
initdb -D $HOME/bdr-data -U postgres --auth-host=md5 --auth-local=peer
which will make the data directory.
Upgrading a 9.4 data directory
BDR will come with scripts to upgrade a 9.4 data directory to 9.4bdr or convert a 9.4bdr data directory back to plain unpatched 9.4.
Adding a node
Bringing up a new node involves:
- Doing preliminary setup to ensure the new node will be able to connect to existing nodes
- Configuring BDR on the new node and initializing the new node with data
- Adding connections from all existing nodes to the new node
- Restarting all existing nodes so they replicate from the new node
Ensuring the new node will be able to connect
The new node needs to be able to talk to all the existing nodes. This may require changes to their configuration.
Add pg_hba.conf entries for the new node
The new node will have to be able to connect to all the other nodes. So make sure, before you add the new node, that appropriate pg_hba.conf entries on all current nodes exist to permit the new node to exist.
A hostssl entry for the bdr target database(s) with an unrestricted 0.0.0.0/0 IP/CIDR filter can be a good idea when combined with a strong md5 password and/or client certificate authentication, so you don't have to change each node's pg_hba.conf for every new node addition or IP change.
Remember that you need an entry that matches the database, and another with the pseudo-database replication.
Example, for if you're using the user postgres as the BDR user:
hostssl all postgres 0.0.0.0/0 md5 hostssl replication postgres 0.0.0.0/0 md5
(In production, consider using a dedicated user that isn't a superuser for replication; see the security concerns section).
Add firewall rules
If there's a firewall in place on the existing nodes, make sure the new node can talk to them.
Set up .pgpass
If you're using md5 auth, you'll want a .pgpass file in the home directory of the user you're running BDR as. That's usually the postgres user.
The PostgreSQL server will read this file when making connections to other PostgreSQL servers using BDR.
Remember that .pgpass must be mode 0600.
It can be convenient to keep a single .pgpass that you just copy from node to node. Leave the host matching part as a wildcard so you don't have to update it for new nodes.
Collect DSNs (connstrings)
You now need to make a list of connection strings that'll let your new node connect to the exisiting nodes. For most setups you can just copy them from an existing node, then add a new one for the node you copied it from.
An example real-world connection setup from a recent deployment (with some details redacted) is
bdr.connections = 'awsbarman' bdr.awsbarman_dsn = 'dbname=thedb host=the-target-hostname user=bdr port=5433 sslmode=require'
You should test these DSNs with psql as the user BDR will run as. In this case, if BDR was running under the local user postgres:
sudo -u postgres -i psql 'dbname=thedb host=the-target-hostname user=bdr port=5433 sslmode=require'
The -i to sudo is important; it ensures that $HOME etc is set correctly, so the right .pgpass is found.
Populate the node data
Once initial setup is done you need to populate the new node with data from an existing node. There are two ways to do this:
- By physical clone; or
- By logical copy
A physical clone (based on pg_basebackup) copies the whole PostgreSQL instance with all databases on it. It's useful if you're copying an install with multiple databases involved in BDR or an install with nothing else on it. Physical clone requires a pg_basebackup to be run first, ensuring that all database state including current users etc are copied.
Logical clone (based on pg_dump and pg_restore) copies only a single database. It does not copy global state like users, etc. It doesn't have to copy table bloat, indexes, WAL records, etc, but has the additional overhead of applying a dump on the local host.
Both can be good choices, depending on your needs, much like you might use either pg_basebackup or pg_dump + pg_restore for backups.
Using a physical clone
The pg_basebackup utility can be used to create initial copy of the cluster from one of the existing nodes. Use pg_basebackup -X stream so you don't need to configure WAL archiving.
After the initial copy has been created, the postgresql.conf needs to be updated for current node (see Parameter Reference and Bi-Directional Replication Use Cases for more info). The important part is to set bdr.<connection_name>_init_replica to true for the connection(s) pointing to the cluster which was used as origin for pg_basebackup, and adjust all the bdr.bdr_connections entries to be appropriate for the new node.
The bdr_init_copy utility which is provided as part of BDR has to be used to start the node for the first time - it will bring the node to consistent state with rest of the nodes.
Using a logical copy
To make a logical copy you need to initdb a new PostgreSQL instance if you don't have one already running that you intend to use.
This is just a normal initdb like with any other PostgreSQL install.
You can skip this step if you have an existing local PostgreSQL instance and want to add a new database that will be cloned from an existing remote BDR node.
Copy global state (users/groups/roles etc)
Now dump and restore any global state you need from other instances. BDR logical copy does not clone users/groups/roles, foreign servers, and other global state from the target node, so you must copy this yourself. It is generally useful to do a pg_dumpall --globals-only on the origin node and pipe the result to psql on the local node, with something like:
pg_dumpall --globals-only "host=origin.host.name user=postgres" | psql
Create a new empty database
Then you must CREATE DATABASE thedbname TEMPLATE template0 to make the target database for BDR. It is important to use the empty template, template0.
Configure the new database for BDR
Once the database exists, add BDR configuration entries for the bdr.bdr_connections entries for all the other nodes it must communicate with, and set bdr.nodename_init_replica=on for one of them. This entry must also have bdr.nodename_local_replica_dsn = '...superuser localhost dsn ...', i.e. a DSN that gets a superuser connection to the local target DB. For more details on these options see the BDR Parameter Reference.
An example from a real world deployment, with some redaction, is:
bdr.connections = 'thedb' # Password is in the .pgpass file bdr.thedb_dsn = 'host=the-target-server dbname=thedb user=bdr port=5433 sslmode=require' bdr.thedb_init_replica = on bdr.thedb_replica_local_dsn = 'dbname=thedb user=bdr port=5433'
Tip: Test the DSNs
Note that you should test the dsn given in replica_local_dsn and set any required pg_hba.conf entries. Test with psql running as the same user BDR runs as, e.g.:
psql 'dbname=thedb user=bdr port=5433'
Adding connections to the new node
Once the new node is running, you need to add connection entries to all other nodes to point to it.
Remember to both add them to bdr.bdr_connections on each node *and* add the new bdr.conname_dsn entry for it to each node.
Removing a node
The process to remove a node is:
- Remove mention of the node from all configuration files
- Stop all nodes
- Start all nodes (note this is not the same thing as restarting all nodes)
- Delete the node record from bdr_nodes
- Drop all slots that point to the removed node
Syncing all nodes
Finding a lagging node
If a node doesn't keep up it can cause slots to fall behind and pg_xlog on other nodes to fill. Monitoring by admin should prevent this.
Dealing with full pg_xlog
If a slot doesn't progress, pg_xlog can fill up. Admin must deal with this problem.
BDR backups are no different to regular PostgreSQL - you can use pg_dump, pg_basebackup, pg_start_backup/rsync/pg_stop_backup with archiving, a file system level atomic snapshot, PgBarman, or whatever suits you.