Postgres Pro
Downloads
Home   >   mailing lists

Thread: merging 2 dumps

merging 2 dumps

From
Nabil Sayegh
Date:
Hi,

I need to merge 2 versions of a database manually via pg_dump.
I'd like to diff them to see the changes.
Unfortunately the data contain newlines.

Is it possible to quote them, so that each row will be only 1 row in the
dumps ?
\n or something like that

TIA
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

Re: merging 2 dumps

From
Nabil Sayegh
Date:
Am Fre, 2003-08-01 um 14.51 schrieb Nabil Sayegh:
> Unfortunately the data contain newlines.
>
> Is it possible to quote them, so that each row will be only 1 row in the
> dumps ?
> \n or something like that

Oops, \n means NULL

Any idea ?
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

Re: merging 2 dumps

From
Tom Lane
Date:
Nabil Sayegh <postgresql@e-trolley.de> writes:
> I need to merge 2 versions of a database manually via pg_dump.
> I'd like to diff them to see the changes.
> Unfortunately the data contain newlines.
> Is it possible to quote them, so that each row will be only 1 row in the
> dumps ?
> \n or something like that

What PG version are you using??  All recent releases *do* dump newline
as \n, for example

COPY fooey (f1) FROM stdin;
hi
hi\nthere
ho
\.


            regards, tom lane

Re: merging 2 dumps

From
Nabil Sayegh
Date:
Am Fre, 2003-08-01 um 17.01 schrieb Tom Lane:

> > \n or something like that
>
> What PG version are you using??  All recent releases *do* dump newline
> as \n, for example

pg_dump (PostgreSQL) 7.3.3

> COPY fooey (f1) FROM stdin;
> hi
> hi\nthere
> ho
> \.

I always use: pg_dump -D ... (I don't really like COPY)

Is it also possible to have newlines converted when doing
psql -c "SELECT ..."

tfyh
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

Re: merging 2 dumps

From
Nabil Sayegh
Date:
Am Fre, 2003-08-01 um 17.13 schrieb Nabil Sayegh:
> Am Fre, 2003-08-01 um 17.01 schrieb Tom Lane:
>
> > > \n or something like that
> >
> > What PG version are you using??  All recent releases *do* dump newline
> > as \n, for example
>
> pg_dump (PostgreSQL) 7.3.3

Should I have mentioned, that there are Windows cr/lf ?

tia
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

Re: merging 2 dumps

From
Nabil Sayegh
Date:
Am Fre, 2003-08-01 um 17.01 schrieb Tom Lane:

> What PG version are you using??  All recent releases *do* dump newline
> as \n, for example
>
> COPY fooey (f1) FROM stdin;

Hm, I checked it and it does convert them to \n
BUT only without -D switch
Any idea ?
I'm kind of afraid to use COPY because of referential integrity,
triggers etc.
What's your opinion ?
Is it as reliable like -D (7.3.3) ?

tfyh
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

Re: merging 2 dumps

From
Tom Lane
Date:
Nabil Sayegh <postgresql@e-trolley.de> writes:
> I'm kind of afraid to use COPY because of referential integrity,
> triggers etc.

COPY used to have some problems, but they are all fixed.

            regards, tom lane

Re: merging 2 dumps

From
Nabil Sayegh
Date:
Am Sam, 2003-08-02 um 04.27 schrieb Tom Lane:
> Nabil Sayegh <postgresql@e-trolley.de> writes:
> > I'm kind of afraid to use COPY because of referential integrity,
> > triggers etc.
>
> COPY used to have some problems, but they are all fixed.

I migrated from 7.2.? to 7.3.3 lately.
So the database in question still has old-style referential integrity
constraints.
When I dump the db without -D and force errors by manually editing the
dump and changing a foreign key to a value that doesn't exist, the
import won't recognize it.
There is no errormsg. and the data gets inserted into the table with the
'wrong' value.

Is that because of the old-style RI ?

--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

Re: merging 2 dumps / dumping only 1 schema

From
Nabil Sayegh
Date:
Am Sam, 2003-08-02 um 18.43 schrieb Nabil Sayegh:

> There is no errormsg. and the data gets inserted into the table with the
> 'wrong' value.
>
> Is that because of the old-style RI ?

Apart from that:
Is it possible to pg_dump a specific table from a specific schema ?
The db has n schemas. Each schema belongs to a different user.
When I do
pg_dump -U user_of_specific_schema -a -t table_to_dump
I get pg_dump: Attempt to lock table "table_to_dump" failed.  ERROR:
other_user: permission denied

Shouldn't it use the search_path ?

Apparently it tries to dump all tables from all schema.

It also doesnt accept the table as schema.table.
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de