Postgres不同实例、不同用户直接的数据导出导入

公司生产环境在AWS使用了 Aurora Postgres Serveless 的数据库。

测试环境是在内网搭建了一个postgres 13数据库。

那测试和生产的实例不同，数据库不同，用户名也不同，需要把测试环境的A库的schema.public迁移到生产B库的schema.public。

普及一下概念，PG的权限分三层，数据库、schema、schema中的对象。

那么用pg_dump的话，如果不带任何参数，那会把权限也给带上，那就麻烦大了去了！

所以正确的导出、导入步骤如下：

一、首先要观察源库，看看都有什么extention，在schema.public有什么Functions

如上图所示，看到uuid_generate

1# 看看服务器版本
2select version();
3PostgreSQL 13.14

那就很奇怪，extension 只有一个plpgsql，functions却有一堆uuid_generate

那十有八九需要用到extension uuid-oosp

先去目的库建一个吧

1create extension "uuid-ossp";

二、dump数据

这里要注意，不要把任何权限的东西带进来

1pg_dump -h 172.16.8.1 -U postgres -d source --schema=public --no-owner --no-privileges --file=export.sql
2
3# 全备份的命令
4# export PGPASSWORD="xxxxxxxx"
5# pg_dump -U bbc -h localhost -p 5432 bbc > dc_system.sql

然后要编辑看看这个导入的文件

发现有create schema的语句，如果目的数据库已经有了，那需要去掉。

再看下面：

建立了一个FUNCTION，如果已经建立了extension，那也会报错，不过这2种错误我们都可以直接不用管，直接执行试试

三、导入目标库

跑一跑，看一看：

1psql -h 10.8.0.1 -U dest -d dest -f export.sql

报了不少错

与上面相对应，建schema.public和FUNCTION时的错，后面就没有错误了，这说明就正确导入了

四、一些措施

第三步我们可以看到有问题，Anyway，都无法避免。

都需要编辑export.sql或者忽略错误来避免。

测了一下，如果我们把schema.public给干掉，也无法避免这种情况！

 1#用admin用户登录 target 库，改变schema.public的owner
 2ALTER SCHEMA public OWNER TO target_user;
 3
 4#改好后用target_user登录 target 库
 5#这时候就可以干掉了schema.public了，然后重建，重建后就只有 target_user 对 schema.public 有权限了
 6DROP SCHEMA public CASCADE;
 7CREATE SCHEMA public AUTHORIZATION target_user;
 8
 9#如上，target_user对target库中的schema.public拥有所有权限
10#那就可以导入数据了
11
12#导入后需要把owner给改回去，依然需要用admin用户登录 target 库，改owner
13ALTER SCHEMA public OWNER TO admin;
14
15#然后赋予PUBLIC的UC权限
16GRANT USAGE ON SCHEMA public TO public;
17GRANT CREATE ON SCHEMA public TO public;
18
19#就恢复初始状态的权限了

上面的操作最好用pgAdmin来完成，否则太苦逼了。

Postgres不同实例、不同用户直接的数据导出导入

相关文章