Postgresql Replication

replication.md

Setup

1. Installation

 wget -O- https://apt.releases.hashicorp.com/gpg | sudo gpg --dearmor -o /usr/share/keyrings/hashicorp-archive-keyring.gpg

 echo "deb [signed-by=/usr/share/keyrings/hashicorp-archive-keyring.gpg] https://apt.releases.hashicorp.com $(lsb_release -cs) main" | sudo tee /etc/apt/sources.list.d/hashicorp.list

 sudo apt update && sudo apt install vagrant

2. Vagrantfile

Vagrant.configure("2") do |config|
  config.vm.define "instance1" do |instance1|
    instance1.vm.box = "ubuntu/bionic64"
  end

  config.vm.define "instance2" do |instance2|
    instance2.vm.box = "ubuntu/bionic64"
  end

  config.vm.define "instance3" do |instance3|
    instance3.vm.box = "ubuntu/bionic64"
  end

  config.vm.define "instance4" do |instance4|
    instance4.vm.box = "ubuntu/bionic64"
  end

  config.vm.define "instance5" do |instance5|
    instance5.vm.box = "ubuntu/bionic64"
  end
  
  config.vm.network "public_network", bridge: "wlo1"

  config.vm.provision "shell", inline: <<-SHELL
    apt-get update
    apt-get install -y postgresql postgresql-contrib
  SHELL
end

3. Usage

• vagrant up

Replication

Master

• 192.168.1.15

Commands

• sudo -u postgres psql
• CREATE ROLE replica_user WITH REPLICATION LOGIN PASSWORD 'password';
• sudo vim /etc/postgresql/10/main/postgresql.conf
  • Add listen_addresses = '192.168.1.15'
  • Add wal_level = replica
  • Add wal_log_hints = on
• sudo vim /etc/postgresql/10/main/pg_hba.conf
  • Add host replication replica_user 192.168.1.16/24 md5
  • Add host replication replica_user 192.168.1.17/24 md5
  • Add host replication replica_user 192.168.1.18/24 md5
  • Add host replication replica_user 192.168.1.19/24 md5
• sudo systemctl restart postgresql

Slaves

• 192.168.1.16
• 192.168.1.17
• 192.168.1.18
• 192.168.1.19

Commands

• sudo systemctl stop postgresql
• sudo rm -rv /var/lib/postgresql/10/main/
• sudo pg_basebackup -h 192.168.1.15 -U replica_user -X stream -v -R -W -D /var/lib/postgresql/10/main
• sudo chown postgres -R /var/lib/postgresql/10/main/
• sudo systemctl restart postgresql

Test

Run it on the Master

• sudo -u postgres psql
• SELECT client_addr, state FROM pg_stat_replication;