Search Knowledge Base Articles

PostgreSQL Database Replication

Primary Server

Setup Firewalls to allow connectivity 

sudo ufw allow from <standbyIP> to any port <port> proto <protocol:-tcp>
sudo ufw reload

Test from <standbyIP>

telnet <primaryserver> <port> # Must established connection

Create replicator user with replication features.

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'Passwrrd';

Modify main configuration file

wal_level=logical

Established which hosts are allowed to connect for replication 

nano <postgres>/pg_hba.conf 

# Set Client authentication method. SSL is preferred 
hostssl replication replicator <standbyip>/32 md5

Standby Server

Create a backup into the $PGDATA directory for postgres

pg_basebackup-h $PRIMARY_HOST -U $PRIMARY_USER -D $STANDBYDIR -P-R-v-Xstream

-R: The option creates two files; an empty recovery configuration file called standby.signal and a primary node connection settings file called postgresql.auto.conf. The standby.signal file contains connection information about the primary node and the postgresql.auto.conf file informs your replica cluster that it should operate as a standby server.

Did you find this article useful?


Related Articles

  • oAuth2 Authentication

    https://dzone.com/articles/the-right-flow-for-the-job-which-oauth-20-flow-sho OAuth 2.0 is a well-adopted delegated authorization framework that is a...

  • KAFKA Use cases

    Data Streaming: Think of data as a fast-flowing river. We need a way to tap into that stream, harness its power, and direct it where it needs to ...

  • Starting a new Linux Ubuntu Server and secure it with UFW Firewall

    First, log in to the server using SSH as the root. 1.- Create a user with sudo capabilitiessudo adduser <name>sudo usermod -aG sudo <nam...

  • Install docker in Linux ubuntu

    #!/bin/bash # Function to check if a command is available command_exists() {     command -v "$1" >/dev/null 2>&1 } # Fu...