Postgresql – How to create a blank version of a PostgreSQL database

dumppostgresqlrestoreschemasequence

I've been using pgAdmin to create a database and test it.

I'm at a point now where I need to install this db on different servers. I've been using pg_dump to do this and I've been able to muddle my way through.

But I'm wondering if there's a way to "reset" all the "current" values in any sequence that I have set up?
If you have any other general suggestions on how to create "empty" or "blank" databases, can you let me know?

Best Answer

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 RESTART.

To restart all sequences in the database, retrieve them from the catalog table pg_class:

DO
$do$
BEGIN

EXECUTE (
   SELECT string_agg('ALTER SEQUENCE ' || oid::regclass || ' RESTART;', E'\n')
   FROM   pg_class
   WHERE  relkind = 'S'  -- 'S' for sequences
   );

END
$do$

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.

Related Question