文档中心>云数据库 PostgreSQL>操作指南>备份与恢复>在云服务器上恢复 PostgreSQL 数据

在云服务器上恢复 PostgreSQL 数据

最近更新时间:2024-08-21 10:28:11

我的收藏
当用户出现数据丢失或者损坏时,可通过控制台 克隆实例 功能对数据进行按时间点恢复或者按备份集恢复,可恢复的时间根据日志保留时间和全量备份时间来决定。您也可以直接下载备份恢复至自建数据库。下面为您详细描述。

通过控制台下载备份进行恢复

1. 前置条件

本文默认您已经在待恢复数据的云服务器 CVM 中完成了安装与备份数据相同版本的 PostgreSQL 数据库,详细安装教程请参考 PostgreSQL 官方文档。Linux 系统的云服务器配置请参见 快速配置 Linux 云服务器

2. 使用 postgres 用户创建恢复目录

在云服务器中创建恢复目录,并设置权限。
mkdir -p /var/lib/pgsql/16/recovery
chown postgres /var/lib/pgsql/16/recovery
其中,recovery 为示例目录,用户可自行修改恢复目录。后续示例中不同版本目录名将不再区分描述,请以实际为准,如PostgreSQL 12.x 为/var/lib/pgsql/12

3. 下载全量备份文件

3.1. 登录 PostgreSQL 控制台,在实例列表,单击操作列的管理进入管理页面。
3.2. 选择备份恢复页,在数据备份列表中,根据备份时间选择需要恢复的备份集,单击操作列的下载
3.3. 根据提供的 VPC 网络地址或外网地址链接下载备份文件。
说明:
使用 VPC 网络地址下载备份时,云数据库须与云服务器处于同一 VPC,备份需下载至/var/lib/pgsql/16/recovery目录。
使用外网地址下载备份时,下载后需将备份文件上传至云服务器中的/var/lib/pgsql/16/recovery目录,请参见 如何将本地文件拷贝到云服务器
上传完示例如下:
[postgres@VM-0-5-tencentos recovery]$ ls -lh
total 9.1M
-rw-r--r-- 1 postgres postgres 9.1M Aug 5 12:40 pgsql_1026780_data_2024-08-05_automatic-20240805015133.tar.zst

4. 解压全量备份文件

执行如下命令,解压全量备份文件。下载文件后需要使用 zstd 进行解压,如没有该工具,需要进行安装。
cd /var/lib/pgsql/16/recovery
tar -I zstd -xvf pgsql_1026780_data_2024-08-05_automatic-20240805015133.tar.zst
解压后如下图:
[postgres@VM-0-5-tencentos recovery]$ ls -lh
total 9.2M
-rw------- 1 postgres postgres 225 Aug 5 01:51 backup_label
-rw------- 1 postgres postgres 225 Jul 30 20:36 backup_label.old
drwx------ 6 postgres postgres 4.0K Aug 5 12:45 base
-rw------- 1 postgres postgres 56 Aug 5 01:00 current_audit_logfiles
-rw------- 1 postgres postgres 35 Aug 5 00:00 current_logfiles
drwx------ 2 postgres postgres 4.0K Aug 5 12:45 global
drwx------ 2 postgres postgres 4.0K Jul 30 20:36 pg_commit_ts
drwx------ 2 postgres postgres 4.0K Jul 30 20:36 pg_dynshmem
-rw------- 1 postgres postgres 308 Jul 30 20:36 pg_hba.conf
-rw------- 1 postgres postgres 2.6K Jul 30 20:36 pg_ident.conf
drwx------ 4 postgres postgres 4.0K Jul 31 10:19 pg_logical
drwx------ 4 postgres postgres 4.0K Jul 30 20:36 pg_multixact
drwx------ 2 postgres postgres 4.0K Jul 30 20:36 pg_notify
drwx------ 2 postgres postgres 4.0K Aug 5 01:51 pg_replslot
drwx------ 2 postgres postgres 4.0K Jul 30 20:36 pg_serial
drwx------ 2 postgres postgres 4.0K Jul 30 20:36 pg_snapshots
-rw-r--r-- 1 postgres postgres 9.1M Aug 5 12:40 pgsql_1026780_data_2024-08-05_automatic-20240805015133.tar.zst
drwx------ 2 postgres postgres 4.0K Jul 30 20:36 pg_stat
drwx------ 2 postgres postgres 4.0K Jul 30 20:36 pg_stat_tmp
drwx------ 2 postgres postgres 4.0K Jul 30 20:54 pg_subtrans
drwx------ 2 postgres postgres 4.0K Jul 30 20:36 pg_tblspc
drwx------ 2 postgres postgres 4.0K Jul 30 20:36 pg_twophase
-rw------- 1 postgres postgres 3 Jul 30 20:36 PG_VERSION
drwx------ 3 postgres postgres 4.0K Aug 5 12:45 pg_wal
drwx------ 2 postgres postgres 4.0K Jul 30 20:36 pg_xact
-rw------- 1 postgres postgres 2.3K Jul 30 20:36 postgresql.conf
-rw------- 1 postgres postgres 0 Jul 30 20:36 standby.signal
-rw------- 1 postgres postgres 0 Aug 5 01:51 tablespace_map
-rw------- 1 postgres postgres 2 Jul 30 20:36 TENCENTDB_RELEASE

5. 删除多余的临时文件

执行如下命令,删除多余的临时文件。
rm -rf backup_label

6. 修改配置文件

6.1. 将配置文件postgresql.conf中的以下选项注释掉,注释方法:在行首使用#。 如有多个该选项,则全部注释掉。
synchronous_standby_names
shared_preload_libraries
local_preload_libraries
pg_stat_statements.max
pg_stat_statements.track
archive_mode
archive_command
synchronous_commit
tencentdb_az_five
extension_blacklist
disable_dblink_connect_to_other
tencentdb_enable_trusted_extension
basebackup_exclude_paths
tencentdb_enable_superuser_unsafe_behaviour
说明:
需要注释 include = 'standby.conf' 这一行。
log_destination = 'csvlog,auditlog' 需要改为 log_destination = 'csvlog'
6.2. 修改配置文件postgresql.conf
port = '5432' ##将port参数的值修改为5432
6.3. 在postgresql.conf文件末尾追加配置,表示不再使用强同步模式。
synchronous_commit = local
synchronous_standby_names = ''

7. 使用 root 用户更改文件夹权限

chmod 0700 /var/lib/pgsql/16/recovery
chown postgres:postgres /var/lib/pgsql/16/recovery -R
修改后如下图:


8.(可选)应用增量备份文件

如跳过该步骤,则数据库的内容为开始做全量备份时数据库的内容。 将 xlog 文件放入/var/lib/pgsql/16/recovery/pg_wal文件夹下,如下载的备份中不包含pg_wal目录,请将pg_xlog目录修改为pg_wal,pg 会自动重放 xlog 日志。 例如12:00时做的全量备份,如果在该全量备份的基础上,在pg_wal文件夹下放置12:00至13:00的所有 xlog,则数据库能恢复到13:00时的数据内容。
说明:
PostgreSQL 版本为 9.x 时,则为/var/lib/pgsql/9.x/recovery/pg_xlog文件夹。
8.1. 选择备份恢复页,在日志备份列表中,选择需要恢复的日志备份集,单击操作列的下载。下载后如下图:



8.2. 解压日志至pg_wal文件夹。
tar -I zstd -xvf pgsql_1026780_xlog_2024-08-05_20240805133238_20240805133241-20240805133241-00000001000000000000000B_00000001000000000000000F.tar.zst


9. 使用 postgres 用户启动数据库

/usr/local/pgsql/bin/pg_ctl start -D /var/lib/pgsql/16/recovery -l logfile

10. 登录数据库验证

10.1. 验证数据库是否运行。
/usr/local/pgsql/bin/pg_ctl status -D /var/lib/pgsql/16/recovery
如提示"server is running",则代表数据库正在运行。



10.2. 登录数据库
[postgres@VM-0-5-tencentos recovery]$ /usr/local/pgsql/bin/psql -h127.0.0.1 -p 5432 -Udbadmin -dpostgres
psql (16.0)
Type "help" for help.

postgres=>

通过手动导出数据进行恢复

您也可以手动导出备份数据,然后在腾讯云云服务器上进行恢复操作,该方案在 Windows 和 Linux 下同样适用,与物理文件所在的文件系统无关。
注意:
不建议使用 postgres 数据库作为目标数据库。
建议您在目标端使用新建的 database 来导入数据。
如果您在目标端使用了已存在数据的 database 来导入,可能会因为对象冲突而失败。

1.在云服务器下 dump 出数据

命令格式为:pg_dump -h <host> -p <port> -U <username> -Fc <dbname> <dumpdir>,更多使用指引请参考 pg_dump 官方文档
参数
说明
host
云数据库 PostgreSQL 实例的连接地址。
port
云数据库 PostgreSQL 实例的端口。
username
云数据库 PostgreSQL 实例的账号名称。
-Fc
输出格式,-Fc 格式适合 pg_restore 进行还原。
dbname
需要导出的 database 名称。
dumpdir
导出的备份文件的路径和名称。
示例如下:
pg_dump -h 10.0.13.13 -p 5432 -U dbadmin -Fc testdb > testdb_bkp.dump
命令提示Password:时,输入访问账号密码,则文件会导出成功。您可以执行ll testdb_bkp.dump确保文件生成。




2.在自建数据库上创建对应的 user

连上目标端自建数据库,创建对应的数据库账号,确保目标端账号存在。示例如下:
[postgres@VM-0-14-tencentos root]$ psql -h127.0.0.1 -p5432 -Upostgres -dpostgres
psql (16.0)
Type "help" for help.

postgres=# \\du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS

postgres=# CREATE USER dbadmin WITH PASSWORD '123456' SUPERUSER;
CREATE ROLE
postgres=# \\du
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
dbadmin | Superuser
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS

postgres=#
以上示例中创建的账号为 superuser ,您也可以基于自身需要进行 user 的系统权限和对象权限的赋权。

3.在自建数据库上创建对应的 database

使用新创建的 user 登录数据库,如果需要恢复的 database 不存在,则创建 database,示例如下:
[postgres@VM-0-14-tencentos root]$ psql -h127.0.0.1 -p5432 -Udbadmin -dpostgres
psql (16.0)
Type "help" for help.

postgres=# select datname from pg_database;
datname
-----------
template1
template0
postgres
(3 rows)

postgres=# create database testdb;
CREATE DATABASE
postgres=# select datname from pg_database;
datname
-----------
template1
template0
postgres
testdb
(4 rows)

postgres=#

4.在云服务器上恢复数据

命令格式为:pg_restore -h <host> -p <port> -U <username> -d <dbname> <dumpdir> -c ,更多使用指引请参考 pg_restore 官方文档
参数
说明
host
自建 PostgreSQL 实例的连接地址。
port
自建 PostgreSQL 实例的端口。
username
自建 PostgreSQL 实例的账号名称。
dbname
需要导入的 database 名称。
dumpdir
需要导入的备份文件的路径和名称。
-c
-c 为可选参数,表示在恢复数据之前先清空目标数据库中涉及的数据。
示例如下:
pg_restore -h 127.0.0.1 -p 5432 -U dbadmin -d testdb testdb_bkp.dump -c

5.登录数据库查验数据

使用新建的 user 登录数据库,并查看数据是否恢复。示例如下:
[postgres@VM-0-14-tencentos root]$ psql -h127.0.0.1 -p5432 -Udbadmin -dpostgres
psql (16.0)
Type "help" for help.

postgres=# \\c testdb
You are now connected to database "testdb" as user "dbadmin".
testdb=# SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
table_name
------------
sbtest1
sbtest10
sbtest2
sbtest3
sbtest4
sbtest5
sbtest6
sbtest7
sbtest8
sbtest9
(10 rows)

testdb=#