Обсуждение: Problems with PG_DUMP and restore

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

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

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
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco
 

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

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