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

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

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

--- Andreas Pflug <pgadmin@pse-consulting.de> 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/

ow wrote:

--- Andreas Pflug <pgadmin@pse-consulting.de> 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.

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

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

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.sennhauser@bluewin.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

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

Andreas Pflug <pgadmin@pse-consulting.de> 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

--- Tom Lane <tgl@sss.pgh.pa.us> 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/

ow <oneway_111@yahoo.com> writes:

--- Tom Lane <tgl@sss.pgh.pa.us> 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

--- Tom Lane <tgl@sss.pgh.pa.us> 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/

ow <oneway_111@yahoo.com> writes:

--- Tom Lane <tgl@sss.pgh.pa.us> 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

Tom Lane wrote:

Andreas Pflug <pgadmin@pse-consulting.de> 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

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

Andreas Pflug <pgadmin@pse-consulting.de> 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

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

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.

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).

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