如何手动迁移postgresql数据库账号及对象权限

环境准备

1. 在目标库所在子网下申请一台ECS，建议规格：2U/4G，建议磁盘大小：40G；操作系统：CentOS7.X。确保与源库的网络是连通的。
2. 安装目标库对应版本的postgresql客户端工具，以pg12为例，命令如下。

   安装 PostgreSQL 仓库 RPM 包：yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

   更新 yum 缓存： yum clean all;yum makecache

   安装pg12客户端包: yum install -y postgresql12

数据库账号迁移

1. 创建并进入独立的目录。

   mkdir -p ./sqls; cd ./sqls

2. 参考如下命令，使用具有系统表pg_catalog.authid上select权限的账号，从源数据库导出账号迁移的sql脚本。

   PGPASSWORD=源库密码 pg_dumpall -h 源库IP -p 源库端口 -U 源库用户名 -r | grep -v "rdsAdmin" | grep -v -E '^(CREATE|ALTER) ROLE "?(rdsBackup|rdsMetric|rdsRepl|rdsgroup|root)'  | awk  '{gsub(" NOSUPERUSER","");gsub(" SUPERUSER","");gsub(/ GRANTED BY.*;/, ";");print}' > roles.sql

   

   1. 命令中的连接信息需替换为实际的源库连接信息
   2. 如果源库连接账号无法获取系统表pg_catalog.authid的select权限，可使用如下命令导出账号迁移脚本后编辑脚本，在脚本中的CREATE ROLE语句“;”前添加“ password '密码'”（密码请替换为对应账号的实际密码）填充密码信息：

      PGPASSWORD=源库密码 pg_dumpall -h 源库IP -p 源库端口 -U 源库用户名 -r --no-role-passwords | grep -v "rdsAdmin" | grep -v -E '^(CREATE|ALTER) ROLE "?(rdsBackup|rdsMetric|rdsRepl|rdsgroup|root)'  | awk  '{gsub(" NOSUPERUSER","");gsub(" SUPERUSER","");gsub(/ GRANTED BY.*;/, ";");print}' > roles.sql

   3. 入云场景下，目标库不可操作superuser账号。
   4. 入云场景下，目标库高权限账号为root，如果源库为提供了高权限账号的云数据库，需将脚本中源库的高权限账号的授予替换为root。参考命令如下：

      sed -i -E 's/GRANT "?源库高权限账号"? TO/GRANT root TO/g' roles.sql

   5. 源库存在以"pg_"开头的定制系统账号时，如果这些账号在目标库不存在，需进行过滤，参考命令如下：

      sed -i -E  '/ "?系统账号名称"?[ ;]/d' roles.sql

   6. 可按照业务需要对roles.sql脚本进行进一步编辑。
3. 通过psql在目标库使用root用户执行源库导出的sql脚本。参考命令如下（请将命令中的连接信息需替换为实际的目标库连接信息）：

   PGPASSWORD=目标库密码 psql -h 目标库IP -p 目标库端口 -U root -d postgres -f roles.sql > roles.log

数据库对象权限迁移

1. 创建并进入独立的目录。

   mkdir -p ./sqls; cd ./sqls

2. 参考如下命令，使用高权限账号从源库导出对象权限迁移的sql脚本。

   PGPASSWORD=源库密码 pg_dump h 源库IP -p 源库端口 -U 源库用户名 -d 待迁移数据库名称 -s --section=pre-data |  grep -E '^ALTER .* OWNER TO |^GRANT |^REVOKE ' | grep -v '"rdsAdmin"' | grep -v -E ' ON .* pg_| TO pg_| FROM pg_| SERVER .* | FOREIGN TABLE .* ' | awk '{gsub(" TO root WITH GRANT OPTION","TO root");print}' > ./privileges.sql

   

   1. 命令中的连接信息需替换为实际的源库连接信息
   2. 如果源库存在厂商定制的系统模式，需过滤对应模式相关的对象，参考命令如下：

      sed -i -E '/ SCHEMA 系统模式名称 | ON .* 系统模式名称\./d' ./privileges.sql

   3. 如果源库存在厂商定制的系统表，需过滤对应系统表的操作，参考命令如下：

      sed -i -E '/ .* 系统表模式名\.系统表名称 /d' ./privileges.sql

   4. 如果源库存在厂商定制的系统账号，可按照业务需要过滤对应账号的操作，参考命令如下：

      sed -i -E '/ TO  "?系统账号名称"?[ ;]| FROM "?系统账号名称"?[ ;]/d' ./privileges.sql

   5. 可按照业务需要对privileges.sql脚本进行进一步编辑
3. 通过psql在目标库使用root用户执行源库导出的sql脚本。参考命令如下（请将命令中的连接信息需替换为实际的目标库连接信息）：

   PGPASSWORD=目标库密码 psql -h 目标库IP -p 目标库端口 -U root -d 目标数据库名称 -f privileges.sql > privileges.log

完整示例

如下为一个完整的示例模版，可按照业务场景进行修改后使用，其中中文描述的信息需修改为实际的数据库信息。

#!/bin/bash

SRC_DB_HOST=源库IP
SRC_DB_PORT=源库端口
SRC_DB_USER=源库用户名
SRC_DB_PASS=源库密码
SRC_DB_NAME=源数据库名称
DEST_DB_HOST=目标库IP
DEST_DB_PORT=目标库端口
DEST_DB_USER=目标库用户名
DEST_DB_PASS=目标库密码
DEST_DB_NAME=目标数据库名称

# 创建并进入独立目录
mkdir -p ./sqls
cd ./sqls

# 导出源库账号迁移脚本
PGPASSWORD=${SRC_DB_PASS} pg_dumpall -h ${SRC_DB_HOST} -p ${SRC_DB_PORT} -U ${SRC_DB_USER} -r | grep -v "rdsAdmin" | grep -v -E '^(CREATE|ALTER) ROLE "?(rdsBackup|rdsMetric|rdsRepl|rdsgroup|root)'  | awk  '{gsub(" NOSUPERUSER","");gsub(" SUPERUSER","");gsub(/ GRANTED BY.*;/, ";");print}' > roles.sql
# 修改高权限账号为root，视源库场景使用
sed -i -E 's/GRANT "?源库高权限账号"? TO/GRANT root TO/g' roles.sql
# 过滤厂商定制的系统账号，视源库场景使用
sed -i -E  '/ "?系统账号名称"?[ ;]/d' roles.sql
# 目标库执行账号迁移脚本
PGPASSWORD=${DEST_DB_PASS} psql -h ${DEST_DB_HOST} -p ${DEST_DB_PORT} -U root -d postgres -f roles.sql > roles.log

# 导出源库对象权限迁移脚本
PGPASSWORD=${SRC_DB_PASS} pg_dump -h ${SRC_DB_HOST} -p ${SRC_DB_PORT} -U ${SRC_DB_USER} -d ${SRC_DB_NAME} -s --section=pre-data | grep -E '^ALTER .* OWNER TO |^GRANT |^REVOKE ' | grep -v '"rdsAdmin"' | grep -v -E ' ON .* pg_| TO pg_| FROM pg_| SERVER .* | FOREIGN TABLE .* ' | awk '{gsub(" TO root WITH GRANT OPTION","TO root");print}' > ./privileges.sql
# 过滤厂商定制的系统模式，视源库场景使用
sed -i -E '/ SCHEMA 系统模式名称 | ON .* 系统模式名称\./d' ./privileges.sql
# 过滤厂商定制的系统表，视源库场景使用
sed -i -E '/ .* 系统表模式名\.系统表名称 /d' ./privileges.sql
# 过滤厂商定制的系统账号，视源库场景使用
sed -i -E '/ TO  "?系统账号名称"?[ ;]| FROM "?系统账号名称"?[ ;]/d' ./privileges.sql
# 目标库执行对象权限迁移脚本
PGPASSWORD=${DEST_DB_PASS} psql -h ${DEST_DB_HOST} -p ${DEST_DB_PORT} -U root -d ${DEST_DB_NAME} -f privileges.sql > privileges.log