Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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:
Still, you may get:

ERROR: source database "originaldb" is being accessed by other users
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.
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:
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;
Copy structure and data from the old database to the new one:
pg_dump old_database_name | psql new_database_name;
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.


Popular posts from this blog

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL pgBadger