how to create "empty" or "blank" databases
I would use
pg_dump with the
--schema-only option to create the dump for an empty database:
pg_dump mydb -s > /mypath/myfile.sql
Using Linux, you could also pipe the output to
psql and create a new, empty database in the same (or any other) database cluster right away. Example for the same cluster:
createdb mytemplate; pg_dump mydb -s | psql mytemplate
If you should create the database in a different db cluster, remember that roles or tablespaces are cluster-wide objects and not included in the dump of a single db. So, any custom roles (or other cluster-wide objects) have to be present in the new cluster before restoring to it.
This is much faster than truncating existing tables.
a way to "reset" all the "current" values in any sequence
There is a dedicated command for that:
ALTER SEQUENCE RESTART;
Unless defined otherwise, this resets a sequence to
1. To define otherwise, use:
ALTER SEQUENCE RESTART WITH 123;
Or save the desired start value with the sequence:
ALTER SEQUENCE my_seq START WITH 123;
And use the simple form to
To restart all sequences in the database, retrieve them from the catalog table
SELECT string_agg('ALTER SEQUENCE ' || oid::regclass || ' RESTART;', E'\n')
WHERE relkind = 'S' -- 'S' for sequences
Sufficient privileges required. Superusers pass this test automatically.
Careful with that! You normally don't want to lose the current value for all sequences in your database. In any case, you may want to check what the
SELECT cooks up before executing it.
So, I would create an empty copy and then run the above
DO command. Then
pg_dump again and restore to different servers.