inchirags@gmail.com Chirag's PostgreSQL DBA Tutorial https://www.chirags.in
PgDog + PostgreSQL 17 High-Availability (HA) on Ubuntu 24.04 LTS
Step-by-step tutorial for setting up PgDog + PostgreSQL 17 High-Availability (HA) on Ubuntu 24.04 LTS
🧭 Architecture
Role IP Address Description
Primary Server 192.168.136.129 PostgreSQL 17 Primary Server
Replica Server 192.168.136.130 PostgreSQL 17 Replica Server
Default password everywhere: admin@123
Default password everywhere: admin@123
🐕 PgDog + PostgreSQL 17 — 2 Node HA Setup (Ubuntu 24.04)
- System Preparation
Run on both servers:
sudo apt update && sudo apt upgrade -y
sudo apt install -y wget curl gnupg2 lsb-release build-essential git
- Install PostgreSQL 17
Add PostgreSQL repo
sudo sh -c 'echo "deb [signed-by=/etc/apt/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ noble-pgdg main" > \
/etc/apt/sources.list.d/pgdg.list'
Create a Keyring for PostgreSQL:
sudo mkdir -p /etc/apt/keyrings
Download the PostgreSQL Signing Key:
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/keyrings/postgresql.gpg
sudo apt update
sudo apt install -y postgresql-17 postgresql-client-17
Set postgres password:
sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 'admin@123';"
Enable and start PostgreSQL:
sudo systemctl enable postgresql
sudo systemctl start postgresql
- Configure PostgreSQL for Replication
On both servers, edit postgresql.conf:
sudo nano /etc/postgresql/17/main/postgresql.conf
Set:
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10
On Primary (192.168.136.129) and replica (192.168.136.130), edit pg_hba.conf:
sudo nano /etc/postgresql/17/main/pg_hba.conf
Add:
host replication postgres 192.168.136.0/24 md5
host all postgres 192.168.136.0/24 md5
Restart PostgreSQL:
sudo systemctl restart postgresql
- Install PgDog
On both servers:
Install Dependencies (on both nodes)
sudo apt update && sudo apt install -y \
cmake clang curl pkg-config libssl-dev git build-essential
PgDog is written in Rust, so you'll also need Rust installed:
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh
source $HOME/.cargo/env
Clone the Official Repository:
git clone https://github.com/pgdogdev/pgdog.git
cd pgdog
Build PgDog from Source:
cargo build --release
Check if pgdog binary exists:
Run:
ls -l /usr/local/bin/pgdog
- If you see No such file, you need to copy it:
sudo cp /home/dept/pgdog/target/release/pgdog /usr/local/bin/
sudo chmod +x /usr/local/bin/pgdog
-
If the file exists, make sure it is executable:
sudo chmod +x /usr/local/bin/pgdog
Create /etc/pgdog/pgdog.toml:
mkdir -p /etc/pgdog/
sudo nano /etc/pgdog/pgdog.toml
Paste this for your 2-node cluster:
[general]
host = "0.0.0.0"
port = 6432
[[databases]]
name = "postgres"
host = "192.168.136.129"
port = 5432
role = "primary"
[[databases]]
name = "postgres"
host = "192.168.136.130"
port = 5432
role = "replica"
Create /etc/pgdog/users.toml:
sudo nano /etc/pgdog/users.toml
Paste:
[[users]]
name = "postgres"
database = "postgres"
password = "admin@123"
- Configure PgDog
Create config:
sudo nano /etc/pgdog/pgdog.conf
On Primary (192.168.136.129)
[node]
name = "node1"
ip = "192.168.136.129"
port = 5432
role = "primary"
user = "postgres"
password = "admin@123"
[cluster]
nodes = [
{ name="node1", ip="192.168.136.129", port=5432 },
{ name="node2", ip="192.168.136.130", port=5432 }
]
On Replica (192.168.136.130)
[node]
name = "node2"
ip = "192.168.136.130"
port = 5432
role = "standby"
user = "postgres"
password = "admin@123"
[cluster]
nodes = [
{ name="node1", ip="192.168.136.129", port=5432 },
{ name="node2", ip="192.168.136.130", port=5432 }
]
Test PgDog manually:
Before using systemd, confirm it runs:
/usr/local/bin/pgdog --config /etc/pgdog/pgdog.toml --users /etc/pgdog/users.toml
- If it starts and prints logs → binary works ✅
The compiled binary will be in target/release/pgdog.
This is the recommended method for production deployment.
Create a systemd Service:
Create a service file:
sudo nano /etc/systemd/system/pgdog.service
Paste:
[Unit]
Description=PgDog PostgreSQL HA Proxy
After=network.target postgresql.service
[Service]
ExecStart=/usr/local/bin/pgdog --config /etc/pgdog/pgdog.toml --users /etc/pgdog/users.toml
Restart=always
User=postgres
Group=postgres
[Install]
WantedBy=multi-user.target
Enable + start:
sudo systemctl daemon-reexec
sudo systemctl enable pgdog
sudo systemctl start pgdog
sudo systemctl status pgdog
- Initialize Replica
On Replica (192.168.136.130):
sudo systemctl stop postgresql
rm -rf /var/lib/postgresql/17/main/*
sudo -u postgres pg_basebackup -h 192.168.136.129 -D /var/lib/postgresql/17/main -U postgres -Fp -Xs -P -R -W
password: admin@123
sudo systemctl start postgresql
- Restart PgDog
On both servers:
sudo systemctl restart pgdog
- Normal Connection Test
From any client (even server1 itself):
psql -h 192.168.136.129 -p 6432 -U postgres -d postgres
You should get a postgres=# prompt.
Check which node you are connected to:
SELECT inet_server_addr() AS server, pg_is_in_recovery() AS is_replica;
If is_replica = f → you’re on the primary.
If is_replica = t → you’re on the standby.
- Failover Test
On the primary (192.168.136.129):
sudo systemctl stop postgresql
Now reconnect via PgDog proxy:
psql -h 192.168.136.129 -p 6432 -U postgres -d postgres
Then run:
SELECT inet_server_addr() AS server, pg_is_in_recovery() AS is_replica;
👉 You should now be connected to 192.168.136.130 with is_replica = f (because it got promoted).
- Recovery / Switchover
Start the old primary again:
sudo systemctl start postgresql
PgDog should see it come back online.
For any doubts and query, please write on YouTube video 📽️ comments section.
Note : Flow the Process shown in video 📽️.
😉Please Subscribe for more videos:
https://www.youtube.com/@chiragtutorial
💛Don't forget to, 💘Follow, 💝Like, Share 💙&, Comment
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.
Top comments (0)