PostgreSQL Logical Backup’s TOC File

11 minute read

背景

一位网友问到的几个逻辑备份和还原的问题, 本文对这几个问题进行简单的讲解和测试。

1. 调整逻辑备份的还原顺序.

2. 逻辑备份的TOC文件的格式是什么意思.

3. 是否可以通过修改TOC文件中schema的内容达到把表导入不同schema的目的?

这个想法可能来自oracle imp的fromuser , touser参数.

下面来测试一下 :

1. 调整逻辑备份的还原顺序

pg_restore 的man中有示例

       To reorder database items, it is first necessary to dump the table of contents of the archive:  
  
           $ pg_restore -l db.dump > db.list  
  
       The listing file consists of a header and one line for each item, e.g.:  
  
           ;  
           ; Archive created at Mon Sep 14 13:55:39 2009  
           ;     dbname: DBDEMOS  
           ;     TOC Entries: 81  
           ;     Compression: 9  
           ;     Dump Version: 1.10-0  
           ;     Format: CUSTOM  
           ;     Integer: 4 bytes  
           ;     Offset: 8 bytes  
           ;     Dumped from database version: 8.3.5  
           ;     Dumped by pg_dump version: 8.3.8  
           ;  
           ;  
           ; Selected TOC Entries:  
           ;  
           3; 2615 2200 SCHEMA - public pasha  
           1861; 0 0 COMMENT - SCHEMA public pasha  
           1862; 0 0 ACL - public pasha  
           317; 1247 17715 TYPE public composite pasha  
           319; 1247 25899 DOMAIN public domain0 pasha  
  
       Semicolons start a comment, and the numbers at the start of lines refer to the internal archive ID assigned to  
       each item.  
  
       Lines in the file can be commented out, deleted, and reordered. For example:  
  
           10; 145433 TABLE map_resolutions postgres  
           ;2; 145344 TABLE species postgres  
           ;4; 145359 TABLE nt_header postgres  
           6; 145402 TABLE species_records postgres  
           ;8; 145416 TABLE ss_old postgres  
      
       could be used as input to pg_restore and would only restore items 10 and 6, in that order:  
  
           $ pg_restore -L db.list db.dump  

首先dmp一个库出来

pg_dump -f ./digoal.dmp -F c -C -h 127.0.0.1 -U postgres digoal  

然后创建TOC文件

pg_restore -f ./digoal.list -F c -l ./digoal.dmp  

TOC文件内容如下

;  
; Archive created at Thu Apr 12 09:32:27 2012  
;     dbname: digoal  
;     TOC Entries: 126  
;     Compression: -1  
;     Dump Version: 1.12-0  
;     Format: CUSTOM  
;     Integer: 4 bytes  
;     Offset: 8 bytes  
;     Dumped from database version: 9.1.3  
;     Dumped by pg_dump version: 9.1.3  
;  
;  
; Selected TOC Entries:  
;  
2878; 1262 16386 DATABASE - digoal postgres  
7; 2615 25070 SCHEMA - digoal digoal  
5; 2615 2200 SCHEMA - public postgres  
2879; 0 0 COMMENT - SCHEMA public postgres  
2880; 0 0 ACL - public postgres  
191; 3079 12425 EXTENSION - plpgsql   
2881; 0 0 COMMENT - EXTENSION plpgsql   
192; 3079 16442 EXTENSION - pgfincore   
2882; 0 0 COMMENT - EXTENSION pgfincore   
606; 1247 25073 TYPE digoal breakpoint digoal  
609; 1247 25076 TYPE digoal frame digoal  
612; 1247 25079 TYPE digoal proxyinfo digoal  
615; 1247 25082 TYPE digoal targetinfo digoal  
618; 1247 25085 TYPE digoal var digoal  
589; 1247 24696 TYPE public breakpoint postgres  
592; 1247 24699 TYPE public frame postgres  
601; 1247 24708 TYPE public proxyinfo postgres  
595; 1247 24702 TYPE public targetinfo postgres  
598; 1247 24705 TYPE public var postgres  
245; 1255 25086 FUNCTION digoal debugger_test(integer) digoal  
255; 1255 25276 FUNCTION digoal dy(text, text) digoal  
254; 1255 25277 FUNCTION digoal dy(text, text, integer) digoal  
246; 1255 25087 FUNCTION digoal exchange_rows(integer, integer) digoal  
247; 1255 25088 FUNCTION digoal exchange_rows(integer, numeric, integer, numeric) digoal  
248; 1255 25089 FUNCTION digoal exchange_rows(text, numeric, integer, numeric) digoal  
249; 1255 25090 FUNCTION digoal f_user_login(integer) digoal  
250; 1255 25091 FUNCTION digoal f_user_login_0(integer) digoal  
251; 1255 25092 FUNCTION digoal f_user_login_1(integer) digoal  
252; 1255 25093 FUNCTION digoal f_user_login_2(integer) digoal  
253; 1255 25094 FUNCTION digoal f_user_login_3(integer) digoal  
256; 1255 25095 FUNCTION digoal f_user_login_4(integer) digoal  
257; 1255 25096 FUNCTION digoal f_user_login_insupd_0(integer) digoal  
258; 1255 25097 FUNCTION digoal f_user_login_insupd_1(integer) digoal  
259; 1255 25098 FUNCTION digoal f_user_login_insupd_2(integer) digoal  
260; 1255 25099 FUNCTION digoal f_user_login_insupd_3(integer) digoal  
261; 1255 25100 FUNCTION digoal f_user_login_insupd_4(integer) digoal  
262; 1255 25101 FUNCTION digoal f_user_login_sel_0(integer) digoal  
263; 1255 25102 FUNCTION digoal f_user_login_sel_1(integer) digoal  
264; 1255 25103 FUNCTION digoal f_user_login_sel_2(integer) digoal  
265; 1255 25104 FUNCTION digoal f_user_login_sel_3(integer) digoal  
266; 1255 25105 FUNCTION digoal f_user_login_sel_4(integer) digoal  
267; 1255 25106 FUNCTION digoal f_user_login_upd_0(integer) digoal  
268; 1255 25107 FUNCTION digoal f_user_login_upd_1(integer) digoal  
269; 1255 25108 FUNCTION digoal f_user_login_upd_2(integer) digoal  
270; 1255 25109 FUNCTION digoal f_user_login_upd_3(integer) digoal  
217; 1255 25110 FUNCTION digoal f_user_login_upd_4(integer) digoal  
238; 1255 25111 FUNCTION digoal f_user_logout(integer) digoal  
239; 1255 25112 FUNCTION digoal f_user_logout_0(integer) digoal  
240; 1255 25113 FUNCTION digoal f_user_logout_1(integer) digoal  
241; 1255 25114 FUNCTION digoal f_user_logout_2(integer) digoal  
242; 1255 25115 FUNCTION digoal f_user_logout_3(integer) digoal  
243; 1255 25116 FUNCTION digoal f_user_logout_4(integer) digoal  
244; 1255 25117 FUNCTION digoal test_update() digoal  
222; 1255 24710 FUNCTION public pldbg_abort_target(integer) postgres  
223; 1255 24711 FUNCTION public pldbg_attach_to_port(integer) postgres  
224; 1255 24712 FUNCTION public pldbg_continue(integer) postgres  
225; 1255 24713 FUNCTION public pldbg_create_listener() postgres  
226; 1255 24714 FUNCTION public pldbg_deposit_value(integer, text, integer, text) postgres  
227; 1255 24715 FUNCTION public pldbg_drop_breakpoint(integer, oid, integer) postgres  
218; 1255 24716 FUNCTION public pldbg_get_breakpoints(integer) postgres  
228; 1255 24719 FUNCTION public pldbg_get_proxy_info() postgres  
219; 1255 24717 FUNCTION public pldbg_get_source(integer, oid) postgres  
220; 1255 24718 FUNCTION public pldbg_get_stack(integer) postgres  
237; 1255 24728 FUNCTION public pldbg_get_target_info(text, "char") postgres  
229; 1255 24720 FUNCTION public pldbg_get_variables(integer) postgres  
230; 1255 24721 FUNCTION public pldbg_select_frame(integer, integer) postgres  
231; 1255 24722 FUNCTION public pldbg_set_breakpoint(integer, oid, integer) postgres  
232; 1255 24723 FUNCTION public pldbg_set_global_breakpoint(integer, oid, integer, integer) postgres  
233; 1255 24724 FUNCTION public pldbg_step_into(integer) postgres  
234; 1255 24725 FUNCTION public pldbg_step_over(integer) postgres  
235; 1255 24726 FUNCTION public pldbg_wait_for_breakpoint(integer) postgres  
236; 1255 24727 FUNCTION public pldbg_wait_for_target(integer) postgres  
221; 1255 24709 FUNCTION public plpgsql_oid_debug(oid) postgres  
187; 1259 25324 TABLE digoal a_parent digoal  
189; 1259 25340 TABLE digoal a_deleted digoal  
188; 1259 25332 TABLE digoal a_undeleted digoal  
190; 1259 25348 TABLE digoal test digoal  
175; 1259 25139 TABLE digoal user_info_0 digoal  
176; 1259 25145 TABLE digoal user_info_1 digoal  
177; 1259 25151 TABLE digoal user_info_2 digoal  
178; 1259 25157 TABLE digoal user_info_3 digoal  
179; 1259 25163 TABLE digoal user_info_4 digoal  
180; 1259 25169 TABLE digoal user_login_rec digoal  
181; 1259 25175 TABLE digoal user_logout_rec digoal  
182; 1259 25181 TABLE digoal user_session_0 digoal  
183; 1259 25186 TABLE digoal user_session_1 digoal  
184; 1259 25191 TABLE digoal user_session_2 digoal  
185; 1259 25196 TABLE digoal user_session_3 digoal  
186; 1259 25201 TABLE digoal user_session_4 digoal  
164; 1259 24600 VIEW public all_tables postgres  
2883; 0 0 ACL public all_tables postgres  
163; 1259 24596 VIEW public dba_tables postgres  
2884; 0 0 ACL public dba_tables postgres  
162; 1259 24588 VIEW public user_tables postgres  
2885; 0 0 ACL public user_tables postgres  
2874; 0 25340 TABLE DATA digoal a_deleted digoal  
2872; 0 25324 TABLE DATA digoal a_parent digoal  
2873; 0 25332 TABLE DATA digoal a_undeleted digoal  
2875; 0 25348 TABLE DATA digoal test digoal  
2860; 0 25139 TABLE DATA digoal user_info_0 digoal  
2861; 0 25145 TABLE DATA digoal user_info_1 digoal  
2862; 0 25151 TABLE DATA digoal user_info_2 digoal  
2863; 0 25157 TABLE DATA digoal user_info_3 digoal  
2864; 0 25163 TABLE DATA digoal user_info_4 digoal  
2865; 0 25169 TABLE DATA digoal user_login_rec digoal  
2866; 0 25175 TABLE DATA digoal user_logout_rec digoal  
2867; 0 25181 TABLE DATA digoal user_session_0 digoal  
2868; 0 25186 TABLE DATA digoal user_session_1 digoal  
2869; 0 25191 TABLE DATA digoal user_session_2 digoal  
2870; 0 25196 TABLE DATA digoal user_session_3 digoal  
2871; 0 25201 TABLE DATA digoal user_session_4 digoal  
2856; 2606 25331 CONSTRAINT digoal a_parent_name_key digoal  
2858; 2606 25339 CONSTRAINT digoal a_undeleted_name_key digoal  
2836; 2606 25274 CONSTRAINT digoal user_info_0_pkey digoal  
2838; 2606 25270 CONSTRAINT digoal user_info_1_pkey digoal  
2840; 2606 25272 CONSTRAINT digoal user_info_2_pkey digoal  
2842; 2606 25268 CONSTRAINT digoal user_info_3_pkey digoal  
2844; 2606 25266 CONSTRAINT digoal user_info_4_pkey digoal  
2846; 2606 25264 CONSTRAINT digoal user_session_0_pkey digoal  
2848; 2606 25256 CONSTRAINT digoal user_session_1_pkey digoal  
2850; 2606 25262 CONSTRAINT digoal user_session_2_pkey digoal  
2852; 2606 25260 CONSTRAINT digoal user_session_3_pkey digoal  
2854; 2606 25258 CONSTRAINT digoal user_session_4_pkey digoal  
2859; 1259 25354 INDEX digoal idx_test_info digoal  

简单的解释一下TOC文件中entry的意思 :

截取自

src/bin/pg_dump/pg_backup_archiver.c

void  
PrintTOCSummary(Archive *AHX, RestoreOptions *ropt)  
{  
        ArchiveHandle *AH = (ArchiveHandle *) AHX;  
        TocEntry   *te;  
        OutputContext sav;  
        char       *fmtName;  
  
        sav = SaveOutput(AH);  
        if (ropt->filename)  
                SetOutput(AH, ropt->filename, 0 /* no compression */ );  
  
        ahprintf(AH, ";\n; Archive created at %s", ctime(&AH->createDate));  
        ahprintf(AH, ";     dbname: %s\n;     TOC Entries: %d\n;     Compression: %d\n",  
                         AH->archdbname, AH->tocCount, AH->compression);  
  
        switch (AH->format)  
        {  
                case archFiles:  
                        fmtName = "FILES";  
                        break;  
                case archCustom:  
                        fmtName = "CUSTOM";  
                        break;  
                case archTar:  
                        fmtName = "TAR";  
                        break;  
                default:  
                        fmtName = "UNKNOWN";  
        }  
  
        ahprintf(AH, ";     Dump Version: %d.%d-%d\n", AH->vmaj, AH->vmin, AH->vrev);  
        ahprintf(AH, ";     Format: %s\n", fmtName);  
        ahprintf(AH, ";     Integer: %d bytes\n", (int) AH->intSize);  
        ahprintf(AH, ";     Offset: %d bytes\n", (int) AH->offSize);  
        if (AH->archiveRemoteVersion)  
                ahprintf(AH, ";     Dumped from database version: %s\n",  
                                 AH->archiveRemoteVersion);  
        if (AH->archiveDumpVersion)  
                ahprintf(AH, ";     Dumped by pg_dump version: %s\n",  
                                 AH->archiveDumpVersion);  
  
        ahprintf(AH, ";\n;\n; Selected TOC Entries:\n;\n");  
  
        /* We should print DATABASE entries whether or not -C was specified */  
        ropt->createDB = 1;  
  
        for (te = AH->toc->next; te != AH->toc; te = te->next)  
        {  
                if (ropt->verbose || _tocEntryRequired(te, ropt, true) != 0)  
                        ahprintf(AH, "%d; %u %u %s %s %s %s\n", te->dumpId,  
                                         te->catalogId.tableoid, te->catalogId.oid,  
                                         te->desc, te->namespace ? te->namespace : "-",  
                                         te->tag, te->owner);  
                if (ropt->verbose && te->nDeps > 0)  
                {  
                        int                     i;  
  
                        ahprintf(AH, ";\tdepends on:");  
                        for (i = 0; i < te->nDeps; i++)  
                                ahprintf(AH, " %d", te->dependencies[i]);  
                        ahprintf(AH, "\n");  
                }  
        }  
  
        if (ropt->filename)  
                RestoreOutput(AH, sav);  
}  

1. 分号开头表示这行被注释掉了.

2. 2878; 1262 16386 DATABASE - digoal postgres 这行的意思

2878 对应 dumpId  
1262 对应 catalogId.tableoid  
16386 对应 catalogId.oid  
DATABASE 对应 desc  
- 对应 te->namespace ? te->namespace : "-"  
digoal 对应 tag  
postgres 对应 owner  

通过调整顺序和添加注释可以达到定制化还原的目的, 调整顺序时需要注意依赖关系, 如创建plpgsql的子句必须在创建函数前面. 创建表必须在创建这个表的约束和索引, 触发器等前面.

下面我们调整一下顺序, 把user_info_4 表创建提前到user_info_0 前面.

179; 1259 25163 TABLE digoal user_info_4 digoal  
175; 1259 25139 TABLE digoal user_info_0 digoal  
176; 1259 25145 TABLE digoal user_info_1 digoal  
177; 1259 25151 TABLE digoal user_info_2 digoal  
178; 1259 25157 TABLE digoal user_info_3 digoal  

利用这个TOC文件还原看看顺序是否变更.

pg_restore -F c -L ./digoal.list -c -s -h 127.0.0.1 -U postgres ./digoal.dmp  

截取一段还原日志如下, 反映出来这个顺序调整已经OK了.

--  
-- Name: user_info_4; Type: TABLE; Schema: digoal; Owner: digoal; Tablespace: digoal_03  
--  
  
CREATE TABLE user_info_4 (  
    userid integer NOT NULL,  
    engname text,  
    cnname text,  
    occupation text,  
    birthday date,  
    signname text,  
    email text,  
    qq numeric,  
    crt_time timestamp without time zone,  
    mod_time timestamp without time zone  
);  
  
  
ALTER TABLE digoal.user_info_4 OWNER TO digoal;  
  
SET default_tablespace = digoal_04;  
  
--  
-- Name: user_info_0; Type: TABLE; Schema: digoal; Owner: digoal; Tablespace: digoal_04  
--  
  
CREATE TABLE user_info_0 (  
    userid integer NOT NULL,  
    engname text,  
    cnname text,  
    occupation text,  
    birthday date,  
    signname text,  
    email text,  
    qq numeric,  
    crt_time timestamp without time zone,  
    mod_time timestamp without time zone  
);  
  
  
ALTER TABLE digoal.user_info_0 OWNER TO digoal;  

最后, 是否可以通过修改TOC文件中schema的内容达到把表导入不同schema的目的?

从测试结果来看是不可以的, 修改后TOC文件的内容如下 :

;  
; Archive created at Thu Apr 12 09:32:27 2012  
;     dbname: digoal  
;     TOC Entries: 126  
;     Compression: -1  
;     Dump Version: 1.12-0  
;     Format: CUSTOM  
;     Integer: 4 bytes  
;     Offset: 8 bytes  
;     Dumped from database version: 9.1.3  
;     Dumped by pg_dump version: 9.1.3  
;  
;  
; Selected TOC Entries:  
;  
2878; 1262 16386 DATABASE - digoal postgres  
7; 2615 25070 SCHEMA - digoal digoal  
5; 2615 2200 SCHEMA - public postgres  
2879; 0 0 COMMENT - SCHEMA public postgres  
2880; 0 0 ACL - public postgres  
191; 3079 12425 EXTENSION - plpgsql  
2881; 0 0 COMMENT - EXTENSION plpgsql  
192; 3079 16442 EXTENSION - pgfincore  
2882; 0 0 COMMENT - EXTENSION pgfincore  
187; 1259 25324 TABLE public a_parent digoal  
2872; 0 25324 TABLE DATA public a_parent digoal  
2856; 2606 25331 CONSTRAINT public a_parent_name_key digoal  

目的是要把 a_parent 表还原到public SCHEMA下面.

还原

pg_restore -F c -L ./digoal.list -c -s -h 127.0.0.1 -U postgres ./digoal.dmp  

日志输出如下 :

--  
-- PostgreSQL database dump  
--  
  
SET statement_timeout = 0;  
SET client_encoding = 'UTF8';  
SET standard_conforming_strings = on;  
SET check_function_bodies = false;  
SET client_min_messages = warning;  
  
SET search_path = digoal, pg_catalog;  
  
ALTER TABLE ONLY digoal.a_parent DROP CONSTRAINT a_parent_name_key;  
DROP TABLE digoal.a_parent;  
DROP EXTENSION pgfincore;  
DROP EXTENSION plpgsql;  
DROP SCHEMA public;  
DROP SCHEMA digoal;  
--  
-- Name: digoal; Type: SCHEMA; Schema: -; Owner: digoal  
--  
  
CREATE SCHEMA digoal;  
  
  
ALTER SCHEMA digoal OWNER TO digoal;  
  
--  
-- Name: public; Type: SCHEMA; Schema: -; Owner: postgres  
--  
  
CREATE SCHEMA public;  
  
  
ALTER SCHEMA public OWNER TO postgres;  
  
--  
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres  
--  
  
COMMENT ON SCHEMA public IS 'standard public schema';  
  
  
--  
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:   
--  
  
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;  
  
  
--  
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:   
--  
  
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';  
  
  
--  
-- Name: pgfincore; Type: EXTENSION; Schema: -; Owner:   
--  
  
CREATE EXTENSION IF NOT EXISTS pgfincore WITH SCHEMA public;  
  
  
--  
-- Name: EXTENSION pgfincore; Type: COMMENT; Schema: -; Owner:   
--  
  
COMMENT ON EXTENSION pgfincore IS 'examine and manage the os buffer cache';  
  
  
SET search_path = digoal, pg_catalog;  
  
SET default_tablespace = '';  
  
SET default_with_oids = false;  
  
--  
-- Name: a_parent; Type: TABLE; Schema: digoal; Owner: digoal; Tablespace:   
--  
  
CREATE TABLE a_parent (  
    name text NOT NULL,  
    other_cols text  
);  
  
  
ALTER TABLE digoal.a_parent OWNER TO digoal;  
  
--  
-- Name: a_parent_name_key; Type: CONSTRAINT; Schema: digoal; Owner: digoal; Tablespace:   
--  
  
ALTER TABLE ONLY a_parent  
    ADD CONSTRAINT a_parent_name_key UNIQUE (name);  
  
  
--  
-- Name: public; Type: ACL; Schema: -; Owner: postgres  
--  
  
REVOKE ALL ON SCHEMA public FROM PUBLIC;  
REVOKE ALL ON SCHEMA public FROM postgres;  
GRANT ALL ON SCHEMA public TO postgres;  
GRANT ALL ON SCHEMA public TO PUBLIC;  
  
  
--  
-- PostgreSQL database dump complete  
--  

显然修改TOC entry中的namespace列并没有达到我们预期的目的, pg_restore还原时不会根据这个列的值来定义search_path.

这些内容都在dmp文件中定义好了.

我们把TOC改成如下 :

;  
; Archive created at Thu Apr 12 09:32:27 2012  
;     dbname: digoal  
;     TOC Entries: 126  
;     Compression: -1  
;     Dump Version: 1.12-0  
;     Format: CUSTOM  
;     Integer: 4 bytes  
;     Offset: 8 bytes  
;     Dumped from database version: 9.1.3  
;     Dumped by pg_dump version: 9.1.3  
;  
;  
; Selected TOC Entries:  
;  
2878  
7  
5  
2879  
2880  
191  
2881  
192  
2882  
187  
2872  
2856  

使用pg_restore -F c -L ./digoal.list -c -s -h 127.0.0.1 -U postgres ./digoal.dmp也可以还原.

原因就在pg_restore只认TOC文件的dumpID. 分号后面都是注释. 所以我们前面改的都是注释里面的内容, 当然是无效的。

要实现向ORACLE那样的fromuser和touser功能, 只能在导入后用

ALTER TABLE name  
    SET SCHEMA new_schema  

语法实现了.

或者等PostgreSQL后续的pg_restore版本增加这类功能. 理论上来说是可行的.

参考

man pg_dump

man pg_restore

src/bin/pg_dump/pg_backup_archiver.c

Flag Counter

digoal’s 大量PostgreSQL文章入口