Crunchy PostgreSQL database restore via pgo command

可以使用pgo命令很方便的恢復一個Crunchy PostgreSQL資料庫，但是現在的命令只是支援在原來的cluster上做恢復，希望後面能支援恢復到一個新的Postgrescluster上。

在做資料庫恢復之前先了解一下pgbackrest有哪些restore的型別
Recovery type.
The following recovery types are supported:
(1) default - recover to the end of the archive stream.
(2) immediate - recover only until the database becomes consistent. This option is only supported on PostgreSQL >= 9.4.
(3) lsn - recover to the LSN (Log Sequence Number) specified in --target. This option is only supported on PostgreSQL >= 10.
(4) name - recover the restore point specified in --target.
(5) xid - recover to the transaction id specified in --target.
(6) time - recover to the time specified in --target.
(7) preserve - preserve the existing recovery.conf file.
(8) standby - add standby_mode=on to recovery.conf file so cluster will start in standby mode.
(9) none - no recovery.conf file is written so PostgreSQL will attempt to achieve consistency using WAL segments present in pg_xlog/pg_wal. Provide the required WAL segments or use the archive-copy setting to include them with the backup.

其他詳細資訊見  

Crunchy PostgreSQL cluster can be restored from a pgBackRest backup when the cluster is corrupted and cannot be recovered.

Execute the restore command:

➜  ~ oc pgo -n project-demo restore demo --repoName=repo1
WARNING: You are about to restore from pgBackRest with {options:[] repoName:repo1}
WARNING: This action is destructive and PostgreSQL will be unavailable while its data is restored.
Do you want to continue? (yes/no): yes
postgresclusters/demo patched
➜  ~

In this example, the default repo name is repo1. If you have named it differently, make sure to specify the correct repo name.
Because this is a potentially destructive operation, you might see the following warnings and prompts to continue the restore operation.


Get pods and roles

➜  ~ oc get pods --selector=postgres-operator.crunchydata.com/instance-set -L postgres-operator.crunchydata.com/role
NAME                    READY   STATUS    RESTARTS   AGE     ROLE
demo-instance1-49sl-0   5/5     Running   0          8m27s   replica
demo-instance1-jnzt-0   5/5     Running   0          8m27s   master
➜  ~

Perform some database changes

➜  ~ oc exec -it demo-instance1-jnzt-0 -c database -- /bin/sh
sh-4.4$ 
sh-4.4$ psql demo
psql (14.4)
Type "help" for help.
demo=# create table test(id int not null);
CREATE TABLE
demo=# select current_timestamp;
       current_timestamp       
-------------------------------
 2023-02-16 09:54:40.548835+00
(1 row)
demo=# insert into test values(1),(2),(3);
INSERT 0 3
demo=# select current_timestamp;
       current_timestamp       
-------------------------------
 2023-02-16 09:54:53.922798+00
(1 row)
demo=# insert into test values(4),(5),(6);
INSERT 0 3
demo=# select current_timestamp;
       current_timestamp       
-------------------------------
 2023-02-16 09:55:07.333022+00
(1 row)

check database backup information

➜  ~ oc pgo show backup demo
stanza: db
    status: ok
    cipher: none
    db (current)
        wal archive min/max (14): 000000010000000000000001/00000001000000000000000C
        full backup: 20230216-094600F
            timestamp start/stop: 2023-02-16 09:46:00 / 2023-02-16 09:47:18
            wal start/stop: 000000010000000000000004 / 000000010000000000000004
            database size: 36.4MB, database backup size: 36.4MB
            repo1: backup set size: 4.5MB, backup size: 4.5MB
        full backup: 20230216-095254F
            timestamp start/stop: 2023-02-16 09:52:54 / 2023-02-16 09:53:10
            wal start/stop: 000000010000000000000006 / 000000010000000000000007
            database size: 36.8MB, database backup size: 36.8MB
            repo1: backup set size: 4.5MB, backup size: 4.5MB
        diff backup: 20230216-095254F_20230216-100518D
            timestamp start/stop: 2023-02-16 10:05:18 / 2023-02-16 10:05:22
            wal start/stop: 000000010000000000000009 / 00000001000000000000000A
            database size: 37.8MB, database backup size: 4.6MB
            repo1: backup set size: 4.5MB, backup size: 413.3KB
            backup reference list: 20230216-095254F
        incr backup: 20230216-095254F_20230216-100549I
            timestamp start/stop: 2023-02-16 10:05:49 / 2023-02-16 10:05:51
            wal start/stop: 00000001000000000000000C / 00000001000000000000000C
            database size: 37.9MB, database backup size: 1.8MB
            repo1: backup set size: 4.5MB, backup size: 94.5KB
            backup reference list: 20230216-095254F, 20230216-095254F_20230216-100518D
➜  ~

Restore to a time point

➜  ~ oc pgo -n project-demo restore demo --repoName repo1 --options '--type=time --target="2023-02-16 09:54:53+00"'
WARNING: You are about to restore from pgBackRest with {options:[--type=time --target="2023-02-16 09:54:53+00"] repoName:repo1}
WARNING: This action is destructive and PostgreSQL will be unavailable while its data is restored.
Do you want to continue? (yes/no): yes
postgresclusters/pnst patched
➜  ~

Restore from a backup

➜  ~ oc pgo -n project-demo restore demo --repoName repo1 --options '--type=none --set="20230216-095254F_20230216-100518D"'
WARNING: You are about to restore from pgBackRest with {options:[--type=immediate --set="20230216-095254F_20230216-100518D"] repoName:repo1}
WARNING: This action is destructive and PostgreSQL will be unavailable while its data is restored.
Do you want to continue? (yes/no): yes
postgresclusters/pnst patched
➜  ~