Back

Clone or copy a PostgreSQL database in Production

added on :: 7 January 2015


Firstly how to clone in the database on the server side


connect to your database with psql command (bellow is how to for a Amazon RDS instance)
psql -U databasesername -h mysetver.rds.bzone.rds.amazonaws.com -p 5432 mydatabase_production

Then enter your database password.

(If you are using an IP as the host put the IP address after -h)

then create a database with same configurations as the database you are trying to copy

eg:
CREATE DATABASE newdatabse_name WITH ENCODING='UTF8' 
OWNER=databasesername LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' CONNECTION LIMIT=-1;
\q   to exit

Then:  in your command line (not in the psql mode)

pg_dump -U databasesername -h rdsdb.hellohello.region.rds.amazonaws.com -p 5432 live_production_db | psql -U databasesername -h 
rdsdb.hellohello.region.amazonaws.com -p 5432 copy_databasename

Now this is where it would prompt you for the password. Make sure you enter the password twice.


2nd way to do the above


If your database is not the live database you can do the bellow. If it is the live database it would give you multiple connection error.

Connect to your db server and then


CREATE DATABASE newdatabse_name WITH ENCODING='UTF8' OWNER=databasesername TEMPLATE=mydatabase_production LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' CONNECTION LIMIT=-1;
Keyword here is "Template"

to quit and run.....
\q 

If you want to do a local copy of the server database

Create a empty database

pg_dump -U databasesername -h rdsdb.hellohello.region.rds.amazonaws.com -p 5432 live_production_db | psql -h localhost -p 5432 lcaldb_name
If you get "ERROR:  role "batman" does not exist"

sudo -u postgres createuser batman
then try again

Hope this adds value to you.
Back