Re: pg_dump dependency / physical hot backup

Rod Taylor wrote:

>>There might be discussions whether its better to script
>>CREATE TABLE xxx ..;
>>ALTER TABLE xxx ADD PRIMARY KEY ....;
>>ALTER TABLE xxx ADD FOREIGN KEY ....;
>>or
>>CREATE TABLE xxx (...., PRIMARY KEY (..), FOREIGN KEY (..));
>>
>>I'd opt for the second version (a little formatted, maybe :-)
>>
>>
>
>Well.. the second one will be much slower when the foreign keys verify.
>
>
Verifying zero rows in the freshly created table should be quite fast...

>Primary, unique constraints I'll buy in the create statement. Check
>constraints and defaults are a little fuzzier.
>
>Logic will be required to pull them out in the event they call functions
>which depends on the table or we enable subqueries (assertion like
>constraints) in them.
>
>
>
Yes, complicated constructions might prevent creating a table's objects
in a single step. It's probably possible to design an object that can't
be extracted automatically and restored at all (e.g. a view using itself).

I wonder if there have been discussions about other ways to
backup/restore. The current pg_dump/pg_restore way will rebuild all
indexes etc, resulting in a fresh and consistent database after restore,
enabling backend upgrades, but it's tricky to guarantee everything runs
smoothly. And it can be quite slow. In a case of a disaster recovery,
this is probably very unlucky.
I wonder if a mixture of pg_dump and physical cluster backup(currently
only possible if backend is shut down) could be implemented, i.e. a
BACKUP sql command. This command should stream out all data from the
physical files, taking a snapshot. When restoring, the command would
create a new database from the input data, by more or less copying the
data to files. This way, all (hopefully non-existent) inconsistencies in
the database would be restored as well (probably including non-vacuumed
rows), but because no checks are applied the restore process would be as
fast as possible. This would be possible only for the same
backend/architecture version, but in case of disaster recovery that's
enough.

Regards,
Andreas