inchirags@gmail.com Chirag's PostgreSQL DBA Tutorial https://www.chirags.in
*************************************************************************************
* Streaming Replication of PostgreSQL 17 in Ubuntu 24.04 LTS *
*************************************************************************************
This comprehensive guide provides detailed instructions for setting up streaming replication between two PostgreSQL 17 servers on Ubuntu 24.04 LTS. We will configure one server as the primary (master) and the other as the standby (replica). The replication will be secured with the password admin@123.
Server Details:
Primary Server (Master) IP: 192.168.224.134
Standby Server (Replica) IP: 192.168.224.135
Replication User Password: admin@123
Prerequisites
Two Ubuntu 24.04 LTS Servers: One will act as the primary server, and the other as the standby server.
PostgreSQL 17 Installed: On both servers.
Root or Sudo Access: Administrative privileges to install and configure software.
Network Connectivity: Both servers should be able to communicate over the network.
Table of Contents
Install PostgreSQL 17 on Both Servers
Configure the Primary Server (Master)
Create a Replication User
Configure PostgreSQL Settings
Adjust Authentication Methods
Reload PostgreSQL Service
Configure the Standby Server (Replica)
Stop PostgreSQL Service
Clear Existing Data Directory
Take a Base Backup from Primary
Configure Recovery Settings
Start PostgreSQL on Standby Server
Verify Streaming Replication
Testing Failover (Optional)
Conclusion
Step 1: Install PostgreSQL 17 on Both Servers
Perform the following steps on both the primary and standby servers.
1.1 Update and Upgrade System Packages
sudo apt update
sudo apt upgrade -y
1.2 Add PostgreSQL Repository
To get PostgreSQL 17, we need to add the official PostgreSQL APT repository.
sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
Next, add the PostgreSQL repository:
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
1.3 Install PostgreSQL 17
After adding the repository, update the package list and install PostgreSQL 17.
sudo apt update
sudo apt -y install postgresql postgresql-client
1.4 Verify Installation
psql --version
You should see output similar to:
psql (PostgreSQL) 17.x
Step 2: Configure the Primary Server (Master)
Perform the following steps on the primary server (192.168.224.134).
2.1 Create a Replication User
Switch to the postgres user and create a replication role.
sudo -i -u postgres
psql
Inside the psql shell, run:
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'admin@123';
Exit the psql shell:
\q
exit
2.2 Configure PostgreSQL Settings
Edit the postgresql.conf file to allow replication connections and configure WAL settings.
sudo nano /etc/postgresql/17/main/postgresql.conf
Modify or add the following parameters:
# Listen on all interfaces
listen_addresses = '*'
# Set the maximum number of replication slots
max_wal_senders = 10
# Set the amount of WAL retained
wal_level = replica
# Configure WAL segments
wal_keep_size = 512MB
# Set maximum number of replication connections
max_replication_slots = 10
hot_standby = on
2.3 Adjust Authentication Methods
Edit the pg_hba.conf file to allow the standby server to connect.
sudo nano /etc/postgresql/17/main/pg_hba.conf
Add the following line at the end of the file:
# Allow replication connections from the standby server
host replication replicator 192.168.224.135/32 md5
2.4 Reload PostgreSQL Service
sudo systemctl restart postgresql
Step 3: Configure the Standby Server (Replica)(192.168.224.135)
Already Installed PostgreSQL or follow previous steps and install PostgreSQL 17.
Perform the following steps on the standby server (192.168.224.135).
3.1 Stop PostgreSQL Service
sudo systemctl stop postgresql
3.2 Copy and Clear Existing Data Directory
cp -R /var/lib/postgresql/17/main/ /var/lib/postgresql/17/main_old/
Warning: This will remove all existing data on the standby server.
sudo rm -rf /var/lib/postgresql/17/main/*
3.3 Take a Base Backup from Primary
Use pg_basebackup to clone the primary server's data directory.
su - postgres
pg_basebackup -h 192.168.224.134 -D /var/lib/postgresql/17/main/ -U replicator -P -v -R -X stream -C -S slaveslot1
-h 192.168.224.134: Primary server IP.
-D /var/lib/postgresql/17/main/: Destination directory.
-U replicator: Replication user.
When prompted for a password, enter admin@123.
Notice that standby.signal is created, standby.signal file to indicate this server is a standby.
ls -ltrh /var/lib/postgresql/17/main/
Alternatively, you can create a standby.signal file if not available:
sudo touch /var/lib/postgresql/17/main/standby.signal
3.5 Set Permissions
Ensure the PostgreSQL data directory has the correct permissions.
root@dept:/home/dept# sudo chown -R postgres:postgres /var/lib/postgresql/17/main/
root@dept:/home/dept# sudo chmod 700 /var/lib/postgresql/17/main/
Step 4: Start PostgreSQL on Standby Server
sudo systemctl start postgresql
Check the status to ensure it's running without errors:
sudo systemctl status postgresql
Step 5: Verify Streaming Replication
Perform these steps on both servers to confirm that replication is working.
5.1 On Primary Server
Check the replication status in master server:
sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"
You should see an entry for the standby server with details like state, sent_location, write_location, etc.
Example output:
pid | usesysid | usename | application_name | client_addr | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | ...
-----+----------+------------------+------------------+-----------------+---------+-----------+-----------+-----------+------------+-----
1234| 16386 | replicator | 17/main | 192.168.224.135 | streaming| ...
Exit psql:
q
5.2 If you want to enable synchronous, the run the below command on master database server and reload postgresql service.
root@dept:/home/dept# sudo -u postgres psql
SELECT * FROM pg_stat_replication;
ALTER SYSTEM SET synchronous_standby_names TO '*';
Exit the psql shell:
\q
Run the below command in root user:
root@dept:/home/dept# systemctl reload postgresql
5.3 On Standby Server
Check the replication status:
Run:
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
The output should be t, indicating the server is in recovery mode (standby).
sudo -u postgres psql -c "select * from pg_stat_wal_receiver;"
5.4 Test Data Replication
On the primary server, create a test table and insert some data.
sudo -i -u postgres
psql
Run:
CREATE DATABASE testdb;
\c testdb
CREATE TABLE replication_test (id INT PRIMARY KEY, data TEXT);
INSERT INTO replication_test VALUES (1, 'First row');
Exit psql:
\q
exit
On the standby server, verify that the data is replicated.
sudo -i -u postgres
psql
Run:
\c testdb
SELECT * FROM replication_test;
You should see the data inserted on the primary server.
Step 6: Testing Failover (Optional)
Note: This step is optional and should be performed with caution, preferably in a testing environment.
6.1 Simulate Primary Server Failure
On the primary server, stop PostgreSQL:
sudo systemctl stop postgresql
6.2 Promote Standby Server to Primary
On the standby server, promote it to primary:
sudo -i -u postgres
pg_ctlcluster 17 main promote
Alternatively, create a trigger file:
sudo -i -u postgres
touch /var/lib/postgresql/17/main/promote.signal
6.3 Verify Promotion
Check that the standby server is now the primary:
psql
Run:
SELECT pg_is_in_recovery();
The output should be f, indicating it's no longer in recovery mode.
6.4 Reconfigure Original Primary as Standby
To reintegrate the original primary server as a standby, you would need to:
Reconfigure it as a standby.
Perform a base backup from the new primary.
Follow similar steps as configuring the standby server.
Conclusion
You have successfully set up streaming replication between two PostgreSQL 17 servers on Ubuntu 24.04 LTS. The standby server now continuously replicates data from the primary server, providing high availability and data redundancy.
Additional Considerations
Monitoring Replication Lag: Regularly monitor replication lag to ensure data is being replicated timely.
WAL Archiving: Consider setting up WAL archiving for point-in-time recovery.
Synchronous Replication: For zero data loss, configure synchronous replication, though it may impact performance.
Security: Ensure that network connections are secured, possibly using SSL/TLS for encryption.
References
PostgreSQL Official Documentation
Understanding Streaming Replication
Let me know if you need further assistance or explanations on any of the steps!
For any doubts and query, please write on YouTube video comments section.
Note : Flow the Process shown in video.
Please, Subscribe and like for more videos:
https://youtube.com/@chiragstutorial
Don't forget to, Follow, Like, Share &, Comment
Tutorial Link :
https://www.chirags.in/tutorials/PostgreSQL_Database
Thanks & Regards,
Chitt Ranjan Mahto "Chirag"
_________________________________________________________________________________________
Note: All scripts used in this demo will be available in our website.
Link will be available in description.
