Re: Restoring a postgres database

On Wed, 9 Jul 2003, Martijn van Oosterhout wrote:

> Ok, in your experience, are all depndancy loops either:
>
> - CHECKs or DEFAULTs that refer to functions that don't exist yet
> - FUNCTIONs that refer to tables that don't exist yet

I'm not sure which is the most common. I think for me it have been
functions that refer to tables that dont exist. I actually don't remember
exactly what it have been. I know I once tried to write a small parser
that concisted of a couple of functions that called each other. In the end
I wrote that in the client instead, but it is one example I've had.

> Because they can all be solved predefining functions and then fully
> declaring them at the end. Are there other possibilities?

I think almost all things is solvable like that. Even tables you can
create as an empty table and add columns afterwards if you like.

Foreign keys is something that very well can point in both directions
between two tables. I've had that in a database, at least once. In this
case there can be problems with restoring the data also unless you check
all constraints in the very end. Otherwise you need to add the rows first
and then update them to get all the "pointers" right.

Maybe a better solution is to make all constraints deferable until the end
of the dump, even such "constraints" as checking if a table exist that a
function refer to. Then you could put the things in any order in the file
and it will work anyway. This view appeals to me.

The problem with dumps is that you don't notice the problems until you
need to restore a database, something that you don't do very often. I have
a lot of dumps stored which is my backups, but I don't know for sure that
these will be easy to restore from (I can always fix them myslef by hand).

--
/Dennis