可以使用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. 其他详细信息见 https://pgbackrest.org/command.html 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
➜ ~
