Verifying zero rows in the freshly created table should be quite fast...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.
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).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.
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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
