# Restore

> Restore PostgreSQL from Backup
---

You can use the pre-configured pgbackrest to perform Point-in-Time Recovery (PITR) in Pigsty.

- [**Manually**](#manually):  PITR with the `pg-pitr` hint script, do it manually, more flexible with more complexity.
- [**Playbook**](#playbook): PITR with the `pgsql-pitr.yml` playbook, automatic, but less flexible and more error-prone.

If you are very convenient with your configuration, you can use the fully automatic playbook,
otherwise, consider do it step by step manually


--------

## Quick Start

If you want to roll back the `pg-meta` cluster to the previous timepoint, adding the `pg_pitr`:

```yaml
pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta2
    pg_pitr: { time: '2025-07-13 10:00:00+00' }  # restore from the latest backup
```

Then run the `pgsql-pitr.yml` playbook, it will roll back the `pg-meta` cluster to the specified timepoint.

```bash
./pgsql-pitr.yml -l pg-meta
```

--------

## Restore PITR

The `archive_mode` will be **disabled** on recovered cluster to prevent unwanted WAL writes.
If the recovered database status is ok, you can enable the `archive_mode` and make a full backup.

```bash {title="postgres @ pg-meta $"}
psql -c 'ALTER SYSTEM RESET archive_mode; SELECT pg_reload_conf();'
pg-backup full    # take a new full backup
```


--------

## Recovery Target

You can specify different types of recovery targets in `pg_pitr`, but they are mutually exclusive:

- [`time`](https://www.postgresql.org/docs/current/runtime-config-wal.html#RECOVERY-TARGET-TIME): which time point to restore?
- [`name`](https://www.postgresql.org/docs/current/runtime-config-wal.html#RECOVERY-TARGET-NAME): restore to a named restore point (created by `pg_create_restore_point`)
- [`xid`](https://www.postgresql.org/docs/current/runtime-config-wal.html#RECOVERY-TARGET-XID): restore to a specific transaction ID (TXID/XID)
- [`lsn`](https://www.postgresql.org/docs/current/runtime-config-wal.html#RECOVERY-TARGET-LSN): restore to a specific LSN (Log Sequence Number) point

The recovery [`type`](https://www.postgresql.org/docs/current/runtime-config-wal.html#RECOVERY-TARGET-TYPE) will be set accordingly if any of the above parameters is specified,
otherwise it will be set to `latest` (the end of the WAL archive stream).
The special `immediate` type can be used to instruct pgbackrest to minimize the recovery time by stop at the first consistent point.



### Target Type

```yaml tab="latest"
pg_pitr: { }  # restore to the latest status (wal archive stream end)
```
```yaml tab="time"
pg_pitr: { time: "2025-07-13 10:00:00+00" }
```
```yaml tab="lsn"
pg_pitr: { lsn: "0/4001C80" }
```
```yaml tab="xid"
pg_pitr: { xid: "250000" }
```
```yaml tab="name"
pg_pitr: { name: "some_restore_point" }
```
```yaml tab="immediate"
pg_pitr: { type: "immediate" }
```

### By Time

The most frequently used target is the time point; you can specify the time point to restore to:

```bash {title="restore to a timepoint"}
./pgsql-pitr.yml -e '{"pg_pitr": { "time": "2025-07-13 10:00:00+00" }}'
```

Time should be a valid PostgreSQL [`TIMESTAMP`](https://www.postgresql.org/docs/17/datatype-datetime.html#DATATYPE-DATETIME-INPUT-TIME-STAMPS), `YYYY-MM-DD HH:MM:SS+TZ` is recommended.


### By Name

You can create a named restore point with [`pg_create_restore_point`](https://www.postgresql.org/docs/current/functions-admin.html#id-1.5.8.34.5.5.2.2.1.1.1.1):

```sql
SELECT pg_create_restore_point('shit_incoming');
```

And use that named restore point in PITR:

```bash
./pgsql-pitr.yml -e '{"pg_pitr": { "name": "shit_incoming" }}'
```


### By XID

If you have a transaction that accidentally deleted some data, the best way to recover is to restore the database to the state before that transaction.

```bash {title="restore right before a transaction"}
./pgsql-pitr.yml -e '{"pg_pitr": { "xid": "250000", exclusive: true }}'
```

You can find the exact transaction id from monitoring dashboard, or find it from `TXID` from the CSVLOG.

**Inclusive vs Exclusive**

The target parameter is "inclusive" by default, which means the recovery will include the target point.
The `exclusive` flag will exclude that exact target, like the xid 24999 will be the last transaction being replayed

This only applies to `time`, `xid`, `lsn` recovery targets, check [`recovery_target_inclusive`](https://www.postgresql.org/docs/current/runtime-config-wal.html#RECOVERY-TARGET-INCLUSIVE) for details.


### By LSN

PostgreSQL uses the [LSN](https://www.postgresql.org/docs/current/datatype-pg-lsn.html) (Log Sequence Number) to identify the position of a WAL record.
You can find it everywhere, like the PG LSN panel from Pigsty dashboards.

```bash {title="restore to a LSN"}
./pgsql-pitr.yml -e '{"pg_pitr": { "lsn": "0/4001C80", timeline: "1" }}'
```

To restore to an exact point in the WAL stream, you may also specify the [`timeline`](https://www.postgresql.org/docs/current/runtime-config-wal.html#RECOVERY-TARGET-TIMELINE) parameter (default to `latest`)





--------

## Recovery Source

- `cluster`: which cluster to restore? the current `pg_cluster` will be used by default, you can use any other cluster in the same pgbackrest repo
- `repo`: overwrite the backup repo, use the same format in `pgbackrest_repo`
- `set`: the `latest` backup set is used by default, but you can specify a specific pgbackrest backup by label



Pigsty will recover from the pgbackrest backup repository, if you are using a centralized backup repo (like MinIO/S3),
you can specify another "stanza" (another cluster's backup directory) to restore from.

```yaml
pg-meta2:
  hosts: { 10.10.10.11: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta2
    pg_pitr: { cluster: pg-meta }  # restore from the pg-meta cluster backup
```

The above configuration will mark the PITR procedure to use the `pg-meta` stanza.
You can also pass the `pg_pitr` parameter via CLI args:

```bash {title="pitr pg-meta2 with pg-meta backup"}
./pgsql-pitr.yml -l pg-meta2 -e '{"pg_pitr": { "cluster": "pg-meta" }}'
```

You can also use these targets when pitr from another cluster:

```bash
./pgsql-pitr.yml -l pg-meta2 -e '{"pg_pitr": { "cluster": "pg-meta", "time": "2025-07-14 08:00:00+00" }}'
```




--------

## Break Down

This approach is semi-automatic, you will participate in the PITR process to make key decisions.

For example, this configuration will restore the `pg-meta` cluster itself to the specified timepoint

```yaml
pg-meta:
  hosts: { 10.10.10.10: { pg_seq: 1, pg_role: primary } }
  vars:
    pg_cluster: pg-meta2
    pg_pitr: { time: '2025-07-13 10:00:00+00' }  # restore from the latest backup
```

Let's do this one step by step:

```bash
./pgsql-pitr.yml -l pg-meta -t down     # pause patroni HA
./pgsql-pitr.yml -l pg-meta -t pitr     # run the pitr procedure
./pgsql-pitr.yml -l pg-meta -t up       # generate pgbackrest config and restore script
```

```yaml
# down                 : # stop ha and shutdown patroni and postgres
#   - pause            : # pause patroni auto failover
#   - stop             : # stop patroni and postgres service
#     - stop_patroni   : # stop patroni service
#     - stop_postgres  : # stop postgres service
# pitr                 : # perform the PITR procedure
#   - config           : # generate pgbackrest config and restore script
#   - restore          : # run the pgbackrest restore command
#   - recovery         : # start postgres and complete recovery
#   - verify           : # verify the recovered cluster control data
# up:                  : # start postgres / patroni and resume ha
#   - etcd             : # clean up etcd metadata before launching
#   - start            : # start patroni and postgres service
#     - start_postgres : # start postgres service
#     - start_patroni  : # start patroni service
#   - resume           : # resume patroni auto failover
```


--------

## PITR Definition

There are more options available in the `pg_pitr` parameter:

```yaml
pg_pitr:                        # define a PITR task
    cluster: "some_pg_cls_name"   # Source cluster name
    type: latest                  # Recovery target type: time, xid, name, lsn, immediate, latest
    time: "2025-01-01 10:00:00+00" # Recovery target: time, exclusive with xid, name, lsn
    name: "some_restore_point"    # Recovery target: named restore point, exclusive with time, xid, lsn
    xid:  "100000"                # Recovery target: transaction ID, exclusive with time, name, lsn
    lsn:  "0/3000000"             # Recovery target: log sequence number, exclusive with time, name, xid
    timeline: latest              # Target timeline, can be an integer, latest by default,
    exclusive: false              # Exclude the target point, default false?
    action: pause                 # Post-recovery action: pause, promote, shutdown
    archive: false                # Preserve archive settings? false by default
    db_exclude: [ template0, template1 ]
    db_include: []
    link_map:
      pg_wal: '/data/wal'
      pg_xact: '/data/pg_xact'
    process: 4                    # Parallel restore processes
    repo: {}                      # Repository to restore from
    data: /pg/data                # where to restore the data
    port: 5432                    # listen port of the recovered instance
```