Deploy Pigsty on Proxmox VE

Experimental

Early seedling — a raw idea, just planted.

·
Developing

Growing — being actively worked on.

·
Understanding

Maturing — deepening insight on the topic.

·
Sharing

Ready to share — polished enough for others.

·
Final

Evergreen — considered complete.

Pigsty seems to be a great way to self deploy postgresql. However their v4 docs seem to be overly complex.

This is a WIP document documenting my steps and learning and trying to make it more reproducible.

It also doesn't cover all possible options and is opiniated based on our own use-case. Always conduct latest official documentation for all options.

Requirements:

  • at least 4 ssh accesible bare Linux OS x86_64 or aarch64 nodes (physical machines bare metal or VMs) (1 INFRA, 3 WORKER)
  • one of 14 supported maintream distributions, use recommendations first. I do prefer ubuntu, than debian, than rocky.
  • all nodes use the same Linux distribution, architecture, and version.
  • at least 1 core and 2 gig ram (no upper limit, parameters are automaticly tuned based on available ressources)
  • xfs file system
  • /data owned by root:root with 755
  • attional disks mounted as /data, /dataN
  • static network with fixed internal IPv4 address
  • outbund internet access
  • locale set to en_US with collation to C.UTF-8 (PostgreSQL logs are in English)

First, prepare hardware, nodes, disks, network, VIP, domain, software, and filesystems “Preparation”

Steps:

  1. Create VMs (e.g. 2c8ram)

  2. Install OS with fixed internal IPv4

  3. Update OS (make sure tar & acl installed)

  1. Install pigsty on INFRA node

    Terminal window
    curl -fsSL https://repo.pigsty.io/get | bash;
    cd ~/pigsty

  2. Configure deployment needs manual adjustment

    _Pigsty provides a configure script as a configuration wizard that automatically generates an appropriate pigsty.yml configuration file based on your current environment.

    This is an optional script: if you already understand how to configure Pigsty, you can directly edit the pigsty.yml configuration file and skip the wizard._

    Terminal window
    ./configure -s -g -c ha/full
    sudo vi pigsty.yml

    Edit IP addresses, Network Interfaces, PG Cluster & PG Database

  1. Check admin requirements

    Terminal window
    ./node.yml -k -K -t node_admin -e ansible_user=admin -e node_admin_username=pigsty-admin node_admin_uid=888

  1. Deploy to VMs

    Terminal window
    ./deploy.yml
    # ./deploy.yml -k -K

  2. Access Endpoints

  3. (Optional) configure custom domain

  4. Use Service to access PG

    • Read-write service (primary): Write data: Only carried by the primary.
    • Read-only service (replica): Read data: Can be carried by replicas, but can also be carried by the primary if no replicas are available
    • Default direct access service (default): Service that allows (admin) users to bypass the connection pool and directly access the database
    • Offline replica service (offline): Dedicated replica that doesn’t handle online read-only traffic, used for ETL and analytical queries
    • Synchronous replica service (standby): Read-only service with no replication delay, handled by synchronous standby/primary for read-only queries
    • Delayed replica service (delayed): Access older data from the same cluster from a certain time ago, handled by delayed replicas

    https://pigsty.io/docs/pgsql/service/#default-service.

Notes: