Re: errors reloading with pg_dump

Hi Shaunnx

you may consider

something like...

pg_dumpall -d -U postgres | psql

etc
regds
mallah



> Howdy:
>
> Running PostgreSQL 7.2.1 on RedHat Linux 7.2 kernel 2.4.7-10.
>
> This past weekend, I was trying to test PostgreSQL 7.2.2 on a backup  box.  I did a pg_dump of
> my production database (7.2.1) and tried  to load it on my development DB (7.2.2).  When I did,
> I got
> a slew of errors pertaining to invalid data inside the tables.
>
> For example, if I tried to restore a table that has an invalid date in  the date column, an
> error is generated.  My questions are:
>
> 1) If the errors are generated, does that mean that the record is  skipped each time there is
> invalid data in the table or the table is  skipped?
>
> 2) How can I check the integrity of the new database if pg_dump
> is not loading all of the information from the production database?
>
> 3) I tried doing something like:
>
> [snip]
>
> pg_dump -i -Fc -S postgres -d testdb | pg_restore -v -h 192.168.0.2 -S \  postgres -d testdb
>
> [/snip]
>
> And got a list or errors - mostly because a few tables did not have read  permissions set to
> them.  I have to go threw them by hand to change permissions  (It's odd: how is it that you can
> create a table but the table will NOT have  the owner or it's permissions on it?  like so:
>
> [snip permissions]
>
> testdb=> \dp forums_auth
>  Access privileges for database "testdb"
>       Table            | Access privileges
> -----------------------------------+-------------------
> forums_auth            |
> forums_auth_id_seq     |
>
> [/snip permissions]
>
>
>
> I'm sure I can do a count or something of each table, but, this also  means my backup /
> recovery method is screwy because I can't
> recover anything that PostgreSQL throws errors at.
>
> How is everyone else in the world reloading tables that may have
> invalid data?
>
> Thanks!
>
> -X
>
> ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and
> unsubscribe commands go to majordomo@postgresql.org



-----------------------------------------
Get your free web based email at trade-india.com.
   "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/