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

  • 时间:
  • 浏览:0

music=# \c music gaoqiang

music=> \d

    id integer,

 public | summary | table | postgres

DROP TABLE

验证数据全版性与属性:

-bash-4.1$ psql music postgres

SET client_encoding = 'UTF8';

 summary   | gaoqiang   | gaoqiang

SET default_tablespace = '';

COPY 2

--

在导出的脚本含有这麼 一行:

-bash-4.1$ 

 Schema |  Name   | Type  |  Owner   

--

CREATE SCHEMA

SET search_path = mayday, pg_catalog;

SET search_path = gaoqiang, pg_catalog; ----标红的2行一俩个多是决定导入到那个schema中,一俩个多是决定表的属性,还可不不能设定表空间和oid,肯能有须要可不不能设置

SET

SET

验证表的属主和模式已改变:

SET search_path = mayday, pg_catalog;

--

  从备份导出原有的schema gaoqiang的数据--->新建用户、模式 mayday--->修改相关配置--->导入数据到新的模式Mayday--->验证数据全版性以及属性

music=# select * from mayday.summary;

 mayday | summary | table | gaoqiang   ---发现该表的属主属性有点儿大问题

COPY 2

ALTER TABLE summary OWNER TO gaoqiang;

-bash-4.1$ psql music mayday

music=# create schema authorization mayday;

COPY summary (id, name) FROM stdin;

SET

  Schema  |  Name   | Type  |  Owner   

  2 | GaoQiang is not 2

SET

 Schema |  Name   | Type  | Owner  

SET lock_timeout = 0;

--

music=# \c music postgres

创建新的用户和模式:

music=> \d

SET search_path = gaoqiang, pg_catalog;

--

-bash-4.1$ psql music 

ERROR:  schema "gaoqiang" already exists

ALTER TABLE

ALTER TABLE

CREATE TABLE

--

(1 row)

music=> \c music postgres

SET standard_conforming_strings = on;

ALTER TABLE

 gaoqiang | summary | table | gaoqiang

 Schema |  Name   | Type  | Owner  

查看表属性:

-bash-4.1$ psql music mayday

ALTER SCHEMA

--------+---------+-------+--------

操作目的:

-- PostgreSQL database dump

 id |       name        

--

           List of relations

-- PostgreSQL database dump complete

         List of relations

-bash-4.1$ 

music=# select * from gaoqiang.summary;

 id |       name        

ALTER SCHEMA

(4 rows)

2    GaoQiang is not 2

导出数据库music中的模式gaoqiang的表形态和数据:

 mayday | summary | table | mayday

music=# \c music mayday

先删除刚才的测试表,只是 我再进行导入操作,补救冲突:

在修改模式路径的然后,直接修改该励志的话 也可。

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

ALTER SCHEMA gaoqiang OWNER TO gaoqiang;

--

psql (9.4.1)

SET default_with_oids = false;

-----------+------------+------------

  今天外国明星微博 问到一俩个多大问题,是在数据迁移的场景中,想把源库的数据迁移到不同的schema下面,比如从schema gaoqiang,迁移到schema mayday。

法律最好的最好的办法2:

    name text

补救法律最好的最好的办法有2种,都很简单:

psql (9.4.1)

psql (9.4.1)

SET

  1 | GaoQiang

CREATE ROLE

CREATE TABLE

         List of relations

Type "help" for help.

SET statement_timeout = 0;

music=# create user mayday with password 'mayday';

用mayday登录数据库查看表的属性:

(2 rows)

--------+---------+-------+----------

--------+---------+-------+----------

          List of relations

You are now connected to database "music" as user "mayday".

 mayday | summary | table | mayday

SET

-bash-4.1$ vi gaoqiang.sql 

You are now connected to database "music" as user "postgres".

);

--------+---------+-------+--------

SET

法律最好的最好的办法1:

You are now connected to database "music" as user "gaoqiang".

(1 row)

music=> \d

You are now connected to database "music" as user "postgres".

bash-4.1$ cat gaoqiang.sql 

SET

-- Name: summary; Type: TABLE; Schema: gaoqiang; Owner: gaoqiang; Tablespace: 

--

  1 | GaoQiang

SET

  2 | GaoQiang is not 2

music=> \d

SET

  PostgreSQL数据库在不同模式之间迁移数据,可用于在异机数据迁移的场景。

CREATE SCHEMA gaoqiang;

SET

(2 rows)

SET

ALTER TABLE summary OWNER TO gaoqiang;

ERROR:  schema "gaoqiang" already exists

开始英文英语 迁移:

SET check_function_bodies = false;

把那我的:

SET

----+-------------------

1    GaoQiang

SET

SET

-bash-4.1$ psql music 

OK!~~~

开始英文英语 迁移:

 Schema |  Name   | Type  |  Owner   

\.

操作思路:

music=> \q

 tablename | tableowner | schemaname 

  schema(模式)这俩 概念在Oracle中,可不不能把用户认为只是 我schema,比如用户gaoqiang的模式只是 我gaoqiang;在或多或少数据库中 不一定是一一严格对应的,具有一定的灵活性。在PostgreSQL数据库中,模式和用户可不不能单独创建,也可同时创建。

----+-------------------

CREATE TABLE summary (

修改pg_dump导出的文件gaoqiang.sql:

改成:

--

SET

-- Name: gaoqiang; Type: SCHEMA; Schema: -; Owner: gaoqiang

SET

-- Data for Name: summary; Type: TABLE DATA; Schema: gaoqiang; Owner: gaoqiang

ALTER TABLE summary OWNER TO mayday;

music=> drop table summary;

Type "help" for help.

          List of relations

music=# alter table mayday.summary OWNER TO mayday;

----------+---------+-------+----------

SET

SET client_min_messages = warning;

music=> \d

用DBA用户连接数据库查询2张不同模式的表:

music=# select tablename,tableowner,schemaname from pg_tables where tablename = 'summary';

(1 row)

(1 row)

-bash-4.1$ vi /tmp/gaoqiang.sql 

-bash-4.1$ cat gaoqiang.sql |grep mayday

Type "help" for help.

 summary   | mayday     | mayday

(1 row)