PostgreSQL - Copy database

Sometimes, instead of back up and restore to a new db and wait your time if you only want to copy a database and put in the same server, you can use the following query:

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

Note that this query only successfully if no sessions existing in originaldb. So, you can check originaldb sessions and kill them with this function pg_terminate_backend(procpid)

Backup/Restore postgresSQL database with pgAdmin

BACKUP

1. Choose database need to backup

2. Save to backup file, choose COMPRESS as default

The GUI actually calls pg_dump command to do the backup

Choose Done to complete. You can see the backup file in selected folder

The backup as COMPRESS, so it cannot be read with text editor

If you want the backup is readable, you can choose backup type is PLAIN

 

RESTORE

1. Create an empty database

2. Choose the created database, and choose Restore

3. Select the backup file

The GUI actually calls pg_restore command to do the restore

4. Click OK to get the task execute. Database is restored to a new one.