pgo体验2

发表于 | 分类于

初始化数据库

默认的需要创建 configmap, 但 configmap 大小有限制，手动初始化数据库

将 sql 文件 放入 /mnt/imwl/initdb /mnt/imwl 是共享目录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
apiVersion: batch/v1
kind: Job
metadata:
  name: init-db
  namespace: postgres-operator
spec:
  parallelism: 1
  template:
    spec:
      containers:
      - image: registry.test.datacanvas.com:55443/test/crunchy-postgres:ubi8-13.9-2
        name: init-db
        command: ['sh', '-c', 'for sql in `ls /tmp/*.sql`;do psql -h $PGHOST -p $PGPORT -U $PGUSER -d postgres -v ON_ERROR_STOP=1 -f $sql --quiet;done']
        volumeMounts:
        - mountPath: /tmp
          name: init-db
        env:
        - name: PGPASSWORD
          valueFrom:
            secretKeyRef:
              name: test-pguser-postgres
              key: password
        - name: PGHOST
          valueFrom:
             secretKeyRef:
               name: test-pguser-postgres
               key: host
        - name: PGPORT
          valueFrom:
            secretKeyRef:
              name: test-pguser-postgres
              key: port
        - name: PGUSER
          valueFrom:
             secretKeyRef:
               name: test-pguser-postgres
               key: user
      restartPolicy: OnFailure
      volumes:
      - name: init-db
        hostPath:
          path: /mnt/imwl/initdb
          type: Directory
  backoffLimit: 5

备份

前文配置了定时备份

做一次备份

1
kubectl annotate -n postgres-operator postgrescluster imwl postgres-operator.crunchydata.com/pgbackrest-backup="$(date)"

重新备份 --overwrite

1
kubectl annotate -n postgres-operator postgrescluster imwl --overwrite postgres-operator.crunchydata.com/pgbackrest-backup="$(date)"

恢复

新建一个 new-imwl 的集群

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: new-imwl
  namespace: postgres-operator
spec:  # 从 imwl 集群克隆
  dataSource:
    postgresCluster:
      clusterName: imwl
      repoName: repo1
#      options:  # 按时间点恢复
#      - --type=time
#      - --target="2022-06-09 14:15:11-04"
  image: registry.test.datacanvas.com:55443/test/crunchy-postgres:ubi8-13.9-2
  postgresVersion: 13
  instances:
    - dataVolumeClaimSpec:
        storageClassName: "rook-ceph-block-retain"
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 20Gi
  backups:
    pgbackrest:
      image: registry.test.datacanvas.com:55443/test/crunchy-pgbackrest:ubi8-2.41-2
      repos:
      - name: repo1
        volume:
          volumeClaimSpec:
            storageClassName: "rook-ceph-block-retain"
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 40Gi

查验, 数据正常

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
[root@imwl-01 high-availability]# kubectl get pvc -n postgres-operator  |grep new-imwl
new-imwl-00-5879-pgdata     Bound    pvc-f61d731c-524e-4f74-b0ef-0f73be5fa4de   20Gi       RWO            rook-ceph-block-retain   10m
new-imwl-repo1              Bound    pvc-d3ad6b64-7ff0-4b47-b461-4a609de34b4e   40Gi       RWO            rook-ceph-block-retain   9m33s
[root@imwl-01 high-availability]# kubectl get pv  |grep new-imwl
pvc-d3ad6b64-7ff0-4b47-b461-4a609de34b4e   40Gi       RWO            Retain           Bound      postgres-operator/new-imwl-repo1              rook-ceph-block-retain            9m43s
pvc-f61d731c-524e-4f74-b0ef-0f73be5fa4de   20Gi       RWO            Retain           Bound      postgres-operator/new-imwl-00-5879-pgdata     rook-ceph-block-retain            10m
[root@imwl-01 high-availability]# kubectl get svc -n postgres-operator  |grep new-imwl
new-imwl-ha          ClusterIP   10.68.52.59     <none>        5432/TCP         9m53s
new-imwl-ha-config   ClusterIP   None            <none>        <none>           9m53s
new-imwl-pods        ClusterIP   None            <none>        <none>           10m
new-imwl-primary     ClusterIP   None            <none>        5432/TCP         9m53s
new-imwl-replicas    ClusterIP   10.68.132.189   <none>        5432/TCP         9m53s

[root@imwl-01 high-availability]# kubectl exec -it -n postgres-operator  new-imwl-00-5879-0 -- sh
Defaulted container "database" out of: database, replication-cert-copy, pgbackrest, pgbackrest-config, postgres-startup (init), nss-wrapper-init (init)
sh-4.4$ export PGPASSWORD="example_2021!"
sh-4.4$ psql -h 10.68.52.59 -p 5432 -U postgres
psql (13.9)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 test       | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | test=CTc/postgres
 hive      | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | hive=CTc/postgres
 imwl      | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | imwl=CTc/postgres
 postgres  | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres
 template0 | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(6 rows)

只还原数据，不新建集群, 改原有配置，只用加下面几行

1
2
3
4
5
6
7
8
9
10
spec:
  backups:
    pgbackrest:
      restore:
        enabled: true
        repoName: repo1
        options:
        - --type=time
        - --target="2022-06-09 14:15:11-04"
      #  - --db-include=hive  # 只还原单个数据库

执行

kubectl annotate -n postgres-operator postgrescluster imwl --overwrite postgres-operator.crunchydata.com/pgbackrest-restore=id1

一些基本操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 重启
kubectl patch postgrescluster/imwl -n postgres-operator --type merge   --patch '{"spec":{"metadata":{"annotations":{"restarted":"'"$(date)"'"}}}}'

# 关闭
kubectl patch postgrescluster/imwl -n postgres-operator --type merge  --patch '{"spec":{"shutdown": true}}'

# 开机
kubectl patch postgrescluster/imwl -n postgres-operator --type merge  --patch '{"spec":{"shutdown": false}}'

# 暂停
kubectl patch postgrescluster/imwl -n postgres-operator --type merge  --patch '{"spec":{"paused": true}}'

# 恢复
kubectl patch postgrescluster/imwl -n postgres-operator --type merge  --patch '{"spec":{"paused": false}}'

查看 master 数据库 pod

1
kubectl -n postgres-operator get pods --selector=postgres-operator.crunchydata.com/role=master -o jsonpath='{.items[*].metadata.labels.postgres-operator\.crunchydata\.com/instance}'

查看 replica

1
kubectl -n postgres-operator get pods --selector=postgres-operator.crunchydata.com/role=replica -o jsonpath='{.items[*].metadata.labels.postgres-operator\.crunchydata\.com/instance}'

手动主备切换

1
2
3
kubectl annotate -n postgres-operator postgrescluster hippo imwl postgres-operator.crunchydata.com/trigger-switchover="$(date)"

kubectl annotate -n postgres-operator postgrescluster hippo imwl postgres-operator.crunchydata.com/trigger-switchover="$(date)" --overwrite

额外备份sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
#!/bin/bash

# docker run --name some-postgres -e POSTGRES_PASSWORD=example_2021! -v /opt:/opt -d postgres:13.9

PSQL_HOST="imwl-ha.postgres-operator.svc.cluster.local"
PSQL_PORT="5432"
PSQL_USER="postgres"
PSQL_PASS='password!'
BACKUP_DIRECTORY="/opt/backups/postgresql/imwl"
DEFAULT_DATE="$(date +%Y%m%d)"
BACKUP_LOGDIR="/opt/backups/logs"
REMOTE_BACKUP_DIRECTORY="/opt/backups/postgresql/imwl"
REMOTE_HOST="172.20.19.57"
REMOTE_HOST_PASS="passwd"
REMOTE_HOST2="172.20.19.59"
REMOTE_HOST_PASS2="passwd"

logger(){
    [ ! -d "${BACKUP_LOGDIR}" ] && mkdir -p ${BACKUP_LOGDIR}
    local logfile="${BACKUP_LOGDIR}/pgbackup-${DEFAULT_DATE}.log"
    local logtype=$1; local msg=$2
    datetime=`date +'%F %H:%M:%S'`
    format1="${datetime}  [line: `caller 0 | awk '{print$1}'`]"
    format2="${logtype}: ${msg}"
    logformat="$format1  $format2"
    echo -e "${logformat}" | tee -a $logfile
}

backup_database(){
    local databases=(
        hive
        test
    )

    if [ ! -d "${BACKUP_DIRECTORY}" ];then
        mkdir -p ${BACKUP_DIRECTORY}
    fi

    for db in ${databases[@]}
    do
        postgres_dump $db
    done

    logger INFO "Databases backed up to ${BACKUP_DIRECTORY} directory"
}

postgres_dump(){
    local database=$1
    logger INFO "Dumping database ${database} ..."

    export PGPASSWORD=${PSQL_PASS}
    docker exec -i -e PGPASSWORD=$PSQL_PASS some-postgres  /usr/bin/pg_dump -h ${PSQL_HOST} -p ${PSQL_PORT} -U ${PSQL_USER} ${database} |gzip > ${BACKUP_DIRECTORY}/${DEFAULT_DATE}-${database}.sql.gz

    # docker exec -i -e PGPASSWORD=$PSQL_PASS some-postgres  /usr/bin/pg_dumpall -h ${PSQL_HOST} -p ${PSQL_PORT} -U ${PSQL_USER} ${database} |gzip > ${BACKUP_DIRECTORY}/${DEFAULT_DATE}-full.sql.gz
    
    #  docker run --rm -i -e PGPASSWORD=$PSQL_PASS  postgres:13.9 /usr/bin/pg_dumpall  -h ${PSQL_HOST} -p ${PSQL_PORT} -U ${PSQL_USER} ${database} |gzip > ${BACKUP_DIRECTORY}/${DEFAULT_DATE}-full.sql.gz

    if [ ${PIPESTATUS[0]} -ne 0 -o ${PIPESTATUS[1]} -ne 0 ];then
        logger ERROR "Failed to dump database ${database}!"
        exit 1
    fi

    logger INFO "Database ${database} dump complete"
}

backup_database


#远程备份，保存3天
sshpass -p $REMOTE_HOST_PASS scp $BACKUP_DIRECTORY/$DEFAULT_DATE* root@$REMOTE_HOST:$REMOTE_BACKUP_DIRECTORY
logger INFO "Databases backed up to remote directory: ${REMOTE_BACKUP_DIRECTORY}"

sshpass -p $REMOTE_HOST_PASS2 scp $BACKUP_DIRECTORY/$DEFAULT_DATE* root@$REMOTE_HOST2:$REMOTE_BACKUP_DIRECTORY
logger INFO "Databases backed up to remote directory: ${REMOTE_BACKUP_DIRECTORY}"


#检查当前备份文件数量是否大于9，若是，删除3天之前的备份文件
file_num=`ls -l $BACKUP_DIRECTORY |grep "^-"|wc -l`
if [ $file_num -gt 9 ];then
  find $BACKUP_DIRECTORY -mtime +2 -exec rm -rfv {} \;
  logger INFO "Delete backup files three days ago"
fi

恢复

1
psql -f xxxx.sql -h ${PSQL_HOST} -p ${PSQL_PORT} -U ${PSQL_USER} ${database}

拿文件恢复
不推荐

1
2
3
4
5
[root@k8s-60 pg13]#  docker run --rm -it --name postgres  -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=example_2021! -v /root/pg13:/var/lib/postgresql/data  -v /etc/localtime:/etc/localtime -p 15432:5432 postgres:13.9  su - postgres -c '/usr/lib/postgresql/13/bin/pg_resetwal -f  /var/lib/postgresql/data'
Write-ahead log reset

[root@k8s-60 pg13]#  docker run -d --name postgres --restart=always  -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=example_2021! -v /root/pg13:/var/lib/postgresql/data  -v /etc/localtime:/etc/localtime -p 15432:5432 postgres:13.9
dccddceec900708489f10e4c9462d0e62d0d4340aabb5804360253f523cc994b

推荐
恢复基础备份，然后 wal日志数据库恢复 参考

https://blog.csdn.net/zimu312500/article/details/124283358

关闭空闲连接

1
2
3
select * from pg_stat_activity;

SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE current_query='<IDLE>'