prerequisite :

1. Ensure internet connectivity is established for the installation of PostgreSQL.
2. Nano or vim is required for editing config files 
3. PostgreSQL, version 12, is to be installed on both nodes.
4. Administrative privileges (sudo access) are required for modifying configuration files.
5. Temporarily disable the firewall to facilitate the necessary configurations.
6. Root access is essential for instances where deletion of slave data files is necessary.

master slave hardware specs

we will be implement master-slave streaming replication using two VMS running ubuntu 22 jammy
both node will have PostgreSQL 12 installed , and configured with ip in same subnet
below are VM details .
1.postgresqlDB01 : 10.10.10.77
2.postgresqlDB02 : 10.10.10.78

installing PostgreSQL

first check if PostgreSQL 12 is available in Ubuntu repositories using the below command
apt-cache madison postgresql-12 

If PostgreSQL 12 isn’t available in our current repository, the next step involves adding the PostgreSQL 12 repository to our Ubuntu machine. This requires importing the GPG key and then adding the repository. To accomplish this, we’ll use the following command.
Note: Internet access on the VM is a prerequisite for these steps

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

next update the ubuntu repository to add links for PostgreSQL 12

echo “deb http://apt.postgresql.org/pub/repos/apt/ lsb_release -cs-pgdg main” |sudo tee /etc/apt/sources.list.d/pgdg.list

We’ve successfully updated the repository. Now, we’ll proceed with the installation of PostgreSQL 12. The following command will be used for this purpose

sudo apt update sudo apt -y install postgresql-12 postgresql-client-12

once the installation is completed , next steps is to verify if the PostgreSQL services are running.

systemctl status postgresql 

Configuring Master

Now we will create a database user “replication” who will carry out the task of replication.
switch to postgres user that will be automatically added to ubuntu once we installed PostgreSQL

su - postgres

if you are unable to switch to postgres user then reset its password using the below command 

sudo passwd postgres

then enter PostgreSQL t-sql configuration suing psql

create a database user “replication” who will carry out the task of replication using the below command , also note down the password and username you will need it in the slave configuration 

CREATE USER replication REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD '123456789';

check if the role is created successfully by using the below command 

\du 

Next, we’ll adjust the maximum number of connections permitted for the replication user. This is done by executing the following command within the psql client.

ALTER ROLE replication CONNECTION LIMIT -1;

The configuration files for PostgreSQL are typically found in the following directory:

/etc/postgresql/12/main

We need to modify a file named postgresql.conf to configure this server as the master. While both nano and vim are suitable editors for this task, I will be using nano for this purpose
Note : Make sure to use a user account with sudo privileges or the root account for these steps, as administrative access is required to edit the postgresql.conf file.
sudo nano /etc/postgresql/12/main/postgresql.conf 

Edit the following parameter in the postgresql.conf file. If the line is currently commented out, uncomment it to activate the option
you can use Ctrl+w to search and go to desire line 

listen_addresses = ‘*’
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64

once done click Ctrl+x then y , then hit enter

now Slave server need authentication for replication. Now append following line to/etc/postgresql/12/main/pg_hba.conf file
sudo nano /etc/postgresql/12/main/pg_hba.conf
# Replace 10.10.10.78 with slave server's private IP host replication replication 10.10.10.78/24 md5

next steps is to restart postgresql services 

systemctl restart postgresql systemctl status postgresql 

We have finished all configuration in Master Server and our master server is ready for replication

Configuring Slave

for the slave its mandatory to first stop PostgreSQL services 

systemctl stop postgresql systemctl status postgresql 

next eddit the config file with the below parameter
sudo nano /etc/postgresql/12/main/postgresql.conf

listen_addresses = 'localhost,158.245.203.119' wal_level = replica max_wal_senders = 10 wal_keep_segments = 64 hot_standby = on

Now append following line to /etc/postgresql/12/main/pg_hba.conf file

sudo nano /etc/postgresql/12/main/pg_hba.conf

# Replace 10.10.10.77 with slave master’s private IP host replication replication 10.10.10.77/24 md5

For the next step, we need to remove the slave data directory. This task requires root privileges, so ensure you switch to the root user before proceeding

cd /var/lib/postgresql/12/main/ sudo rm -rfv *

Now, we’ll synchronize the slave database with the master database by executing the following command. This will transfer all the data from the master to the slave

sudo su postgres cd /var/lib/postgresql/12/main/ pg_basebackup -h 10.10.10.77 -U replication -p 5432 -D /var/lib/postgresql/12/main/ -Fp -Xs -P -R

As mentioned earlier, remember the password set for the replication user we created. In the following step, you will be prompted to enter the password for this replication use

Once the fetching process is complete, proceed to start the PostgreSQL service

systemct start postgresql 

Congratulations, you have successfully replicated your database! You can verify this by making any change in the master database and observing that it gets immediately replicated in the slave database

test master slave configuration

first will create database called Test DB;
then I will create table, insert random values then we will check the slave if the data is replication
To proceed with your plan:

1. Create Database TestDB:
   • First, create a database named TestDB. Use the following SQL command:CREATE DATABASE TestDB;
2. Create a Table and Insert Random Values:
   • After creating TestDB, switch to this database:\c TestDB
   • Then, create a table. Let’s say you create a table named example_table:CREATE TABLE example_table ( id SERIAL PRIMARY KEY, data VARCHAR(100) );
   • Next, insert some random values into example_table:INSERT INTO example_table (data) VALUES (‘RandomValue1’); INSERT INTO example_table (data) VALUES (‘RandomValue2’); INSERT INTO example_table (data) VALUES (‘RandomValue3’);
3. Check Replication on the Slave:
   • Finally, on the slave server, check if the data has been replicated. You can do this by querying the same table on the slave server:SELECT * FROM example_table;

If the slave is properly replicating data from the master, you should see the same rows that you inserted on the master.

monitoring replication

We can verify the replication status by using the following command. If the state displays ‘streaming’, it indicates that everything is functioning correctly

SELECT * FROM pg_stat_replication;