What's a slave and master? In postgresql, we can have a database called Replica, whose data is always synced with the primary database(Master). Slave is usually used for backup and read only.
- Master server ip address: 94.101.184.217 - Slave server ip address: 5.253.25.185
$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib postgresql-client
$ sudo service postgresql status
$ sudo service postgresql enable
$ sudo service postgresql startNote:
- After installing and start postgresql on both servers, if you run the bellow command on both servers, you will see that the cluster membership type for both mains shows that the master and slave relationship between these two nodes is not established.
$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
14 main 5432 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log$ sudo apt-get install ufw
$ sudo ufw allow postgresql
$ sudo ufw enable
$ sudo ufw statusNote:
We have two config files in postgresql, (postgresql.conf) and (pg_hba.conf), the difference between these two files: The postgresql.conf file is used as a base for configs such as port and the amount of memory used in the entire function.
File pg_hba.conf is related to authentication and access level of users to postgresql server
$ sudo -i -u postgres
$ psqlpostgres=# CREATE USER replication REPLICATION LOGIN ENCRYPTED PASSWORD 'replica9999';
# Configure /etc/postgresql/14/main/postgresql.conf
listen_addresses = 'localhost,(master node ip address)'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64Problem 1 After restart postgresql service in master; i received the following error LOG: unrecognized configuration parameter "wal_keep_segments" in file "/etc/postgresql/14/main/postgresql.conf" line 299 >>>> Solve this problem: With PostgreSQL 13 things will look a bit different. wal_keep_segments is replaced by wal_keep_size. The parameter has still the same purpose. Define the kept log files segments, but now we define the size of the log files segments instead of the number. If you don’t specify a unit, MB will be taken as the default. There is a simple formular, that helps you to calculate the value of the wal_keep_size: wal_keep_size = wal_keep_segments * wal_segment_size (typically 16MB) So I replaced wal_keep_segments with wal_keep_size
# configure /etc/postgresql/14/main/pg_hba.conf
host all replication (slave node ip address) md5$ sudo service postgresql restart
$ sudo service postgresql status
# After restarting postgresql,
# by viewing its log, you can understand that
# the postgresql service has been successfully executed
$ sudo tail -f /var/log/postgresql/postgresql-14-main.log$ sudo service postgresql stop
$ sudo service postgresql statuslisten_addresses = 'localhost,(slave node ip address)'
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1024
hot_standby = onhost all replication (master node ip address) md5$ sudo cd /var/lib/postgresql/12/main/
$ sudo rm -rf *$ sudo su postgres
pg_basebackup -h (master node ip address) -U replication -p 5432 -D /var/lib/postgresql/14/main/ -Fp -Xs -P -RProblem 2: # I received this error by executing the above command in the slave: postgres@vm-she-slave:~$ pg_basebackup -h (master node ip address) -U replication -p 5432 -D /var/lib/postgresql/14/main/ -Fp -Xs -P -R pg_basebackup: error: connection to server at "94.101.184.217", port 5432 failed: FATAL: no pg_hba.conf entry for replication connection from host "188.121.109.185", user "replication", SSL encryption connection to server at "94.101.184.217", port 5432 failed: FATAL: no pg_hba.conf entry for replication connection from host "188.121.109.185", user "replication", no encryption >>>> Solve this problem: edit /etc/postgresql/14/main/pg_hba.conf in master and slave nodes # replice dellow line host all replication (master node ip address) md5 # by host replication replication (master node ip address) md5
In master run below commands
$ sudo su - postgres
$ psql
postgres=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 87400
usesysid | 16718
usename | replication
application_name | 14/main
client_addr | 5.253.25.185
client_hostname |
client_port | 57334
backend_start | 2023-10-02 06:24:04.23601+00
backend_xmin |
state | streaming
sent_lsn | 0/5000148
write_lsn | 0/5000148
flush_lsn | 0/5000148
replay_lsn | 0/5000148
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2023-10-02 07:36:32.997614+00Test create table in Master node postgres=# CREATE DATABASE test; postgres=# \c test postgres=# CREATE TABLE car (id int, name varchar(150)); postgres=# INSERT INTO car (id, name) VALUES (1, 'BMW'); postgres=# INSERT INTO car (id, name) VALUES (2, 'mustang');
For test in Slave node postgres=# \c test postgres=# \dt # To see all the tables in the test database postgres=# SELECT * FROM car; id | name ----+--------- 1 | BMW 2 | mustang (2 rows)