psql切换schema_PostgreSQL不同模式(SCHEMA)之间迁移数据-阿里云开发者社区

痛定思痛。 2022-12-31 15:25 560阅读 0赞

操作目的：

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

ERROR:  schema "gaoqiang" already exists

ALTER SCHEMA

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

ERROR:  schema "gaoqiang" already exists

ALTER SCHEMA

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！~~~

psql切换schema_PostgreSQL不同模式(SCHEMA)之间迁移数据-阿里云开发者社区

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关阿里云安装mysql启动报错_为什么mysqld启动报错-阿里云开发者社区

相关 hdfs phoenix 导入_将hdfs数据写入到phoenix-问答-阿里云开发者社区-阿里云

相关 psql切换schema_PostgreSQL不同模式(SCHEMA)之间迁移数据-阿里云开发者社区

相关 python 权限管理 mongodb_MongoDB权限管理代码分析-阿里云开发者社区

相关阿里mysql 触发器_mysql之触发器trigger-阿里云开发者社区

相关 ubuntu 安装配置mysql_Ubuntu安装配置mysql-阿里云开发者社区

相关 mysql 两行的差异_SELECT语句的两行之间的MySQL差异?mysql-问答-阿里云开发者社区-阿里云...

相关 mysql高级渗透_mysql高级渗透经验-阿里云开发者社区

相关 mdf转mysql sqlyog_使用sqlyog将sql server 迁移到mysql-阿里云开发者社区

相关阿里云RDS跨账号迁移数据