Testing Postgresql replication

I wanted to try PostgresSQL’s streaming replication using two VMs managed with vagrant. This is how I did.

VM setup

Create a Vagrantfile

vagrant init

it can look like this:

Vagrant.configure("2") do |config|
  config.vm.box = "generic/debian10"
  
  config.vm.define "main" do |main|
    main.vm.hostname = "main.test"
    main.vm.network :private_network, ip: "192.168.60.4"
  end
  
  config.vm.define "replica" do |replica|
    replica.vm.hostname = "replica.test"
    replica.vm.network :private_network, ip: "192.168.60.5"
  end
end

Start them with

vagrant up

Then install postgres on main:

vagrant ssh main
sudo apt update
sudo apt install postgresql postgresql-contrib

and the same on replica. You can automate this in the Vagrantfile if you wish, or do it manually as I did.

Check the database is running with

sudo -u postgres psql -c "SELECT version();"

and open a psql console with

sudo -u postgres psql

Create a database on main with

create database pippo;
\c pippo
CREATE TABLE books (id SERIAL PRIMARY KEY, title VARCHAR(100) NOT NULL, primary_author VARCHAR(100) NULL );
insert into books(title, primary_author) values('my title', 'dante');

Edit /etc/postgresql/11/main/postgresql.conf on main and make sure it will listen to its external address:

listen_addresses = '*'

The default behavior is to listen for TCP/IP connections only on the local loopback address localhost.

Restart:

sudo systemctl restart postgresql

Create on main the role able to read from the replica:

CREATE ROLE reader WITH REPLICATION PASSWORD 'reader' LOGIN;

Now let this user connect from the replica by adding to /etc/postgresql/11/main/pg_hba.conf:

host    replication reader      192.168.60.5/32     md5

Restart:

sudo systemctl restart postgresql

You need to run the initial import. Clear the existing data directory of the replica db:

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/11/main
sudo -u postgres mkdir /var/lib/postgresql/11/main
sudo -u postgres chmod 700 /var/lib/postgresql/11/main
sudo -u postgres pg_basebackup -h 192.168.60.4 -p 5432 -U reader -D /var/lib/postgresql/11/main/ -Fp -Xs -R

The -R flag of the last command tells the replica to act as a standby server, ie a replica.

Run this on the main database to check the replication is up and running:

SELECT client_addr, state FROM pg_stat_replication;

 client_addr  |   state
--------------+-----------
 192.168.60.5 | streaming