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;
