Creating a copy of a database in PostgreSQL

Problem: I need to clone production database to the testing purpose, mean I want to create the copy of the production database, so what would be the correct way to copy entire database to a new one.

Solutions: All the given answers are best, it depends on what resources are available for you.

Using SQL command

You can use any existing database as a template to create a new database in PostgreSQL.

Run the following command in psql to create a copy of a database.

CREATE DATABASE destination_db WITH TEMPLATE source_db OWNER dbuser;

Some point we have to keep in mind when creating a copy of the database using the above command in PostgreSQL,

1- Source database name and destination database name cannot be the same.

2- PostgreSQL doesn't (to date) intended as a general-purpose "COPY DATABASE" facility.

3- Due to the principal limitations, there should not be any connected session to the source database while copied being processed.

4- The copied process becomes failed if any session is connected to source_db.

Using pgAdmin

This is an excellent way to copy a database, because the above command may create an issue while copied production database. Using pgAdmin we can create a copy database in two steps.

1-  Backup

2-  Restore

I know it is a long process, but it is good practice and safety.

Using pg_dump

Using pg_dump is the best way to create the backup, it also has some compression option that gives you much smaller files.

pg_dump --create --format=custom --compress=5 ==file=db.source source_db

Where compress is the compression level (0 to 9) and create tells pg_dump to add commands to create the database.

Restore the database by using the following command

pg_restore -d destination_db db.source

Using pg_dumpall

Backup command

pg_dumpall > db.out

         

Restore command

psql -f db.out postgres

pg_dumpall command saves all databases on the PostgreSQL cluster. pg_dumpall command has some disadvantages. Its approach is that you end up with a potentially 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 super_user account.