Home / How To / How to set up PostgreSQL Streaming Replication with Replication Slots on Debian 10

How to set up PostgreSQL Streaming Replication with Replication Slots on Debian 10



PostgreSQL is a powerful and feature-rich relational database management system (RDBMS). It is free and open source and has been developed since 1996. Postgres offers various ways to archive and replicate data, one of which is flow replication. In this mode, a primary (main) instance handles the main active database and performs operations. The secondary (slave) instance copies all changes from the primary, retaining an identical copy of the active database. The secondary server can also accept read-only queries. If the primary fails, the secondary server can exit standby mode and act as the new master (this is called failover).

PostgreSQL replication usually relies on pre-logging (WAL), the process of logging data changes before writing it to disk. These WAL records are then either copied to a second node as files (file-based log freight) or directly streamed between nodes (flow replication). In most cases, the later delay for changes to the main node to be received by the standby node decreases.

The problem with using stream replication without file-based logging is that the secondary server may miss some WAL records if primary throws them too soon. A number of configuration parameters can reduce this risk but often have unnecessary storage costs. The solution is replication hatches, a feature provided by Postgres that ensures that the primary server discards WAL records only after they have been received by the standby node.

We will set streaming replication with replication tracks on two Debian 1

0 nodes. 19659005] Requirements
  • Two identical Debian 10 instances.
  • Root access to both instances.
  • $ EDITOR environment variable should be set at both instances. [19659009] Step 1: Install PostgreSQL

    Update and restart both nodes:

      apt update
    apt upgrade -y
    reboot
    

    Install Postgres on both nodes and make sure PostgreSQL is enabled and running:

      apt install -y postgresql
    systemctl enable --now [email protected]
    

    Note: When updating PostgreSQL, updating standby is first the safer option according to their documentation.

    Step 2: Initial Configuration

    By default, PostgreSQL only listens to the loopback interface and is not available externally. Change the listener address on both nodes by editing postgresql.conf :

      $ EDITOR /etc/postgresql/11/main/postgresql.conf
    

    Find the following line:

      #listen_addresses = & # 39; localhost & # 39;
    

    Change it to:

      list_addresses = & # 39; node_ip_address, 127.0.0.1 & # 39;
    

    If both nodes share the same local network, you can use private addresses for node_ip_address but Postgres are not available for the Internet. Otherwise, use public addresses.

    Save the change and then restart in both cases:

      systemctl reboot [email protected]

    Step 3: Master configuration

    This step only applies to the primary / master server.

    Open Postgres terminal:

      sudo -u postgres psql
    

    The standby node uses a user to connect to the master. Create it:

      postgres = # CREATE ROLE replicator LOGIN REPLICATION ENCRYPTED PASSWORD & # 39; replicator_password & # 39 ;;
    

    Then create a replication track and exit:

      postgres = # SELECT * FROM pg_create_physical_replication_slot (& # 39; replicator & # 39;);
    postgres = #  q
    

    For the sake of simplicity, the replication role and track are both named "replicator", although they do not need to be identical.

    Then create an entry pg_hba.conf to allow replicator users to connect from standby to master. Open it:

      $ EDITOR /etc/postgresql/11/main/pg_hba.conf
    

    Add the following line to the end:

      host replication replicator standby_ip_address / 32 md5
    

    Restart the master instance:

      systemctl restart [email protected]

    Step 4: Basic backup

    The commands in this step should be run on the secondary / slave server.

    First stop Postgres on secondary node:

      systemctl stop [email protected]
    

    Backing up the old data directory:

      etc. / var / lib / postgresql / 11 / main / /var/lib/postgresql/11/main.bak
    

    Use the following command to clone the master data directory to the slave:

      pg_basebackup -h master_ip_address -U replicator -D / var / lib / postgresql / 11 / main / -P - password --slot replicator
    

    You will be prompted for a password. Enter the password you chose for the replicator role while creating it on the master. When the transfer is complete you grant ownership of the data directory to postgres user:

      chown -R postgres: postgres / var / lib / postgresql / 11 / main 

    Step 5: Standby Configuration

    This step only applies to the secondary / slave server.

    Enable hot standby mode in postgresql.conf :

      $ EDITOR /etc/postgresql/11/main/postgresql.conf
    

    Find and deselect the following line:

      #hot_standby = on
    

    Create file recovery.conf in the Postgres data directory:

      $ EDITOR /var/lib/postgresql/11/main/recovery.conf
    

    Enable standby mode:

      standby_mode = & # 39; on & # 39;
    

    Set the replication connection parameters with the references created on the master:

      primary_conninfo = & # 39; host = master_ip_address port = 5432 user = replicator password = replicator_password & # 39;
    

    Set the name of the replication tray that you created on the master:

      primary_slot_name = & # 39; replicator & # 39;
    

    Set the path to a failover trigger file:

      trigger_file = & # 39; /var/lib/postgresql/11/main/failover.trigger'
    

    If the trigger_file parameter is set, Postgres will leave standby mode and start normal operation as a primary server when this trigger file is created. This parameter is not mandatory.

    After creation of recovery.conf grant ownership to postgres user:

      chown postgres: postgres / var / lib / postgresql / 11 / main / recovery.conf
    

    You can now start Postgres:

      systemctl start [email protected]
    

    It is now in standby mode and should replicate all new transactions.

    Test

    Test replication

    To test replication, perform all write operations on the master. For example, create a new master database:

      sudo -u postgres psql -c "CREATE DATABASE replitest"
    

    Wait a few seconds and then list the databases on the slave:

      sudo -u postgres psql -c " l"
    

    You should see that the replitest database was really replicated by the standby server:

      List of databases
    Name | Owner | Encoding | Sort | Ctype | Access permissions
    ----------- + ---------- + ---------- + ------------- + - - ---------- + -----------------------
    postgres | postgres | UTF8 | sv_US.UTF-8 | sv_US.UTF-8 |
    replitest | postgres | UTF8 | sv_US.UTF-8 | sv_US.UTF-8 |
    mall0 | postgres | UTF8 | sv_US.UTF-8 | sv_US.UTF-8 | = c / postgres +
    | | | | | Postgres = CtC / postgres
    mall1 | postgres | UTF8 | sv_US.UTF-8 | sv_US.UTF-8 | = c / postgres +
    | | | | | Postgres = CtC / postgres
    (4 lines) 

    Failure Testing

    NOTE: Failover testing shown here will require resetting the standby server after failover.

    Since Postgres is in standby mode, you should not be able to perform all write operations on the secondary node before failover. For example, execute the following command:

      sudo -u postgres psql -c "CREATE DATABASE test"
    

    The command should fail:

      ERROR: Cannot execute CREATE DATABASE in a read-only transaction
    

    To signal failover, create the trigger file specified in recovery.conf

      touch /var/lib/postgresql/11/main/failover.trigger
    

    Wait a few seconds and then try performing a write operation. For example:

      sudo -u postgres psql -c "CREATE DATABASE test2"
    

    Since Postgres no longer functions as a standby, the operation will succeed. Postgres will also rename your recovery.conf file to recovery.done and will delete the trigger file.

    To return to standby, stop Postgres on (previous) secondary node:

      systemctl stop [email protected]
    

    Reset data directory:

      etc. / var / lib / postgresql / 11 / main / /var/lib/postgresql/11/main.2.bak
    pg_basebackup -h master_ip_address -U replicator -D / var / lib / postgresql / 11 / main / -P - password - partial replicator
    chown -R postgres: postgres / var / lib / postgresql / 11 / main
    

    And recreate recovery.conf :

      cp /var/lib/postgresql/11/main.2.bak/recovery.done / var / lib / postgresql / 11 / main / recovery. conf
    

    Finally restart Postgres:

      systemctl start [email protected]
    

    The secondary instance is now back to standby. You may want to retry replication at this time.

    Exit

    Delete unnecessary databases on the root node, for example:

      sudo -u postgres psql
    postgres = # DROP DATABASE replitest;
    

    And delete the old data directories in your standby node:

      rm /var/lib/postgresql/11/main.bak -r
    rm /var/lib/postgresql/11/main.2.bak -r
    

Source link