pg_dump 迁移表到其他模式

作者：瀚高PG实验室 （Highgo PG Lab）- 天蝎座 

使用pg_dump方式将highgo数据库下的public.test表迁移到highgo数据库下的schema hg下


highgo=# select * from pg_tables where tablename='test';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | test      | highgo     |            | f          | f        | f           | f
(1 row)

highgo=# select * from test;
 a 
---
 1
(1 row)

highgo=# create user hg with password 'hg';
CREATE ROLE
highgo=# create schema authorization hg;
CREATE SCHEMA

pg_dump -h localhost -p 5866 -U highgo -F p -t test  -f '/home/highgo/beifen.dmp'

修改导出的文件/home/highgo/beifen.dmp。将模式和属主修改为hg。

[highgo@hgdb ~]$ psql -d highgo -U highgo -f '/home/highgo/beifen.dmp'
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
COPY 1

highgo=# select tablename,tableowner,schemaname from pg_tables where tablename = 'test';
 tablename | tableowner | schemaname 
-----------+------------+------------
 test      | highgo     | public
 test      | hg         | hg
(2 rows)

highgo=# select * from hg.test;
 a 
---
 1
(1 row)

成功！