PostgreSQL不同模式(SCHEMA)之间迁移数据

sdfouhdso888

  

操作目的：
  PostgreSQL数据库在不同模式之间迁移数据，可用于在异机数据迁移的场景。

  今天网友问到一个问题，是在数据迁移的场景中，想把源库的数据迁移到不同的schema下面，比如从schema gaoqiang，迁移到schema mayday。

  schema（模式）这种概念在Oracle中，可以把用户认为就是schema，比如用户gaoqiang的模式就是gaoqiang；在其他数据库中不一定是一一严格对应的，具有一定的灵活性。在PostgreSQL数据库中，模式和用户可以单独创建，也可一起创建。
   
操作思路：
  从备份导出原有的schema gaoqiang的数据--->新建用户、模式 mayday--->修改相关配置--->导入数据到新的模式Mayday--->验证数据完整性以及属性


导出数据库music中的模式gaoqiang的表结构和数据：

-bash-4.1$ pg_dump -d music -n gaoqiang -f /tmp/gaoqiang.sql

• -bash-4.1$ cat gaoqiang.sql
  
  
• --
  
• -- PostgreSQL database dump
  
• --
  
• 
  
• SET statement_timeout = 0;
  
• SET lock_timeout = 0;
  
• SET client_encoding = 'UTF8';
  
• SET standard_conforming_strings = on;
  
• SET check_function_bodies = false;
  
• SET client_min_messages = warning;
  
• 
  
• --
  
• -- Name: gaoqiang; Type: SCHEMA; Schema: -; Owner: gaoqiang
  
• --
  
• 
  
• CREATE SCHEMA gaoqiang;
  
• 
  
• 
  
• ALTER SCHEMA gaoqiang OWNER TO gaoqiang;
  
• 
  
• SET search_path = gaoqiang, pg_catalog; ----标红的2行一个是决定导入到那个schema中，一个是决定表的属性，还可以设定表空间和oid，如果有需要可以设置
  
• 
  
• SET default_tablespace = '';
  
• 
  
• SET default_with_oids = false;
  
• 
  
• --
  
• -- Name: summary; Type: TABLE; Schema: gaoqiang; Owner: gaoqiang; Tablespace:
  
• --
  
• 
  
• CREATE TABLE summary (
  
•     id integer,
  
•     name text
  
• );
  
• 
  
• 
  
• ALTER TABLE summary OWNER TO gaoqiang;
  
• 
  
• --
  
• -- Data for Name: summary; Type: TABLE DATA; Schema: gaoqiang; Owner: gaoqiang
  
• --
  
• 
  
• COPY summary (id, name) FROM stdin;
  
• 1    GaoQiang
  
• 2    GaoQiang is not 2
  
• \.
  
• 
  
• 
  
• --
  
• -- PostgreSQL database dump complete
  
• --
  

创建新的用户和模式：
music=# \c music postgres
You are now connected to database "music" as user "postgres".
music=# create user mayday with password 'mayday';
CREATE ROLE
music=# create schema authorization mayday;
CREATE SCHEMA


修改pg_dump导出的文件gaoqiang.sql：
把原来的：
SET search_path = gaoqiang, pg_catalog;

改成：
SET search_path = mayday, pg_catalog;

-bash-4.1$ vi gaoqiang.sql
-bash-4.1$ cat gaoqiang.sql |grep mayday
SET search_path = mayday, pg_catalog;


开始迁移:
-bash-4.1$ psql music </tmp/gaoqiang.sql
SET
SET
SET
SET
SET
SET
ERROR:  schema "gaoqiang" already exists
ALTER SCHEMA
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 2
-bash-4.1$



用mayday登录数据库查看表的属性：
-bash-4.1$ psql music mayday
psql (9.4.1)
Type "help" for help.

查看表属性：
music=> \d
          List of relations
Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
mayday | summary | table | gaoqiang   ---发现该表的属主属性有点问题
(1 row)



处理方法有2种，都很简单：

方法1：
-bash-4.1$ psql music postgres
psql (9.4.1)
Type "help" for help.

music=# alter table mayday.summary OWNER TO mayday;
ALTER TABLE
music=# \c music mayday
You are now connected to database "music" as user "mayday".
music=> \d
         List of relations
Schema |  Name   | Type  | Owner  
--------+---------+-------+--------
mayday | summary | table | mayday
(1 row)



方法2：



先删除刚才的测试表，然后再进行导入操作，避免冲突：
music=> drop table summary;
DROP TABLE


在导出的脚本中有这么一行：
ALTER TABLE summary OWNER TO gaoqiang;

在修改模式路径的时候，直接修改该语句也可。


-bash-4.1$ vi /tmp/gaoqiang.sql
ALTER TABLE summary OWNER TO mayday;






music=> \d
          List of relations
Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
public | summary | table | postgres
(1 row)

music=> \q

开始迁移：
-bash-4.1$ psql music </tmp/gaoqiang.sql
SET
SET
SET
SET
SET
SET
ERROR:  schema "gaoqiang" already exists
ALTER SCHEMA
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 2
-bash-4.1$






验证表的属主和模式已改变：
-bash-4.1$ psql music mayday
psql (9.4.1)
Type "help" for help.
music=> \d
         List of relations
Schema |  Name   | Type  | Owner  
--------+---------+-------+--------
mayday | summary | table | mayday
(1 row)



验证数据完整性与属性：

用DBA用户连接数据库查询2张不同模式的表：
music=> \c music postgres
You are now connected to database "music" as user "postgres".
music=# select * from gaoqiang.summary;
id |       name        
----+-------------------
  1 | GaoQiang
  2 | GaoQiang is not 2
(2 rows)

music=# select * from mayday.summary;
id |       name        
----+-------------------
  1 | GaoQiang
  2 | GaoQiang is not 2
(2 rows)

music=# \c music gaoqiang
You are now connected to database "music" as user "gaoqiang".
music=>\d

           List of relations
  Schema  |  Name   | Type  |  Owner   
----------+---------+-------+----------
gaoqiang | summary | table | gaoqiang
(1 row)


music=# select tablename,tableowner,schemaname from pg_tables where tablename = 'summary';
tablename | tableowner | schemaname
-----------+------------+------------
summary   | gaoqiang   | gaoqiang
summary   | mayday     | mayday
(4 rows)



OK！~~~