postgres 11 master slave replication in ubuntu14.04

Install Postgres

add-apt-repository ‘deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main’
wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –
apt-get update
apt-get install postgresql-11

MASTER: 192.168.33.50
SLAVE1: 192.168.33.51
SLAVE2: 192.168.33.52

On master

postgres@master:~$ su – postgres

Note: Set password less auth b/w master & salve, master & slave2 vice-versa

postgres@master:~$ service postgresql status
11/main (port 5432): online

postgres@master:~$ ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/var/lib/postgresql/.ssh/id_rsa):
Created directory ‘/var/lib/postgresql/.ssh’.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /var/lib/postgresql/.ssh/id_rsa.
Your public key has been saved in /var/lib/postgresql/.ssh/id_rsa.pub.
The key fingerprint is:
8e:fd:36:c6:52:32:36:1f:32:18:72:d8:06:51:2d:e4 postgres@vagrant-ubuntu-trusty-64
The key’s randomart image is:
+–[ RSA 2048]—-+
| .+o. |
| … . |
| +E. |
| o = |
| + oS |
| .+B o |
| ..oX . |
| ..* |
| +.. |
+—————–+

postgres@master:~$ cat .ssh/id_rsa.pub
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDRpbtMmnSkPR80Gcr9FfLzOmc227XZ186DnoxDXZjFS9r/MMj5EbqTesRZjCS3zUE3szrrv9DwxIQWfJ8ip0cmTURVLLwPiIHJk2iLXjUheDR9Pke78ernDWOvdIlb6zqcJI68jVqeprK8kimmnhNPYIOFthh40k6jIpHaKPHvMYZuUXyMmlc6xQGcZH3yy0pMqCpvcumRyhEwQEwep0JaruUStDOL1Bw0PGqlweN8yhGYtsPOcgVTYSphEsvhR2wk/P82s35bEW5o3yHeBYpfpY9QROUn2PURq4zn52biscBUN1JPRidE5JROjeo7LvXF2KLuSf8JXlaz4areNQXj postgres@vagrant-ubuntu-trusty-64

Copy public key On slave 1
postgres@slave1:~$ vim .ssh/authorized_keys

Do Vice-versa on slave1 & master & in b/w master & slave2

postgres@master:~$ psql
psql (11.3 (Ubuntu 11.3-1.pgdg14.04+1))
Type “help” for help.

postgres=# \du
List of roles
Role name | Attributes | Member of
———–+————————————————————+———–
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=#
postgres=# \dn
List of schemas
Name | Owner
——–+———-
public | postgres
(1 row)

postgres=# \dt
Did not find any relations.
postgres=# \d
Did not find any relations.

postgres@master:~$ psql -c “CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 2 ENCRYPTED PASSWORD ‘mypassword’;”
CREATE ROLE

root@master:~# vi /etc/postgresql/11/main/pg_hba.conf
host replication rep 192.168.33.51/32 md5
host replication rep 192.168.33.52/32 md5

root@master:~# vim /etc/postgresql/11/main/postgresql.conf
listen_addresses = ‘localhost,192.168.33.50’
wal_level = ‘hot_standby’
archive_mode = on
archive_command = ‘cd .’
max_wal_senders = 2
hot_standby = on

root@master:~# service postgresql restart

  • Restarting PostgreSQL 11 database server postgres@master:~$ netstat -tulpn |grep 5432
    (Not all processes could be identified, non-owned process info
    will not be shown, you would have to be root to see it all.)
    tcp 0 0 192.168.33.50:5432 0.0.0.0:* LISTEN 7574/postgres
    tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 7574/postgres

Slave1:

root@slave1:~# service postgresql stop

  • Stopping PostgreSQL 11 database server root@slave1:~# vim /etc/postgresql/11/main/pg_hba.conf
    host replication rep 192.168.33.50/32 md5

root@slave1:~# vim /etc/postgresql/11/main/postgresql.conf
listen_addresses = ‘localhost,192.168.33.151’
wal_level = ‘hot_standby’
archive_mode = on
archive_command = ‘cd .’
max_wal_senders = 2
hot_standby = on

Slave2:

root@slave1:~# service postgresql stop

  • Stopping PostgreSQL 11 database server

root@slave1:~# vim /etc/postgresql/11/main/pg_hba.conf
host replication rep 192.168.33.50/32 md5

root@slave1:~# vim /etc/postgresql/11/main/postgresql.conf
listen_addresses = ‘localhost,192.168.33.152’
wal_level = ‘hot_standby’
archive_mode = on
archive_command = ‘cd .’
max_wal_senders = 2
hot_standby = on

Master

postgres@master:~$ psql -c “select pg_start_backup(‘initial_backup’);”

pg_start_backup

0/3000028
(1 row)

postgres@master:~$ rsync -cva –inplace –exclude=pg_xlog /var/lib/postgresql/11/main/ 192.168.33.51:/var/lib/postgresql/11/main/
sending incremental file list
./
backup_label
postmaster.pid
base/
base/1/

postgres@master:~$ psql -c “select pg_stop_backup();”
NOTICE: pg_stop_backup complete, all required WAL segments have been archived

pg_stop_backup

0/3000130
(1 row)

Slave1:

postgres@slave1:~$ vim /var/lib/postgresql/11/main/recovery.conf
standby_mode = ‘on’
primary_conninfo = ‘host=192.168.33.50 port=5432 user=rep password=mypassword’
trigger_file = ‘/tmp/postgresql.trigger.5432’

root@slave1:~# service postgresql start

  • Starting PostgreSQL 11 database server

root@slave1:~# netstat -tulpn
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 192.168.33.51:5432 0.0.0.0:* LISTEN 4870/postgres
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 4870/postgres

Slave2:


postgres@slave2:~$ vim /var/lib/postgresql/11/main/recovery.conf
standby_mode = ‘on’
primary_conninfo = ‘host=192.168.33.50 port=5432 user=rep password=mypassword’
trigger_file = ‘/tmp/postgresql.trigger.5432’

root@slave2:~# service postgresql start

  • Starting PostgreSQL 11 database server

root@slave2:~# netstat -tulpn
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 192.168.33.51:5432 0.0.0.0:* LISTEN 4870/postgres
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 4870/postgre

master

postgres=# CREATE database test123;
CREATE DATABASE

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

postgres=# DROP DATABASE test123;
DROP DATABASE

postgres=# create database db1;
CREATE DATABASE

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

postgres=# \c db1
You are now connected to database “db1” as user “postgres”.

db1=# create table firsttable(id serial PRIMARY KEY, url VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL,description VARCHAR (255),last_update DATE);
CREATE TABLE

check replication on slave instance

db1=# select * from firsttable;
id | url | name | description | last_update
—-+—–+——+————-+————-
(0 rows)

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

Unknown's avatar

Published by mshaik

Leave a comment