【AICon】 如何构建高效的 RAG 系统?RAG 技术在实际应用中遇到的挑战及应对策略?>>> 了解详情
写点什么

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

  • 2019-09-26
  • 本文字数:11214 字

    阅读完需:约 37 分钟

使用 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 ~]$

复制代码

修改角色/用户创建脚本

依次按如下内容修改 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';
复制代码

使用 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;...............................
复制代码

修改导出文件

改动内容:


  • 将 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;
复制代码

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

这里以 RDS master user 执行:


[postgres@ip-172-31-46-20 ~]$ psql -hmydb.xxxxxxxxxxxx.rds.cn-northwest-1.amazonaws.com.cn postgres rootPassword 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.sqlCREATE ROLECREATE ROLECREATE ROLECREATE ROLECREATE ROLECREATE ROLECREATE ROLECREATE ROLECREATE ROLECREATE ROLECREATE ROLECREATE ROLECREATE ROLECREATE ROLECREATE ROLECREATE ROLECREATE ROLECREATE ROLECREATE ROLECREATE ROLEALTER ROLEpostgres=>
复制代码

导入数据:执行数据脚本

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


postgres=> \i testuserbb.sqlSETSETSETSETSET set_config ------------ (1 row)
SETSETSETCREATE DATABASEALTER DATABASEpsql (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".SETSETSETSETSET set_config ------------ (1 row)
SETSETSETCREATE EXTENSIONpsql:testuserbb.sql:50: ERROR: must be owner of extension plpgsqlSETSETCREATE TABLEALTER TABLECREATE TABLEALTER TABLECREATE SEQUENCEALTER TABLEALTER SEQUENCECREATE TABLEALTER TABLECREATE SEQUENCEALTER TABLEALTER SEQUENCECREATE TABLEALTER TABLECREATE SEQUENCEALTER TABLEALTER SEQUENCECREATE TABLEALTER TABLECREATE SEQUENCEALTER TABLEALTER SEQUENCECREATE TABLEALTER TABLECREATE SEQUENCEALTER TABLEALTER SEQUENCECREATE TABLEALTER TABLECREATE SEQUENCEALTER TABLEALTER SEQUENCEALTER TABLEALTER TABLEALTER TABLEALTER TABLEALTER TABLEALTER TABLECOPY 3COPY 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 TABLEALTER TABLEALTER TABLEALTER TABLEALTER TABLEALTER TABLEALTER TABLECREATE INDEXtestuserbb=>
复制代码

查看导入的数据

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


testuserbb=> \c testuserbb testuserbbPassword 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_historytestuserbb=>
复制代码

调整属主为原用户

以 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 rootPassword 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 TABLEtestuserbb=> ALTER TABLE public.role_permission OWNER TO testuserbb;ALTER TABLEtestuserbb=> ALTER TABLE public.role_permission_id_seq OWNER TO testuserbb;ALTER TABLEtestuserbb=> ALTER TABLE public.sys_permission OWNER TO testuserbb;ALTER TABLEtestuserbb=> ALTER TABLE public.sys_permission_id_seq OWNER TO testuserbb;ALTER TABLEtestuserbb=> ALTER TABLE public.sys_role OWNER TO testuserbb;ALTER TABLEtestuserbb=> ALTER TABLE public.sys_role_id_seq OWNER TO testuserbb;ALTER TABLEtestuserbb=> ALTER TABLE public.sys_user OWNER TO testuserbb;ALTER TABLEtestuserbb=> ALTER TABLE public.sys_user_id_seq OWNER TO testuserbb;ALTER TABLEtestuserbb=> ALTER TABLE public.sys_user_operation_log OWNER TO testuserbb;ALTER TABLEtestuserbb=> ALTER TABLE public.sys_user_operation_log_id_seq OWNER TO testuserbb;ALTER TABLEtestuserbb=> ALTER TABLE public.user_role OWNER TO testuserbb;ALTER TABLEtestuserbb=> ALTER TABLE public.user_role_id_seq OWNER TO testuserbb;ALTER TABLEtestuserbb=> 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=>
复制代码

问题说明

错误 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=>
复制代码

错误 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;GRANT ALL ON SCHEMA public TO root;GRANT ALL ON SCHEMA public TO PUBLIC;
复制代码


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


REVOKE ALL ON SCHEMA public FROM PUBLIC;REVOKE ALL ON SCHEMA public FROM root;
复制代码


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


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


revoke all on database <database name> from public;
复制代码


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


grant connect on database test to mytestuser; 
复制代码


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


作者介绍:


闫静


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


本文转载自 AWS 技术博客。


原文链接:


https://amazonaws-china.com/cn/blogs/china/migrant-postgresql-to-aws-rds-postgresql-best-practice/


2019-09-26 16:44756
用户头像

发布了 1804 篇内容, 共 87.6 次阅读, 收获喜欢 73 次。

关注

评论

发布
暂无评论
发现更多内容

喜讯!阿里云数据库PolarDB荣获第12届PostgreSQL中国技术大会“开源数据库杰出贡献奖”

阿里云数据库开源

开源数据库 polarDB 阿里云数据库 PolarDB-PG PolarDB for PostgreSQL

云图说丨云数据库GaussDB(for MySQL)事务拆分大揭秘

华为云开发者联盟

数据库 后端 华为云 华为云开发者联盟 企业号 3 月 PK 榜

GitLab 凭借什么连续 3 年上榜 Gartner 应用程序安全测试魔力象限?听听 GitLab 自己的分析

极狐GitLab

DevOps DevSecOps 安全测试 极狐GitLab 安全合规

解密数仓高可用failover流程

华为云开发者联盟

数据库 后端 华为云 华为云开发者联盟 企业号 3 月 PK 榜

ChatGPT作者John Schulman:我们成功的秘密武器

OneFlow

人工智能 深度学习 ChatGPT

IoT平台设备标签功能和规则引擎组合最佳实践——设备接入类

阿里云AIoT

sql 监控 物联网 API 定位技术

及刻周边惠:拥抱HarmonyOS原子化服务

HarmonyOS开发者

HarmonyOS

瓴羊Quick BI真心不错,已获得官方认可!

对不起该用户已成仙‖

瓴羊Quick BI更合适“中国式报表”需求!

巷子

设备离线时控制指令如何下发:通过设备影子实现离线设备的控制指令触达方案——设备管理运维类

阿里云AIoT

物联网

Java面试一个月,心态崩了……

程序知音

Java java面试 Java进阶 后端技术 Java面试八股文

如何通过C#/VB.NET代码在Word中插入或删除脚注

在下毛毛雨

C# .net word 脚注

defi质押LP流动性挖矿dapp系统开发详情(案例)

开发微hkkf5566

DLRover:蚂蚁开源大规模智能分布式训练系统

SOFAStack

人工智能 互联网 DLRover

如何判断多账号是同一个人?用图技术搞定 ID Mapping

NebulaGraph

图数据库 风险控制 安全控制

面向新时代,海泰方圆战略升级!“1465”隆重发布!

电子信息发烧客

浪潮 KaiwuDB x 山东重工 | 打造离散制造业 IIoT 标杆解决方案

KaiwuDB

数据库 iiot 制造业

bucket表:数仓存算分离中CU与DN解绑的关键

华为云开发者联盟

数据库 后端 华为云 华为云开发者联盟 企业号 3 月 PK 榜

车载小程序发展现状:使用环境、用户体验、应用场景及未来趋势

没有用户名丶

小程序化

云计算生态该怎么做?阿里云计算巢打了个样

云布道师

云计算 阿里云

DLRover:蚂蚁开源大规模智能分布式训练系统

AI Infra

互联网 智能 训练智能

【物联网开发实战】- 设备上云方案详解——设备接入类

阿里云AIoT

物联网 传感器

探索ChatGPT技术在文本生成、机器翻译领域的简单应用 | 社区征文

兴科Sinco

人工智能 机器翻译 OpenAPI openai ChatGPT

博睿“她”力量 :这份专业值得信赖

博睿数据

博睿数据 节日祝福

CNStack 多集群服务:基于 OCM 打造完善的集群管理能力

阿里巴巴云原生

阿里云 云原生 kubenetes 集群管理

什么是信创产品?怎么成为信创产品?

行云管家

信创 国产化

汇率市场大幅波动,用友BIP全球司库助力企业外汇避险

用友BIP

金融 外汇避险

数据安全特点有哪些?现在企业如何保障数据安全?

行云管家

数据安全 堡垒机 数据泄露

2023年2月国产数据库大事记-墨天轮

墨天轮

数据库 opengauss TiDB oceanbase 国产数据库

喜马拉雅基于DeepRec构建AI平台实践

阿里云大数据AI技术

人工智能 深度学习 推理 企业号 3 月 PK 榜 稀疏学习

中小企业需要统一的快速开发平台吗?

力软低代码开发平台

使用 pg_dump 将云下 PostgreSQL 迁移到AWS RDS PostgreSQL最佳实践_文化 & 方法_亚马逊云科技 (Amazon Web Services)_InfoQ精选文章