Introduction to PostgreSQL BDR

    Introduction to PostgreSQL BDR

    PostgreSQL is not only a stable and reliable DBMS, but also a plus to all, it is a dynamically developing product in which a variety of breakthrough things appear 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 read database. Using it, you can create reliable configurations by distributing the load of reading between nodes. However, as I wrote above, the product is developing, and today the article will focus on the new BDR (Bi-Directional Replication) technology.

    A few terms for those who are not in the topic:
    WAL (Write Ahead Log) - a transaction log, based on it built-in stream replication in postgres, the DBMS writes everything that happens to the data in the database there.
    SR (Streaming Replication) - the general name of the built-in streaming replication which is based on the WAL, everything that is written in the WAL, then sent to the slaves and played. There are physical and logical streaming replication.
    PLSR (Physical Log Streaming Replication) - physical stream replication (what has already been implemented and works), everything that gets into the WAL is replicated to the server slave without further analysis, this is a change in data / scheme and lower-level things (full page writes, vacuum, hint bit settings).
    LLSR (Logical Log Streaming Replication)- logical streaming replication (appearing in 9.4) is also based on WAL logs, but it is already more intelligent and only a certain part of the logs is extracted for replication in which the database schema and data changes are described (that is, some low-level things are eliminated).

    What is hidden under the term BDR?
    BDR (Bi-Directional Replication) is a new feature added to the PostgreSQL kernel that provides advanced replication tools. At the moment, this is implemented as a small patch and module. It is stated that it will only be fully in PostgreSQL 9.5 (now 9.3-stable and 9.4-beta1).

    In short, BDR allows you to create geographically distributed asynchronous multi-master configurations (oh yes, baby) using the built-in logical streaming replication LLSR.

    However, BDR is not a tool for clustering, as there are no global lock managers or transaction coordinators (hello Postgres-XC/ Xl). Each node is independent of the others, which would be impossible if blocking managers were used. Each of the nodes contains a local copy of the data identical to the data on the other nodes. Requests are also executed only locally (to make it more clear what I’m talking about, I’ll give a comparison with Postgres-XC / Postgres-XL, where all the servers work as if in one team, transactions are ruled by the global transaction manager, and requests from the application go to the coordinator (s ) which sends the received requests to any working node to be executed, here). At the same time, each of the nodes is internally consistent at any time, while the whole group of servers is ultimately consistent .

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

    The most noticeable difference from streaming replication is that BDR (LLSR) operates with per-database replication, while classical PLSR replicates the entire instance (per-cluster replication), i.e. all databases inside the instance.

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

    So there’s probably enough theory, a little practice. Already there is an opportunity to test Bi-Directional replication.

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

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

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

    # su - postgres
    $ curl -s ";a=blob_plain;f=contrib/bdr/scripts/;hb=refs/heads/bdr-next" | bash

    We add the directory with postgres executable files 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. Postgresql.conf is not necessary to be edited in advance ; 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 the database, after which we move on to setting up postgresql.conf. First, configure the upstream wizard. In the configuration below, we indicate the need to load the bdr library ( shared_preload_libraries ), determine the level of detail of WAL logs to logical ( wal_level ), determine the number of slots for replication, the maximum possible 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 determine the configuration for BDR: specify the name of the connection and settings for connecting to the remote host. It is worth noting that the name specified in bdr.connections is arbitrary (I have this name of the virtual machine), the main thing is that the specified name must 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= 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= port=5432 user=postgres dbname=staging_db'
    bdr.vm12_init_replica = on
    bdr.vm12_replica_local_dsn = 'host= port=5432 user=postgres dbname=staging_db'

    Setting up the second node is not much different, in particular, here in the BDR configuration we indicate the need to initialize 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 the cluster must have an empty database that will continue to participate in replication.

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

    Now we determine the authentication settings on both nodes, in my case everything is allowed. For production installations, of course, this cannot be done.

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

    Restart both nodes and look at the logs
    $ pg_ctl -l logfile -D data/ -w restart

    upstream master:
    ~ $ tail Quatnities VM12 -f the logfile
    the LOG: unexpected the EOF on standby connection
    the LOG: starting class logical DECODING for slot bdr_16384_6029905891437956874_1_16384__
    the DETAIL: streaming Transactions Committing the after 0 / 1898F90, reading WAL from 0 / 1898C30
    the LOG: class logical DECODING found! consistent point AT 0 / 1898C30
    the DETAIL : running xacts with xcnt == 0
    LOG: starting background worker process "bdr (6029905879776466735,1,16384,): vm13: apply"

    downstream master:
    vm13 ~ $ tail -f logfile
    LOG: registering background worker “bdr (6029905891437956874,16384,): vm12: apply”
    LOG: starting background worker process “bdr (6029905891437956874,1,16384,): vm12: apply”
    LOG: logical decoding found consistent point at 0 / 18A4290
    DETAIL: running xacts with xcnt == 0
    LOG: exported logical decoding snapshot: "0000071B-1" with 0 xids
    LOG: starting logical decoding for slot bdr_16384_6029905879776466735_1_16384__
    DETAIL: streaming committing , reading WAL from 0 / 18A4290
    LOG: logical decoding found consistent point at 0 / 18A4290
    DETAIL: running xacts with xcnt == 0

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

    A couple more points. Temporarily stop replication by turning off the downstream wizard. However, it is worth noting that a stopped replica leads to the fact that the upstream master continues to accumulate WAL logs, which in turn can lead to uncontrolled consumption of disk space. Therefore, it is highly discouraged to turn off the replica for a long time.
    Removing a replica is forever done by deleting 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’ll tell you my impressions ... Of course, I have some questions about the operation of BDR, the answers to which will most likely have to be determined experimentally. But already at this stage I like this new tool, it can be configured easily and quickly, plus it already works despite the fact that it will officially appear only at 9.5 (and this is about a year later). Thus, with the release, one more tool will be added with the help of which it will be possible to create reliable fault-tolerant configurations, and this is wonderful. PostgreSQL from release to release is only getting better and better.

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

    PS Read links:
    BDR User Guide
    Logical Log Streaming Replication
    PostgreSQL WAL Shipping and Streaming Replication

    Also popular now: