pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblems 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>
2001-07-10 16:47 ` Re: Problems with PG_DUMP and restore Richard Huxton <dev@archonet.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 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 ` Re: Problems with PG_DUMP and restore 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 14:50 Problems with PG_DUMP and restore Josh Berkus <josh@agliodbs.com>
2001-07-10 16:47 ` Re: Problems with PG_DUMP and restore Richard Huxton <dev@archonet.com>
@ 2001-07-10 18:50 ` Josh Berkus <josh@agliodbs.com>
2001-07-11 08:47 ` Re: Problems with PG_DUMP and restore 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 14:50 Problems with PG_DUMP and restore Josh Berkus <josh@agliodbs.com>
2001-07-10 16:47 ` Re: Problems with PG_DUMP and restore Richard Huxton <dev@archonet.com>
2001-07-10 18:50 ` Re: Problems with PG_DUMP and restore Josh Berkus <josh@agliodbs.com>
@ 2001-07-11 08:47 ` Richard Huxton <dev@archonet.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
* 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
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