- 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
- 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.conflisten_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 Slave serverhost replication replicate 192.168.33.22/32 md5 - Restart postgreSQL
- root@master:~# service postgresql restart
- Download and installation
- vagrant init ubuntu/bionic64
- Update IP and hostname in
Vagrantfile- config.vm.network "private_network", ip: "192.168.33.22"
- config.vm.hostname = 'master'
- Start up box
- vagrant up
- Connected to the virtual box and install postgreSQL
➜ vagrant ssh
- root@slave:~# sudo su
- root@slave:~# apt update
- root@slave:~# apt install postgresql -y
- Stop the postgreSQL first
- root@slave:~# service postgresql stop
- Modify the
/etc/postgresql/10/main/postgresql.confhot_standby = on - Modify the
/etc/postgresql/10/main/pg_hba.conf, allow access from the Master serverhost replication replicate 192.168.33.11/24 md5 - Next, we need to delete all the files inside the
PGDATAfolder. need to check the data folder frompostgresql.conf.root@slave:~# rm -rf /var/lib/postgresql/10/main/* - Now, we will copy all the data from the master with the
pg_basebackupcommand. 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
PGDATAdirectory. 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.confin/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"'- 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
- Explanation for each line:
- All done, let's start postgreSQL on Slave
- service postgresql start
If everything is fine, we have completed all the configuration of master-slave replication, let's verify the data.
- check the
replicatestatusvagrant@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
testdbfrom master PostgreSQLpostgres=# \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
testdbfrom Slave PostgresSQLpostgres=# \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) - Nice, all good.
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
*** Don't forget this section in the Slave.