digoal
2012-04-12
PostgreSQL , pg_restore , TOC
一位网友问到的几个逻辑备份和还原的问题, 本文对这几个问题进行简单的讲解和测试。
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
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
