登录 / 注册
IT168技术开发频道
IT168首页 > 技术开发 > 技术开发技术 > 正文

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

2015-11-10 00:00    it168网站 原创  作者: jackson198574 编辑: 崔月

  【IT168 技术】本操作目的是为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
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
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)

至此,迁移已经全部完成。

原文链接:http://blog.chinaunix.net/uid-20802110-id-5284598.html

  • IT168企业级IT168企业级
  • IT168文库IT168文库

扫一扫关注

行车视线文章推荐

首页 评论 返回顶部