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