Introduction to PostgreSQL BDR

Introduction to PostgreSQL BDR

PostgreSQL is not only a stable and reliable database, but plus to all it is a dynamically developing product, in which a variety of breakthrough things emerge from release to release. At one time, one of these technologies was streaming replication. This is a high-performance replication that makes it very easy and cheap to scale a database for reading. Using it you can create reliable configurations by distributing the load on the reading between nodes. However, as I wrote above, the product is developing, and today the article will deal with the new technology BDR (Bi-Directional Replication).

Some terms for those who are not in the subject:
WAL (Write Ahead Log) is a transaction log, the built-in stream replication is based on it in a postgres, the DBMS writes there everything that happens to the data in the database.
SR (Streaming Replication) is a generic name for embedded streaming replication that is based on WAL, everything written in WAL, then sent to slaves and played back. There are physical and logical stream replication.
PLSR (Physical Log Streaming Replication) - physical stream replication (something that is already implemented and working), everything that got into WAL without subsequent parsing is replicated to the server slave, this is both a change in data / schema and lower-level things (full page writes, vacuum, hint bit settings).
LLSR (Logical Log Streaming Replication) - logical stream replication (will appear in 9.4) is also based on WAL logs, but more intelligent and only a certain part of the logs are extracted for replication, which describe changes in the database schema and data (that is, some low-level things are eliminated) .

What is the term BDR?
BDR (Bi-Directional Replication) is a new functionality added to the PostgreSQL core that provides advanced replication tools. At the moment it is implemented in the form of a small patch and module. It is declared that it will only be fully in PostgreSQL 9.5 (now 9.3-stable and 9.4-beta1).

In short, the BDR allows you to create geographically distributed asynchronous multi-master configurations (oh yeah, baby) using the built-in logical stream replication LLSR.
')
However, BDR is not a clustering tool; there are no global lock managers or transaction coordinators (hi Postgres-XC / XL). Each node is independent of the others, which would be impossible if lock managers were used. Each node contains a local copy of the data identical to the data on the other nodes. Requests are also performed only locally (to make it clearer, I'll compare with Postgres-XC / Postgres-XL, where all servers work as if in one team, transactions are controlled by the global transaction manager, and requests from the application go to the coordinator (s) ) which sends executed requests to any work node, here). Moreover, each of the nodes is internally consistent at all times, the entire server group is eventually ( consistently consistent ).

The uniqueness of BDR lies in the fact that it is unlike neither the built-in stream replication nor the existing trigger-based solutions (Londiste, Slony, Bucardo).

The most notable difference from stream replication is that BDR (LLSR) operates with databases (per-database replication), and classic PLSR replicates the entire instance (per-cluster replication), i.e. all bases inside the instance.

Existing restrictions and features:
1. All data changes caused by INSERT / DELETE / UPDATE are replicated (TRUNCATE at the time of this writing has not yet been implemented)
2. Most schema change (DDL) operations are replicated successfully. Unsupported DDLs are fixed by the replication module and are rejected with an error (at the time of writing, the article CREATE TABLE… AS was not working)
3. Definitions of tables, types, extensions, etc. must be identical between upstream and downstream masters.
4. Actions that are reflected in WAL, but not represented as logical changes are not replicated to another node (writing full pages, vacuuming tables, etc.). Thus, logical stream replication (LLSR) is free from some of the overhead that is present in physical PLSR streaming replication (nevertheless, this does not mean that LLSR requires less network bandwidth than for PLSR).

So perhaps enough theory, a little practice. Already now there is an opportunity to test Bi-Directional replication.

Installation is performed on two virtual machines with CentOS 6.5 minimal. Install the necessary packages for the assembly:

# yum install readline-devel zlib-devel yum-utils -y # yum groupinstall "Development Tools" -y 

Go to the postgres account and install postgresql with BDR support. It is worth noting here that the guys from 2ndQuadrant wrote an installer so that those who would like to try would not make much effort to install and configure, for which they are a bunch of greens.

 # su - postgres $ 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 

Add the directory with the postgres executables to the PATH environment variable and immediately check for psql . Who does not know, the export command is one-time, so if you plan to use or play with BDR for a long time, then add this command to. Your user's bashrc (if you have bash of course).

 $ export PATH=$HOME/2ndquadrant_bdr/bdr/bin:$PATH $ psql --version psql (PostgreSQL) 9.4beta1_bdr0601 

We initialize the database directories on both nodes and then immediately start. It is not necessary to edit postgresql.conf beforehand, at the first start we will create a test database which will be replicated in the future.

 $ initdb -D data/ -A trust -U postgres $ pg_ctl -l logfile -D data/ -w start $ psql -c 'create database staging_db' 

We created a database, and then proceed to configure postgresql.conf. First we set up an upstream wizard. In the configuration below, we specify the need to load the bdr library ( shared_preload_libraries ), determine the level of WAL logs to logical ( wal_level ), determine the number of slots for replication, the maximum number of processes involved in sending WAL logs ( wal_senders ), and enable time tracking for the operation COMMIT what is needed to resolve conflicts (last-UPDATE-wins). Then at the end of the file we define the configuration for the BDR: we specify the name of the connection and the settings for connecting to the remote node. It is worth noting that the name specified in bdr.connections is arbitrary (I have this virtual machine name), the main thing is that the specified name should participate in the names of the underlying parameters.

 $ vi data/postgresql.conf listen_address = '*' shared_preload_libraries = 'bdr' wal_level = logical wal_senders = 4 max_replication_slots = 4 track_commit_timestamp = on bdr.connections = 'vm13' bdr.vm13_dsn = 'host=192.168.122.13 port=5432 user=postgres dbname=staging_db' 

Now configuration downstream wizard. First, I give a description of the configuration and then its analysis below.

 $ vi data/postgresql.conf listen_address = '*' shared_preload_libraries = 'bdr' wal_level = logical wal_senders = 4 max_replication_slots = 4 track_commit_timestamp = on bdr.connections = 'vm12' bdr.vm12_dsn = 'host=192.168.122.12 port=5432 user=postgres dbname=staging_db' bdr.vm12_init_replica = on bdr.vm12_replica_local_dsn = 'host=127.0.0.1 port=5432 user=postgres dbname=staging_db' 

Configuring the second node differs little, in particular here in the BDR configuration we indicate the need to perform initialization of the replica ( bdr.vm12_init_replica ) from the node specified in bdr.vm12_dsn to the local database whose details are specified in bdr.vm12_replica_local_dsn . The last parameter is required if the database cluster is initialized using initdb (just our case) and in this case there should be an empty database in the cluster that will later participate in replication.

In the case of initialization via pg_basebackup, the option bdr.vm12_replica_local_dsn is not needed.

Now we define the authentication settings on both nodes, in my case everything is allowed. For production installations, of course, you can't do that.

 $ vi data/pg_hba.conf host all all 192.168.122.0/24 trust host replication postgres 192.168.122.0/24 trust 

We restart both nodes and look at the logs.

 $ pg_ctl -l logfile -D data/ -w restart 

Everything is good in the logs and there are no ERROR messages (and if there is, check the configs or sin on the developers))). This completes the setup and launch. Now you can check the work through the creation of tables in both databases.

Another couple of moments. Temporary replication is stopped by turning off the downstream wizard. However, it is worth noting that a stopped replica causes the upstream wizard to continue accumulating WAL logs, which in turn can lead to uncontrollable disk space consumption. Therefore, it is not recommended to permanently turn off the replica.
Permanently delete the replica by removing the BDR configuration on the downstream server and then restarting the downstream wizard. Then you need to remove the corresponding replication slot on the upstream wizard using the pg_drop_replication_slot ('slotname') function. Available slots can be viewed using the pg_get_replication_slots () function.

As a conclusion, I will say my impressions ... Of course, I have some questions about the operation of BDR, the answers to which will most likely have to be clarified experimentally. But already at this stage I like this new tool, it is configured quickly and easily, plus it already works, despite the fact that it will officially appear only at 9.5 (about a year later). Thus, with the release will be added another tool with which you can create reliable fault-tolerant configurations, and that's fine. PostgreSQL from release to release only gets better and better.

Actually that's all. Thank you all for your attention.

PS Links to read:
BDR User Guide
Logical Log Streaming Replication
PostgreSQL WAL Shipping and Streaming Replication