Re: pg_restore and FK constraints with large dbs

pgSql 7.4.rc2

Hi,

Am somewhat lost about how one should use pg_restore with large dbs. For
simplicity, let's assume that db has only one (1) table with huge amout of
rows. Tried several approaches:

1) dump the data only, create the table with all indexes and constraints (from
script that has nothing to do with pg_restore), import the data. The import
part was running for hours (14+) with no end in sight. Had to kill it.

2) dump the data only, create the table with no indexes or constraints, import
the data, recreate indexes and constraints.

This approach produced better results. Data were imported in about two (2)
hours, indexes also were created fairly quickly. Howerver, creattion of FK
constraints is killing performance again. It appears in the past there was some
discussion regarding the ability to create a FK constraint without actually
checking the records. It makes a lot of sense when importing data from
pg_restore since data comes from db where the constraint was in place, hence
it's clean.

Any ideas? Thanks

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

ow writes:

> 1) dump the data only, create the table with all indexes and constraints (from
> script that has nothing to do with pg_restore), import the data. The import
> part was running for hours (14+) with no end in sight. Had to kill it.

Dump the data and the schema and it will do it in the right order.

--
Peter Eisentraut peter_e(at)gmx(dot)net

--- Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> ow writes:
>
> > 1) dump the data only, create the table with all indexes and constraints
> (from
> > script that has nothing to do with pg_restore), import the data. The import
> > part was running for hours (14+) with no end in sight. Had to kill it.
>
> Dump the data and the schema and it will do it in the right order.

Can you clarify how this would be better than the second option I described?
Unless pg_restore can somehow create FK constraints differently (and more
efficiently) than using "ALTER TABLE xxx ADD CONSTRAINT", I do not see how this
would help.

Thanks

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

ow writes:

> Can you clarify how this would be better than the second option I described?
> Unless pg_restore can somehow create FK constraints differently (and more
> efficiently) than using "ALTER TABLE xxx ADD CONSTRAINT", I do not see how this
> would help.

If you you pg_dump and dump schema and data together, then there is some
magic to temporarily disable foreign key constraints. Try it out.
Perhaps you can adopt the statements to your particular restoration method
as well, if it turns out necessary.

--
Peter Eisentraut peter_e(at)gmx(dot)net

--- Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> If you you pg_dump and dump schema and data together, then there is some
> magic to temporarily disable foreign key constraints. Try it out.
> Perhaps you can adopt the statements to your particular restoration method
> as well, if it turns out necessary.
>

No longer have the db to extract data and schema together (have only data
extracts), but I'll try later.

However, if FK constraints are temporarily disabled in pg_restore, why is it
necessary to arrage the statimements in the order that satisfies FK
constraints? One could be doing this for days ...

Wouldn't be simpler for pg_restore to do the following:
1) create table structures only (no indexes or constraints)
2) create indexes
3) create constraints

This way, there's no need to rearrange the order of imports.

Thanks

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

pgSql 7.4.rc2

--- Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> If you you pg_dump and dump schema and data together, then there is some
> magic to temporarily disable foreign key constraints. Try it out.

Dunno. Don't see any magic so far. In fact, pg_restore appears to use
internally the same SQL to verify that the validity of the FKs as "ALTER TABLE
xxx ADD CONSTRAINT". And it's not any faster either.

Is there a special option that one should specify to suspend FK checks during
pg_restore? Otherwise, pg_restore's performance is ... umm, not very good, to
put it mildly.

Thanks

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

ow writes:

> --- Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> > If you you pg_dump and dump schema and data together, then there is some
> > magic to temporarily disable foreign key constraints. Try it out.
>
> Dunno. Don't see any magic so far. In fact, pg_restore appears to use
> internally the same SQL to verify that the validity of the FKs as "ALTER TABLE
> xxx ADD CONSTRAINT". And it's not any faster either.

Read again. No one was talking of pg_restore.

--
Peter Eisentraut peter_e(at)gmx(dot)net

--- Peter EiEisentrautpeter_e(at)gmgmxet> wrote:
>
> Read again. No one was talking of pg_restore.

Perhaps I should clarify.

First, I ran pg_dump to extract schema and data *together*. Then I ran
pg_restore to restore the db. It took about 1 hour to create tables and copy
the data, then about 40 min to create indexes, then pg_restore spent 4.5 hours
checking one (1) FKFKonstraint (80M table with FKFKsgainst 20K table with PKPKs

4.5 hours to check one FKFKonstraint - this is want I meant by bad performance.
I'm looking for a way to suspend FKFKhecks since data is coming from pg_dump
and, hence, it's clean.

Thanks

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

Where can we place wishes for PostgreSQL v7.5 and 8.0 ??? Is it
pgsql-hackers ???

-------------------------------------------------------

Oli Sennhauser
Database-Engineer (Oracle & PostgreSQL)
Rebenweg 6
CH - 8610 Uster / Switzerland

Phone (+41) 1 940 24 82 or Mobile (+41) 79 450 49 14
e-Mail oli(dot)sennhauser(at)bluewin(dot)ch
Website http://mypage.bluewin.ch/shinguz/PostgreSQL/

Secure (signed/encrypted) e-Mail with a Free Personal SwissSign ID: http://www.swisssign.ch

Import the SwissSign Root Certificate: http://swisssign.net/cgi-bin/trust/import

On Sun, 16 Nov 2003, ow wrote:

> --- Peter EiEisentrautpeter_e(at)gmgmxet> wrote:
> >
> > Read again. No one was talking of pg_restore.
>
> Perhaps I should clarify.
>
> First, I ran pg_dump to extract schema and data *together*. Then I ran
> pg_restore to restore the db. It took about 1 hour to create tables and copy
> the data, then about 40 min to create indexes, then pg_restore spent 4.5 hours
> checking one (1) FKFKonstraint (80M table with FKFKsgainst 20K table with PKPKs
>
> 4.5 hours to check one FKFKonstraint - this is want I meant by bad performance.
> I'm looking for a way to suspend FKFKhecks since data is coming from pg_dump
> and, hence, it's clean.

Only assuming that no changes were made between dump and restore. This
could be changes to schema or data done manually, but it could also be a
locale or possibly encoding change if you have any textual foreign keys.

--- Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
> Only assuming that no changes were made between dump and restore. This
> could be changes to schema or data done manually, but it could also be a
> locale or possibly encoding change if you have any textual foreign keys.

I'm restoring the database, meaning that (a) it's believed that current db is
corrupt and needs to be restored or (b) the db is moved to a different server.
Hence any changes after the last dump will be blown away anyway, wouldn't they?
One would have to apply the changes manually or whatever, once the restore is
completed.

The problem is that restoration process is taking too long, mostly due to FK
checks, as it was described earlier, and it appears there's no way to speed
this up.

Sadly, I'm looking at m*Sql and see that they have "SET FOREIGN_KEY_CHECKS=0"
command.

Thanks

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree

On Sun, 16 Nov 2003, ow wrote:

>
> --- Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> wrote:
> > Only assuming that no changes were made between dump and restore. This
> > could be changes to schema or data done manually, but it could also be a
> > locale or possibly encoding change if you have any textual foreign keys.
>
> I'm restoring the database, meaning that (a) it's believed that current db is
> corrupt and needs to be restored or (b) the db is moved to a different server.
> Hence any changes after the last dump will be blown away anyway, wouldn't they?
> One would have to apply the changes manually or whatever, once the restore is
> completed.

Locale/Encoding changes are not part of the inside of the database at all
so there's no guarantees for that. For the others, sometimes people do
work on textual representations of dumps. Neither of those is a reason not
to have an option to turn them off in restore, but the check should be
allowed if desired.

If you look at the -hackers discussion, options for this were talked
about recently, but it came pretty late in the 7.4 development cycle,
after beta started. It might happen for 7.5 if a consensus is reached for
behavior.

--- Rudi Starcevic wrote:

> I'm not trying to upset anyone just trying to help you get an answer for your

> issues.

Who knows what you're trying to do? You did not provide any answers and why you
decided to quote the *same* email *twice* if it was sent only *once* I may
never know.

Anyway, that's not important. What important is that there's no way to suspend
FK checks when restoring a database. Depending on the size of the database, FK
checks alone may add hours or maybe even days to the time that's otherwise
would be necessary to run pg_restore. People with large dbs should be aware of
that.

If one needs to suspend FK checks during db import one may have to consider
another RDBMS ... like mySql, for example.

__________________________________
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree