PgDog + PostgreSQL 17 High-Availability (HA) on Ubuntu 24.04 LTS #pgdog #postgresqltutorial #postgres

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)

1. 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

2. 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

3. 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

4. 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"

5. 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

6. 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

7. Restart PgDog

On both servers:

sudo systemctl restart pgdog

8. 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.

9. 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).

10. 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.

EndFragment