Skip to content
My Docs
Blog

Basic postgresql set up

Architecture

MachineIp AddressRole
postgres110.1.0.101Postgres, Patroni, HAProxy, Etcd
postgres210.1.0.102Postgres, Patroni
postgres310.1.0.103Postgres, Patroni

Install etcd:

Terminal window
sudo mkdir -p /db/etcd
sudo chown -R postgres:postgres /db/etcd


ETCD_VER=v3.3.18
GOOGLE_URL=https://storage.googleapis.com/etcd
DOWNLOAD_URL=${GOOGLE_URL}
curl -L ${DOWNLOAD_URL}/${ETCD_VER}/etcd-${ETCD_VER}-linux-amd64.tar.gz -o etcd-${ETCD_VER}-linux-amd64.tar.gz
tar xzvf etcd-${ETCD_VER}-linux-amd64.tar.gz
sudo cp etcd-${ETCD_VER}-linux-amd64/etcd* /usr/local/bin
rm -rf etcd-${ETCD_VER}-linux-amd64.tar.gz etcd-${ETCD_VER}-linux-amd64/

Install packages:

  • Install postgresql-15, patroni, etcd on all nodes:

    Terminal window
    sudo apt install dirmngr ca-certificates software-properties-common apt-transport-https lsb-release curl -y
    curl -fSsL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /usr/share/keyrings/postgresql.gpg > /dev/null
    echo deb [arch=amd64,arm64,ppc64el signed-by=/usr/share/keyrings/postgresql.gpg] http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main | sudo tee /etc/apt/sources.list.d/postgresql.list
    sudo apt update -y
    sudo apt install -y postgresql-client-15 postgresql-15
    sudo systemctl stop postgresql
    sudo apt install -y python3-pip python3-testresources python3-psycopg2 net-tools
    sudo pip3 install --upgrade setuptools
    sudo pip3 install patroni python-etcd

  • (Optional) Install haproxy on haproxy node:

    Terminal window
    sudo apt install net-tools
    sudo apt -y install haproxy

Configuring:

Etcd on node postgres1

/etc/default/etcd
ETCD_NAME=postgres1
ETCD_INITIAL_CLUSTER="postgres1=http://10.1.0.101:2380,postgres2=http://10.1.0.102:2380,postgres3=http://10.1.0.103:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.1.0.101:2380"
ETCD_LISTEN_PEER_URLS="http://10.1.0.101:2380"
ETCD_LISTEN_CLIENT_URLS="http://localhost:2379,http://10.1.0.101:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://10.1.0.101:2379"
  • Start the etcd service to apply the changes on node postgres1
Terminal window
sudo systemctl enable --now etcd
sudo systemctl start etcd
sudo systemctl status etcd
  • Check the etcd cluster members on node postgres1
Terminal window
sudo etcdctl member list

Etcd on node postgres2

/etc/default/etcd
ETCD_NAME=postgres2
ETCD_INITIAL_CLUSTER="postgres1=http://10.1.0.101:2380,postgres2=http://10.1.0.102:2380,postgres3=http://10.1.0.103:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.1.0.102:2380"
ETCD_LISTEN_PEER_URLS="http://10.1.0.102:2380"
ETCD_LISTEN_CLIENT_URLS="http://localhost:2379,http://10.1.0.102:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://10.1.0.102:2379"
/etc/etcd.conf
name: postgres1
data-dir: /db/etcd
initial-advertise-peer-urls: http://10.1.0.101:2380
listen-peer-urls: http://10.1.0.101:2380
listen-client-urls: http://10.1.0.101:2379
advertise-client-urls: http://10.1.0.101:2379
initial-cluster: "postgres1=http://10.1.0.101:2380,postgres2=http://10.1.0.102:2380,postgres3=http://10.1.0.103:2380"
/etc/etcd.conf
name: postgres2
data-dir: /db/etcd
initial-advertise-peer-urls: http://10.1.0.102:2380
listen-peer-urls: http://10.1.0.102:2380
listen-client-urls: http://10.1.0.102:2379
advertise-client-urls: http://10.1.0.102:2379
initial-cluster: "postgres1=http://10.1.0.101:2380,postgres2=http://10.1.0.102:2380,postgres3=http://10.1.0.103:2380"
/etc/etcd.conf
name: postgres3
data-dir: /db/etcd
initial-advertise-peer-urls: http://10.1.0.103:2380
listen-peer-urls: http://10.1.0.103:2380
listen-client-urls: http://10.1.0.103:2379
advertise-client-urls: http://10.1.0.103:2379
initial-cluster: "postgres1=http://10.1.0.101:2380,postgres2=http://10.1.0.102:2380,postgres3=http://10.1.0.103:2380"

Etcd on node postgres3

/etc/default/etcd
ETCD_NAME=postgres3
ETCD_INITIAL_CLUSTER="postgres1=http://10.1.0.101:2380,postgres2=http://10.1.0.102:2380,postgres3=http://10.1.0.103:2380"
ETCD_INITIAL_CLUSTER_STATE="new"
ETCD_INITIAL_CLUSTER_TOKEN="etcd-cluster"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.1.0.103:2380"
ETCD_LISTEN_PEER_URLS="http://10.1.0.103:2380"
ETCD_LISTEN_CLIENT_URLS="http://localhost:2379,http://10.1.0.103:2379"
ETCD_ADVERTISE_CLIENT_URLS="http://10.1.0.103:2379"

Patroni on node postgres1

/etc/patroni.yml
scope: postgres
name: postgres1


restapi:
    listen: 10.1.0.101:8008
    connect_address: 10.1.0.101:8008


etcd:
    host: 10.1.0.101:2379, 10.1.0.102:2379, 10.1.0.103:2379


bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:


  initdb:
  - encoding: UTF8
  - data-checksums


  pg_hba:
  - host replication replicator 127.0.0.1/32 md5
  - host replication replicator 10.1.0.101/0 md5
  - host replication replicator 10.1.0.102/0 md5
  - host replication replicator 10.1.0.103/0 md5
  - host all all 0.0.0.0/0 md5


  users:
    admin:
      password: admin
      options:
        - createrole
        - createdb


postgresql:
  listen: 10.1.0.101:5432
  connect_address: 10.1.0.101:5432
  data_dir: /var/lib/postgresql/15/main
  bin_dir: /usr/lib/postgresql/15/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: replicator
    superuser:
      username: postgres
      password: postgres
  parameters:
      unix_socket_directories: '.'


tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
Terminal window
sudo mkdir -p /data/patroni
sudo chown postgres:postgres /data/patroni
sudo chmod 700 /data/patroni

Patroni on node postgres2

/etc/patroni.yml
scope: postgres
name: postgres2


restapi:
    listen: 10.1.0.102:8008
    connect_address: 10.1.0.102:8008


etcd:
    host: 10.1.0.101:2379, 10.1.0.102:2379, 10.1.0.103:2379


bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    postgresql:
      use_pg_rewind: true
      use_slots: true
      parameters:


  initdb:
  - encoding: UTF8
  - data-checksums


  pg_hba:
  - host replication replicator 127.0.0.1/32 md5
  - host replication replicator 10.1.0.101/0 md5
  - host replication replicator 10.1.0.102/0 md5
  - host replication replicator 10.1.0.103/0 md5
  - host all all 0.0.0.0/0 md5


  users:
    admin:
      password: admin
      options:
        - createrole
        - createdb


postgresql:
  listen: 10.1.0.102:5432
  connect_address: 10.1.0.102:5432
  data_dir: /var/lib/postgresql/15/main
  bin_dir: /usr/lib/postgresql/15/bin
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: bae0-a5db-4068-ac4
    superuser:
      username: postgres
      password: 31db-20f4-48d2-8b4
  parameters:
      unix_socket_directories: '.'


tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false
/etc/systemd/system/patroni.service
[Unit]
Description=High availability PostgreSQL Cluster
After=syslog.target network.target


[Service]
Type=simple
User=postgres
Group=postgres
ExecStart=/usr/local/bin/patroni /etc/patroni.yml
KillMode=process
TimeoutSec=30
Restart=no


[Install]
WantedBy=multi-user.targ
Terminal window
sudo systemctl start patroni
sudo systemctl status patroni
  • Config HAProxy on node haproxy:
/etc/haproxy/haproxy.cfg
global


        maxconn 100
        log     127.0.0.1 local2


defaults
        log global
        mode tcp
        retries 2
        timeout client 30m
        timeout connect 4s
        timeout server 30m
        timeout check 5s


listen stats
    mode http
    bind *:7000
    stats enable
    stats uri /


listen postgres
    bind *:5000
    option httpchk
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgres1 10.1.0.101:5432 maxconn 100 check port 8008
    server postgres2 10.1.0.102:5432 maxconn 100 check port 8008
    server postgres3 10.1.0.103:5432 maxconn 100 check port 8008


listen postgres-replica
    bind *:5001
    option httpchk GET /replica
    http-check expect status 200
    default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
    server postgres1 10.1.0.101:5432 maxconn 100 check port 8008
    server postgres2 10.1.0.102:5432 maxconn 100 check port 8008
    server postgres3 10.1.0.103:5432 maxconn 100 check port 8008
Terminal window
sudo systemctl restart haproxy
  • Test
Terminal window
patronictl -c /etc/patroni.yml list

Pooling connection with pgBouncer

Terminal window
COPY (
SELECT '"' || rolname || '" "' ||
coalesce(rolpassword, '') || '"'
FROM pg_authid
)
TO '/tmp/userlist.txt';
  • Install
Terminal window
sudo apt install -y pgbouncer
/etc/pgbouncer/pgbouncer.ini
[databases]
* = host=10.1.0.101 port=5432


[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
admin_users = postgres
auth_file = /etc/pgbouncer/userlist.txt
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5

Migration guide:

  • First install all necessary packages by following the above steps
  • Mount the stackgres data disk to the node
Terminal window
sudo rm -rf /var/lib/postgresql/15/main
sudo mount /dev/sdb /var/lib/postgresql/15
sudo chown -R postgres:postgres /var/lib/postgresql/15
sudo mv /var/lib/postgresql/15/data /var/lib/postgresql/15/main
sudo chmod 700 /var/lib/postgresql/15/main