PostgreSQL 9.3.1 on amd64-portbld-freebsd9.2, compiled by cc (GCC) 4.2.1 20=
070831 patched [FreeBSD], 64-bit\q
pg_restore (PostgreSQL) 9.3.1
Summary:
I was exploring table restoration options of pg_restore to deal with issues=
of users configuring selected tables into a non-workable state and encoun=
tered 2 variations that I think are bugs. In both cases I am starting with=
a common archival file that was created by pg_dump.
e.g. pg_dump -Fc my_db -U <superuser> -f archvice_file
Case 1. pg_restore - n admin -c -v -1 -t tbla -t tblb -U user archive_f=
ile;
Recreates the table and data but does not re-create any constraints or trig=
gers that are associated with tbla or tblb.
This may be the intended consequence of -c and -t combination but it is no=
t clear from the documentation.
I note that if I restore the entire schema with only the -n and no -t opti=
ons it restores all tables correctly.
According to the documentation -t restores the definition and/or data of th=
e named table only. Perhaps I am incorrectly considering the Primary Keys=
and Foreign Keys part of the table definition ?
Example Case 1.
pg_restore -n admin -c -v -1 -t translator_sys -t translator_sys_mbr -U red=
com /var/ace/pg_backups/1-acedb-alt_install.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 9.3.1
-- Dumped by pg_dump version 9.3.1
-- Started on 2014-01-14 16:59:34 EST
BEGIN;
SET statement_timeout =3D 0;
SET lock_timeout =3D 0;
SET client_encoding =3D 'UTF8';
SET standard_conforming_strings =3D on;
SET check_function_bodies =3D false;
SET client_min_messages =3D warning;
pg_restore: dropping TABLE DATA translator_sys_mbr
SET search_path =3D admin, pg_catalog;
pg_restore: dropping TABLE DATA translator_sys
pg_restore: dropping TABLE translator_sys_mbr
DROP TABLE admin.translator_sys_mbr;
pg_restore: dropping TABLE translator_sys
DROP TABLE admin.translator_sys;
pg_restore: creating TABLE translator_sys
SET search_path =3D admin, pg_catalog;
SET default_tablespace =3D '';
SET default_with_oids =3D false;
--
-- TOC entry 352 (class 1259 OID 350256)
-- Dependencies: 200 6
-- Name: translator_sys; Type: TABLE; Schema: admin; Owner: redcom; Tablesp=
ace:
-- Data Pos: 0
--
CREATE TABLE translator_sys (
translator_id integer DEFAULT nextval('sys.translator_id_seq'::regclass=
) NOT NULL,
pattern_key character varying,
name character varying,
comments character varying,
owner_user_id integer,
mgcp_digit_map character varying
);
ALTER TABLE admin.translator_sys OWNER TO redcom;
pg_restore: creating TABLE translator_sys_mbr
--
-- TOC entry 353 (class 1259 OID 350263)
-- Dependencies: 1057 1057 6
-- Name: translator_sys_mbr; Type: TABLE; Schema: admin; Owner: redcom; Tab=
lespace:
-- Data Pos: 0
--
CREATE TABLE translator_sys_mbr (
name character varying,
tu_id integer DEFAULT 0,
translator_id integer NOT NULL,
tid_seq integer NOT NULL,
ws_grp_seq integer DEFAULT 0,
obj_seq integer DEFAULT 1,
entry_type integer DEFAULT 0,
pattern_key character varying,
pattern character varying,
pattern_match integer DEFAULT 0,
screen_class integer DEFAULT 0,
term_mode character varying,
port_id integer,
tag_id integer,
cause__q850 integer,
cause__redcom integer,
new_translator_id integer,
new_screen_class integer,
duration integer,
address character varying,
macro_type integer,
owner_usr_id integer,
owner_port_id integer,
trans_result integer DEFAULT 0 NOT NULL,
sys_note character varying,
continue_translating character varying,
call_priority__level integer,
user_message character varying,
label character varying,
label_reference character varying,
row_type sys.rsrc_type_enum DEFAULT 'real'::sys.rsrc_type_enum,
callback character varying,
early_media character varying,
web_app_info character varying,
cause__mgcp integer,
cause__rfc4411 integer,
cause__sdp_warning integer,
cause__sip integer,
cause__comment character varying,
orig_identity_name character varying,
orig_identity_number character varying,
orig_identity_host character varying,
orig_identity_user character varying,
term_identity_name character varying,
term_identity_number character varying,
term_identity_host character varying,
term_identity_user character varying
);
ALTER TABLE admin.translator_sys_mbr OWNER TO redcom;
--
-- TOC entry 4224 (class 0 OID 350256)
-- Dependencies: 352
-- Data for Name: translator_sys; Type: TABLE DATA; Schema: admin; Owner: r=
edcom
-- Data Pos: 841023
--
pg_restore: processing data for table "translator_sys"
COPY translator_sys (translator_id, pattern_key, name, comments, owner_user=
_id, mgcp_digit_map) FROM stdin;
1 $tran:user:address_ Standard Translator Normal starting tra=
nslator \N \N
\.
--
-- TOC entry 4225 (class 0 OID 350263)
-- Dependencies: 353
-- Data for Name: translator_sys_mbr; Type: TABLE DATA; Schema: admin; Owne=
r: redcom
-- Data Pos: 841116
--
pg_restore: processing data for table "translator_sys_mbr"
COPY translator_sys_mbr (name, tu_id, translator_id, tid_seq, ws_grp_seq, o=
bj_seq, entry_type, pattern_key, pattern, pattern_match, screen_class, term=
_mode, port_id, tag_id, cause__q850, cause__redcom, new_translator_id, new_=
screen_class, duration, address, macro_type, owner_usr_id, owner_port_id, t=
rans_result, sys_note, continue_translating, call_priority__level, user_mes=
sage, label, label_reference, row_type, callback, early_media, web_app_info=
, cause__mgcp, cause__rfc4411, cause__sdp_warning, cause__sip, cause__comme=
nt, orig_identity_name, orig_identity_number, orig_identity_host, orig_iden=
tity_user, term_identity_name, term_identity_number, term_identity_host, te=
rm_identity_user) FROM stdin;
lines 0 1 1 0 1 7 \N \N 0 =
0 \N \N \N \N\N \N \N \N \N =
1 \N \N 0 \N \N \N \N \N =
\Nreal \N \N \N \N \N \N \N \N =
\N \N \N \N \N \N\N \N
\.
pg_restore: setting owner and privileges for TABLE translator_sys
pg_restore: setting owner and privileges for TABLE translator_sys_mbr
pg_restore: setting owner and privileges for TABLE DATA translator_sys
pg_restore: setting owner and privileges for TABLE DATA translator_sys_mbr
COMMIT;
-- Completed on 2014-01-15 08:34:03 EST
--
-- PostgreSQL database dump complete
--
Case 2. pg_restore -c -a -v -1 -n admin -t tbla -t tblb -U superuser ar=
chive_file
In this -a, data only, case I was hopeful that the combined -c option wou=
ld either DELETE or TRUNCATE the data prior to the COPY command.
There is an indication from pg_restore that it is attempting to do it but n=
o commands are added to the output strean to cause that.
I wonder what the expected behavior here is ? The attempt ultimately fails =
because the COPY command fails because the existing
data and data to be inserted would break Primary Key constraints. ( Becaus=
e the existing data is not dropped ? -c ? )
Example Case 2.
pg_restore -n admin -c -a -v -1 -t translator_sys -t translator_sys_mbr -U =
redcom /var/ace/pg_backups/1-acedb-alt_install.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 9.3.1
-- Dumped by pg_dump version 9.3.1
-- Started on 2014-01-14 16:59:34 EST
BEGIN;
SET statement_timeout =3D 0;
SET lock_timeout =3D 0;
SET client_encoding =3D 'UTF8';
SET standard_conforming_strings =3D on;
SET check_function_bodies =3D false;
SET client_min_messages =3D warning;
pg_restore: dropping TABLE DATA translator_sys_mbr
SET search_path =3D admin, pg_catalog;
pg_restore: dropping TABLE DATA translator_sys
SET search_path =3D admin, pg_catalog;
--
-- TOC entry 4224 (class 0 OID 350256)
-- Dependencies: 352
-- Data for Name: translator_sys; Type: TABLE DATA; Schema: admin; Owner: r=
edcom
-- Data Pos: 841023
--
pg_restore: processing data for table "translator_sys"
COPY translator_sys (translator_id, pattern_key, name, comments, owner_user=
_id, mgcp_digit_map) FROM stdin;
1 $tran:user:address_ Standard Translator Normal starting tra=
nslator \N \N
\.
--
-- TOC entry 4225 (class 0 OID 350263)
-- Dependencies: 353
-- Data for Name: translator_sys_mbr; Type: TABLE DATA; Schema: admin; Owne=
r: redcom
-- Data Pos: 841116
--
pg_restore: processing data for table "translator_sys_mbr"
COPY translator_sys_mbr (name, tu_id, translator_id, tid_seq, ws_grp_seq, o=
bj_seq, entry_type, pattern_key, pattern, pattern_match, screen_class, term=
_mode, port_id, tag_id, cause__q850, cause__redcom, new_translator_id, new_=
screen_class, duration, address, macro_type, owner_usr_id, owner_port_id, t=
rans_result, sys_note, continue_translating, call_priority__level, user_mes=
sage, label, label_reference, row_type, callback, early_media, web_app_info=
, cause__mgcp, cause__rfc4411, cause__sdp_warning, cause__sip, cause__comme=
nt, orig_identity_name, orig_identity_number, orig_identity_host, orig_iden=
tity_user, term_identity_name, term_identity_number, term_identity_host, te=
rm_identity_user) FROM stdin;
lines 0 1 1 0 1 7 \N \N 0 =
0 \N \N \N \N\N \N \N \N \N =
1 \N \N 0 \N \N \N \N \N =
\Nreal \N \N \N \N \N \N \N \N =
\N \N \N \N \N \N\N \N
\.
pg_restore: setting owner and privileges for TABLE DATA translator_sys
pg_restore: setting owner and privileges for TABLE DATA translator_sys_mbr
COMMIT;
-- Completed on 2014-01-15 08:46:58 EST
--
-- PostgreSQL database dump complete
--
For reference purposes here is the definitions of my tbla and tblb.
CREATE TABLE admin.translator_sys
(
translator_id integer NOT NULL DEFAULT nextval('sys.translator_id_seq'::r=
egclass),
pattern_key character varying,
name character varying,
comments character varying,
owner_user_id integer,
mgcp_digit_map character varying,
CONSTRAINT translator_sys_pkey PRIMARY KEY (translator_id),
CONSTRAINT translator_sys_owner_user_id_fkey FOREIGN KEY (owner_user_id)
REFERENCES admin."user" (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
)
WITH (
OIDS=3DFALSE
);
ALTER TABLE admin.translator_sys
OWNER TO redcom;
GRANT ALL ON TABLE admin.translator_sys TO ace_db_client;
GRANT ALL ON TABLE admin.translator_sys TO ace_web_portal;
GRANT ALL ON TABLE admin.translator_sys TO redcom;
COMMENT ON TABLE admin.translator_sys
IS 'Define the global aspects of a tranlsation set.';
-- Trigger: translator_sys_audit on admin.translator_sys
-- DROP TRIGGER translator_sys_audit ON admin.translator_sys;
CREATE TRIGGER translator_sys_audit
AFTER INSERT OR UPDATE OF pattern_key OR DELETE
ON admin.translator_sys
FOR EACH ROW
EXECUTE PROCEDURE sys.resource_post_change();
CREATE TABLE admin.translator_sys_mbr
(
name character varying,
tu_id integer DEFAULT 0,
translator_id integer NOT NULL,
tid_seq integer NOT NULL,
ws_grp_seq integer DEFAULT 0,
obj_seq integer DEFAULT 1,
entry_type integer DEFAULT 0,
pattern_key character varying,
pattern character varying,
pattern_match integer DEFAULT 0,
screen_class integer DEFAULT 0,
term_mode character varying,
port_id integer,
tag_id integer,
cause__q850 integer,
cause__redcom integer,
new_translator_id integer,
new_screen_class integer,
duration integer,
address character varying,
macro_type integer,
owner_usr_id integer,
owner_port_id integer,
trans_result integer NOT NULL DEFAULT 0,
sys_note character varying,
continue_translating character varying,
call_priority__level integer,
user_message character varying,
label character varying,
label_reference character varying,
row_type sys.rsrc_type_enum DEFAULT 'real'::sys.rsrc_type_enum,
callback character varying,
early_media character varying,
web_app_info character varying,
cause__mgcp integer,
cause__rfc4411 integer,
cause__sdp_warning integer,
cause__sip integer,
cause__comment character varying,
orig_identity_name character varying,
orig_identity_number character varying,
orig_identity_host character varying,
orig_identity_user character varying,
term_identity_name character varying,
term_identity_number character varying,
term_identity_host character varying,
term_identity_user character varying,
CONSTRAINT translator_sys_mbr_pkey PRIMARY KEY (translator_id, tid_seq),
CONSTRAINT tidfk FOREIGN KEY (translator_id)
REFERENCES admin.translator_sys (translator_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT translator_sys_call_priority_fkey FOREIGN KEY (call_priority__=
level)
REFERENCES admin.call_priority_level_enum (value) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_entry_type_fkey FOREIGN KEY (entry_type)
REFERENCES admin.translator_entry_type_enum (value) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_mbr_cause__mgcp_fkey FOREIGN KEY (cause__mgcp)
REFERENCES admin.cause_mgcp (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_mbr_cause__q850_fkey FOREIGN KEY (cause__q850)
REFERENCES admin.cause_q850 (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_mbr_cause__redcom_fkey FOREIGN KEY (cause__redc=
om)
REFERENCES admin.cause_redcom (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_mbr_cause__rfc4411_fkey FOREIGN KEY (cause__rfc=
4411)
REFERENCES admin.cause_rfc4411 (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_mbr_cause__sdp_warning_fkey FOREIGN KEY (cause_=
_sdp_warning)
REFERENCES admin.cause_sdp (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_mbr_cause__sip_fkey FOREIGN KEY (cause__sip)
REFERENCES admin.cause_sip (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_owner_port_id_fkey FOREIGN KEY (owner_port_id)
REFERENCES admin.port (port_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_pattern_match_fkey FOREIGN KEY (pattern_match)
REFERENCES admin.translator_pattern_match_enum (value) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_trans_result_fkey FOREIGN KEY (trans_result)
REFERENCES admin.translator_result_enum (value) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=3DFALSE
);
ALTER TABLE admin.translator_sys_mbr
OWNER TO redcom;
GRANT ALL ON TABLE admin.translator_sys_mbr TO ace_db_client;
GRANT ALL ON TABLE admin.translator_sys_mbr TO ace_web_portal;
GRANT ALL ON TABLE admin.translator_sys_mbr TO redcom;
COMMENT ON TABLE admin.translator_sys_mbr
IS 'This table defines the translation for a given translator id. Should =
match definition of translator_user_mbr.';
-- Trigger: translator_sys_mbr_audit on admin.translator_sys_mbr
-- DROP TRIGGER translator_sys_mbr_audit ON admin.translator_sys_mbr;
CREATE TRIGGER translator_sys_mbr_audit
AFTER INSERT OR UPDATE OR DELETE
ON admin.translator_sys_mbr
FOR EACH ROW
EXECUTE PROCEDURE sys.resource_post_change();
I suspect a very simple reference case of two tables with fewer columns in =
the same schema each with a PK definition, and a FK definition from one ta=
ble would further confirm both case observations.
I would think that Case 2 would be a desirable selective restoral case that=
if not supported currently ought to be a wish list item for pg_restore in =
the future.
Respectfully,
Dave Day
