Creating a copy of a database in PostgreSQL

A command-line version of Bell's answer:

createdb -O ownername -T originaldb newdb

This should be run under the privileges of the database master, usually postgres.

To clone an existing database with postgres you can do that

/* KILL ALL EXISTING CONNECTION FROM ORIGINAL DB (sourcedb)*/
SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'SOURCE_DB' AND procpid <> pg_backend_pid();

/* CLONE DATABASE TO NEW ONE(TARGET_DB) */
CREATE DATABASE TARGET_DB WITH TEMPLATE SOURCE_DB OWNER USER_DB;

IT will kill all the connection to the source db avoiding the error

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

Don't know about pgAdmin, but pgdump gives you a dump of the database in SQL. You only need to create a database by the same name and do 'psql mydatabase < mydump' to restore all of the tables and their data and all access privileges.

In production environment, where the original database is under traffic, I'm simply using:

pg_dump production-db | psql test-db

I pieced this approach together with the examples from above. I'm working on an "under load" server and got the error when I attempted the approach from @zbyszek. I also was after a "command line only" solution.

createdb: database creation failed: ERROR: source database "exampledb" is being accessed by other users.

Here's what worked for me (Commands prepended with nohup to move output into a file and protect from a server disconnect):

1. nohup pg_dump exampledb > example-01.sql
2. createdb -O postgres exampledbclone_01

   my user is "postgres"

3. nohup psql exampledbclone_01 < example-01.sql

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.

Regards

What's the correct way to copy entire database (its structure and data) to a new one in pgAdmin?

Answer:

CREATE DATABASE newdb WITH TEMPLATE originaldb;

Tried and tested.

PostgreSQL 9.1.2:

$ CREATEDB new_db_name -T orig_db_name -O db_user;

First, sudo as the database user:

sudo su postgres

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

For those still interested, I have come up with a bash script that does (more or less) what the author wanted. I had to make a daily business database copy on a production system, this script seems to do the trick. Remember to change the database name/user/pw values.

#!/bin/bash

if [ 1 -ne $# ]
then
  echo "Usage `basename $0` {tar.gz database file}"
  exit 65;
fi

if [ -f "$1" ]
then
  EXTRACTED=`tar -xzvf $1`
  echo "using database archive: $EXTRACTED";
else
  echo "file $1 does not exist"
  exit 1
fi


PGUSER=dbuser
PGPASSWORD=dbpw
export PGUSER PGPASSWORD

datestr=`date +%Y%m%d`


dbname="dbcpy_$datestr"
createdbcmd="CREATE DATABASE $dbname WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' CONNECTION LIMIT = -1;"
dropdbcmp="DROP DATABASE $dbname"

echo "creating database $dbname"
psql -c "$createdbcmd"

rc=$?
if [[ $rc != 0 ]] ; then
  rm -rf "$EXTRACTED"
  echo "error occured while creating database $dbname ($rc)"
  exit $rc
fi


echo "loading data into database"
psql $dbname < $EXTRACTED > /dev/null

rc=$?

rm -rf "$EXTRACTED"

if [[ $rc != 0 ]] ; then
  psql -c "$dropdbcmd"
  echo "error occured while loading data to database $dbname ($rc)"
  exit $rc
fi


echo "finished OK"

If the database has open connections, this script may help. I use this to create a test database from a backup of the live-production database every night. This assumes that you have an .SQL backup file from the production db (I do this within webmin).

#!/bin/sh

dbname="desired_db_name_of_test_enviroment"
username="user_name"
fname="/path to /ExistingBackupFileOfLive.sql"

dropdbcmp="DROP DATABASE $dbname"
createdbcmd="CREATE DATABASE $dbname WITH OWNER = $username "

export PGPASSWORD=MyPassword



echo "**********"
echo "** Dropping $dbname"
psql -d postgres -h localhost -U "$username" -c "$dropdbcmp"

echo "**********"
echo "** Creating database $dbname"
psql -d postgres -h localhost -U "$username" -c "$createdbcmd"

echo "**********"
echo "** Loading data into database"
psql -d postgres -h localhost -U "$username" -d "$dbname" -a -f "$fname"

To create database dump

cd /var/lib/pgsql/
pg_dump database_name> database_name.out

To resote database dump

psql -d template1
CREATE DATABASE database_name WITH  ENCODING 'UTF8' LC_CTYPE 'en_US.UTF-8' LC_COLLATE 'en_US.UTF-   8' TEMPLATE template0;
CREATE USER  role_name WITH PASSWORD 'password';
ALTER DATABASE database_name OWNER TO role_name;
ALTER USER role_name CREATEDB;
GRANT ALL PRIVILEGES ON DATABASE database_name to role_name;


CTR+D(logout from pgsql console)
cd /var/lib/pgsql/

psql -d database_name -f database_name.out

Using pgAdmin, disconnect the database that you want to use as a template. Then you select it as the template to create the new database, this avoids getting the already in use error.

Try this:

CREATE DATABASE newdb WITH ENCODING='UTF8' OWNER=owner TEMPLATE=templatedb LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' CONNECTION LIMIT=-1;

gl XD