Clarify how to use pg_dump & pg_restore with periods

[pmenke-de]

Relating to #22, I've recently stumbled over the problem of "how to dump & restore system versioned tables".

My personal preference of pg_dump --format=custom --file=dump.psql my_source_database & pg_restore --format=custom --clean --if-exists --single-transcation -d my_target_database doesn't seem to work with periods, as it doesn't seem to like, how pg_restore directly fiddles with its managed tables.

My current workaround for this is

1. ensure the periods extension is installed in the target database
2. create the dump with pg_dump as described above
3. create a use-list, that excludes the periods extension from the restore: pg_restore --format=custom --list dump.psql | grep -Ev 'EXTENSION - (periods|btree_gist) > dump.use-list
4. disable periods's event-triggers:

   ALTER EVENT TRIGGER periods_drop_protection DISABLE;
   ALTER EVENT TRIGGER periods_health_checks DISABLE;
   ALTER EVENT TRIGGER periods_rename_following DISABLE;

5. restore the dump, using the use-list pg_restore --format=custom --single-transaction --clean --if-exists --use-list=dump.use-list -d my_target_database dump.psql
6. re-enable the event-triggers:

   ALTER EVENT TRIGGER periods_drop_protection ENABLE;
   ALTER EVENT TRIGGER periods_health_checks ENABLE;
   ALTER EVENT TRIGGER periods_rename_following ENABLE;

Is there any other recommended way to dumping and restoring a database with periods?

A couple of notes:

• The above workaround probably only works correctly, if the entire database is dumped and restored - or at least all periods employing tables all at once -, as the dumped & restored tables in periods's schema (also called periods by default) contain meta-data for all periods employing tables.
• btree_gist needs to be excluded from the use-list too, as it's a dependency of periods and thus can't be dropped and recreated during restore
• pg_dump currently only supports including named extensions (-e argument) but doesn't support excluding named extensions, which makes the use of use-lists necessary

[df7cb]

Generally, pg_dump/pg_restore want the target database to be empty. The --clean flag is a blunt workaround that fails quite often, I wouldn't recommend relying on it.

[pmenke-de]

Ok, I understand that --clean may be unsupported because of that. Thanks for the clarification.

What remains then, is the problem described in #22, which happens even without --clean in an otherwise empty database:

$> psql <<EOF
CREATE DATABASE test;
\c test
create extension periods cascade;

create table test (id int not null primary key);

select periods.add_system_time_period('test');
select periods.add_system_versioning('test');
EOF

# [... psql output ...]

$> pg_dump -F c -d test -f test.psql
$> psql -c 'DROP DATABASE test;' -c 'CREATE DATABASE test;'
$> pg_restore -F c -d test --single-transaction test.psql
pg_restore: error: could not execute query: FEHLER:  cannot grant DELETE to "public.test_history"; history objects are read-only
CONTEXT:  PL/pgSQL-Funktion periods.health_checks() Zeile 138 bei RAISE
Command was: REVOKE USAGE ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;

Even after some trying around I yet wasn't able to make pg_restore work, without disabling the event-trigger first.
With my (probably incomplete) patch, this still fails with:

pg_restore: error: could not execute query: FEHLER:  cannot revoke SELECT directly from "public.test_history", revoke SELECT from "public.test" instead
CONTEXT:  PL/pgSQL-Funktion periods.health_checks() Zeile 255 bei RAISE
Command was: REVOKE ALL ON TABLE public.test_history FROM postgres;
GRANT SELECT ON TABLE public.test_history TO postgres;

[jmealo]

Can we clarify whether this only happens with --clean or if dump/restore fail in general with periods?