Re: pg_dump -- data and schema only?

I haven't seen this option, and does anyone thing it is a good idea?

A option to pg_dump and maybe pg_dump all, that dumps only the table
declarations and the data. No owners, tablespace, nothing.

This, I think, would allow more generic PostgreSQL data transfers.

Am Donnerstag, den 04.08.2005, 10:26 -0400 schrieb Mark Woodward:
> I haven't seen this option, and does anyone thing it is a good idea?
>
> A option to pg_dump and maybe pg_dump all, that dumps only the table
> declarations and the data. No owners, tablespace, nothing.
>
> This, I think, would allow more generic PostgreSQL data transfers.

pg_dump -s maybe?

See man pg_dump:

-s

--schema-only
Dump only the object definitions (schema), not data.

Usually one dumps the database with -Fc and then construct
SQL for data and DDL via pg_restore from this binary dump.
You can then use pg_restore -l, edit (for example via sed)
and use it with -L to only generate SQL for these objects.

--
Tino Wildenhain <tino(at)wildenhain(dot)de>

> Am Donnerstag, den 04.08.2005, 10:26 -0400 schrieb Mark Woodward:
>> I haven't seen this option, and does anyone thing it is a good idea?
>>
>> A option to pg_dump and maybe pg_dump all, that dumps only the table
>> declarations and the data. No owners, tablespace, nothing.
>>
>> This, I think, would allow more generic PostgreSQL data transfers.
>
> pg_dump -s maybe?
>
> See man pg_dump:
>
> -s
>
> --schema-only
> Dump only the object definitions (schema), not data.
>
> Usually one dumps the database with -Fc and then construct
> SQL for data and DDL via pg_restore from this binary dump.
> You can then use pg_restore -l, edit (for example via sed)
> and use it with -L to only generate SQL for these objects.
>

Actually, there isn't a setting to just dump the able definitions and the
data. When you dump the schema, it includes all the tablespaces,
namespaces, owners, etc.

Just the table nd object declarations and data would be useful.

Am Donnerstag, den 04.08.2005, 11:52 -0400 schrieb Mark Woodward:
> > Am Donnerstag, den 04.08.2005, 10:26 -0400 schrieb Mark Woodward:
> >> I haven't seen this option, and does anyone thing it is a good idea?
> >>
> >> A option to pg_dump and maybe pg_dump all, that dumps only the table
> >> declarations and the data. No owners, tablespace, nothing.
> >>
> >> This, I think, would allow more generic PostgreSQL data transfers.
> >
> > pg_dump -s maybe?
> >
> > See man pg_dump:
> >
> > -s
> >
> > --schema-only
> > Dump only the object definitions (schema), not data.
> >
> > Usually one dumps the database with -Fc and then construct
> > SQL for data and DDL via pg_restore from this binary dump.
> > You can then use pg_restore -l, edit (for example via sed)
> > and use it with -L to only generate SQL for these objects.
> >
>
> Actually, there isn't a setting to just dump the able definitions and the
> data. When you dump the schema, it includes all the tablespaces,
> namespaces, owners, etc.
>
> Just the table nd object declarations and data would be useful.

Reread my comment on the -l / -L option to pg_restore. You can
do all that with it :-)

--
Tino Wildenhain <tino(at)wildenhain(dot)de>

"Mark Woodward" <pgsql(at)mohawksoft(dot)com> writes:
> Actually, there isn't a setting to just dump the able definitions and the
> data. When you dump the schema, it includes all the tablespaces,
> namespaces, owners, etc.

> Just the table nd object declarations and data would be useful.

pg_dump -t table ?

regards, tom lane

> "Mark Woodward" <pgsql(at)mohawksoft(dot)com> writes:
>> Actually, there isn't a setting to just dump the able definitions and
>> the
>> data. When you dump the schema, it includes all the tablespaces,
>> namespaces, owners, etc.
>
>> Just the table nd object declarations and data would be useful.
>
> pg_dump -t table ?

I guess what I'm not being very clear.

pg_dump -# mydb
>>
--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;
SET client_min_messages = warning;

SET default_with_oids = true;

--
-- Name: rtz; Type: TABLE; Schema: public; Owner: markw; Tablespace:
--

CREATE TABLE rtz (
tlid integer,
rtsq integer,
zip4l integer,
zip4r integer
);

--
-- Data for Name: rtz; Type: TABLE DATA; Schema: public; Owner: markw
--

COPY rtz (tlid, rtsq, zip4l, zip4r) FROM stdin;
208014102 0 0 0
208014098 0 0 0
207023736 0 0 0
208014112 0 0 0
207027749 0 0 0

.....

That way, it can go "easily" between various PG versions.