Dump table using pg_dump vs pg_restore -f

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Dump table using pg_dump vs pg_restore -f

Adrian Klaver-4
Postgres 10.6

I am getting different output using:

pg_dump -d production -U postgres  -s -t container -f cont.sql

vs

pg_restore -s -t container -f container.sql production_112818.out

For the pg_dump version I get:

<Boiler plate stuff>

CREATE TABLE public.container (
     c_id character varying(10) NOT NULL,
     cdesc character varying(30) NOT NULL,
     cell_per smallint NOT NULL,
     c_size character varying(10) NOT NULL,
     ts_insert timestamp(0) without time zone DEFAULT now(),
     ts_update timestamp(0) without time zone,
     user_update character varying(20),
     user_insert character varying(20) DEFAULT "session_user"(),
     v_number integer,
     us_vol numeric(5,2),
     metric_vol numeric(5,2),
     metric_unit character varying(3),
     c_number character varying(20),
     us_unit character varying(5),
     c_note text,
     c_units integer NOT NULL
);


ALTER TABLE public.container OWNER TO postgres;

--
-- Name: TABLE container; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON TABLE public.container IS 'Base table for plant container
info. Used as source of c_id for other tables.';


--
-- Name: container container_pkey; Type: CONSTRAINT; Schema: public;
Owner: postgres
--

ALTER TABLE ONLY public.container
     ADD CONSTRAINT container_pkey PRIMARY KEY (c_id);


--
-- Name: container container_delete; Type: TRIGGER; Schema: public;
Owner: postgres
--

CREATE TRIGGER container_delete AFTER DELETE ON public.container FOR
EACH ROW EXECUTE PROCEDURE utility.archive_del_record();

... more triggers

For the pg_restore I get:

<Boiler plate stuff>

CREATE TABLE public.container (
     c_id character varying(10) NOT NULL,
     cdesc character varying(30) NOT NULL,
     cell_per smallint NOT NULL,
     c_size character varying(10) NOT NULL,
     ts_insert timestamp(0) without time zone DEFAULT now(),
     ts_update timestamp(0) without time zone,
     user_update character varying(20),
     user_insert character varying(20) DEFAULT "session_user"(),
     v_number integer,
     us_vol numeric(5,2),
     metric_vol numeric(5,2),
     metric_unit character varying(3),
     c_number character varying(20),
     us_unit character varying(5),
     c_note text,
     c_units integer NOT NULL
);


ALTER TABLE public.container OWNER TO postgres;

--
-- PostgreSQL database dump complete
--

In the docs:

https://www.postgresql.org/docs/10/app-pgrestore.html

I see:

"
Note

This flag does not behave identically to the -t flag of pg_dump. There
is not currently any provision for wild-card matching in pg_restore, nor
can you include a schema name within its -t.
"

That to me reads that the difference is for the search method for table
names only.

The description for:
-t table

Includes:

"Restore definition and/or data of only the named table. ..."

That in my mind would include the PK and the table triggers.

Am I misunderstanding?

--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Dump table using pg_dump vs pg_restore -f

Adrian Klaver-4
On 11/29/18 7:31 AM, Adrian Klaver wrote:

> Postgres 10.6
>
> I am getting different output using:
>
> pg_dump -d production -U postgresĀ  -s -t container -f cont.sql
>
> vs
>
> pg_restore -s -t container -f container.sql production_112818.out
>

>
> In the docs:
>
> https://www.postgresql.org/docs/10/app-pgrestore.html
>
> I see:
>
> "
> Note
>
> This flag does not behave identically to the -t flag of pg_dump. There
> is not currently any provision for wild-card matching in pg_restore, nor
> can you include a schema name within its -t.
> "
>
> That to me reads that the difference is for the search method for table
> names only.
>
> The description for:
> -t table
>
> Includes:
>
> "Restore definition and/or data of only the named table. ..."
>
> That in my mind would include the PK and the table triggers.
>
> Am I misunderstanding?
>

Just wondering,  am I the only one seeing this issue?

Am I doing something wrong?


--
Adrian Klaver
[hidden email]