Shulou(Shulou.com)06/02 Report--

Reference website:

Postgres official website backup PITR documentation

Postgresql Continuous Archive and Point-in-Time Recovery (PITR)

1. Create postgres database in k8s

[yaml file for creating postgres download ()

You need to modify the data persistence method in the 70-statefulsets-postgres.yaml file, i.e.

storageClassName: 'nfs-client' #The nfs-client here is modified to a persistent device of its own k8s. The nfs service that has been built is used here.

Start the postgres database:

wget xxxxxxx.xxxxxx #download yaml file kubectl create namespace postgres #create namespacekubens postgres named postgres #To enter this namespace, kubens tool is used to switch namespaces. You need to go to github to search kubectx tool. Binary installation can use kubectl apply -f postgres/*.yaml #to start postgres database. All actions are completed in postgres namespace.

What to open in the postgres profile:

vim postgresql.confwal_level='hot_standby' #wal_level is set to at least replicaarchive_mode ='on 'archive_command ='test! -f /backup/archivedir/%f && cp %p /backup/archivedir/%f'

Check to see if the postgres database startup is complete:

lopes-MacBook-Pro:postgres-demo_wal2json lope$ kubectl get podsNAME READY STATUS RESTARTS AGEpostgres-0 1/1 Running 0 38m

Postgres started successfully at k8s.

2. Data preparation

Go to postgres operation

kubens postgres #Enter namespacekubectl exec -it postgres-0 sh #Enter postgres command

Backup the underlying database file

pg_basebackup -D /backup/backup -h postgres-0 -Fp -R -Pv -l postgrebackup-20191112 #This file is the base file for recovery

Create postgres log backup directory

mkdir /backup/archivedir #Postgres logs will be automatically imported into this directory, which is also the key to PITR

Create a test table

psql #Enter postgres database\c sso #Select sso database\d #Check that there is no table under this database create table test01 (id int primary key,name varchar(20));insert into test01 values(1,'a'),(2,' b'),(3,'c');select current_timestamp; # 2019-11-12 06:04:50.71881+00select pg_switch_wal(); # 0/A000158

Delete test table

delete from test01;select current_timestamp; # 2019-11-12 06:07:36.529161+00select pg_switch_wal(); # 0/C 000003, Data Recovery Demo

Modify the/backup/backup/recovery.done file (recovery.done if recovery.conf)

vim recovery.donerestore_command='cp /backup/archivedir/%f %p'recovery_target_time='2019-11-12 06:04:50.71881+00' #The time here is modified to the time point you want to recover recovery_target_timeline='latest'

Base Data File Recovery

mv /pgdata/postgres-0 /pgdata/postgres-0_bak #Destroy original data file cp -r /backup /pgdata/postgres-0 #copy backup file as database file cd postgres-0

rm -rf pg_wal/0 && rm -rf pg_wal/archive_status/ #Delete old log files for PITR to recover through logs

Restart postgres to automatically enter recovery mode

kubectl delete pods postgres-0kubectl get pods

After successful restart, you can enter the database to check whether it has been restored to the predetermined data.

kubectl exec -it postgres-0 shpsql\c sso\dselect * from test01;

If there is an operation error, resulting in failure to enter the pod of postgres, you can delete the pvc of the pod and restart the pod to restart the operation.

kubectl scale sts postgres --replicas=0 #close postgres before deleting pvclopes-MacBook-Pro:postgres-demo_wal2json lope$ kubectl get pvcNAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGEbackup-postgres-0 Bound pvc-1be89954-98f9-4f9d-a15a-780d5432d38a 30Gi RWO nfs-client 122mpgdata-postgres-0 Bound pvc-6f25fd78-282c-4604-a2f6-e9a8c767e002 30Gi RWO nfs-client 71mlopes-MacBook-Pro:postgres-demo_wal2json lope$ kubectl delete pvc pgdata-postgres-0 #Delete pgdata, backup does not delete

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.