how to copy entire database to new database?

[joevol]

I wish to replicate an entire schema - all tables, keys, indexes, sequences, views, functions, and data. I will need to to do this eventually through a script, so that I can reset a schema, on demand, to a starting point. (We are using this schema for a product demonstration, and need the ability to run multiple scemas for multiuple users, as well as reset each schema on demand for the next demonstration.) What's the best approach? We will have a base "master" schema, rarely changed, as a source for the loads. It would be useful if the load re-created all the components so that changes to the master would be reflected in the copies.

Additional information - my database has blobs, so simple sql will not suffice. I am using
pg_dump -b -Fc demo > xx.dump
as the command line to dump the database. I cannot restore this database to a new database
=# drop database abcabc;
=# create database abcabc;
pg_restore -d abcabc xx.dump

...many lines...
pg_restore: NOTICE: CREATE TABLE will create implicit sequence 'fin_transaction_transaction_id_seq' for SERIAL column 'fin_transaction.transaction_id'
...many lines...
pg_restore: [archiver (db)] could not execute query: ERROR: Relation 'fin_transaction_transaction_id_seq' already exists

So pg_restore creates an implicit sequence when the table is created, and then errors out when the actual sequence should be created - yet it created many of the sequences that are defined and used identically. The restore created 113 of 124 tables, 110 of 131 views, 4 of 6 functions, 76 of 89 sequences (at point of failure), but has not yet gotten to the index creation.

 

[rosenk]

You should add [tt]-c[/tt] to the pg_restore command. That tells pg_restore to drop database objects before recreating them.

If you're still having problems, you can drop the database first, then do
[tt]pg_restore -C -d template0 xx.dump[/tt]

That will create the database from the dump file to restore into. (template0 is just used to issue the [tt]CREATE DATABASE[/tt] command.)

 

[ceco]

the idea of dump and restore should work, but consider this one (read it all if you like to not miss something important)

http://www.de.postgresql.org/docs/7.4/interactive/manage-ag-templatedbs.html

however if you change tha structure of the template it will not be available to the already created databases (this problem however exists if you use dump-restore procedure)

 

[joevol]

What you guys handed me may be useful, but it appears to be completely useless. I drop the new database, create the new database under psql. Then, using pg_restore, I restore the database which was created using a from a pg_dump command.
The create database command is (from psql)
create database lshoaf template template0;
The pg_dump command is
pg_dump -b -Fc -i demo > xx.dump
the restore command is
pg_restore -O -s -d lshoaf xx.dump

The restore routine loads most tables, most functions, most sequences, and then fails on the creation of a sequence that was implicitly created when the table was ceated. This loks like a core postgresql system error to me. Quite a few other sequences were implicitly created, and then "re-created" when they occurred in the dump file.

Unless I am really missing something, the problem is not with the database creation. I am using "template0" as the template.

 

[ceco]

now I can't think of what the problem is with dump and restore (it is too late here), but try the other approach

create you base database as template and then

from command prompt
CREATE DATABASE your_new_db TEMPLATE your_template_db;
createdb -T your_template_database new_database
or from psql
createdb -T your_template_db your_new_db;

where your_new_db is your new database
is your original template database your_template_db

as I can imagine it is prity much like the thing you like
(for details just read the link I've sent)