BDR Quick Start
From PostgreSQL wiki
This guide discusses how to quickly configure a test install of Bi-Directional Replication (BDR) for experimentation and learning. These instructions are not suitable for a production install, as they neglect security considerations, proper system administration procedure, etc. If you're trying to set up a production BDR install, see the BDR Admin Guide.
Creating and starting a test BDR install
To set up BDR you'll need to:
- Install a patched copy of PostgreSQL that can support BDR;
- Install the BDR extension;
- initdb a new data directory or upgrade your current one to support BDR;
- Start one or more additional BDR-patched PostgreSQL instances for replication
- Logical senders and receivers on each node
For simplicitly this guide assumes that you want to start a new server instance rather than BDR-enable an existing one. As a further simplification, the instructions show the creation of multiple PostgreSQL instances on a single computer rather than the normal BDR deployment pattern of one instance per computer on multiple computers.
Installing the patched PostgreSQL binaries
BDR is distributed as source code in the 2ndquadrant_bdr repository on git.postgresql.org
git clone git://git.postgresql.org/git/2ndquadrant_bdr.git cd 2ndquadrant_bdr git checkout bdr-pg/REL9_4_STABLE
PostgreSQL 9.3 and below do not support BDR, and 9.4 requires patches, so this guide will not work for you if you are trying to use a normal install of PostgreSQL. (It is expected that PostgreSQL 9.5 will support the BDR extension without additional patches).
Currently BDR is supported on Linux and Mac OS X. It should probably work on other Unix-like operating systems such as FreeBSD but has not yet been tested on these. BDR is not supported on Windows; although there are no fundamental technical barriers to supporting Windows, this has not been a priority of the project.
Installing BDR from RPMs
If you are using a Red Hat derived distro, you will find it easiest to install BDR packages using yum.
Compiling PostgreSQL with BDR
If no packages are available for your distro, or if you'd prefer to compile BDR from source, a script to download and compile BDR is provided for your convenience. For those who prefer to do the install entirely by hand, see installing BDR from source.
If you've installed using RPMs you can skip this step.
To run the script:
curl -s "http://git.postgresql.org/gitweb/?p=2ndquadrant_bdr.git;a=blob_plain;f=contrib/bdr/scripts/bdr_quickstart.sh;hb=refs/heads/bdr-next" | bash
(Now, on an aside, that script could've been almost anything. It's safer to download scripts like that, read them, then run the downloaded copy.)
When it finishes, the script will print:
--------------------------- BDR compiled and installed. Sources at /home/myuser/2ndquadrant_bdr/bdr-src Installed to /home/myuser/2ndquadrant_bdr/bdr Now add it to your PATH: export PATH=/home/myuser/2ndquadrant_bdr/bdr/bin:$PATH and carry on with the quickstart at https://wiki.postgresql.org/wiki/BDR_Quick_Start ---------------------------
Adjusting your environment
To actually use these new binaries you will need to do as the quickstart script suggested and:
or, if you installed from RPMs, run:
This only affects the terminal you ran it in and makes no permanent changes. For how to apply the change permanently see adjusting your environment.
Creating BDR-enabled PostgreSQL instances
Since we're creating new PostgreSQL instances for this example, run:
initdb -D $HOME/2ndquadrant_bdr/bdr5598 -A trust -U postgres
initdb -D $HOME/2ndquadrant_bdr/bdr5599 -A trust -U postgres
to make two new PostgreSQL database server instances ("clusters"). This will work with both source installs and RPM packages.
The -A trust option tells PostgreSQL to turn off user authentication. This should never be used in production, it just keeps this quickstart simpler. Securely configuring BDR is covered in BDR Administration.
Start the server
We'll run the two servers on ports 5598 and 5599 respectively, so they don't conflict with each other or with PostgreSQL's default port 5432.
To start the servers, which won't yet be connected to each other, run:
pg_ctl -l $HOME/2ndquadrant_bdr/bdr5598.log -D $HOME/2ndquadrant_bdr/bdr5598 -o "-p 5598" -w start
pg_ctl -l $HOME/2ndquadrant_bdr/bdr5599.log -D $HOME/2ndquadrant_bdr/bdr5599 -o "-p 5599" -w start
Each server will start up then switch to running in the background, printing:
waiting for server to start.... done server started
If you instead get:
waiting for server to start........ stopped waiting pg_ctl: could not start server Examine the log output.
then take a look at $HOME/2ndquadrant_bdr/bdr5598.log or bdr5599.log (depending on which failed to start) to see what happened. Most likely you already have a server running on the target port or you're repeating a step and the BDR postgres server is already running on that port.
These servers won't start automatically on boot.
Create the databases
Each node needs a database to participate in BDR, so create them now:
createdb -p 5598 -U postgres bdrdemo createdb -p 5599 -U postgres bdrdemo
It is important that you leave the database on the server on port 5599 empty, since we'll be initializing it from the node on port 5598 shortly.
You can create some tables on the node on port 5598 and add some data now, if you like. To connect with psql, use:
psql -p 5598 -U postgres bdrdemo
Enable the BDR extension
You now have a running PostgreSQL server. It behaves like any ordinary PostgreSQL server at this point, but it's time to change that.
Add the following lines to the end of postgresql.conf for both servers ($HOME/2ndquadrant_bdr/bdr5598/postgresql.conf and $HOME/2ndquadrant_bdr/bdr5599/postgresql.conf):
# Generic settings required for BDR #---------------------------------- # Allow two other peer nodes, plus one for init_replica max_replication_slots = 3 # Two peer nodes, plus two slots for pg_basebackup max_wal_senders = 4 # Record data for logical replication wal_level = 'logical' track_commit_timestamp = on # Load BDR shared_preload_libraries = 'bdr' # Make sure there are enough background worker slots for BDR to run max_worker_processes = 10 # These aren't required, but are useful for diagnosing problems #log_error_verbosity = verbose #log_min_messages = debug1 #log_line_prefix = 'd=%d p=%p a=%a%q ' # Useful options for playing with conflicts #bdr.default_apply_delay=2000 # milliseconds #bdr.log_conflicts_to_table=on
This part of the configuration covers the generic settings required to use a two-node BDR configuration. They're discussed in more detail in the BDR Parameter Reference.
Now append this to the end of node 5598's configuration file ($HOME/2ndquadrant_bdr/bdr5598/postgresql.conf) only:
# BDR connection configuration for node 5598 #------------------------------------------- bdr.connections = 'bdr5599' bdr.bdr5599_dsn = 'dbname=bdrdemo user=postgres port=5599'
and this to the end of node 5599's configuration file ($HOME/2ndquadrant_bdr/bdr5599/postgresql.conf) only:
# BDR connection configuration for node 5599 #------------------------------------------- bdr.connections = 'bdr5598' bdr.bdr5598_dsn = 'dbname=bdrdemo user=postgres port=5598' bdr.bdr5598_init_replica = on bdr.bdr5598_replica_local_dsn = 'dbname=bdrdemo user=postgres port=5599'
These parts specify a two-node BDR configuration in which the bdrdemo database on the PostgreSQL instance on port 5598 is replicated to and from the bdrdemo database on the instance on port 5599. Each node has to be told how to connect to the other node(s) in its configuration file.
The init_replica option that appears only on node 5599 means that when BDR first starts on bdr5599, the bdrdemo database's contents are automatically copied from bdr5598 into bdr5599 so that replication can begin from a consistent point. (Explicit configuration of this will be removed in a later release).
Add a pg_hba.conf entry to allow replication
PostgreSQL requires that you explicitly enable replication in the host-based access control file pg_hba.conf. So edit $HOME/2ndquadrant_bdr/bdr5598/pg_hba.conf and add the following lines (or uncomment the ones already there):
local replication postgres trust host replication postgres 127.0.0.1/32 trust host replication postgres ::1/128 trust
... then do the same on bdr5599.
To learn more about this, see the docs on pg_hba.conf.
Restart the servers
Now that you've created the databases to use and configured BDR, it's time to restart the servers so BDR will get turned on. Use the same pg_ctl command, but with restart instead of start:
pg_ctl -l $HOME/2ndquadrant_bdr/bdr5598.log -D $HOME/2ndquadrant_bdr/bdr5598 -o "-p 5598" -w restart
pg_ctl -l $HOME/2ndquadrant_bdr/bdr5599.log -D $HOME/2ndquadrant_bdr/bdr5599 -o "-p 5599" -w restart
(Optional) Check the logs
Take a look at $HOME/2ndquadrant_bdr/bdr5599.log:
You should see a few lines like:
Dumping remote database "dbname=bdrdemo user=postgres port=5598" with 1 concurrent workers to "/tmp/postgres-bdr-0000076D-1.11322" Restoring dump to local DB "dbname=bdrdemo user=postgres port=5599" with 1 concurrent workers from "/tmp/postgres-bdr-0000076D-1.11322"
indicating that db1 has been copied to db2, then lines similar to:
LOG: starting background worker process "bdr (6028730235379497978,1,16385,): bdr5598: apply" DETAIL: streaming transactions committing after 0/19D1588, reading WAL from 0/19D01D0
In the log $HOME/2ndquadrant_bdr/bdr5598.log you won't see the lines about the dump and restore, since it's the first master. Just:
LOG: starting background worker process "bdr (6028730235379497978,1,16384,): bdr5599: apply" DETAIL: streaming transactions committing after 0/19D01D0, reading WAL from 0/19D0198
They won't be exactly the same, but this indicates normal startup.
At time of writing it's normal to get a CONFLICT message, a FATAL: Role "myusername" does not exist and a few other messages. These are cosmetic and will be removed in later releases.
Using your new BDR server
You can now connect to bdrdemo on port 5598 or port 5599 and make changes. The changes you make will get replicated to the other database.
To connect to 5599, use:
psql "port=5599 user=postgres dbname=bdrdemo"
(For 5598, just change the port).
When you connect to the server on port 5599, you should see that the table you created before joining the node now exists.
Additionally, if you INSERT INTO it, UPDATE it or DELETE FROM it now the changes will replicate to the other node.
DDL like CREATE TABLE from5599(id serial primary key, message text not null); will also be replicated. DDL works with a voting and locking scheme that means that
Here's an example showing a psql session:
Where to go from here?
You should now read the BDR Admin Guide, or just start experimenting.