Creating a copy of a database in PostgreSQL

Question

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

User · Answer

Open the Main Window in pgAdmin and then open another Query Tools Window
In the main windows in pgAdmin,

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

Goto the Query Tools Window

Run 2 queries as 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 terminate all active sessions with TemplateDB and then you can now select it as the template to create the new TargetDB database, this avoids getting the already in use error.)

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

User · Answer

Copying an  under load  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     nohup pg dump exampledb  gt  example-01 sql createdb -O postgres exampledbclone 01     my user is  postgres   nohup psql exampledbclone 01  lt  example-01 sql

User · Answer

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

User · Answer

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   To disconnect all other users from the database  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  lt  gt  pg backend pid

User · Answer

Here s the whole process of creating a copying over a database using only pgadmin4 GUI  via backup and restore   Postgres comes with Pgadmin4  If you use macOS you can press CMD SPACE and type pgadmin4 to run it  This will open up a browser tab in chrome      Steps for copying  1  Create the backup  Do this by rightclicking the database -   backup     2  Give the file a name   Like test12345  Click backup  This creates a binary file dump  it s not in a  sql format    3  See where it downloaded  There should be a popup at the bottomright of your screen  Click the  more details  page to see where your backup downloaded to    4  Find the location of downloaded file  In this case  it s  users vincenttang    5  Restore the backup from pgadmin  Assuming you did steps 1 to 4 correctly  you ll have a restore binary file  There might come a time your coworker wants to use your restore file on their local machine  Have said person go to pgadmin and restore  Do this by rightclicking the database -   restore     6  Select file finder  Make sure to select the file location manually  DO NOT drag and drop a file onto the uploader fields in pgadmin  Because you will run into error permissions  Instead  find the file you just created     7  Find said file  You might have to change the filter at bottomright to  All files   Find the file thereafter  from step 4  Now hit the bottomright  Select  button to confirm    8  Restore said file  You ll see this page again  with the location of the file selected  Go ahead and restore it    9  Success  If all is good  the bottom right should popup an indicator showing a successful restore  You can navigate over to your tables to see if the data has been restored propery on each table   10  If it wasn t successful   Should step 9 fail  try deleting your old public schema on your database  Go to  Query Tool     Execute this code block   DROP SCHEMA public CASCADE  CREATE SCHEMA public      Now try steps 5 to 9 again  it should work out  EDIT - Some additional notes  Update PGADMIN4 if you are getting an error during upload with something along the lines of  archiver header 1 14 unsupported version  during restore

User · Answer

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  lt   EXTRACTED  gt   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

User · Answer

PostgreSQL 9 1 2     CREATEDB new db name -T orig db name -O db user

User · Answer

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

User · Answer

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

User · Answer

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 pid  FROM pg stat activity  WHERE pg stat activity datname    SOURCE DB  AND pid  lt  gt  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

User · Answer

To create database dump  cd  var lib pgsql  pg dump database name gt  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

User · Answer

pgAdmin4  1 Select DB you want to copy and disconnect it  Rightclick  quot Disconnect DB quot   2 Create a new db next to the old one   Give it a name  In the  quot definition quot  tab select the first table as an Template  dropdown menu   Hit create and just left click on the new db to reconnect

User · Answer

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

User · Answer

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

User · Answer

If you want to copy whole schema you can make a pg dump with following command   pg dump -h database host com -d database name -n schema name -U database user --password  And when you want to import that 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  More info about connection strings  https   www postgresql org docs current libpq-connect html LIBPQ-CONNSTRING  Or then just combining it in 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

User · Answer

In production environment  where the original database is under traffic  I m simply using   pg dump production-db   psql test-db

User · Answer

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

User · Answer

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  lt  my dump   to restore all of the tables and their data and all access privileges

User · Answer

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

User · Answer

From the documentation  using createdb or CREATE DATABASE with templates is not encouraged      Although it is possible to copy a database other than template1 by   specifying its name as the template  this is not  yet  intended as a   general-purpose    COPY DATABASE    facility  The principal limitation is   that no other sessions can be connected to the template database while   it is being copied  CREATE DATABASE will fail if any other connection   exists when it starts  otherwise  new connections to the template   database are locked out until CREATE DATABASE completes    pg dump or pg dumpall is a good way to go for copying database AND ALL THE DATA  If you are using a GUI like pgAdmin  these commands are called behind the scenes when you execute a backup command  Copying to a new database is done in two phases  Backup and Restore  pg dumpall saves all of the databases on the PostgreSQL cluster  The disadvantage to this approach is that you end up with a potentially very large text file full of SQL required to create the database and populate the data  The advantage of this approach is that you get all of the roles  permissions  for the cluster for free  To dump all databases do this from the superuser account  pg dumpall  gt  db out   and to restore  psql -f db out postgres   pg dump has some compression options that give you much smaller files  I have a production database I backup twice a day with a cron job using  pg dump --create --format custom --compress 5 --file db dump mydatabase   where compress is the compression level  0 to 9  and create tells pg dump to add commands to create the database  Restore  or move to new cluster  by 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 for managing permissions  These are not copied by pg dump  Also  we have not dealt with the settings in postgresql conf and pg hba conf  if you re moving the database to another server   You ll have to figure out the conf settings on your own  But there is a trick I just discovered for backing up roles  Roles are managed at the cluster level and you can ask pg dumpall to backup just the roles with the --roles-only command line switch

[postgresql] Creating a copy of a database in PostgreSQL

Examples related to postgresql