pg_restore - table restoration options - odd behaivors. - Mailing list pgsql-bugs

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