Introduction
Streaming replication in PostgreSQL allows a standby server to stay up-to-date with the primary server in real-time or near real-time. Here’s a step-by-step guide to set up streaming replication in PostgreSQL 14:
PostgreSQL Master name and IP address:
Hostname : PGmaster
IP Address : 192.168.0.210
PostgreSQL Slave/Replica name and IP address:
Hostname : PGSlave
IP Address : 192.168.0.211
On Master and Slave servers, PostgreSQL 14 must have installed.
Lets Start:
Step1: Configurations on master server
1.1. On master server, configure the IP address(es) listen to for connections from clients in postgresql.conf
by removing # in front of listen_address and give *. Which means listen connections from all.
Allow Connections from Any IP Address:
- By setting
listen_addressesto'*', PostgreSQL allows connections from any IP address. This is particularly useful in scenarios where your database server needs to accept connections from various clients on different networks.
|
1 2 3 |
listen_addresses = '*' |
1.2. Now, connect to PostgreSQL on master server and create replica login.
|
1 2 3 |
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'admin@123'; |
1.3. Enter the following entry pg_hba.conf file which is located in /etc/postgresql/14/main on Ubuntu(debian systems).
host:- Indicates that this line defines a host-based access rule.
replication:- Specifies the database for which this rule applies. In this case, it’s for replication, meaning it’s controlling access for replication connections.
replicator:- Specifies the user that is allowed to connect for replication. In PostgreSQL, each database role (user) has specific privileges, and in replication scenarios, a dedicated user, often named “replicator,” is commonly used.
192.168.0.211/24:- Specifies the IP address range that is allowed to connect. In this case, it’s
192.168.0.211with a subnet mask of/24, meaning it includes all IP addresses in the range192.168.0.0to192.168.0.255. Only connections from this IP range will be accepted.
- Specifies the IP address range that is allowed to connect. In this case, it’s
md5:- Specifies the authentication method to be used. In this context, it’s set to
md5, indicating that password-based authentication using an MD5-hashed password is required. The connecting client needs to provide a valid username and password.
- Specifies the authentication method to be used. In this context, it’s set to
|
1 2 3 |
host replication replicator 192.168.0.211/24 md5 |
1.4. Now, restart the PostgreSQL on Master server by using below command.
|
1 2 3 |
sudo systemctl restart postgresql |
Step2: Configurations on slave(standby) server
2.1. We have to stop PostgreSQL on Slave server by using following command.
|
1 2 3 |
sudo systemctl stop postgresql |
2.2. Now, switch to postgres user and take backup of main(data) directory.
|
1 2 3 4 |
su - postgres cp -R /var/lib/postgresql/14/main/ /var/lib/postgresql/14/main_old/ |
2.3. Now, remove the contents of main(data) directory on slave server.
|
1 2 3 |
rm -rf /var/lib/postgresql/14/main/ |
2.4. Now, use basebackup to take the base backup with the right ownership with postgres(or any user with right permissions).
|
1 2 3 |
pg_basebackup -h 192.168.0.210 -D /var/lib/postgresql/14/main/ -U replicator -P -v -R -X stream -C -S slaveslot1 |
-h 192.168.0.210:- Specifies the hostname or IP address of the PostgreSQL server from which to take the base backup. In this case, it’s set to
192.168.0.210.
- Specifies the hostname or IP address of the PostgreSQL server from which to take the base backup. In this case, it’s set to
-D /var/lib/postgresql/14/main/:- Specifies the target directory where the base backup will be stored. In this example, it’s set to
/var/lib/postgresql/14/main/. This should be an empty directory, aspg_basebackupwill populate it with the necessary files.
- Specifies the target directory where the base backup will be stored. In this example, it’s set to
-U replicator:- Specifies the username (role) to use when connecting to the PostgreSQL server. In this case, it’s set to
replicator. This is likely the user with replication privileges.
- Specifies the username (role) to use when connecting to the PostgreSQL server. In this case, it’s set to
-P:- Indicates that
pg_basebackupshould prompt for a password. This is necessary when connecting to the PostgreSQL server, especially when the-Uflag is used.
- Indicates that
-v:- Enables verbose mode, providing more detailed information about the backup process.
-R:- Enables the creation of a replication slot during the backup. A replication slot is used to track the changes made to the database, making it useful for setting up streaming replication.
-X stream:- Specifies the method of transferring data during the backup. In this case, it’s set to
stream, indicating that the backup should be performed using streaming replication.
- Specifies the method of transferring data during the backup. In this case, it’s set to
-C:- Specifies that the backup should be taken in a consistent state, meaning it will not include any changes made to the database during the backup process.
-S slaveslot1:- Specifies the name of the replication slot to be created. Replication slots are used to keep track of the changes sent to replicas in streaming replication setups. In this case, a slot named
slaveslot1is created.
- Specifies the name of the replication slot to be created. Replication slots are used to keep track of the changes sent to replicas in streaming replication setups. In this case, a slot named
In summary, this pg_basebackup command is creating a base backup of a PostgreSQL cluster running on the server with IP 192.168.0.210. The backup will be stored in the directory /var/lib/postgresql/14/main/. It uses the replicator user for authentication, prompts for the user’s password, and sets up a replication slot named slaveslot1 for streaming replication. The backup is taken in a consistent state (-C) and is transferred using streaming replication (-X stream). The -R flag ensures that a replication slot is created during the backup process.
Note : Then provide the password for user replicator created in master server.
|
1 2 3 4 5 6 |
pg_basebackup: initiating base backup, waiting for checkpoint to complete .................................... pg_basebackup: syncing data to disk ... pg_basebackup: base backup completed |
2.5. Notice that standby.signal is created and the connection settings are appended to postgresql.auto.conf.
|
1 2 3 |
ls -ltrh /var/lib/postgresql/14/main/ |
2.6. postgresql.conf and there is a standby.signal file present in the data directory.
2.7. Now connect the master server, you should be able to see the replication slot called slotslave1 when you open the pg_replication_slots view as follows.
|
1 2 3 |
SELECT * FROM pg_replication_slots; |
Step3. Test replication setup
3.1. Now start PostgreSQL on slave(standby) server.
|
1 2 3 |
systemctl start postgresql |
3.2. Now, try to create object or database in slave(standby) server. It throws error, because slave(standby) is read-only server.
|
1 2 3 |
create database slave1; |
3.3. WE can check the status on standby using below command.
|
1 2 3 |
SELECT * FROM pg_stat_wal_receiver; |
3.4. Now, verify the replication type synchronous or aynchronous using below command on master database server.
|
1 2 3 |
SELECT * FROM pg_stat_replication; |
3.5. Lets create a database in master server and verify its going to replicate to slave or not.
|
1 2 3 |
create database stream; |
3.6. Now, connect to slave and verify the database copied or not.
|
1 2 3 |
select datname from pg_database; |
3.7. If you want to enable synchronous, the run the below command on master database server and reload postgresql service.
|
1 2 3 4 5 |
ALTER SYSTEM SET synchronous_standby_names TO '*'; systemctl reload postgresql |
Thats all. We have successfully setup streaming replication in PostgreSQL step by step on Ubuntu.
Post-Setup
- Test the Replication:
-
- Create some test data on the primary and ensure it replicates to the standby.
- Check the replication status with
pg_stat_replicationon the primary.
- Monitoring and Maintenance:
-
- Regularly monitor replication lag and the health of both servers.
- Plan for failover and backup strategies.
Considerations
- Network Configuration: Ensure reliable network connectivity between the primary and standby servers.
- Firewall Settings: Adjust firewall settings to allow required connections.
- Version Compatibility: Use the same PostgreSQL version on both primary and standby servers.
- Security: Use strong passwords and consider using SSL connections for replication.
- Data Integrity: Regularly validate the data integrity on the standby server.
This setup provides a basic streaming replication configuration. Depending on your environment and requirements, you might need to consider additional configurations like synchronous replication, multiple standbys, or using replication slots. Always refer to the PostgreSQL documentation for detailed information and best practices.