langfuse使用的postgresql异机备份和恢复(docker)

环境:
OS:Centos 7

1.备份
##备份
export PGPASSWORD=postgres
/opt/pg16/bin/pg_basebackup -D "/backup/pgbak/basebackup/20241202" -Ft -Pv -U postgres -h 172.16.1.65 -p5432

2.压缩备份
带上-z参数进行压缩
cd /backup/pgbak/basebackup
[yeemiao@middle basebackup]$ tar -zcvf 20241118.tar.gz ./20241118

3.备份文件拷贝到另外一台部署了 langfuse 的机器
cd /backup/pgbak/basebackup
scp 20241118.tar.gz root@192.168.1.134:/soft/pgbak/

4.目的机器解压备份文件
[root@localhost pgbak]#cd /soft/pgbak
[root@localhost pgbak]#tar -xzvf 20241118.tar.gz
解压后的文件如下:
[root@localhost 20241118]# ls
backup_manifest base.tar pg_wal.tar
[root@localhost 20241118]# pwd
/soft/pgbak/20241118

5.新环境删除掉原有的重新创建一个新的(新环境可用的话,该步骤省略)
[root@localhost langfuse]#cd /home/middle/langfuse/langfuse
[root@localhost langfuse]#docker compose down
[root@localhost langfuse]#docker compose up -d

6.将归档日志拷贝到容器里面(需要提前做,要不恢复的时候提示找不到日志文件)
pg_wal.tar解压到归档目录
[root@host134 ~]#tar -xvf /soft/pgbak/20241118/pg_wal.tar -C /home/middle/langfuse/archivelog ##先解压到宿主机目录,目录需要提前创建
把归档日志拷贝到外挂的数据目录,这里有多少就拷贝多少
cp /home/middle/langfuse/archivelog/000000010000000E0000002B /home/middle/langfuse/pgdata/

登录容器
docker exec -ti langfuse-db-1 /bin/bash

容器里创建存放归档文件的目录
root@41f038664bc3:/# cd /var/lib/postgresql
root@41f038664bc3:/var/lib/postgresql# mkdir archivelog

归档日志放到指定目录
root@41f038664bc3:/#mv /var/lib/postgresql/data/000000010000000E0000002B /var/lib/postgresql/archivelog

修改权限
chown -R postgres:postgres /var/lib/postgresql/archivelog
chmod 0700 /var/lib/postgresql/archivelog

否则会报如下的错误:
cp: cannot open '/var/lib/postgresql/archivelog/000000010000000E0000002B' for reading: Permission denied

7.停掉langfuse
[root@localhost langfuse]# cd /home/middle/langfuse/langfuse
[root@localhost langfuse]# docker compose stop

8.删除data目录并创建空的data目录
[root@localhost langfuse]# cd /home/middle/langfuse
[root@localhost langfuse]# mv pgdata bak_pgdata
[root@localhost langfuse]# mkdir pgdata

9.将备份tar文件解压到数据目录
目前root账号下面操作的,后面需要记得修改权限
base.tar文件解压到data目录
[root@host134 ~]#tar -xvf /tmp/pgbak/20241118/base.tar -C /home/middle/langfuse/pgdata ##解压数据目录

9.修改配置文件
cd /home/middle/langfuse/pgdata
vi postgresql.conf 修改如下参数
restore_command = 'cp /var/lib/postgresql/archivelog/%f %p'
recovery_target_timeline = 'latest'

10.生成recovery.signal标识文件
[root@localhost pgdata]# cd /home/middle/langfuse/pgdata
[root@localhost pgdata]# touch recovery.signal

11.启动容器

[root@localhost langfuse]# cd /home/middle/langfuse/langfuse
[root@localhost langfuse]# docker compose start
[+] Running 2/2
 ? Container langfuse-db-1               Healthy                                                              20.0s 
 ? Container langfuse-langfuse-server-1  Started                                 "docker-entrypoint.s…"   3 months ago   Restarting (1) 24 seconds ago                                                                                              langfuse-db-1

12.查看日志
docker logs langfuse-db-1

13.登录pg查看

[root@localhost langfuse]# psql -h localhost -U postgres -p5432
Password for user postgres: 
psql (14.11, server 16.4 (Debian 16.4-1.pgdg120+1))
WARNING: psql major version 14, server major version 16.
         Some psql features might not work.
Type "help" for help.

postgres=# \dt
                  List of relations
 Schema |           Name           | Type  |  Owner   
--------+--------------------------+-------+----------
 public | Account                  | table | postgres
 public | Session                  | table | postgres
 public | _prisma_migrations       | table | postgres
 public | api_keys                 | table | postgres
 public | audit_logs               | table | postgres
 public | batch_exports            | table | postgres
 public | cron_jobs                | table | postgres
 public | dataset_items            | table | postgres
 public | dataset_run_items        | table | postgres
 public | dataset_runs             | table | postgres
 public | datasets                 | table | postgres
 public | eval_templates           | table | postgres
 public | events                   | table | postgres
 public | job_configurations       | table | postgres
 public | job_executions           | table | postgres
 public | llm_api_keys             | table | postgres
 public | membership_invitations   | table | postgres
 public | models                   | table | postgres
 public | observations             | table | postgres
 public | organization_memberships | table | postgres
 public | organizations            | table | postgres
 public | posthog_integrations     | table | postgres
 public | project_memberships      | table | postgres
 public | projects                 | table | postgres
 public | prompts                  | table | postgres
 public | score_configs            | table | postgres
 public | scores                   | table | postgres
 public | sso_configs              | table | postgres
 public | trace_sessions           | table | postgres
 public | traces                   | table | postgres
 public | users                    | table | postgres
 public | verification_tokens      | table | postgres
(32 rows)

postgres=# select * from users;