Creating A New Template For Postgres database

One thing that is sometimes useful when you have multiple developers accessing the same Postgres database server is to setup your own template so that you can avoid contention. That way when someone is in the template goofing around your automated unit tests that create a new database don’t break.

To do that you create the template as a regular database then change it to a template by tweaking the internal postgres parameter. Note that you’ll need permission to do this of course.

$ createdb template_donotuse
$ psql template_donotuse
# update pg_database set datistemplate=true  where datname='template';

Postgres allows the use of any existing database on the server as a template when creating a new database. I'm not sure whether pgAdmin gives you the option on the create database dialog but you should be able to execute the following in a query window if it doesn't:

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;
Still, you may get:

ERROR:  source database "originaldb" is being accessed by other users

Solution:

Note that originaldb needs to be idle (no write transactions) for this to work.

no other session will be connected on orginal db 

while the originaldb is being copied by create database with template, postgresql does not allow creating a new connection to it, so no changes are possible.

ANOTHER METHOD  TO CREATING POSTGRES DATABSE USING TEMPLATE:

You can even do "pg_dump -U postgres sourcedb | psql -U postgres newdb" although the efficiency of this technique may be questionable (since you probably end up context switching between reads and writes) 

You can even get your dump from a remote machine via ssh: ssh dbserver pg_dump DBNAME | psql NEWDB ... or pg_dump DBNAME | ssh otherserver pgsql NEWDB ... Permissions and authentication of course need to be handled however you want to handle them.

Go to PostgreSQL command line:

psql

Create the new database, give the rights and exit:

CREATE DATABASE new_database_name;
GRANT ALL PRIVILEGES ON DATABASE new_database_name TO my_user;
\d

Copy structure and data from the old database to the new one:

pg_dump old_database_name | psql new_database_name;

USING PGADMIN:

In pgAdmin you can make a backup from your original database, and then just create a new database and restore from the backup just created:

Right click the source database, Backup... and dump to a file.
Right click, New Object, New Database... and name the destination.
Right click the new database, Restore... and select your file.