Creating a copy of the database in PostgreSQL

Question

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

Answer 1

Postgres allows you to use any existing database on the server as a template when creating a new database. I'm not sure if pgAdmin gives a parameter in the database creation dialog, but you can do the following in the query window if it is not:

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser; 

However, you can get:

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

To fix this, you can use this query

 SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'originaldb' AND pid <> pg_backend_pid(); 

Answer 2

Command line version Answer call :

 createdb -O ownername -T originaldb newdb 

This should be done under the privileges of the database wizard, usually postgres.

Answer 3

To clone an existing database using postgres, you can do this

 /* KILL ALL EXISTING CONNECTION FROM ORIGINAL DB (sourcedb)*/ SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'SOURCE_DB' AND pid <> pg_backend_pid(); /* CLONE DATABASE TO NEW ONE(TARGET_DB) */ CREATE DATABASE TARGET_DB WITH TEMPLATE SOURCE_DB OWNER USER_DB; 

IT will destroy the entire connection to the db source, avoiding the error

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

Answer 4

In a production environment where the source database is under traffic, I just use:

 pg_dump production-db | psql test-db 

Answer 5

I don't know about pgAdmin, but pgdump gives you a database dump in SQL. You need to create a database with the same name and make

 psql mydatabase < my dump 

to restore all tables and their data and all access privileges.

Answer 6

First, sudo as a database user:

 sudo su postgres 

Go to the PostgreSQL command line:

 psql 

Create a new database, give rights and exit:

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

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

 pg_dump old_database_name | psql new_database_name 

Answer 7

I applied this approach along with the examples above. I am working on a server "under load" and received an error when I tried to use the @zbyszek method. I was also 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 added with nohup to move output to a file and protect against server shutdown):

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

  My user: postgres

• nohup psql exampledbclone_01 < example-01.sql

Answer 8

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

• Right-click the source database, Backup ... and dump to file.
• Right-click, new object, new database ... and name the destination.
• Right-click on the new database, Restore ... and select the file.

Answer 9

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

Answer:

 CREATE DATABASE newdb WITH TEMPLATE originaldb; 

Tried and tested.

Answer 10

PostgreSQL 9.1.2:

 $ CREATEDB new_db_name -T orig_db_name -O db_user; 

Answer 11

For those who are still interested, I came up with a bash script that does (more or less) what the author wanted. I had to make a copy of the daily business database 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" 

Answer 12

Creating a database dump

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

Dump 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 

Answer 13

If the database has open connections, this may help the script. I use this to create a test database from a real-time database backup every night. This assumes that you have a .SQL backup file from the production database (I do this in 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" 

Answer 14

From the documentation, it is not recommended to use createdb or CREATE DATABASE with templates:

Although you can copy a database other than template1 by specifying its name as a template, this (for now) is not intended for the universal DATABASE COPY tool. The main limitation is that no other sessions can be connected to the template database while it is being copied. CREATE DATABASE will fail if there is any other connection at startup; otherwise, new connections to the template database are blocked until CREATE DATABASE is completed.

pg_dump or pg_dumpall is a good way to copy the database and all the data. If you use a graphical interface such as pgAdmin, these commands are invoked behind the scenes when you run the backup command. Copying to a new database is done in two steps: backup and restore

pg_dumpall saves all databases in a PostgreSQL cluster. The disadvantage of this approach is that in the end you get a potentially very large text file, full of SQL, needed to create a database and populate the data. The advantage of this approach is that you get all the roles (permissions) for the cluster for free. To reset all databases, do this from the root account

 pg_dumpall > db.out 

and restore

 psql -f db.out postgres 

pg_dump has several compression options that give you smaller files. I have a production database that I backup twice a day using cron

 pg_dump --create --format=custom --compress=5 ==file=db.dump mydatabase 

where compress is the compression level (from 0 to 9), and the create command tells pg_dump that you need to add commands to create the database. Restore (or move to a new cluster) using

 pg_restore -d newdb db.dump 

where newdb is the name of the database you want to use.

Other things to think about

PostgreSQL uses ROLES to manage permissions. They are not copied by pg_dump . In addition, we did not deal with the settings in postgresql.conf and pg_hba.conf (if you are moving the database to another server). You will need to determine the conf settings yourself. But there is one trick I just discovered for backing up roles. Roles are managed at the cluster level, and you can ask pg_dumpall to backup only roles using the --roles-only command line --roles-only .

Answer 15

Using pgAdmin, disable the database that you want to use as a template. Then you select it as a template for creating a new database, this avoids a usage error.

Answer 16

If you want to copy the entire schema, you can create pg_dump with the following command:

pg_dump -h database.host.com -d database_name -n schema_name -U database_user --password

And when you want to import this dump, you can use:

psql "host=database.host.com user=database_user password=database_password dbname=database_name options=--search_path=schema_name" -f sql_dump_to_import.sql

Additional information about connection strings: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING.

Or simply combine it into one liner:

 pg_dump -h database.host.com -d postgres -n schema_name -U database_user --password | psql "host=database.host.com user=database_user password=database_password dbname=database_name options=--search_path=schema_name" 

Answer 17

1. Open the main window in pgAdmin, and then open another query tool window.
2. In the main windows in pgAdmin,

Disable the "template" database that you want to use as a template.

3. Go to the query tool window

Run 2 queries as shown below

 SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'TemplateDB' AND pid <> pg_backend_pid(); 

(The above SQL statement will end all active sessions with TemplateDB, and then you can select it as a template to create a new TargetDB database, this will avoid the error that is already used.)

 CREATE DATABASE 'TargetDB' WITH TEMPLATE='TemplateDB' CONNECTION LIMIT=-1; 

Answer 18

Try the following:

 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