This is the multi-page printable view of this section. Click here to print.
This section provides step-by-step tutorials for common PostgreSQL tasks and scenarios.
If a classic 3-node HA deployment experiences simultaneous failure of two nodes (majority), the system typically cannot complete automatic failover and requires manual intervention.
First, assess the status of the other two servers. If they can be brought up quickly, prioritize recovering those two servers. Otherwise, enter the Emergency Recovery Procedure.
The Emergency Recovery Procedure assumes your admin node has failed and only a single regular database node survives. In this case, the fastest recovery process is:
If you access the cluster bypassing HAProxy, you can skip this step. If you access the database cluster through HAProxy, you need to adjust the load balancer configuration to manually direct read/write traffic to the primary.
/etc/haproxy/<pg_cluster>-primary.cfg configuration file, where <pg_cluster> is your PostgreSQL cluster name, e.g., pg-meta.listen pg-meta-primary
bind *:5433
mode tcp
maxconn 5000
balance roundrobin
# Comment out the following four health check lines
#option httpchk # <---- remove this
#option http-keep-alive # <---- remove this
#http-check send meth OPTIONS uri /primary # <---- remove this
#http-check expect status 200 # <---- remove this
default-server inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100
server pg-meta-1 10.10.10.10:6432 check port 8008 weight 100
# Comment out the other two failed machines
#server pg-meta-2 10.10.10.11:6432 check port 8008 weight 100 <---- comment this
#server pg-meta-3 10.10.10.12:6432 check port 8008 weight 100 <---- comment this
After adjusting the configuration, don’t rush to execute systemctl reload haproxy to reload. Wait until after promoting the primary, then execute together. The effect of this configuration is that HAProxy will no longer perform primary health checks (which by default use Patroni), but will directly direct write traffic to the current primary.
Log in to the target server, switch to the dbsu user, execute CHECKPOINT to flush to disk, stop Patroni, restart PostgreSQL, and execute Promote.
sudo su - postgres # Switch to database dbsu user
psql -c 'checkpoint; checkpoint;' # Two Checkpoints to flush dirty pages, avoid long PG restart
sudo systemctl stop patroni # Stop Patroni
pg-restart # Restart PostgreSQL
pg-promote # Promote PostgreSQL replica to primary
psql -c 'SELECT pg_is_in_recovery();' # If result is f, it has been promoted to primary
If you adjusted the HAProxy configuration above, you can now execute systemctl reload haproxy to reload the HAProxy configuration and direct traffic to the new primary.
systemctl reload haproxy # Reload HAProxy configuration to direct write traffic to current instance
After emergency recovery, the second priority is: Avoid Split Brain. Users should prevent the other two servers from coming back online and forming a split brain with the current primary, causing data inconsistency.
Simple approaches:
Then decide the next steps based on the specific situation:
If the other two servers have temporary failures and can be repaired to continue service, follow these steps for repair and rebuild:
After the ETCD cluster quorum is restored, it will resume work. Then start Patroni on the surviving server (current primary) to take over the existing PostgreSQL and regain cluster leadership. After Patroni starts, enter maintenance mode.
systemctl restart patroni
pg pause <pg_cluster>
On the other two instances, create the touch /pg/data/standby.signal marker file as the postgres user to mark them as replicas, then start Patroni:
systemctl restart patroni
After confirming Patroni cluster identity/roles are correct, exit maintenance mode:
pg resume <pg_cluster>
After permanent failure, first recover the ~/pigsty directory on the admin node. The key files needed are pigsty.yml and files/pki/ca/ca.key.
If you cannot retrieve or don’t have backups of these two files, you can deploy a new Pigsty and migrate the existing cluster to the new deployment via Backup Cluster.
Please regularly backup the
pigstydirectory (e.g., using Git for version control). Learn from this and avoid such mistakes in the future.
You can use the surviving node as the new admin node, copy the ~/pigsty directory to the new admin node, then start adjusting the configuration. For example, replace the original default admin node 10.10.10.10 with the surviving node 10.10.10.12:
all:
vars:
admin_ip: 10.10.10.12 # Use new admin node address
node_etc_hosts: [10.10.10.12 h.pigsty a.pigsty p.pigsty g.pigsty sss.pigsty]
infra_portal: {} # Also modify other configs referencing old admin IP (10.10.10.10)
children:
infra: # Adjust Infra cluster
hosts:
# 10.10.10.10: { infra_seq: 1 } # Old Infra node
10.10.10.12: { infra_seq: 3 } # New Infra node
etcd: # Adjust ETCD cluster
hosts:
#10.10.10.10: { etcd_seq: 1 } # Comment out this failed node
#10.10.10.11: { etcd_seq: 2 } # Comment out this failed node
10.10.10.12: { etcd_seq: 3 } # Keep surviving node
vars:
etcd_cluster: etcd
pg-meta: # Adjust PGSQL cluster configuration
hosts:
#10.10.10.10: { pg_seq: 1, pg_role: primary }
#10.10.10.11: { pg_seq: 2, pg_role: replica }
#10.10.10.12: { pg_seq: 3, pg_role: replica , pg_offline_query: true }
10.10.10.12: { pg_seq: 3, pg_role: primary , pg_offline_query: true }
vars:
pg_cluster: pg-meta
Then execute the following command to reset ETCD to a single-node cluster:
./etcd.yml -e etcd_safeguard=false -e etcd_clean=true
Follow the instructions in ETCD Reload Configuration to adjust ETCD Endpoint references.
If the surviving node doesn’t have the INFRA module, configure and install a new INFRA module on the current node. Execute the following command to deploy the INFRA module to the surviving node:
./infra.yml -l 10.10.10.12
Repair monitoring on the current node:
./node.yml -t node_monitor
./pgsql.yml -t pg_conf # Regenerate PG configuration files
systemctl reload patroni # Reload Patroni configuration on surviving node
After repairing each module, you can follow the standard expansion process to add new nodes to the cluster and restore cluster high availability.
You can define an OPTIONAL L2 VIP on a PostgreSQL cluster, provided that all nodes in the cluster are in the same L2 network.
This VIP works on Master-Backup mode and always points to the node where the primary instance of the database cluster is located.
This VIP is managed by the VIP-Manager, which reads the Leader Key written by Patroni from DCS (etcd) to determine whether it is the master.
Define pg_vip_enabled parameter as true in the cluster level to enable the VIP component on the cluster. You can also enable this configuration in the global configuration.
# pgsql 3 node ha cluster: pg-test
pg-test:
hosts:
10.10.10.11: { pg_seq: 1, pg_role: primary } # primary instance, leader of cluster
10.10.10.12: { pg_seq: 2, pg_role: replica } # replica instance, follower of leader
10.10.10.13: { pg_seq: 3, pg_role: replica, pg_offline_query: true } # replica with offline access
vars:
pg_cluster: pg-test # define pgsql cluster name
pg_users: [{ name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] }]
pg_databases: [{ name: test }]
# Enable L2 VIP
pg_vip_enabled: true
pg_vip_address: 10.10.10.3/24
pg_vip_interface: eth1
Beware that pg_vip_address must be a valid IP address with subnet and available in the current L2 network.
Beware that pg_vip_interface must be a valid network interface name and should be the same as the one using IPv4 address in the inventory.
If the network interface name is different among cluster members, users should explicitly specify the pg_vip_interface parameter for each instance, for example:
pg-test:
hosts:
10.10.10.11: { pg_seq: 1, pg_role: primary , pg_vip_interface: eth0 }
10.10.10.12: { pg_seq: 2, pg_role: replica , pg_vip_interface: eth1 }
10.10.10.13: { pg_seq: 3, pg_role: replica , pg_vip_interface: ens33 }
vars:
pg_cluster: pg-test # define pgsql cluster name
pg_users: [{ name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] }]
pg_databases: [{ name: test }]
# Enable L2 VIP
pg_vip_enabled: true
pg_vip_address: 10.10.10.3/24
#pg_vip_interface: eth1
To refresh the VIP configuration and restart the VIP-Manager, use the following command:
./pgsql.yml -t pg_vip
Citus is a PostgreSQL extension that transforms PostgreSQL into a distributed database, enabling horizontal scaling across multiple nodes to handle large amounts of data and queries.
Patroni v3.0+ provides native high-availability support for Citus, simplifying the setup of Citus clusters. Pigsty also provides native support for this.
Note: The current Citus version (12.1.6) supports PostgreSQL 16, 15, and 14, but not PostgreSQL 17 yet. There is no official ARM64 support. Pigsty extension repo provides Citus ARM64 packages, but use with caution on ARM architecture.
Pigsty natively supports Citus. See conf/citus.yml for reference.
Here we use the Pigsty 4-node sandbox to define a Citus cluster pg-citus, which includes a 2-node coordinator cluster pg-citus0 and two Worker clusters pg-citus1 and pg-citus2.
pg-citus:
hosts:
10.10.10.10: { pg_group: 0, pg_cluster: pg-citus0 ,pg_vip_address: 10.10.10.2/24 ,pg_seq: 1, pg_role: primary }
10.10.10.11: { pg_group: 0, pg_cluster: pg-citus0 ,pg_vip_address: 10.10.10.2/24 ,pg_seq: 2, pg_role: replica }
10.10.10.12: { pg_group: 1, pg_cluster: pg-citus1 ,pg_vip_address: 10.10.10.3/24 ,pg_seq: 1, pg_role: primary }
10.10.10.13: { pg_group: 2, pg_cluster: pg-citus2 ,pg_vip_address: 10.10.10.4/24 ,pg_seq: 1, pg_role: primary }
vars:
pg_mode: citus # pgsql cluster mode: citus
pg_version: 16 # citus does not have pg16 available
pg_shard: pg-citus # citus shard name: pg-citus
pg_primary_db: citus # primary database used by citus
pg_vip_enabled: true # enable vip for citus cluster
pg_vip_interface: eth1 # vip interface for all members
pg_dbsu_password: DBUser.Postgres # all dbsu password access for citus cluster
pg_extensions: [ citus, postgis, pgvector, topn, pg_cron, hll ] # install these extensions
pg_libs: 'citus, pg_cron, pg_stat_statements' # citus will be added by patroni automatically
pg_users: [{ name: dbuser_citus ,password: DBUser.Citus ,pgbouncer: true ,roles: [ dbrole_admin ] }]
pg_databases: [{ name: citus ,owner: dbuser_citus ,extensions: [ citus, vector, topn, pg_cron, hll ] }]
pg_parameters:
cron.database_name: citus
citus.node_conninfo: 'sslmode=require sslrootcert=/pg/cert/ca.crt sslmode=verify-full'
pg_hba_rules:
- { user: 'all' ,db: all ,addr: 127.0.0.1/32 ,auth: ssl ,title: 'all user ssl access from localhost' }
- { user: 'all' ,db: all ,addr: intra ,auth: ssl ,title: 'all user ssl access from intranet' }
Compared to standard PostgreSQL clusters, Citus cluster configuration has some special requirements. First, you need to ensure the Citus extension is downloaded, installed, loaded, and enabled, which involves the following four parameters:
repo_packages: Must include the citus extension, or you need to use a PostgreSQL offline package that includes Citus.pg_extensions: Must include the citus extension, i.e., you must install the citus extension on each node.pg_libs: Must include the citus extension at the first position, though Patroni now handles this automatically.pg_databases: Define a primary database that must have the citus extension installed.Second, you need to ensure the Citus cluster is configured correctly:
pg_mode: Must be set to citus to tell Patroni to use Citus mode.pg_primary_db: Must specify the name of the primary database with citus extension, named citus here.pg_shard: Must specify a unified name as the cluster name prefix for all horizontal shard PG clusters, pg-citus here.pg_group: Must specify a shard number, integers starting from zero. 0 represents the coordinator cluster, others are Worker clusters.pg_cluster: Must correspond to the combination of pg_shard and pg_group.pg_dbsu_password: Must be set to a non-empty plaintext password, otherwise Citus will not work properly.pg_parameters: Recommended to set citus.node_conninfo to enforce SSL access and require node-to-node client certificate verification.After configuration, you can deploy the Citus cluster using pgsql.yml just like a regular PostgreSQL cluster.
After defining the Citus cluster, deploy it using the pgsql.yml playbook:
./pgsql.yml -l pg-citus # Deploy Citus cluster pg-citus
Using any member’s DBSU (postgres) user, you can list the Citus cluster status with patronictl (alias: pg):
$ pg list
+ Citus cluster: pg-citus ----------+---------+-----------+----+-----------+--------------------+
| Group | Member | Host | Role | State | TL | Lag in MB | Tags |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
| 0 | pg-citus0-1 | 10.10.10.10 | Leader | running | 1 | | clonefrom: true |
| | | | | | | | conf: tiny.yml |
| | | | | | | | spec: 20C.40G.125G |
| | | | | | | | version: '16' |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
| 1 | pg-citus1-1 | 10.10.10.11 | Leader | running | 1 | | clonefrom: true |
| | | | | | | | conf: tiny.yml |
| | | | | | | | spec: 10C.20G.125G |
| | | | | | | | version: '16' |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
| 2 | pg-citus2-1 | 10.10.10.12 | Leader | running | 1 | | clonefrom: true |
| | | | | | | | conf: tiny.yml |
| | | | | | | | spec: 10C.20G.125G |
| | | | | | | | version: '16' |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
| 2 | pg-citus2-2 | 10.10.10.13 | Replica | streaming | 1 | 0 | clonefrom: true |
| | | | | | | | conf: tiny.yml |
| | | | | | | | spec: 10C.20G.125G |
| | | | | | | | version: '16' |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
You can treat each horizontal shard cluster as an independent PGSQL cluster and manage them with the pg (patronictl) command. Note that when using the pg command to manage Citus clusters, you need to use the --group parameter to specify the cluster shard number:
pg list pg-citus --group 0 # Use --group 0 to specify cluster shard number
Citus has a system table called pg_dist_node that records Citus cluster node information. Patroni automatically maintains this table.
PGURL=postgres://postgres:[email protected]/citus
psql $PGURL -c 'SELECT * FROM pg_dist_node;' # View node information
nodeid | groupid | nodename | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster | metadatasynced | shouldhaveshards
--------+---------+-------------+----------+----------+-------------+----------+-----------+-------------+----------------+------------------
1 | 0 | 10.10.10.10 | 5432 | default | t | t | primary | default | t | f
4 | 1 | 10.10.10.12 | 5432 | default | t | t | primary | default | t | t
5 | 2 | 10.10.10.13 | 5432 | default | t | t | primary | default | t | t
6 | 0 | 10.10.10.11 | 5432 | default | t | t | secondary | default | t | f
You can also view user authentication information (superuser access only):
$ psql $PGURL -c 'SELECT * FROM pg_dist_authinfo;' # View node auth info (superuser only)
Then you can use a regular business user (e.g., dbuser_citus with DDL privileges) to access the Citus cluster:
psql postgres://dbuser_citus:[email protected]/citus -c 'SELECT * FROM pg_dist_node;'
When using Citus clusters, we strongly recommend reading the Citus official documentation to understand its architecture and core concepts.
The key is understanding the five types of tables in Citus and their characteristics and use cases:
On the coordinator node, you can create distributed tables and reference tables and query them from any data node. Since 11.2, any Citus database node can act as a coordinator.
We can use pgbench to create some tables and distribute the main table (pgbench_accounts) across nodes, then use other small tables as reference tables:
PGURL=postgres://dbuser_citus:[email protected]/citus
pgbench -i $PGURL
psql $PGURL <<-EOF
SELECT create_distributed_table('pgbench_accounts', 'aid'); SELECT truncate_local_data_after_distributing_table('public.pgbench_accounts');
SELECT create_reference_table('pgbench_branches') ; SELECT truncate_local_data_after_distributing_table('public.pgbench_branches');
SELECT create_reference_table('pgbench_history') ; SELECT truncate_local_data_after_distributing_table('public.pgbench_history');
SELECT create_reference_table('pgbench_tellers') ; SELECT truncate_local_data_after_distributing_table('public.pgbench_tellers');
EOF
Run read/write tests:
pgbench -nv -P1 -c10 -T500 postgres://dbuser_citus:[email protected]/citus # Direct connect to coordinator port 5432
pgbench -nv -P1 -c10 -T500 postgres://dbuser_citus:[email protected]:6432/citus # Through connection pool, reduce client connection pressure
pgbench -nv -P1 -c10 -T500 postgres://dbuser_citus:[email protected]/citus # Any primary node can act as coordinator
pgbench --select-only -nv -P1 -c10 -T500 postgres://dbuser_citus:[email protected]/citus # Read-only queries
For production use of Citus, you typically need to set up streaming replication physical replicas for the Coordinator and each Worker cluster.
For example, simu.yml defines a 10-node Citus cluster:
pg-citus: # citus group
hosts:
10.10.10.50: { pg_group: 0, pg_cluster: pg-citus0 ,pg_vip_address: 10.10.10.60/24 ,pg_seq: 0, pg_role: primary }
10.10.10.51: { pg_group: 0, pg_cluster: pg-citus0 ,pg_vip_address: 10.10.10.60/24 ,pg_seq: 1, pg_role: replica }
10.10.10.52: { pg_group: 1, pg_cluster: pg-citus1 ,pg_vip_address: 10.10.10.61/24 ,pg_seq: 0, pg_role: primary }
10.10.10.53: { pg_group: 1, pg_cluster: pg-citus1 ,pg_vip_address: 10.10.10.61/24 ,pg_seq: 1, pg_role: replica }
10.10.10.54: { pg_group: 2, pg_cluster: pg-citus2 ,pg_vip_address: 10.10.10.62/24 ,pg_seq: 0, pg_role: primary }
10.10.10.55: { pg_group: 2, pg_cluster: pg-citus2 ,pg_vip_address: 10.10.10.62/24 ,pg_seq: 1, pg_role: replica }
10.10.10.56: { pg_group: 3, pg_cluster: pg-citus3 ,pg_vip_address: 10.10.10.63/24 ,pg_seq: 0, pg_role: primary }
10.10.10.57: { pg_group: 3, pg_cluster: pg-citus3 ,pg_vip_address: 10.10.10.63/24 ,pg_seq: 1, pg_role: replica }
10.10.10.58: { pg_group: 4, pg_cluster: pg-citus4 ,pg_vip_address: 10.10.10.64/24 ,pg_seq: 0, pg_role: primary }
10.10.10.59: { pg_group: 4, pg_cluster: pg-citus4 ,pg_vip_address: 10.10.10.64/24 ,pg_seq: 1, pg_role: replica }
vars:
pg_mode: citus # pgsql cluster mode: citus
pg_version: 16 # citus does not have pg16 available
pg_shard: pg-citus # citus shard name: pg-citus
pg_primary_db: citus # primary database used by citus
pg_vip_enabled: true # enable vip for citus cluster
pg_vip_interface: eth1 # vip interface for all members
pg_dbsu_password: DBUser.Postgres # enable dbsu password access for citus
pg_extensions: [ citus, postgis, pgvector, topn, pg_cron, hll ] # install these extensions
pg_libs: 'citus, pg_cron, pg_stat_statements' # citus will be added by patroni automatically
pg_users: [{ name: dbuser_citus ,password: DBUser.Citus ,pgbouncer: true ,roles: [ dbrole_admin ] }]
pg_databases: [{ name: citus ,owner: dbuser_citus ,extensions: [ citus, vector, topn, pg_cron, hll ] }]
pg_parameters:
cron.database_name: citus
citus.node_conninfo: 'sslrootcert=/pg/cert/ca.crt sslmode=verify-full'
pg_hba_rules:
- { user: 'all' ,db: all ,addr: 127.0.0.1/32 ,auth: ssl ,title: 'all user ssl access from localhost' }
- { user: 'all' ,db: all ,addr: intra ,auth: ssl ,title: 'all user ssl access from intranet' }
We will cover a series of advanced Citus topics in subsequent tutorials: