PostgreSQL 迁移用户很简单 --- 我看你的好戏

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共2610人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 ）(1 2 3 4 5 6群均已爆满，新人进7群300+，8群，准备9群)

咱们接着上次那个没礼貌的同学说，64的问题我一直没告诉他，他也忘记了，这不最近遇到了新问题，又问我了。PostgreSQL 迁移中用户的问题是不少的尤其一些ORACLE ，MySQL的DBA的固有概念，可能理解PG的用户的使用方式存在一些概念难点。

我们看看这次又怎么了

(前期过往)

PostgreSQL  用户胡作非为只能受着 --- 警告他

PostgreSQL  加索引系统OOM 怨我了--- 不怨你怨谁

PostgreSQL  “我怎么就连个数据库都不会建？”   --- 你还真不会！

DBA： 诶，你最近写东西还挺多的，我再问个问题呗！

我：嗯，啥事我这有点忙

DBA: 我这事很简单，就一个用户的问题，你给看看，说两句我就会了，文档太麻烦了，我懒得看。

我：我建议你还是先看一下文档，比较好。文档是你的一个学习这个产品的老师，而不是到处去问人。

DBA: 诶，你这人怎么这么不敞亮，问你是给你面子。

我：说吧，你先留下，我一会看，现在忙。

DBA: 是这么回事，我们有一个数据ORACLE 的迁移到PostgreSQL,用户我建上了其实建立一个就可以了，原来我们ORACLE有20多个用户，每个用户是一个schema的Owner，其实要我说建一个用户不就完了都多省事，我们那个老DBA，就那个老梆子，又说我不对，我就说了他一句，“你行你上”，然后在问他该怎么弄，他就不搭理我了，你说我这怎么办，我怕我弄错了，所以过来问你一句。

DBA: 诶，半个小时了，你咋了，死过去了，我这问你呢？

我：我正忙，你等会，另外你们老DBA 说的是对的，当然还有一些情况没有了解，我也不能马上说你不对，你这样，我问点前情

1  你们的用户为什么有20多个 

2  你们ORACLE的程序和应用是怎么管理的 

3  你在PG上是怎么建立的用户，你打算怎么管理迁移后的用户和数据

DBA: 又这么多的问题，ORACLE 20多个每个用户对应一个应用，这也是我们那个老梆子弄的，要我简单，建立一个用户，然后这些应用就用这些表不就完了，就他非要那么弄？ 领导还说他管的好，井井有条，啥玩意，增加工作量。有时间不如多看看股票，我哪里有闲工夫给他弄这个，就顺着他来呗，要不他又得给我告状。

我： 你这就不对了，人家说你不对，你应该礼貌的问人家，为什么，另外你们老DBA对不同的应用建立不同的管理账号，这个是非常正确的，我觉得人家有一定的水平。

DBA: 水平，我看是屁股上挂暖壶，有个瓶子，你快点吧，你和他一样烦人，问个问题哪里那么多话。

我： 呵呵，我们先说说两个数据库的不同

1  ORACLE是没有逻辑库的概念的 

2  ORACLE按照业务或者应用程序模块来管理，那么用户相当于对应schema的管理者，这里ORACLE的schema可以理解为一个逻辑库（用其他数据库管理的视角）

我这样说你认同吗?

DBA: 差不多，就那样吧。

我： PostgreSQL 是包含了，实例，逻辑库，schema的概念的，也就是分三个层次来管理你的业务程序下的表。所以你应该弄清楚以下几点 

1  不同的应用程序是否互访对方的表 

2  是否有其他的用户，兼具了不同schema下的表的读取和写入的权限 

3  你们原有的程序开发准则下，是否不能互相访问对方的表，而是通过接口调用来访问对方的应用程序的表

DBA: 我C，你说了这么多，还没回答我问题，你和尚念经呢？这堆问题，我哪里知道，我问我们那个老梆子，他也不告诉我呀？你就说你怎么弄吧？

我：唉，这里我假设 

1 你们的数据库中的schema 针对的是不同的业务模块 

2 你们的开发规范中，不能进行不同业务模块中的表在数据库底层的互访模式，而是通过应用程序调用来进行

 3 这里我假设你们的数据库里面的数据容量都比较大，有分开备份的需求 所以注意我说的是按照以上的推论来进行的假设。

如果其中的开发规范，你们允许底层不同业务模块的表通过一个账号进行访问，那么这个推论就不能成立了。

DBA: 磨磨唧唧，赶紧的吧，我这听你半小时了，还没入主题。

我：你这样你有20多个用户，这里你可以建立20个POSTGRESQL 的DATABASE,然后将你的ORACLE的数据根据不同的schema来进行分配，每个schema分到一个PostgreSQL的逻辑库。然后你再建库的时候，你可以用20个PG的用户，分别成为这些库的Owner来模拟你原来的管理的风格。

DBA: 哦,这么简单，诶，不对吧，你这个，我一句就问住你，为什么POSTGRESQL 里面有schema ,为什么我不能把数据都导入到一个数据库里面，然后用不同的schema来分，和ORACLE一样不就完了，你这个我还的重新导数，太麻烦了。

我： 这个属于基础性的工作，你还是一次做好，不行和你们老DBA 道个歉，虚心的问人一下，你平时说话和你们老DBA也都是这样，没大没小吗？

DBA: 哼，你管得还真挺多，我就问你schema，PG 和ORACLE 有什么不同，你说就完了，哪里这么多话，唐僧转世。

我：呵呵，你这个可以百度一下，看官方的文档，这样会比较好

DBA: 你会不会，每次问你都这么肉？

我：百度搜的你自己看吧

Schema概念：

Oracle：在Oracle数据库中，schema是一个逻辑分组，用于包含数据库对象，比如表、视图、序列和存储过程等。一个用户可以包含多个schema，每个schema都有自己的命名空间，用于区分不同的对象。用户和schema之间有一对一或一对多的关系。

PostgreSQL：在PostgreSQL中，schema是一个命名空间，用于组织和管理数据库对象（如表、视图、函数等）。每个数据库有一个默认schema（称为public），可以创建多个不同的自定义schema来组织对象。在PostgreSQL中，用户和schema之间没有强制的一对一关系。

访问权限： Oracle：在Oracle中，用户可以拥有自己的schema，通过授权给用户相应的权限可以访问其拥有的schema中的对象。用户需要通过schema名.对象名的方式访问对象。

PostgreSQL：在PostgreSQL中，用户通过在所需的schema前添加前缀来访问对象，例如schema_name.table_name。同时，数据库管理员可以控制用户对不同schema中对象的访问权限。

默认schema：

Oracle：在Oracle中，每个用户都有一个默认的schema，用户在没有明确制定schema的情况下操作对象时，默认使用该schema中的对象。

PostgreSQL：在PostgreSQL中，默认的schema是public，用户可以在创建对象时选择将对象添加到哪个schema中。

DBA: 诶，我可查了，你还留一手，那个PUBLIC的安全问题是怎么回事，怎还和版本有关，我这个PG16还有这个问题吗？ 你这说话不说完的，赶紧的

我：开会，回来再说吧

总结： “不学无术，无以立身；不知礼节，无以立事”。

在我们从ORACLE迁移到POSTGRESQL 需要注意的地方有很多，账号是一个重点

1  原有的开发规范和使用账号的方式是什么

2  原有的ORACLE不同账号下是否有设置访问其他schema的需求

3  使用PostgreSQL 15之前的版本，会遇到public schema 下无权限账号可以创建object的问题，且此问题在PostgreSQL15后解决。

如果遇到PG15之前的版本，解决方案如下

1 在新建的数据库中回收PUBLIC的权限，运行如下的命令 REVOKE CREATE ON SCHEMA public FROM PUBLIC;

2 在数据库中创建新的schema，并且在新的schema下创建对象，如表，视图等

3 如开发需要，可以考虑 1  对特定的用户设置默认访问的schema
ALTER ROLE username SET search_path TO my_schema, public;

2  对整个的数据库进行默认schema的改变 ALTER DATABASE dbname SET search_path TO my_schema, public;