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
There are two ways to add a BDR node:
- By physical clone; or - By logical copy
A physical clone 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 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.
Whichever you use, remember to add bdr.bdr_connections entries for the new node on all other nodes and restart all other nodes after you've bought up the node and before you start writing to it.
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
Discuss init_replica, initdb vs CREATE DATABASE on an existing server.
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.
Then you must CREATE DATABASE thedbname TEMPLATE template0 to make the target database for BDR. It is important to use the empty template, template0.
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.
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.