Setting Up Live Replication in PostgreSQL
PostgreSQL live replication ensures that data from the primary database server is continuously copied to a standby server. This blog outlines step-by-step instructions to set up replication between a primary and standby server, either on the same system or across different systems.
Step 1: Stop the Current Primary Server
Before configuring the primary server for replication, stop the server if it is already running:
```bash
bin/pg_ctl -D demo stop
```
Step 2: Configure the Primary (Demo) Server
2.1 Update postgresql.conf
Edit the PostgreSQL configuration file:
```bash
nano postgresql.conf
```
Add the following lines:
```conf
listen_addresses = '*'
port = 5432
wal_level = replica
```
These settings enable replication and allow connections from external systems.
2.2 Configure Access for Replication
Edit the pg_hba.conf file:
```bash
nano pg_hba.conf
```
Add the following line to allow replication connections:
```conf
host replication all <standby-ip>/32 trust
```
Replace <standby-ip> with the IP address of the standby server.
2.3 Restart the Primary Server
Start the primary server:
```bash
bin/pg_ctl -D demo start
```
Step 3: Create the Standby Server
3.1 Backup the Primary Server
Use pg_basebackup to create a backup of the primary server and initialize the standby directory:
```bash
bin/pg_basebackup -D standby -h <primary-ip> -p 5432 -U postgres -P -v -R --wal-method=stream
```
Replace <primary-ip> with the IP address of the primary server.
3.2 Configure the Standby Server
If -R was not used in pg_basebackup:
Create a standby.signal file to indicate the server is in standby mode:
```bash
touch standby/standby.signal
```
Change the port to avoid conflicts if both servers are on the same system:
port = 5433 else use the same port in the conf file.
Step 4: Start the Standby Server
Start the standby server:
```bash
bin/pg_ctl -D standby start
```
If you encounter permission issues, fix them with:
```bash
chmod 700 /home/cybrosys/PSQL/postgresql/standby
```
Step 5: Test the Replication Setup
5.1 On the Primary Server
If different ports used:
```bash
psql -p 5433 -d postgres
```
Run the following SQL commands:
```psql
CREATE TABLE test(id int);
INSERT INTO test VALUES (1);
```
5.2 On the Standby Server
Check if the test table exists and data is replicated:
```psql
SELECT * FROM test;
```
What Happens When the Primary Server Fails?
In the event of a primary server failure, the standby server will not automatically take over unless failover mechanisms are implemented. By default, the standby server operates in hot standby mode, serving only read-only queries. Here’s how you can handle a failure:
Manual Failover
To promote the standby server to become the new primary manually, execute the following command on the standby server:
```bash
bin/pg_ctl -D standby promote
```
This command transitions the standby server into a read-write primary server.
Automated Failover
For automated failover, consider using tools like Patroni, Pgpool-II, or repmgr. These tools monitor the health of the primary server and promote the standby server automatically in case of failure.
Conclusion
By following these steps, you can set up live replication in PostgreSQL. This ensures high availability and data redundancy, making your system more robust and reliable. Additionally, knowing how to handle primary server failures—either manually or automatically—is essential for maintaining uninterrupted database operations. Learn How to Implement Undo-Redo in Odoo with PostgreSQL to efficiently manage data changes and enhance user experience with seamless rollback and recovery options.