亚马逊AWS官方博客

使用 pg_dump 将云下 PostgreSQL 迁移到AWS RDS PostgreSQL最佳实践

前言

本文主要讲述在将云下 PostgreSQL 数据库迁移到 RDS PostgreSQL 的指导原则及最佳实践，这里主要通过pg_dump来完成迁移。

关于 RDS PostgreSQL

PostgreSQL 号称“世界上最先进开源关系型数据库”，其本身功能丰富而强大，经过30多年的积极开发，在可靠性，功能稳健性和性能方面赢得了良好的声誉。PostgreSQL 已成为许多企业开发人员和初创公司的首选开源关系数据库。Amazon RDS 让用户能够在云中轻松设置、操作和扩展 PostgreSQL 部署。借助 Amazon RDS ，你可以在几分钟内完成可扩展的 PostgreSQL 部署，不仅经济实惠，而且可以调整硬件容量。Amazon RDS 可管理复杂而耗时的管理任务，例如 PostgreSQL 软件安装和升级、存储管理、为获得高可用性和高读取吞吐量而进行的复制，以及为灾难恢复而进行的备份。

借助 Amazon RDS for PostgreSQL，你可以访问非常熟悉的 PostgreSQL 数据库引擎的功能。这意味着你当前用于现有数据库的代码、应用程序和工具也可以用于 Amazon RDS 。Amazon RDS 支持 PostgreSQL 主要版本 11，该版本包括对性能、可靠性、事务管理和查询并行性等方面的多项增强。

只需在 AWS 管理控制台中单击几下鼠标，即可使用自动配置的数据库参数部署 PostgreSQL 数据库，以获得最佳性能。Amazon RDS for PostgreSQL 数据库既可以按照标准存储模式预置，也可以按照预配置 IOPS 模式配置。预置完成后，你可以扩展到 16TB 的存储容量和 40000 IOPS。此外，Amazon RDS for PostgreSQL 还支持你进行扩展并超出单个数据库部署的容量，以便处理高读取量的数据库工作负载。

迁移注意事项

  • 在 RDS PostgreSQL 里出于安全上的考虑，并没有 superuser 和 replication 角色，对应的是 rds_superuser 和 rds_replication
  • 本地 PostgreSQL 我们通常会安装在 postgres 用户下，这个时候在 PostgreSQL 中对应的就会有一个 postgres 超级用户，在 RDS PostgreSQL 中缺省没有此用户
  • 由于 RDS PostgreSQL 的主用户（ master user ）并不具备 superuser 权限，这就导致很多在云下使用 pg_dump 进行数据迁移可以正常执行的命令，在 RDS 上执行会报错

数据库迁移过程

环境说明：云下 PostgreSQL 版本为9.6.12，RDS PostgreSQL 版本为9.6.11。

使用 pg_dumpall 导出角色/用户创建脚本

[postgres@ip-172-31-46-20 ~]$ pg_dumpall -r >role.sql
[postgres@ip-172-31-46-20 ~]$ cat role.sql
--
-- PostgreSQL database cluster dump
--
 
SET default_transaction_read_only = off;
 
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
 
--
-- Roles
--
 
CREATE ROLE testuseraa;
ALTER ROLE testuseraa WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md551420aa22858a6506739fddd0e97c1eb';
CREATE ROLE testuserbb;
ALTER ROLE testuserbb WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5c69d3a23eec2bd9b90d9a9ffa7ce57c3';
CREATE ROLE testusercc;
ALTER ROLE testusercc WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md58048b4e16cea3f1d18518238154f3f04';
CREATE ROLE testuserdd;
ALTER ROLE testuserdd WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5459fbe52f13c2aeb900d7350851b296a';
CREATE ROLE testuseree;
ALTER ROLE testuseree WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5ddc777df0c0473c28a019428d99bc67e';
CREATE ROLE testuserff;
ALTER ROLE testuserff WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5e89c848ed30b2f1fc3189df1f53d3aa0';
CREATE ROLE testusergg;
ALTER ROLE testusergg WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5f7bea764f73f71ea11c5ed7f7c561f9d';
CREATE ROLE testuserhh;
ALTER ROLE testuserhh WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5d8a2a0b9299a25ac75655bfcb08dcab4';
CREATE ROLE testuserii;
ALTER ROLE testuserii WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5f3cb1f8cdf2c350ab93820c47fca9260';
CREATE ROLE testuserkk;
ALTER ROLE testuserkk WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md51718a203a679fb624bc3937eeb760328';
CREATE ROLE testuserll;
ALTER ROLE testuserll WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md561c2fee81e111fcd33e2e8655d538ad8';
CREATE ROLE testusermm;
ALTER ROLE testusermm WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5060eea855664fa433e840b22cdfe689e';
CREATE ROLE testusernn;
ALTER ROLE testusernn WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md50d4e1afd527f87f358dc09b825ec629e';
CREATE ROLE testuserpay;
ALTER ROLE testuserpay WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md54ae41a6a4a668497de9e5d65f3f333eb';
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'md509a2206c3e28caa5aa5a89ffa10d545a';
CREATE ROLE testuserpu;
ALTER ROLE testuserpu WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md58a8e9e7e3c6d2b0a581bae3183b306d2';
CREATE ROLE sensu;
ALTER ROLE sensu WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5cfc9d4fffd6c3f0866087bd861751990';
CREATE ROLE testusersf;
ALTER ROLE testusersf WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5c6984db34f952d0a1f982c76c62aa2b7';
CREATE ROLE smartlms;
ALTER ROLE testusermaWITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md59c4e987eaddd544b72efb115709fce6d';
CREATE ROLE testuserdr;
ALTER ROLE testuserdr WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md54a3f206a24e10cfe3cb34af5fea7daed';
CREATE ROLE testuserwe;
ALTER ROLE testuserwe WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md53fd982fd2b6b7bb31a07ae96ce772795';
CREATE ROLE zabbix;
ALTER ROLE zabbix WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5435f13d666b53dd9b4b829e237213fd8';
ALTER ROLE testuserdd SET default_transaction_read_only TO 'on';
 
 
 
--
-- PostgreSQL database cluster dump complete
--


[postgres@ip-172-31-46-20 ~]$
Python

修改角色/用户创建脚本

依次按如下内容修改role.sql脚本文件，保存为role_change.sql：

  • 去掉所有的 CREATE ROLE 语句
  • 将带有 WITH 选项的 ALTER ROLE 语句全部替换为 CREATE ROLE
  • 去掉 postgres 用户，这是本地 PostgreSQL 的 superuser 用户， RDS 上没有此用户，故使用 master user 代替
CREATE ROLE testuseraa WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md551420aa22858a6506739fddd0e97c1eb';
CREATE ROLE testuserbb WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5c69d3a23eec2bd9b90d9a9ffa7ce57c3';
CREATE ROLE testusercc WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md58048b4e16cea3f1d18518238154f3f04';
CREATE ROLE testuserdd WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5459fbe52f13c2aeb900d7350851b296a';
CREATE ROLE testuseree WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5ddc777df0c0473c28a019428d99bc67e';
CREATE ROLE testuserff WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5e89c848ed30b2f1fc3189df1f53d3aa0';
CREATE ROLE testusergg WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5f7bea764f73f71ea11c5ed7f7c561f9d';
CREATE ROLE testuserhh WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5d8a2a0b9299a25ac75655bfcb08dcab4';
CREATE ROLE testuserii WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5f3cb1f8cdf2c350ab93820c47fca9260';
CREATE ROLE testuserkk WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md51718a203a679fb624bc3937eeb760328';
CREATE ROLE testuserll WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md561c2fee81e111fcd33e2e8655d538ad8';
CREATE ROLE testusermm WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5060eea855664fa433e840b22cdfe689e';
CREATE ROLE testusernn WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md50d4e1afd527f87f358dc09b825ec629e';
CREATE ROLE testuserpay WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md54ae41a6a4a668497de9e5d65f3f333eb';
CREATE ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS PASSWORD 'md509a2206c3e28caa5aa5a89ffa10d545a';
CREATE ROLE testuserpu WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md58a8e9e7e3c6d2b0a581bae3183b306d2';
CREATE ROLE testusersf WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5c6984db34f952d0a1f982c76c62aa2b7';
CREATE ROLE testusermaWITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md59c4e987eaddd544b72efb115709fce6d';
CREATE ROLE testuserdr WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md54a3f206a24e10cfe3cb34af5fea7daed';
CREATE ROLE testuserwe WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md53fd982fd2b6b7bb31a07ae96ce772795';
CREATE ROLE zabbix WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5435f13d666b53dd9b4b829e237213fd8';
ALTER ROLE testuserdd SET default_transaction_read_only TO 'on';
Python

使用 pg_dump 按数据库分别导出

这里以 testuserbb 数据库为例进行操作

[postgres@ip-172-31-46-20 ~]$ pg_dump -C -d testuserbb > testuserbb.sql
[postgres@ip-172-31-46-20 ~]$ 
[postgres@ip-172-31-46-20 ~]$ ls -l testuserbb.sql 
-rw-r--r-- 1 postgres postgres 13532 Apr  2 13:08 testuserbb.sql
[postgres@ip-172-31-46-20 ~]$ 
[postgres@ip-172-31-46-20 ~]$ more testuserbb.sql 
--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.12
-- Dumped by pg_dump version 9.6.12

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: testuserbb; Type: DATABASE; Schema: -; Owner: postgres
--

CREATE DATABASE testuserbb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';


ALTER DATABASE testuserbb OWNER TO postgres;

\connect testuserbb

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
........
........
........
.......
Python

修改导出文件

改动内容：

  • 将 OWNER TO postgres 调整为 OWNER TO root ，这里的root为RDS PostgreSQL的master user
  • 将 OWNER TO testuserbb 调整为 OWNER TO root

如下使用 grep 命令过滤出来的红色高亮部分内容，这里请先保留下来，在数据导入完成之后需要执行这些命令。

[postgres@ip-172-31-46-20 ~]$ cat testuserbb.sql | grep “OWNER TO”
ALTER DATABASE testuserbb OWNER TO postgres;
ALTER TABLE public.flyway_schema_history OWNER TO testuserbb;
ALTER TABLE public.role_permission OWNER TO testuserbb;
ALTER TABLE public.role_permission_id_seq OWNER TO testuserbb;
ALTER TABLE public.sys_permission OWNER TO testuserbb;
ALTER TABLE public.sys_permission_id_seq OWNER TO testuserbb;
ALTER TABLE public.sys_role OWNER TO testuserbb;
ALTER TABLE public.sys_role_id_seq OWNER TO testuserbb;
ALTER TABLE public.sys_user OWNER TO testuserbb;
ALTER TABLE public.sys_user_id_seq OWNER TO testuserbb;
ALTER TABLE public.sys_user_operation_log OWNER TO testuserbb;
ALTER TABLE public.sys_user_operation_log_id_seq OWNER TO testuserbb;
ALTER TABLE public.user_role OWNER TO testuserbb;
ALTER TABLE public.user_role_id_seq OWNER TO testuserbb;

[postgres@ip-172-31-46-20 ~]$ sed -i ‘s/postgres/root/g’ testuserbb.sql

[postgres@ip-172-31-46-20 ~]$ sed -i ‘s/OWNER TO testuserbb/OWNER TO root/g’ testuserbb.sql

[postgres@ip-172-31-46-20 ~]$ cat testuserbb.sql | grep “OWNER TO”

ALTER DATABASE testuserbb OWNER TO root;

ALTER TABLE public.flyway_schema_history OWNER TO root;

ALTER TABLE public.role_permission OWNER TO root;

ALTER TABLE public.role_permission_id_seq OWNER TO root;

ALTER TABLE public.sys_permission OWNER TO root;

ALTER TABLE public.sys_permission_id_seq OWNER TO root;

ALTER TABLE public.sys_role OWNER TO root;

ALTER TABLE public.sys_role_id_seq OWNER TO root;

ALTER TABLE public.sys_user OWNER TO root;

ALTER TABLE public.sys_user_id_seq OWNER TO root;

ALTER TABLE public.sys_user_operation_log OWNER TO root;

ALTER TABLE public.sys_user_operation_log_id_seq OWNER TO root;

ALTER TABLE public.user_role OWNER TO root;

ALTER TABLE public.user_role_id_seq OWNER TO root;

[postgres@ip-172-31-46-20 ~]$

将上述橙色高亮部分替换导出文件testuserbb.sql最后的如下四条命令：

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
Bash

导入数据：执行角色/用户创建脚本

这里以RDS master user执行：

[postgres@ip-172-31-46-20 ~]$ psql -hmydb.xxxxxxxxxxxx.rds.cn-northwest-1.amazonaws.com.cn postgres root
Password for user root: 
psql (9.6.12, server 9.6.11)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> 
postgres=> select user;
 current_user 
--------------
 root
(1 row)

postgres=> \i role_change.sql
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
ALTER ROLE
postgres=>
Python

导入数据：执行数据脚本

忽略出现的comment on extension执行报错。

postgres=> \i testuserbb.sql
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
CREATE DATABASE
ALTER DATABASE
psql (9.6.12, server 9.6.11)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "testuserbb" as user "root".
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
CREATE EXTENSION
psql:testuserbb.sql:50: ERROR:  must be owner of extension plpgsql
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
COPY 3
COPY 0
 setval 
--------
      1
(1 row)

COPY 0
 setval 
--------
      1
(1 row)

COPY 0
 setval 
--------
      1
(1 row)

COPY 2
 setval 
--------
      2
(1 row)

COPY 4
 setval 
--------
      4
(1 row)

COPY 0
 setval 
--------
      1
(1 row)

ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
testuserbb=>
Python

查看导入的数据

这里我们看到导入表的 owner 都已经调整为 root 了（ RDS 的 master user ），因此原先的用户没有查询权限。

testuserbb=> \c testuserbb testuserbb
Password for user testuserbb: 
psql (9.6.12, server 9.6.11)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "testuserbb" as user "testuserbb".
testuserbb=> \dt
                List of relations
 Schema |          Name          | Type  | Owner 
--------+------------------------+-------+-------
 public | flyway_schema_history  | table | root
 public | role_permission        | table | root
 public | sys_permission         | table | root
 public | sys_role               | table | root
 public | sys_user               | table | root
 public | sys_user_operation_log | table | root
 public | user_role              | table | root
(7 rows)

testuserbb=> select * from flyway_schema_history;
ERROR:  permission denied for relation flyway_schema_history
testuserbb=>
Python

调整属主为原用户

以 root 用户（ RDS master user ）执行<修改导出文件>章节红色高亮的命令

ALTER TABLE public.flyway_schema_history OWNER TO testuserbb;

ALTER TABLE public.role_permission OWNER TO testuserbb;

ALTER TABLE public.role_permission_id_seq OWNER TO testuserbb;

ALTER TABLE public.sys_permission OWNER TO testuserbb;

ALTER TABLE public.sys_permission_id_seq OWNER TO testuserbb;

ALTER TABLE public.sys_role OWNER TO testuserbb;

ALTER TABLE public.sys_role_id_seq OWNER TO testuserbb;

ALTER TABLE public.sys_user OWNER TO testuserbb;

ALTER TABLE public.sys_user_id_seq OWNER TO testuserbb;

ALTER TABLE public.sys_user_operation_log OWNER TO testuserbb;

ALTER TABLE public.sys_user_operation_log_id_seq OWNER TO testuserbb;

ALTER TABLE public.user_role OWNER TO testuserbb;

ALTER TABLE public.user_role_id_seq OWNER TO testuserbb;

切换到 testuserbb 数据库以 root 用户连接：

testuserbb=> \c testuserbb root
Password for user root: 
psql (9.6.12, server 9.6.11)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "testuserbb" as user "root".
testuserbb=> \dt
                List of relations
 Schema |          Name          | Type  | Owner 
--------+------------------------+-------+-------
 public | flyway_schema_history  | table | root
 public | role_permission        | table | root
 public | sys_permission         | table | root
 public | sys_role               | table | root
 public | sys_user               | table | root
 public | sys_user_operation_log | table | root
 public | user_role              | table | root
(7 rows)

testuserbb=> ALTER TABLE public.flyway_schema_history OWNER TO testuserbb;
ALTER TABLE
testuserbb=> ALTER TABLE public.role_permission OWNER TO testuserbb;
ALTER TABLE
testuserbb=> ALTER TABLE public.role_permission_id_seq OWNER TO testuserbb;
ALTER TABLE
testuserbb=> ALTER TABLE public.sys_permission OWNER TO testuserbb;
ALTER TABLE
testuserbb=> ALTER TABLE public.sys_permission_id_seq OWNER TO testuserbb;
ALTER TABLE
testuserbb=> ALTER TABLE public.sys_role OWNER TO testuserbb;
ALTER TABLE
testuserbb=> ALTER TABLE public.sys_role_id_seq OWNER TO testuserbb;
ALTER TABLE
testuserbb=> ALTER TABLE public.sys_user OWNER TO testuserbb;
ALTER TABLE
testuserbb=> ALTER TABLE public.sys_user_id_seq OWNER TO testuserbb;
ALTER TABLE
testuserbb=> ALTER TABLE public.sys_user_operation_log OWNER TO testuserbb;
ALTER TABLE
testuserbb=> ALTER TABLE public.sys_user_operation_log_id_seq OWNER TO testuserbb;
ALTER TABLE
testuserbb=> ALTER TABLE public.user_role OWNER TO testuserbb;
ALTER TABLE
testuserbb=> ALTER TABLE public.user_role_id_seq OWNER TO testuserbb;
ALTER TABLE
testuserbb=> 
testuserbb=> \dt
                 List of relations
 Schema |          Name          | Type  |  Owner  
--------+------------------------+-------+---------
 public | flyway_schema_history  | table | testuserbb
 public | role_permission        | table | testuserbb
 public | sys_permission         | table | testuserbb
 public | sys_role               | table | testuserbb
 public | sys_user               | table | testuserbb
 public | sys_user_operation_log | table | testuserbb
 public | user_role              | table | testuserbb
(7 rows)

testuserbb=> 
testuserbb=> 
testuserbb=> select * from flyway_schema_history;
 installed_rank | version | description | type |        script         |  checksum   | installed_by |        installed_on        | execution_time | success 
----------------+---------+-------------+------+-----------------------+-------------+--------------+----------------------------+----------------+---------
              1 | 1       | init        | SQL  | V1__init.sql          | -1594168058 | testuserbb      | 2018-08-13 03:08:11.956161 |              9 | t
              2 | 1.1     | Description | SQL  | V1.1__Description.sql | -1794063459 | testuserbb      | 2018-08-13 03:08:11.983154 |             14 | t
              3 | 1.2     | Description | SQL  | V1.2__Description.sql |  -523681947 | testuserbb      | 2018-11-01 03:29:22.104608 |             42 | t
(3 rows)

testuserbb=>
Python

问题说明

错误01： comment on extension 执行报错

comment on extension 只是给 extension 添加一个注释，并不影响实际使用，可以忽略。另外， plpgsql  缺省在 RDS  里就已经有了，不需再进行创建。可以使用 \dx命令确认一下：

testuserbb=> \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

testuserbb=>
Python

错误02： public SCHEMA 赋权命令执行报错

如下4条命令执行会报错：

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
Python
GRANT ALL ON SCHEMA public TO root;
GRANT ALL ON SCHEMA public TO PUBLIC;
Python

这两个命令的含义是允许 root 或任意登录到一个 database 的用户都可以具备 create/usage 权限，这是缺省行为，因为每当 create database 的时候都会有一个缺省的 public schema 。所以这两条命令不执行也是可以的。

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM root;
Bash

这两条命令是相反的操作，禁止用户登录到数据库之后可以随便创建数据库对象。由于 RDS PostgreSQL master user 没有 superuser 权限，没办法直接执行这两条命令。折中的办法我们可以使用 master user 撤消数据库的所有权限，然后在需要时显式添加回这些权限，以此来实现这两条命令的功用。

以 master user 使用以下命令从数据库中删除所有权限：

revoke all on database <database name> from public;
Python

然后可以根据需要将具体的权限重新添加到特定的用户上。以下命令向名为 mytestuser 的用户授予对名为 test 的数据库的连接权限：

grant connect on database test to mytestuser;
Python

因此，这里可以根据实际需要看是否执行上述这4条命令。如果保持缺省行为，就不需要执行了。

本篇作者

闫静

AWS专业服务团队咨询顾问、云架构师。目前主要负责企业级客户迁移上云、云架构设计和优化、云上容灾、数据库上云相关的咨询、规划及实施。热衷于云基础架构规划、云端数据库架构设计、大数据架构部署、容器平台运维等领域的研究和学习。加入 AWS 之前，曾在 Oracle(中国) 担任数据库售前工程师，在数据库架构设计、运维管理、高可用容灾等方面有多年实践经验。