# Administration > run administrative tasks --- > How to maintain an existing PostgreSQL cluster with Pigsty? Here are some SOP for common pgsql admin tasks - Case 1: [Create Cluster](#create-cluster) - Case 2: [Create User](#create-user) - Case 3: [Create Database](#create-database) - Case 4: [Reload Service](#reload-service) - Case 5: [Reload HBARule](#reload-hbarule) - Case 6: [Config Cluster](#config-cluster) - Case 7: [Append Replica](#append-replica) - Case 8: [Remove Replica](#remove-replica) - Case 9: [Remove Cluster](#remove-cluster) - Case 10: [Switchover](#switchover) - Case 11: [Backup Cluster](#backup-cluster) - Case 12: [Restore Cluster](#restore-cluster) - Case 13: [Adding Packages](#adding-packages) - Case 14: [Install Extension](#install-extension) - Case 15: [Minor Upgrade](#minor-upgrade) - Case 16: [Major Upgrade](#major-upgrade) ------ ## Cheatsheet PGSQL playbooks and shortcuts: ```bash bin/pgsql-add # create pgsql cluster bin/pgsql-user # create pg user on bin/pgsql-db # create pg database on bin/pgsql-svc [...ip] # reload pg service of cluster bin/pgsql-hba [...ip] # reload postgres/pgbouncer HBA rules of cluster bin/pgsql-add [...ip] # append replicas for cluster bin/pgsql-rm [...ip] # remove replicas from cluster bin/pgsql-rm # remove pgsql cluster ``` Patroni admin command and shortcuts: ```bash pg list # print cluster info pg edit-config # edit cluster config pg reload [ins] # reload cluster config pg restart [ins] # restart pgsql cluster pg reinit [ins] # reinit cluster members pg pause # entering maintenance mode (no auto failover) pg resume # exiting maintenance mode pg switchover # switchover on cluster pg failover # failover on cluster ``` pgBackRest [backup](/docs/pgsql/backup) & [restore](/docs/pgsql/backup/restore) command and shortcuts: ```bash pb info # print pgbackrest repo info pg-backup # make a backup, incr, or full backup if necessary pg-backup full # make a full backup pg-backup diff # make a differential backup pg-backup incr # make a incremental backup ./pgsql-pitr.yml -e '{"pg_pitr": { "time": "2025-07-13 10:00:00+00" }}' ./pgsql-pitr.yml -e '{"pg_pitr": { "name": "shit_incoming" }}' ./pgsql-pitr.yml -e '{"pg_pitr": { "xid": "250000", exclusive: true }}' ./pgsql-pitr.yml -e '{"pg_pitr": { "lsn": "0/4001C80", timeline: "1" }}' ``` Systemd components quick reference ```bash systemctl stop patroni # start stop restart reload systemctl stop pgbouncer # start stop restart reload systemctl stop pg_exporter # start stop restart reload systemctl stop pgbouncer_exporter # start stop restart reload systemctl stop node_exporter # start stop restart systemctl stop haproxy # start stop restart reload systemctl stop vip-manager # start stop restart reload systemctl stop postgres # only when patroni_mode == 'remove' ``` ------ ## Create Cluster To create a new Postgres cluster, define it in the inventory first, then init with: ```bash bin/node-add # init nodes for cluster # ./node.yml -l bin/pgsql-add # init pgsql instances of cluster # ./pgsql.yml -l ``` > Beware, perform `bin/node-add` first, then `bin/pgsql-add`, PGSQL works on managed nodes only. ------ ## Create User To create a new business user on the existing Postgres cluster, add user definition to `all.children..pg_users`, then create the user as follows: ```bash bin/pgsql-user # ./pgsql-user.yml -l -e username= ``` ------ ## Create Database To create a new database user on the existing Postgres cluster, add database definition to `all.children..pg_databases`, then create the database as follows: ```bash bin/pgsql-db # ./pgsql-db.yml -l -e dbname= ``` Note: If the database has specified an owner, the user should already exist, or you’ll have to [Create User](#create-user) first. ------ ## Reload Service Services are exposed access point served by HAProxy. This task is used when cluster membership has changed, e.g., [append](#append-replica)/[remove](#remove-replica) replicas, [switchover](#switchover)/failover / exposing new service or updating existing service’s config (e.g., LB Weight) To create new services or reload existing services on entire proxy cluster or specific instances: ```bash bin/pgsql-svc # pgsql.yml -l -t pg_service -e pg_reload=true bin/pgsql-svc [ip...] # pgsql.yml -l ip... -t pg_service -e pg_reload=true ``` ------ ## Reload HBARule This task is used when your Postgres/Pgbouncer HBA rules have changed, you *may* have to reload hba to apply changes. If you have any role-specific HBA rules, you may have to reload hba after a switchover/failover, too. To reload postgres & pgbouncer HBA rules on entire cluster or specific instances: ```bash bin/pgsql-hba # pgsql.yml -l -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload -e pg_reload=true bin/pgsql-hba [ip...] # pgsql.yml -l ip... -t pg_hba,pg_reload,pgbouncer_hba,pgbouncer_reload -e pg_reload=true ``` ------ ## Config Cluster To change the config of a existing Postgres cluster, you have to initiate control command on **admin node with admin user**: ```bash pg edit-config # interactive config a cluster with patronictl ``` Change patroni parameters & `postgresql.parameters`, save & apply changes with the wizard. ------ ## Append Replica To add a new replica to the existing Postgres cluster, you have to add its definition to the inventory: `all.children..hosts`, then: ```bash bin/node-add # init node for the new replica bin/pgsql-add # init pgsql instances on for cluster ``` It will add node `` to pigsty and init it as a replica of the cluster ``. Cluster services will be [reloaded](#reload-service) to adopt the new member ------ ## Remove Replica To remove a replica from the existing PostgreSQL cluster: ```bash bin/pgsql-rm # ./pgsql-rm.yml -l ``` It will remove instance `` from cluster ``. Cluster services will be [reloaded](#reload-service) to kick the removed instance from load balancer. ------ ## Remove Cluster To remove the entire Postgres cluster, just run: ```bash bin/pgsql-rm # ./pgsql-rm.yml -l ``` ------ ## Switchover You can perform a PostgreSQL cluster switchover with patroni cmd. ```bash pg switchover # interactive mode, you can skip that with following options pg switchover --leader pg-test-1 --candidate=pg-test-2 --scheduled=now --force pg-test ``` ------ ## Backup Cluster To create a backup with pgBackRest, run as local dbsu: ```bash pg-backup # make a postgres base backup pg-backup full # make a full backup pg-backup diff # make a differential backup pg-backup incr # make a incremental backup pb info # check backup information ``` Check [Backup](/docs/pgsql/backup/) & PITR for details. ------ ## Restore Cluster To restore a cluster to a previous time point (PITR), run as local dbsu: ```bash ./pgsql-pitr.yml -e '{"pg_pitr": { "time": "2025-07-13 10:00:00+00" }}' ./pgsql-pitr.yml -e '{"pg_pitr": { "name": "shit_incoming" }}' ./pgsql-pitr.yml -e '{"pg_pitr": { "xid": "250000", exclusive: true }}' ./pgsql-pitr.yml -e '{"pg_pitr": { "lsn": "0/4001C80", timeline: "1" }}' ``` Check Backup & [Restore](/docs/pgsql/backup/restore) docs for more details. ------ ## Adding Packages To add the newer version of RPM/DEB packages, you have to add them to [`repo_packages`](/docs/infra/param#repo_packages) and [`repo_url_packages`](/docs/infra/param#repo_url_packages) Then rebuild repo on infra nodes with `./infra.yml -t repo_build` subtask, Then you can install these packages with `ansible` module `package`: ```bash ansible pg-test -b -m package -a "name=pg_cron_15,topn_15,pg_stat_monitor_15*" # install some packages ``` ------ ## Install Extension If you want to install extension on pg clusters, Add them to [`pg_extensions`](/docs/pgsql/param#pg_extensions) and make sure them installed with: ```bash ./pgsql.yml -t pg_ext # install extensions ``` Some extension needs to be loaded in `shared_preload_libraries`, You can add them to [`pg_libs`](/docs/pgsql/param#pg_libs), or [Config](#config-cluster) an existing cluster. Finally, `CREATE EXTENSION ;` on the cluster primary instance to install it. Check [PGSQL Extensions: Install](/docs/pgsql/extension/install) for details. ------ ## Minor Upgrade To perform a minor server version upgrade/downgrade, you have to [add packages](#adding-packages) to yum/apt repo first. Then perform a rolling upgrade/downgrade from all replicas, then switchover the cluster to upgrade the leader. ```bash ansible -b -a "yum upgrade/downgrade -y " # upgrade/downgrade packages pg restart --force # restart cluster ``` ------ ## Major Upgrade The simplest way to achieve a major version upgrade is to create a new cluster with the new version, then [migration](/docs/pgsql/migration/) with logical replication & green/blue deployment. You can also perform an in-place major upgrade, which is not recommended, especially when certain extensions are installed. But it is possible. Assume you want to upgrade PostgreSQL 14 to 15, you have to [add packages](#adding-packages) to yum/apt repo, and guarantee the extensions have the exact same version too. ```bash ./pgsql.yml -t pg_pkg -e pg_version=15 # install packages for pg 15 sudo su - postgres; mkdir -p /data/postgres/pg-meta-15/data/ # prepare directories for 15 pg_upgrade -b /usr/pgsql-14/bin/ -B /usr/pgsql-15/bin/ -d /data/postgres/pg-meta-14/data/ -D /data/postgres/pg-meta-15/data/ -v -c # preflight pg_upgrade -b /usr/pgsql-14/bin/ -B /usr/pgsql-15/bin/ -d /data/postgres/pg-meta-14/data/ -D /data/postgres/pg-meta-15/data/ --link -j8 -v -c rm -rf /usr/pgsql; ln -s /usr/pgsql-15 /usr/pgsql; # fix binary links mv /data/postgres/pg-meta-14 /data/postgres/pg-meta-15 # rename data directory rm -rf /pg; ln -s /data/postgres/pg-meta-15 /pg # fix data dir links ``` --- Section pages: - [Parameter Tuning](/docs/pgsql/admin/tune/): Tuning PostgreSQL parameters - [Maintenance](/docs/pgsql/admin/maintain/): Common system maintenance tasks - [Failure SOP](/docs/pgsql/admin/failure/): Common failures and troubleshooting strategies - [Data Loss Recovery](/docs/pgsql/admin/drop/): Handling accidental deletion of data, tables, and databases