Table of Contents

• 源库
• 导出表结构 恢复时禁用触发器,不导出授权, 不导出 owner
• 导出序列结构(下面 srcdb_ddl 需要根据实际情况替换)
• 导出表数据
• 重置序列初始值
• 目标库以 data 用户执行
• 恢复序列
• 恢复表结构
• 恢复表数据
• 恢复序列初始值 [需要根据实际情况决定是否处理, 如果目标库中已经存在该序列]
• 授权序列
• 授权表

源库

导出表结构 恢复时禁用触发器,不导出授权, 不导出 owner

pg_dump -d otmspmps -n public -s -x --disable-triggers -O  -f tbs_schema_without_owner.sql  -T test1  -t  test2 -t test3 ...

导出序列结构(下面 srcdb_ddl 需要根据实际情况替换)

psql -X -qAt -d otmspmps -o seq_schema.sql <<EOF
select 'create sequence '||sequencename::text 
|| ' start with  ' || start_value::text
||' INCREMENT BY 1' || increment_by::text
|| ' MINVALUE ' || min_value::text
|| ' MAXVALUE ' || max_value::text
|| ' CACHE ' ||cache_size::text
|| case when cycle then ' CYCLE' else ' NO CYCLE' end
|| ';'
from pg_sequences where  schemaname='public' and sequenceowner='srcdb_ddl';
EOF

导出表数据

pg_dump -d otmspmps -n public -a -f tbs_data.sql  -T test1 -t test2 -t test3 ...

重置序列初始值

psql -X -qAt -d otmspmps -o seq_setval.sql <<EOF
select 'select setval(''' ||sequencename || ''',' || last_value || ', ''t'');'  from pg_sequences where  schemaname='public' and sequenceowner='srcdb_ddl' and last_value is not null;
EOF

目标库以 data 用户执行

恢复序列

psql -d posms -U targetdb_ddl -f seq_schema.sql 

恢复表结构

psql -d posms -U targetdb_ddl -f tbs_schema_without_owner.sql 

恢复表数据

psql -d posms -U targetdb_ddl -f tbs_data.sql 

恢复序列初始值 [需要根据实际情况决定是否处理, 如果目标库中已经存在该序列]

psql -d posms -U targetdb_ddl -f seq_setval.sql

授权序列

• 查看目标库序列权限

[postgres@cnsz381306:7236/(rasesql)posms][05-07.13:46:51]M=# \dp+ seq_epms_statement_number
                                              Access privileges
 Schema |           Name            |   Type   |       Access privileges       | Column privileges | Policies 
--------+---------------------------+----------+-------------------------------+-------------------+----------
 public | seq_epms_statement_number | sequence | targetdb_ddl=rwU/targetdb_ddl      +|                   | 
        |                           |          | r_targetdb_ddl_dml=rwU/targetdb_ddl |          
-- grant select,update,usage on sequence xxx to r_targetdb_ddl_dml;

授权表

• 查看目标库表的权限

[postgres@cnsz381306:7236/(rasesql)posms][05-07.13:46:51]M=# \dp+ seq_epms_statement_number
                                              Access privileges
 Schema |           Name            |   Type   |       Access privileges       | Column privileges | Policies 
--------+---------------------------+----------+-------------------------------+-------------------+----------
 public | seq_epms_statement_number | sequence | targetdb_ddl=rwU/targetdb_ddl      +|                   | 
        |                           |          | r_targetdb_ddl_dml=rwU/targetdb_ddl |          


[posmsopr@cnsz381306:7236/(rasesql)posms][05-07.13:48:49]M=> \dp+ test_jk_market
                                          Access privileges
 Schema |        Name        | Type  |        Access privileges        | Column privileges | Policies 
--------+--------------------+-------+---------------------------------+-------------------+----------
 public | test_jk_market | table | targetdb_ddl=arwdDxt/targetdb_ddl    +|                   | 
        |                    |       | r_targetdb_ddl_dml=arwd/targetdb_ddl +|                   | 
        |                    |       | r_targetdb_ddl_qry=r/targetdb_ddl    +|                   | 
        |                    |       | r_targetdb_ddl_dev_qry=r/targetdb_ddl+|                   | 
(1 row)


grant select,delete,update,insert on xxx to r_targetdb_ddl_dml;
grant select on xxx to r_targetdb_ddl_qry;
grant select on xxx to r_targetdb_ddl_dev_qry;