subject:"Re\: \[HACKERS\] pg_restore and create FK without verification check"

ow wrote:

It appears there's not a lot of interest in discussing the possibility of FK
constraint creation WITHOUT the verification check. How then should one handle
the situation with pg_restore and large dbs where creation of FK constraint(s)
may take hours?
 

I'd prefer a backup/restore method that dumps physical data, so at 
restore time there's no need for recreation of FKs. But I didn't see any 
feedback on this proposal either.

Regards,
Andreas


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] pg_restore and create FK without verification check

2003-11-26 Thread Hannu Krosing

Andreas Pflug kirjutas K, 26.11.2003 kell 12:09:
 ow wrote:
 
 
 It appears there's not a lot of interest in discussing the possibility of FK
 constraint creation WITHOUT the verification check. How then should one handle
 the situation with pg_restore and large dbs where creation of FK constraint(s)
 may take hours?
   
 
 
 I'd prefer a backup/restore method that dumps physical data, so at 
 restore time there's no need for recreation of FKs. But I didn't see any 
 feedback on this proposal either.

Was this proposal a separate one from using WAL logs for PITR ?

-
Hannu


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] pg_restore and create FK without verification check

Hannu Krosing wrote:

Andreas Pflug kirjutas K, 26.11.2003 kell 12:09:
 

ow wrote:

   

It appears there's not a lot of interest in discussing the possibility of FK
constraint creation WITHOUT the verification check. How then should one handle
the situation with pg_restore and large dbs where creation of FK constraint(s)
may take hours?
 

I'd prefer a backup/restore method that dumps physical data, so at 
restore time there's no need for recreation of FKs. But I didn't see any 
feedback on this proposal either.
   

Was this proposal a separate one from using WAL logs for PITR ?
 

Yes, I mentioned it just a few days when discussing dependency in pg_dump.
This is somewhat complementary to WAL and PITR. I'm seeking for a fast 
way to dump and restore a complete database, like physical file copy, 
without shutting down the backend. I was thinking of a BACKUP command 
that streams out the files including any indexes and non-vacuumed 
tuples. A database recreated from that wouldn't be as clean as a 
pg_dump/pg_restored database, but it would be up much faster, and there 
wouldn't be any dependency problem.
This doesn't really replace pg_dump/pg_restore, because it probably 
wouldn't be able to upgrade a cluster. Still, it would be helpful for 
disaster recovery.

Regards,
Andreas


---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [HACKERS] pg_restore and create FK without verification check


--- Andreas Pflug [EMAIL PROTECTED] wrote:
 Yes, I mentioned it just a few days when discussing dependency in pg_dump.
 This is somewhat complementary to WAL and PITR. I'm seeking for a fast 
 way to dump and restore a complete database, like physical file copy, 
 without shutting down the backend. I was thinking of a BACKUP command 
 that streams out the files including any indexes and non-vacuumed 
 tuples. A database recreated from that wouldn't be as clean as a 
 pg_dump/pg_restored database, but it would be up much faster, and there 
 wouldn't be any dependency problem.
 This doesn't really replace pg_dump/pg_restore, because it probably 
 wouldn't be able to upgrade a cluster. Still, it would be helpful for 
 disaster recovery.
 

I think creating a FK without verification check is still needed, especially in
case if:
1) original db is corrupted
2) during cluster upgrade
3) there's a need to BACKUP/RESTORE a *schema* instead of db.

Thanks




__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] pg_restore and create FK without verification check

ow wrote:

--- Andreas Pflug [EMAIL PROTECTED] wrote:
 

Yes, I mentioned it just a few days when discussing dependency in pg_dump.
This is somewhat complementary to WAL and PITR. I'm seeking for a fast 
way to dump and restore a complete database, like physical file copy, 
without shutting down the backend. I was thinking of a BACKUP command 
that streams out the files including any indexes and non-vacuumed 
tuples. A database recreated from that wouldn't be as clean as a 
pg_dump/pg_restored database, but it would be up much faster, and there 
wouldn't be any dependency problem.
This doesn't really replace pg_dump/pg_restore, because it probably 
wouldn't be able to upgrade a cluster. Still, it would be helpful for 
disaster recovery.

   

I think creating a FK without verification check is still needed, especially in
case if:
1) original db is corrupted
2) during cluster upgrade
 

Agreed. This might be useful for replication purposes too; in MSSQL, you 
can write CREATE TRIGGER ... NOT FOR REPLICATION. I'd like to see a 
transaction safe way (ENABLE/DISABLE TRIGGER command) for this.

3) there's a need to BACKUP/RESTORE a *schema* instead of db.

Thanks



__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly
 



---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [HACKERS] pg_restore and create FK without verification check

2003-11-26 Thread Oli Sennhauser

Hello

I was asking about this too, one or two weeks ago.

It appears there's not a lot of interest in discussing the
possibility of FK
constraint creation WITHOUT the verification check. How then should
one handle
the situation with pg_restore and large dbs where creation of FK
constraint(s)
may take hours?
I'd prefer a backup/restore method that dumps physical data, so at
restore time there's no need for recreation of FKs. But I didn't see
any feedback on this proposal either.
Was this proposal a separate one from using WAL logs for PITR ?

My question then was:

Q2: New situation: Why is it not a good idea to backup the database
files of a cluster incl. all c_log and x_log (log files last) to get a
physicaly hot backup.
In principle it is the same situation like a server which is crashing
(not a once but during some time). After restoring, it should do a redo
and rollback automatically like after a crash. This methode (physical
hot backup) would increas backup and restore times dramatically.
The answer from Robert Treat was:

Essentially I think you're right, it should behave much like a crashing
server. The main reason why people don't recommend it is that (depending on
your os setup) there is the potential to lose data that has been commited but
not actually written to disk. Note that you shouldn't get corrupted data
from this, but in many cases losing data is just as bad so we don't recomend
it. If you really want to do this, you should really either shut down the
database or get LVM going.
I did not yet many tests. But in principle I was able to hot-backup a
cluster or only one database and restore it. But the answer from Robert
makes me a little afraid. It means for me he/they do not trust theire
recovery mechanism. A definitive answer from Robert is still out.

In my opinion a high grade professional database system (like PostgreSQL
is or want to be) should have some hot backup features. Otherwise you
are NEVER able to handle VLDB's. They were discussing about a 32 TB
PostgreSQL database. And I bet my next bonus this year :-), that they
are also not able to backup and restore it in a reasonable time.

Regards Oli

---

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

smime.p7s
Description: S/MIME Cryptographic Signature

Re: [HACKERS] pg_restore and create FK without verification check

2003-11-26 Thread Zeugswetter Andreas SB SD

  Q2: New situation: Why is it not a good idea to backup the database
  files of a cluster incl. all c_log and x_log (log files last) to get a
  physicaly hot backup.
  In principle it is the same situation like a server which is crashing
  (not a once but during some time). After restoring, it should do a redo
  and rollback automatically like after a crash. This methode (physical
  hot backup) would increas backup and restore times dramatically.
 
 The answer from Robert Treat was:
 
  Essentially I think you're right, it should behave much like a crashing 
  server.  The main reason why people don't recommend it is that (depending on 
  your os setup) there is the potential to lose data that has been commited but 
  not actually written to disk.  Note that you shouldn't get corrupted data 
  from this, but in many cases losing data is just as bad so we don't recomend 
  it.  If you really want to do this, you should really either shut down the 
  database  or get LVM going.

The key issue here is to have a pg_control file to start from with a 
finished checkpoint from before you start to backup. Then you need to 
ensure that you have all logfiles from checkpoint until backup finishes.
The last thing to backup must be the last active x_log.
It would prbbly be a good idea to not have a vacuum running concurrently :-)

And then you need to do a lot of tests, since nobody else does it yet.
I think this is an issue, since it is such high risk, nobody will 
step up easily and say that it is safe.

Andreas

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] pg_restore and create FK without verification check

Andreas Pflug [EMAIL PROTECTED] writes:
 This is somewhat complementary to WAL and PITR. I'm seeking for a fast 
 way to dump and restore a complete database, like physical file copy, 
 without shutting down the backend. I was thinking of a BACKUP command 
 that streams out the files including any indexes and non-vacuumed 
 tuples. A database recreated from that wouldn't be as clean as a 
 pg_dump/pg_restored database, but it would be up much faster, and there 
 wouldn't be any dependency problem.

It's already intended to support this as part of the PITR work.  The
idea is you force a checkpoint and then make a tar-format dump of the
database tree (tar or whatever floats your boat, but anyway a
filesystem-level backup).  The database need not be stopped while you do
this, and you don't need a filesystem that can do snapshots or anything
fancy like that.  The tar dump itself most likely does not represent a
consistent state of the database by the time you are done making it.
That is okay, because you have also been archiving off to tape (or
someplace) all the WAL data generated since that pre-dump checkpoint.
You can continue archiving the WAL series for however far forward from
the original dump you feel like.  If you need to recover, you reload the
database from the tar dump and then replay the WAL series against it.
This is indistinguishable from a crash recovery situation --- the
inconsistent tar dump looks just like a disk that has received some
but not all of the updates since the last checkpoint.  Replay will fix it.

The cool thing about this is you can actually bring the DB to the state
it was in at any specific point in time covered by your WAL archive ---
just run the WAL replay as far as you want, then stop.  Solves the
junior DBA deleted all my data Saturday morning problem, thus PITR.
Now the uncool thing is you need massive amounts of secondary storage to
archive all that WAL data, if your installation has heavy update
activity.  But it seems to me it would address the need you mention
above --- you'd just not bother to continue archiving WAL past the end
of the dump operation.

In principle you could do this today, but we don't have enough
support code in place to make it work smoothly, eg WAL segment files
aren't labeled with enough identifying information to let you manage
an archive full of 'em.  Still it doesn't seem that far away.

 This doesn't really replace pg_dump/pg_restore, because it probably 
 wouldn't be able to upgrade a cluster.

Right, any such physical dump would be limited to restoring a whole
cluster as-is: no imports into other clusters, no selectivity, no fancy
games.  The main reason is you'd have to dump and restore pg_clog along
with the data files.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] pg_restore and create FK without verification check


--- Tom Lane [EMAIL PROTECTED] wrote:
  This doesn't really replace pg_dump/pg_restore, because it probably 
  wouldn't be able to upgrade a cluster.
 
 Right, any such physical dump would be limited to restoring a whole
 cluster as-is: no imports into other clusters, no selectivity, no fancy
 games.  The main reason is you'd have to dump and restore pg_clog along
 with the data files.
 

But that would not help people who would HAVE to use pg_dump/pg_restore (e.g.
to backup/restore a single schema), would it? Depending on the db size, etc.,
creation of FK constraint(s) may take many hours. How should this be handled
then?

Thanks




__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] pg_restore and create FK without verification check

ow [EMAIL PROTECTED] writes:
 --- Tom Lane [EMAIL PROTECTED] wrote:
 Right, any such physical dump would be limited to restoring a whole
 cluster as-is: no imports into other clusters, no selectivity, no fancy
 games.

 But that would not help people who would HAVE to use pg_dump/pg_restore (e.g.
 to backup/restore a single schema), would it? Depending on the db size, etc.,
 creation of FK constraint(s) may take many hours. How should this be handled
 then?

Quite honestly, I think they should check their foreign keys.  In a
partial restore situation there is no guarantee that the referenced
table and the referencing table are being restored at the same time from
the same dump.  An override in that situation looks like a great tool
for shooting yourself in the foot.

People might be more interested in debating this topic with you if we
hadn't discussed it at length just a couple months back.  There wasn't
consensus then that we had to offer an escape hatch, and you've not
offered any argument that wasn't made before.

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [HACKERS] pg_restore and create FK without verification check


--- Tom Lane [EMAIL PROTECTED] wrote:
 Quite honestly, I think they should check their foreign keys.

Generally speaking, I agree. The problem is that verification of FK
constraint(s) may take too long, depending on the size of the db and other
conditions. In my case, on test data, it takes about 1 hour to create tables
and copy the data, then about 40 min to create indexes, then 4.5 hours to
create one (1) FK constraint. In production, we'll have 10-100x more data than
we have for testing.
If we have a problem in production, the time necessary to restore the db is
simply going to kill us.

 People might be more interested in debating this topic with you if we
 hadn't discussed it at length just a couple months back.  There wasn't
 consensus then that we had to offer an escape hatch, and you've not
 offered any argument that wasn't made before.

I'm simply presenting a problem for which I currently do not see any solution
(it's very important for us to be able to restore db within a reasonable amount
of time). If there's no solution and none is planned, then we cannot use pgsql,
can we?

Thanks






__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] pg_restore and create FK without verification check

2003-11-26 Thread Greg Stark


ow [EMAIL PROTECTED] writes:

 --- Tom Lane [EMAIL PROTECTED] wrote:
  Quite honestly, I think they should check their foreign keys.

What should I do if I *know* there will be a FK failure but I want to correct
it manually. Perhaps by creating all the necessary target records, perhaps by
deleting or updating the dead references. Perhaps with a mix of these.

As it stands I have to delete the FK constraint, load the table, and fix the
data. Then recreate the FK constraint -- with the danger that I'll get the
definition wrong -- and wait for the constraint to be verified.

If I could disable and reenable the constraint the danger that I would get the
definition wrong would be eliminated. And if I had already done the work to
ensure there were no broken relationships I would optionally be able to skip
the redundant automatic check. I could even have done the verification myself
while the data wasn't live for example.

The database is a tool. It's annoying to have a tool that tries to outsmart
the user.

-- 
greg


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] pg_restore and create FK without verification check

Tom Lane wrote:

Andreas Pflug [EMAIL PROTECTED] writes:
 

This is somewhat complementary to WAL and PITR. I'm seeking for a fast 
way to dump and restore a complete database, like physical file copy, 
without shutting down the backend. I was thinking of a BACKUP command 
that streams out the files including any indexes and non-vacuumed 
tuples. A database recreated from that wouldn't be as clean as a 
pg_dump/pg_restored database, but it would be up much faster, and there 
wouldn't be any dependency problem.
   

It's already intended to support this as part of the PITR work.  The
idea is you force a checkpoint and then make a tar-format dump of the
database tree (tar or whatever floats your boat, but anyway a
filesystem-level backup).  The database need not be stopped while you do
this, and you don't need a filesystem that can do snapshots or anything
fancy like that.  The tar dump itself most likely does not represent a
consistent state of the database by the time you are done making it.
That is okay, because you have also been archiving off to tape (or
someplace) all the WAL data generated since that pre-dump checkpoint.
You can continue archiving the WAL series for however far forward from
the original dump you feel like.  If you need to recover, you reload the
database from the tar dump and then replay the WAL series against it.
This is indistinguishable from a crash recovery situation --- the
inconsistent tar dump looks just like a disk that has received some
but not all of the updates since the last checkpoint.  Replay will fix it.
The cool thing about this is you can actually bring the DB to the state
it was in at any specific point in time covered by your WAL archive ---
just run the WAL replay as far as you want, then stop.  Solves the
junior DBA deleted all my data Saturday morning problem, thus PITR.
Now the uncool thing is you need massive amounts of secondary storage to
archive all that WAL data, 

Shouldn't be a problem, since there are few databases out there 
worldwide exceeding today's average disk capacity...

if your installation has heavy update
activity.  But it seems to me it would address the need you mention
above --- you'd just not bother to continue archiving WAL past the end
of the dump operation.
 

PITR is cool, no question, it's more than I've been requesting. When the 
database server burns, I'd be quite happy if I could restore to my 
latest tape's point in time, since the WAL log disk probably isn't 
functional too. So having a fast backup of the snapshot when the backup 
CHECKPOINT was issued would be enough, no WAL replay needed.

In principle you could do this today, but we don't have enough
support code in place to make it work smoothly, eg WAL segment files
aren't labeled with enough identifying information to let you manage
an archive full of 'em.  Still it doesn't seem that far away.
 

So I issue CHECKPOINT, and tar the cluster or database. Still, I got two 
questions:
- how to restore a single database
- while tar is running, CHECKPOINT(n+1) might be recorded in some files, 
while others have CHECKPOINT(n). How does the backend know to rollback 
to CHECKPOINT(n)?

Regards,
Andreas


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] pg_restore and create FK without verification check

Greg Stark wrote:

If I could disable and reenable the constraint the danger that I would get the
definition wrong would be eliminated. And if I had already done the work to
ensure there were no broken relationships I would optionally be able to skip
the redundant automatic check. I could even have done the verification myself
while the data wasn't live for example.
 

Since FKs are implemented as trigger, you could disable all triggers on 
the table right now, no? Could be a bit more comfortable, I agree, and 
hope for an upcoming DISABLE TRIGGER command.

While talking about this: I could add ENABLE/DISABLE TRIGGER 
functionality to pgadmin3. Unfortunately, on pg_trigger.tgenabled 
there's still the comment not presently checked everywhere it should 
be, so disabling a trigger by setting this to false does not work 
reliably. I wonder if this is still true for 7.4. I can't imagine that 
this should be so hard to fix.

Regards,
Andreas


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] pg_restore and create FK without verification check

Andreas Pflug [EMAIL PROTECTED] writes:
 In principle you could do this today, but we don't have enough
 support code in place to make it work smoothly, eg WAL segment files
 aren't labeled with enough identifying information to let you manage
 an archive full of 'em.  Still it doesn't seem that far away.

 So I issue CHECKPOINT, and tar the cluster or database. Still, I got two 
 questions:
 - how to restore a single database

You don't.  As I said, any physical backup is going to be
all-or-nothing.  These techniques are not a replacement for pg_dump.

 - while tar is running, CHECKPOINT(n+1) might be recorded in some files, 
 while others have CHECKPOINT(n). How does the backend know to rollback 
 to CHECKPOINT(n)?

That's part of the management code that we need to write before this
will really be very useful; you need to be able to associate the
starting time of a tar dump with the most recent previous CHECKPOINT
in the WAL logs.  Not hard in principle, just takes some code we ain't
got yet.

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] pg_restore and create FK without verification check

Tom Lane wrote:

- how to restore a single database
   

You don't.  As I said, any physical backup is going to be
all-or-nothing.  These techniques are not a replacement for pg_dump.
 

That's sad. I've been backing up and restoring single databases from a 
cluster frequently, so I'd really like the database to be selectable.

That's part of the management code that we need to write before this
will really be very useful; you need to be able to associate the
starting time of a tar dump with the most recent previous CHECKPOINT
in the WAL logs.  Not hard in principle, just takes some code we ain't
got yet.
 

So I lay back patiently (more or less :-)

Regards,
Andreas


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] pg_restore and create FK without verification check

2003-11-26 Thread Stephan Szabo


On Wed, 26 Nov 2003, ow wrote:

  People might be more interested in debating this topic with you if we
  hadn't discussed it at length just a couple months back.  There wasn't
  consensus then that we had to offer an escape hatch, and you've not
  offered any argument that wasn't made before.

 I'm simply presenting a problem for which I currently do not see any solution
 (it's very important for us to be able to restore db within a reasonable amount
 of time). If there's no solution and none is planned, then we cannot use pgsql,
 can we?

You can make your own solution, that's the nice thing about open source
stuff.  If you wanted to go the SET variable route to control alter time
checks of CHECK and FOREIGN KEY constraints, it's almost certainly less
than an hours worth of work.

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [HACKERS] pg_restore and create FK without verification check

2003-11-26 Thread Stephan Szabo


On Wed, 26 Nov 2003, Tom Lane wrote:

 Quite honestly, I think they should check their foreign keys.  In a
 partial restore situation there is no guarantee that the referenced
 table and the referencing table are being restored at the same time from
 the same dump.  An override in that situation looks like a great tool
 for shooting yourself in the foot.

 People might be more interested in debating this topic with you if we
 hadn't discussed it at length just a couple months back.  There wasn't
 consensus then that we had to offer an escape hatch, and you've not
 offered any argument that wasn't made before.

I actually thought the majority in the past discussion thought that an
escape hatch was a good idea, but that the discussion broke down in trying
to determine what sort of hatch that might be (iirc, it got off into the
general discussion of disabling constraints for normal operation as
opposed to at alter time).

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] pg_restore and create FK without verification check

2003-11-26 Thread Robert Treat

On Wed, 2003-11-26 at 12:43, Andreas Pflug wrote:
 Greg Stark wrote:
 
 If I could disable and reenable the constraint the danger that I would get the
 definition wrong would be eliminated. And if I had already done the work to
 ensure there were no broken relationships I would optionally be able to skip
 the redundant automatic check. I could even have done the verification myself
 while the data wasn't live for example.
   
 
 
 Since FKs are implemented as trigger, you could disable all triggers on 
 the table right now, no? Could be a bit more comfortable, I agree, and 
 hope for an upcoming DISABLE TRIGGER command.

ISTM I've done this before... from a pg_dump -Fc backup first do a
pg_dump -s restore (schema only) and then a pg_dump -a
--disable-triggers to load the data without check foreign keys. 

Theres certainly potential for trouble with this method I suppose but it
seems like it accomplish what the original poster requires...


Robert Treat

-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] pg_restore and create FK without verification check

ow [EMAIL PROTECTED] writes:
 --- Tom Lane [EMAIL PROTECTED] wrote:
 Quite honestly, I think they should check their foreign keys.

 Generally speaking, I agree. The problem is that verification of FK
 constraint(s) may take too long, depending on the size of the db and other
 conditions. In my case, on test data, it takes about 1 hour to create tables
 and copy the data, then about 40 min to create indexes, then 4.5 hours to
 create one (1) FK constraint.

If you're seeing this on 7.4, I'd like to see the details of the exact
commands being issued.  If it's not 7.4, it's not a relevant
measurement.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] pg_restore and create FK without verification check

2003-11-26 Thread Stephan Szabo


On Wed, 26 Nov 2003, Tom Lane wrote:

 ow [EMAIL PROTECTED] writes:
  --- Tom Lane [EMAIL PROTECTED] wrote:
  Quite honestly, I think they should check their foreign keys.

  Generally speaking, I agree. The problem is that verification of FK
  constraint(s) may take too long, depending on the size of the db and other
  conditions. In my case, on test data, it takes about 1 hour to create tables
  and copy the data, then about 40 min to create indexes, then 4.5 hours to
  create one (1) FK constraint.

 If you're seeing this on 7.4, I'd like to see the details of the exact
 commands being issued.  If it's not 7.4, it's not a relevant

IIRC, he was. I think the thing causing the difference between his times
and the ones we saw typically when doing the tests was that he didn't have
an index on the fktable's referencing column.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] pg_restore and create FK without verification check

Stephan Szabo wrote:



IIRC, he was. I think the thing causing the difference between his times
and the ones we saw typically when doing the tests was that he didn't have
an index on the fktable's referencing column.
 

A common mistake, can't count how often I created this one... And not 
easy to find, because EXPLAIN won't explain triggers.
I'm planning to create some kind of fk index wizard in pgAdmin3, which 
finds out about fks using columns that aren't covered by an appropriate 
index. Maybe this check could be performed (as a NOTICE) when the fk is 
created?

Regards,
Andreas


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] pg_restore and create FK without verification check

Stephan Szabo [EMAIL PROTECTED] writes:
 On Wed, 26 Nov 2003, Tom Lane wrote:
 If you're seeing this on 7.4, I'd like to see the details of the exact
 commands being issued.  If it's not 7.4, it's not a relevant

 IIRC, he was. I think the thing causing the difference between his times
 and the ones we saw typically when doing the tests was that he didn't have
 an index on the fktable's referencing column.

I'm not convinced it should matter all that much, for the single-query
test method that 7.4 uses.  That's why I wanted to see details.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] pg_restore and create FK without verification check


--- Andreas Pflug [EMAIL PROTECTED] wrote:
 Stephan Szabo wrote:
 
 
 
 IIRC, he was. I think the thing causing the difference between his times
 and the ones we saw typically when doing the tests was that he didn't have
 an index on the fktable's referencing column.
   
 
 
 A common mistake, can't count how often I created this one...

Wrong. It's a mistake to think that you always HAVE to have an index on FK
column. See the links below for more details:

http://archives.postgresql.org/pgsql-admin/2003-11/msg00317.php
http://archives.postgresql.org/pgsql-admin/2003-11/msg00319.php




__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [HACKERS] pg_restore and create FK without verification check

2003-11-26 Thread Christopher Kings-Lynne

A common mistake, can't count how often I created this one... And not 
easy to find, because EXPLAIN won't explain triggers.
I'm planning to create some kind of fk index wizard in pgAdmin3, which 
finds out about fks using columns that aren't covered by an appropriate 
index. Maybe this check could be performed (as a NOTICE) when the fk is 
created?
Weird - I'm planning the exact same thing for phpPgAdmin!

Great minds think alike :P

Chris



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] pg_restore and create FK without verification check

2003-11-26 Thread Kevin Brown

Tom Lane wrote:
 Andreas Pflug [EMAIL PROTECTED] writes:
  In principle you could do this today, but we don't have enough
  support code in place to make it work smoothly, eg WAL segment files
  aren't labeled with enough identifying information to let you manage
  an archive full of 'em.  Still it doesn't seem that far away.
 
  So I issue CHECKPOINT, and tar the cluster or database. Still, I got two 
  questions:
  - how to restore a single database
 
 You don't.  As I said, any physical backup is going to be
 all-or-nothing.  These techniques are not a replacement for pg_dump.

But this is just an artifact of the fact that the WAL is a single
instance-wide entity, rather than a per-database entity.  But since
databases are completely separate entities that cannot be simultaneously
accessed by any query (corrections welcome), there isn't any reason in
principle that the WAL files cannot also be created on a per-database
basis.

I'm sure, of course, that doing so would bring with it a new set of
problems and tradeoffs, so it might not be worth it...


-- 
Kevin Brown   [EMAIL PROTECTED]

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] pg_restore and create FK without verification check

2003-11-26 Thread Alvaro Herrera

On Thu, Nov 27, 2003 at 12:40:28AM +0100, Andreas Pflug wrote:

 A common mistake, can't count how often I created this one... And not 
 easy to find, because EXPLAIN won't explain triggers.

That's a pity.  And the lack of EXPLAINing function execution, too.
Maybe it's not that hard to do?

-- 
Alvaro Herrera (alvherre[a]dcc.uchile.cl)
Just treat us the way you want to be treated + some extra allowance
for ignorance.(Michael Brusser)

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [HACKERS] pg_restore and create FK without verification check

Kevin Brown [EMAIL PROTECTED] writes:
 Tom Lane wrote:
 You don't.  As I said, any physical backup is going to be
 all-or-nothing.  These techniques are not a replacement for pg_dump.

 But this is just an artifact of the fact that the WAL is a single
 instance-wide entity, rather than a per-database entity.  But since
 databases are completely separate entities that cannot be simultaneously
 accessed by any query (corrections welcome), there isn't any reason in
 principle that the WAL files cannot also be created on a per-database
 basis.

WAL is not the bottleneck ... as I already mentioned today, pg_clog (and
more specifically the meaning of transaction IDs) is what really makes a
cluster an indivisible whole at the physical level.

If you want to do separate physical dumps/restores, the answer is to set
up separate clusters (separate postmasters).  Not so hard, is it?

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] pg_restore and create FK without verification check

Alvaro Herrera [EMAIL PROTECTED] writes:
 That's a pity.  And the lack of EXPLAINing function execution, too.
 Maybe it's not that hard to do?

Not sure if it's hard or not, but it'd sure be a nice thing to have.

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] pg_restore and create FK without verification check

2003-11-26 Thread Kevin Brown

Tom Lane wrote:
 Kevin Brown [EMAIL PROTECTED] writes:
  Tom Lane wrote:
  You don't.  As I said, any physical backup is going to be
  all-or-nothing.  These techniques are not a replacement for pg_dump.
 
  But this is just an artifact of the fact that the WAL is a single
  instance-wide entity, rather than a per-database entity.  But since
  databases are completely separate entities that cannot be simultaneously
  accessed by any query (corrections welcome), there isn't any reason in
  principle that the WAL files cannot also be created on a per-database
  basis.
 
 WAL is not the bottleneck ... as I already mentioned today, pg_clog (and
 more specifically the meaning of transaction IDs) is what really makes a
 cluster an indivisible whole at the physical level.
 
 If you want to do separate physical dumps/restores, the answer is to set
 up separate clusters (separate postmasters).  Not so hard, is it?

Well, aside from the fact that separate clusters have completely separate
user databases, listen on different ports, will compete with other
clusters on the same system for resources that would be better managed
by a single cluster, and generally have to be maintained as completely
separate entities from start to finish, no it's not that hard.  ;-)


The ability to restore a single large database quickly is, I think,
a reasonable request, it's just that right now it's difficult (perhaps
impossible) to satisfy that request.


It's probably something that we'll have to deal with if we want PG to be
useful to people managing really large databases on really, really big
iron, though.


-- 
Kevin Brown   [EMAIL PROTECTED]

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] pg_restore and create FK without verification check