阿里云数据库 PPAS 版支持通过逻辑备份文件将云上数据迁移到本地数据库。
操作步骤
- 通过 PostgreSQL 客户端,连接云数据库。
- 执行如下命令,备份数据。[backcolor=transparent]pg_dump [backcolor=transparent]-[backcolor=transparent]U username [backcolor=transparent]-[backcolor=transparent]h hostname [backcolor=transparent]-[backcolor=transparent]p port databasename [backcolor=transparent]-[backcolor=transparent]f filename
参数说明如下:
- username:数据库用户名
- hostname:数据库主机名
- port:数据库端口号
- databasename:要备份的数据库名
- filename:要生成的备份文件名称例如:
-
[backcolor=transparent]
pg_dump [backcolor=transparent]
-[backcolor=transparent]
U ppas_user [backcolor=transparent]
-[backcolor=transparent]
h rdsv07z563m7o25cj550public[backcolor=transparent]
.[backcolor=transparent]
ppas[backcolor=transparent]
.[backcolor=transparent]
rds[backcolor=transparent]
.[backcolor=transparent]
aliyuncs[backcolor=transparent]
.[backcolor=transparent]
com [backcolor=transparent]
-[backcolor=transparent]
p [backcolor=transparent]
3433[backcolor=transparent]
edb [backcolor=transparent]
-[backcolor=transparent]
f ppas[backcolor=transparent]
.[backcolor=transparent]
sql
将备份文件
ppas.sql 放到目标服务器中。执行如下命令将数据恢复到本地数据库。
- [backcolor=transparent]psql [backcolor=transparent]-[backcolor=transparent]U username [backcolor=transparent]-[backcolor=transparent]h hostname [backcolor=transparent]-[backcolor=transparent]d desintationdb [backcolor=transparent]-[backcolor=transparent]p port [backcolor=transparent]-[backcolor=transparent]f dumpfilename[backcolor=transparent].[backcolor=transparent]sql
参数说明如下:
- username:数据库用户名
- hostname:数据库地址
- port:数据库端口号
- databasename:数据库名
- filename:备份文件名称如:
-
[backcolor=transparent]
psql [backcolor=transparent]
-[backcolor=transparent]
U ppas_user [backcolor=transparent]
-[backcolor=transparent]
h localhost [backcolor=transparent]
-[backcolor=transparent]
d edb [backcolor=transparent]
-[backcolor=transparent]
p [backcolor=transparent]
5444[backcolor=transparent]
[backcolor=transparent]
-[backcolor=transparent]
f ppas[backcolor=transparent]
.[backcolor=transparent]
sql
由于 RDS 数据库的权限设置和本地数据库不一致,在数据导入过程当中可能会出现一些与权限相关的 WARNING 或 ERROR,可以忽略,如:
- [backcolor=transparent]WARNING[backcolor=transparent]:[backcolor=transparent] [backcolor=transparent]no[backcolor=transparent] privileges could be revoked [backcolor=transparent]for[backcolor=transparent] [backcolor=transparent]"xxxxx"
- [backcolor=transparent]ERROR[backcolor=transparent]:[backcolor=transparent] role [backcolor=transparent]"xxxxx"[backcolor=transparent] does [backcolor=transparent]not[backcolor=transparent] exist