3

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?

4

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.

0

You can empty your tables out with the TRUNCATE command. Use the CASCADE modifier to force related records to cascade on foreign key relationships.

TRUNCATE TABLE my_table CASCADE;

Then you can use the ALTER SEQUENCE command to restart your sequences.

ALTER SEQUENCE my_sequence RESTART WITH 1;

Then you can set up a template database with your freshly scrubbed database and from that point on when you create databases you can use it as the template.

CREATE DATABASE my_database TEMPLATE my_template_database;
  • A template database is an excellent choice to create new databases inside the same db cluster once we have a clean template. But it can't be used to install this db on different servers like the OP mentioned. – Erwin Brandstetter Nov 22 '13 at 1:19
  • You can definitely move databases between servers, including template databases. – efesar Nov 22 '13 at 15:30
  • Sure you can. But you cannot use a template from a different database cluster in CREATE DATABASE. – Erwin Brandstetter Nov 22 '13 at 16:07
  • I've never run into such a restriction and I've never heard of it. – efesar Nov 22 '13 at 16:35
  • It is simply impossible. Think about it: there is no way to even identify a different db cluster in the syntax of this statement. Can it be that you are confusing the terms "database", "database cluster" and "server"? – Erwin Brandstetter Nov 22 '13 at 17:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.