pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Problems with PG_DUMP and restore
5+ messages / 3 participants
[nested] [flat]

* Problems with PG_DUMP and restore
@ 2001-07-10 14:50  Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Josh Berkus @ 2001-07-10 14:50 UTC (permalink / raw)
  To: pgsql-sql

Folks,

In an effort to do some general cleanup in my database functions, I
dumped the schema (pgdump -s) and the data (pgdump -a) to seperate text
files.

I editied and restored the schema fine. However, when I attemped to
restore the data (via \i filename), it failed selectively; some tables
were restored but many were not. No errors were logged.

Can someone give me some help? The application is due next week, and I
can't afford this setback. Grazie.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco







^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Problems with PG_DUMP and restore
@ 2001-07-10 16:47  Richard Huxton <dev@archonet.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Richard Huxton @ 2001-07-10 16:47 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; pgsql-sql

From: "Josh Berkus" <josh@agliodbs.com>

> Folks,
>
> In an effort to do some general cleanup in my database functions, I
> dumped the schema (pgdump -s) and the data (pgdump -a) to seperate text
> files.
>
> I editied and restored the schema fine. However, when I attemped to
> restore the data (via \i filename), it failed selectively; some tables
> were restored but many were not. No errors were logged.

Try having a look at the order the tables get inserted esp. with regard to
any foreign keys etc. - I'm not sure pgdump is that clever about such
things.

- Richard Huxton




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Problems with PG_DUMP and restore
@ 2001-07-10 18:50  Josh Berkus <josh@agliodbs.com>
  parent: Richard Huxton <dev@archonet.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Josh Berkus @ 2001-07-10 18:50 UTC (permalink / raw)
  To: Richard Huxton <dev@archonet.com>; pgsql-sql

Richard,

> 
> Try having a look at the order the tables get inserted esp. with
> regard to
> any foreign keys etc. - I'm not sure pgdump is that clever about such
> things.

Thanks.  I did try that; however:
1. Even trying re-loading the tables twice did not work, as it should
have with missing foriegn keys.
2. I did not see any Unmatched Reference errors in the log, as I
typically do with missing keys.

Further, I *was* able to re-load the database from a unitary pg_dump
file ... one that includes both DDL and data.  It's just when I split
the pg_dump into two files -- one for DDL, one for data -- that it
fails.

BTW, I'm using 7.1RC2

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Problems with PG_DUMP and restore
@ 2001-07-10 19:35  Andre Schnabel <A_Schnabel@t-online.de>
  0 siblings, 0 replies; 5+ messages in thread

From: A_Schnabel@t-online.de @ 2001-07-10 19:35 UTC (permalink / raw)
  To: pgsql-sql

Hi,

>In an effort to do some general cleanup in my database functions, I
>dumped the schema (pgdump -s) and the data (pgdump -a) to seperate text
>files.

I'm using a similar method for my own project. But I dump the data with
the -d or -D option. This ist not as fast as the raw copy but more flexible.
Maybe you should give it a try.
If you rename some colums of your table, you should use -d (propert INSERT
commands instead of COPY).
If you reorder the colums of your tables you should use -D (propert INSERT
with explicit Attributenames).

You cannot do both (rename and reorder columns)!

Andre

Attachments:

Andre Schnabel.vcf (text/x-vcard, 211B) download BEGIN:VCARD VERSION:2.1 N:Schnabel;Andre FN:Andre Schnabel NICKNAME:Thalion X-WAB-GENDER:2 BDAY:19720509 EMAIL;INTERNET:a_schnabel@gmx.de EMAIL;PREF;INTERNET:a_schnabel@t-online.de REV:20010710T193556Z END:VCARD
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: Problems with PG_DUMP and restore
@ 2001-07-11 08:47  Richard Huxton <dev@archonet.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Richard Huxton @ 2001-07-11 08:47 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; pgsql-sql

From: "Josh Berkus" <josh@agliodbs.com>

> Richard,
> > Try having a look at the order the tables get inserted esp. with
> > regard to
> > any foreign keys etc. - I'm not sure pgdump is that clever about such
> > things.
>
> Thanks.  I did try that; however:
> 1. Even trying re-loading the tables twice did not work, as it should
> have with missing foriegn keys.
> 2. I did not see any Unmatched Reference errors in the log, as I
> typically do with missing keys.
>
> Further, I *was* able to re-load the database from a unitary pg_dump
> file ... one that includes both DDL and data.  It's just when I split
> the pg_dump into two files -- one for DDL, one for data -- that it
> fails.
>
> BTW, I'm using 7.1RC2

Was there not some fix to pgdump in 7.1.1?

Yep -
  pg_dump fixes (Philip)
  pg_dump can dump 7.0 databases (Philip)

Might be worth a quick upgrade & see what happens.

Failing that, it isn't something to do with permissions and pgdump
connecting as various users?

- Richard Huxton




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2001-07-11 08:47 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-10 14:50 Problems with PG_DUMP and restore Josh Berkus <josh@agliodbs.com>
2001-07-10 16:47 ` Richard Huxton <dev@archonet.com>
2001-07-10 18:50   ` Josh Berkus <josh@agliodbs.com>
2001-07-11 08:47     ` Richard Huxton <dev@archonet.com>
2001-07-10 19:35 Re: Problems with PG_DUMP and restore Andre Schnabel <A_Schnabel@t-online.de>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox