PostgreSQL 10 Master-Slave Replication on Virtualbox


Preparations

Needs to download tools

Discover Vagrant Boxes - Linux

Two Vagrant boxes

  • Master IP:  192.168.33.11  –  read/write
  • Slave IP:   192.168.33.22   –  read-only

Postgres Master Configuration

Download and installation

vagrant init ubuntu/bionic64

Update IP and hostname in `Vagrantfile`

config.vm.network "private_network", ip: "192.168.33.11"
config.vm.hostname = 'master'

Start up box

vagrant up

Connected to the virtual box and install PostgreSQL

➜  vagrant ssh
root@master:~# sudo su
root@master:~# apt update
root@master:~# apt install postgresql -y

Create a role dedicated to the replication

root@master:~# sudo su - postgres
postgres@master:~$ psql
- CREATE ROLE replicate WITH REPLICATION LOGIN;
- \password replicate

Modify the `/etc/postgresql/10/main/postgresql.conf`

listen_addresses  = '*'
wal_level         = replica
max_wal_senders   = 3         # Max number of wal sender processes
wal_keep_segments = 64        # In logfile segments, 16MB each

Modify the `/etc/postgresql/10/main/pg_hba.conf`, allow access from the Slave server

host    replication    replicate    192.168.33.22/32    md5

Restart PostgreSQL

root@master:~# service postgresql restart

Postgres Slave Configuration

Download and installation

vagrant init ubuntu/bionic64

Update the Slave IP and hostname in `Vagrantfile`

config.vm.network "private_network", ip: "192.168.33.22"
config.vm.hostname = 'slave'

Start up box

vagrant up

Connected to the virtual box and install PostgreSQL

➜  vagrant ssh
- root@master:~# sudo su
- root@master:~# apt update
- root@master:~# apt install postgresql -y

Stop the PostgreSQL first

root@slave:~# service postgresql stop

Modify the /etc/postgresql/10/main/postgresql.conf

hot_standby = on

Modify the /etc/postgresql/10/main/pg_hba.conf, allow access from Slave server

host    replication    replicate    192.168.33.11/32    md5

Next, we need to delete all the files inside the `PGDATA` folder. need to check the data folder from `postgresql.conf`.

root@slave:~# rm -rf /var/lib/postgresql/10/main/*

Now, we will copy all the data from the master with the `pg_basebackup` command. we also need to run this command as the postgres user.

sudo su - postgres
root@slave:~# pg_basebackup -h 192.168.33.11 -D /var/lib/postgresql/10/main/ -P -U replicate --wal-method=stream
Password:
23687/23687 kB (100%), 1/1 tablespace

Because archive mode has been activated, a new directory must now be created for archiving in the `PGDATA` directory. with the following commands you create the directory, assign the necessary permissions and change the owner to user postgres.

root@slave:~# sudo su
root@slave:~# chmod 700 /var/lib/postgresql/10/main/
root@slave:~# chown -R postgres:postgres /var/lib/postgresql/10/main/

Create a new file named `recovery.conf` in `/var/lib/postgresql/10/main`, adding following command to this file:

standby_mode          = 'on'
primary_conninfo      = 'host=192.168.123.10 port=5432 user=replicate password=[password]'
trigger_file = '/tmp/MasterNow'
# restore_command = 'cp /home/postgresql_wal/%f "%p"'

An explanation for each line:

  • standby_mode = on: specifies that the server must start as a standby server
  • primary_conninfo: the parameters to use to connect to the master
  • trigger_file: if this file exists, the server will stop the replication and act as a master
  • restore_command: this command is only needed if you have used the archive_command on the master

All done, let's start PostgreSQL on Slave

root@master:~# service postgresql restart

If everything is fine, we have completed all the configuration of master-slave replication, let's verify the data.

check the `replicate` status

    vagrant@master:~$ sudo su - postgres
    postgres@master:~$ psql
    psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
    Type "help" for help.
    postgres=# \x on
    Expanded display is on.
    postgres=# SELECT * FROM pg_stat_activity WHERE usename = 'replicate';
    -[ RECORD 1 ]----+------------------------------
    datid            |
    datname          |
    pid              | 7248
    usesysid         | 16388
    usename          | replicate
    application_name | walreceiver
    client_addr      | 192.168.33.22
    client_hostname  |
    client_port      | 42434
    backend_start    | 2020-05-30 08:22:17.163417+00
    xact_start       |
    query_start      |
    state_change     | 2020-05-30 08:22:17.170025+00
    wait_event_type  | Activity
    wait_event       | WalSenderMain
    state            | active
    backend_xid      |
    backend_xmin     |
    query            |
    backend_type     | walsender

Create `testdb` from  master PostgreSQL

    postgres=# \l
                                  List of databases
       Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
    -----------+----------+----------+---------+---------+-----------------------
     postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
     template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
               |          |          |         |         | postgres=CTc/postgres
     template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
               |          |          |         |         | postgres=CTc/postgres
    (3 rows)

    postgres=# create database testdb;
    CREATE DATABASE
    postgres=#

Read `testdb` from  Slave PostgresSQL

    postgres=# \l
                              List of databases
       Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
    -----------+----------+----------+---------+---------+-----------------------
     postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
     template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
               |          |          |         |         | postgres=CTc/postgres
     template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
               |          |          |         |         | postgres=CTc/postgres
     testdb    | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
    (4 rows)

Looks all good now.

Tip: sometimes, we may need to clean and reinstall PostgreSQL. The following script will help you quickly remove PostgreSQL on Ubuntu 18.04.

  •   apt-get —purge remove postgresql
  •   dpkg -l | grep postgres
  •   apt-get —purge remove pgdg-keyring postgresql*
  •   rm -rf /var/lib/postgresql/ /var/log/postgresql/ /etc/postgresql/
  •   deluser postgres
  •   delgroup postgres

Hopes it can help you!