Version v2.7.0 of the documentation is no longer actively maintained. The site that you are currently viewing is an archived snapshot.
For up-to-date documentation, see the latest version.
pgBackRest backup & restore command and shortcuts:
pb info # print pgbackrest repo infopg-backup # make a backup, incr, or full backup if necessarypg-backup full # make a full backuppg-backup diff # make a differential backuppg-backup incr # make a incremental backuppg-pitr -i # restore to the time of latest backup complete (not often used)pg-pitr --time="2022-12-30 14:44:44+08"# restore to specific time point (in case of drop db, drop table)pg-pitr --name="my-restore-point"# restore TO a named restore point create by pg_create_restore_pointpg-pitr --lsn="0/7C82CB8" -X # restore right BEFORE a LSNpg-pitr --xid="1234567" -X -P # restore right BEFORE a specific transaction id, then promotepg-pitr --backup=latest # restore to latest backup setpg-pitr --backup=20221108-105325 # restore to a specific backup set, which can be checked with pgbackrest info
To create a new database user on the existing Postgres cluster, add database definition to all.children.<cls>.pg_databases, then create the database as follows:
Note: If the database has specified an owner, the user should already exist, or you’ll have to Create User first.
Example: Create Business Database
Reload Service
Services are exposed access point served by HAProxy.
This task is used when cluster membership has changed, e.g., append/remove replicas, 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:
Note: patroni unsafe RestAPI access is limit from infra/admin nodes and protected with an HTTP basic auth username/password and an optional HTTPS mode.
Example: Config Cluster with patronictl
Append Replica
To add a new replica to the existing Postgres cluster, you have to add its definition to the inventory: all.children.<cls>.hosts, then:
bin/node-add <ip> # init node <ip> for the new replicabin/pgsql-add <cls> <ip> # init pgsql instances on <ip> for cluster <cls>
It will add node <ip> to pigsty and init it as a replica of the cluster <cls>.
Cluster services will be reloaded to adopt the new member
Example: Add replica to pg-test
For example, if you want to add a pg-test-3 / 10.10.10.13 to the existing cluster pg-test, you’ll have to update the inventory first:
pg-test:
hosts:
10.10.10.11: { pg_seq: 1, pg_role: primary }# existing member 10.10.10.12: { pg_seq: 2, pg_role: replica }# existing member 10.10.10.13: { pg_seq: 3, pg_role: replica }# <--- new member vars: { pg_cluster: pg-test }
then apply the change as follows:
bin/node-add 10.10.10.13 # add node to pigstybin/pgsql-add pg-test 10.10.10.13 # init new replica on 10.10.10.13 for cluster pg-test
which is similar to cluster init but only works on single instance。
[ OK ] init instances 10.10.10.11 to pgsql cluster 'pg-test':
[WARN] reminder: add nodes to pigsty, then install additional module 'pgsql'[HINT] $ bin/node-add 10.10.10.11 # run this ahead, except infra nodes[WARN] init instances from cluster:
[ OK ] $ ./pgsql.yml -l '10.10.10.11,&pg-test'[WARN] reload pg_service on existing instances:
[ OK ] $ ./pgsql.yml -l 'pg-test,!10.10.10.11' -t pg_service
Remove Replica
To remove a replica from the existing PostgreSQL cluster:
It will remove instance <ip> from cluster <cls>.
Cluster services will be reloaded to kick the removed instance from load balancer.
Example: Remove replica from pg-test
For example, if you want to remove pg-test-3 / 10.10.10.13 from the existing cluster pg-test:
bin/pgsql-rm pg-test 10.10.10.13 # remove pgsql instance 10.10.10.13 from pg-testbin/node-rm 10.10.10.13 # remove that node from pigsty (optional)vi pigsty.yml # remove instance definition from inventorybin/pgsql-svc pg-test # refresh pg_service on existing instances to kick removed instance from load balancer
[ OK ] remove pgsql instances from 10.10.10.13 of 'pg-test':
[WARN] remove instances from cluster:
[ OK ] $ ./pgsql-rm.yml -l '10.10.10.13,&pg-test'
And remove instance definition from the inventory:
pg-test:hosts:10.10.10.11:{pg_seq:1, pg_role:primary }10.10.10.12:{pg_seq:2, pg_role:replica }10.10.10.13:{pg_seq:3, pg_role:replica }# <--- remove this after executionvars:{pg_cluster:pg-test }
Finally, you can update pg service and kick the removed instance from load balancer:
bin/pgsql-svc pg-test # reload pg service on pg-test
Remove Cluster
To remove the entire Postgres cluster, just run:
bin/pgsql-rm <cls> # ./pgsql-rm.yml -l <cls>
Example: Remove Cluster
Example: Force removing a cluster
Note: if pg_safeguard is configured for this cluster (or globally configured to true), pgsql-rm.yml will abort to avoid removing a cluster by accident.
You can use playbook command line args to explicitly overwrite it to force the purge:
./pgsql-rm.yml -l pg-meta -e pg_safeguard=false# force removing pg cluster pg-meta
Switchover
You can perform a PostgreSQL cluster switchover with patroni cmd.
pg switchover <cls> # interactive mode, you can skip that with following optionspg switchover --leader pg-test-1 --candidate=pg-test-2 --scheduled=now --force pg-test
Example: Switchover pg-test
$ pg switchover pg-test
Master [pg-test-1]:
Candidate ['pg-test-2', 'pg-test-3'][]: pg-test-2
When should the switchover take place (e.g. 2022-12-26T07:39 )[now]: now
Current cluster topology
+ Cluster: pg-test (7181325041648035869) -----+----+-----------+-----------------+
| Member | Host | Role | State | TL | Lag in MB | Tags |+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-1 | 10.10.10.11 | Leader | running |1|| clonefrom: true|||||||| conf: tiny.yml |||||||| spec: 1C.2G.50G |||||||| version: '15'|+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-2 | 10.10.10.12 | Replica | running |1|0| clonefrom: true|||||||| conf: tiny.yml |||||||| spec: 1C.2G.50G |||||||| version: '15'|+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-3 | 10.10.10.13 | Replica | running |1|0| clonefrom: true|||||||| conf: tiny.yml |||||||| spec: 1C.2G.50G |||||||| version: '15'|+-----------+-------------+---------+---------+----+-----------+-----------------+
Are you sure you want to switchover cluster pg-test, demoting current master pg-test-1? [y/N]: y
2022-12-26 06:39:58.02468 Successfully switched over to "pg-test-2"+ Cluster: pg-test (7181325041648035869) -----+----+-----------+-----------------+
| Member | Host | Role | State | TL | Lag in MB | Tags |+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-1 | 10.10.10.11 | Replica | stopped || unknown | clonefrom: true|||||||| conf: tiny.yml |||||||| spec: 1C.2G.50G |||||||| version: '15'|+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-2 | 10.10.10.12 | Leader | running |1|| clonefrom: true|||||||| conf: tiny.yml |||||||| spec: 1C.2G.50G |||||||| version: '15'|+-----------+-------------+---------+---------+----+-----------+-----------------+
| pg-test-3 | 10.10.10.13 | Replica | running |1|0| clonefrom: true|||||||| conf: tiny.yml |||||||| spec: 1C.2G.50G |||||||| version: '15'|+-----------+-------------+---------+---------+----+-----------+-----------------+
To do so with Patroni API (schedule a switchover from 2 to 1 at a specific time):
To create a backup with pgBackRest, run as local dbsu:
pg-backup # make a postgres base backuppg-backup full # make a full backuppg-backup diff # make a differential backuppg-backup incr # make a incremental backuppb info # check backup information
You can add crontab to node_crontab to specify your backup policy.
# make a full backup 1 am everyday- '00 01 * * * postgres /pg/bin/pg-backup full'# rotate backup: make a full backup on monday 1am, and an incremental backup during weekdays- '00 01 * * 1 postgres /pg/bin/pg-backup full'- '00 01 * * 2,3,4,5,6,7 postgres /pg/bin/pg-backup'
Restore Cluster
To restore a cluster to a previous time point (PITR), run as local dbsu:
pg-pitr -i # restore to the time of latest backup complete (not often used)pg-pitr --time="2022-12-30 14:44:44+08"# restore to specific time point (in case of drop db, drop table)pg-pitr --name="my-restore-point"# restore TO a named restore point create by pg_create_restore_pointpg-pitr --lsn="0/7C82CB8" -X # restore right BEFORE a LSNpg-pitr --xid="1234567" -X -P # restore right BEFORE a specific transaction id, then promotepg-pitr --backup=latest # restore to latest backup setpg-pitr --backup=20221108-105325 # restore to a specific backup set, which can be checked with pgbackrest info
And follow the instructions wizard, Check Backup & PITR for details.
Example: PITR with raw pgBackRest Command
# restore to the latest available point (e.g. hardware failure)pgbackrest --stanza=pg-meta restore
# PITR to specific time point (e.g. drop table by accident)pgbackrest --stanza=pg-meta --type=time --target="2022-11-08 10:58:48"\
--target-action=promote restore
# restore specific backup point and then promote (or pause|shutdown)pgbackrest --stanza=pg-meta --type=immediate --target-action=promote \
--set=20221108-105325F_20221108-105938I restore
Then rebuild repo on infra nodes with ./infra.yml -t repo_build subtask, Then you can install these packages with ansible module package:
ansible pg-test -b -m package -a "name=pg_cron_15,topn_15,pg_stat_monitor_15*"# install some packages
Update Packages Manually
# add repo upstream on admin node, then download them manuallycd ~/pigsty; ./infra.yml -t repo_upstream,repo_cache # add upstream repo (internet)cd /www/pigsty; repotrack "some_new_package_name"# download the latest RPMs# re-create local repo on admin node, then refresh yum/apt cache on all nodescd ~/pigsty; ./infra.yml -t repo_create # recreate local repo on admin node./node.yml -t node_repo # refresh yum/apt cache on all nodes# alternatives: clean and remake cache on all nodes with ansible commandansible all -b -a 'yum clean all'# clean node repo cacheansible all -b -a 'yum makecache'# remake cache from the new repoansible all -b -a 'apt clean'# clean node repo cache (Ubuntu/Debian)ansible all -b -a 'apt update'# remake cache from the new repo (Ubuntu/Debian)
For example, you can then install or upgrade packages with:
ansible pg-test -b -m package -a "name=postgresql15* state=latest"
Install Extension
If you want to install extension on pg clusters, Add them to pg_extensions and make sure them installed with:
./pgsql.yml -t pg_extension # install extensions
Some extension needs to be loaded in shared_preload_libraries, You can add them to pg_libs, or Config an existing cluster.
Finally, CREATE EXTENSION <extname>; on the cluster primary instance to install it.
Example: Install pg_cron on pg-test cluster
ansible pg-test -b -m package -a "name=pg_cron_15"# install pg_cron packages on all nodes# add pg_cron to shared_preload_librariespg edit-config --force -p shared_preload_libraries='timescaledb, pg_cron, pg_stat_statements, auto_explain'pg restart --force pg-test # restart clusterpsql -h pg-test -d postgres -c 'CREATE EXTENSION pg_cron;'# install pg_cron on primary
The simplest way to achieve a major version upgrade is to create a new cluster with the new version, then 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 to yum/apt repo, and guarantee the extensions has exact same version too.
