How to copy a database in PostgreSQL

This article describes several ways to copy databases in PostgreSQL.

In PostgreSQL, you can copy databases in several ways:

  1. Use the CREATE DATABASE statement to copy a database from the template database. This method only works within a PostgreSQL server.
  2. Back up an existing database and restore it to a new database.

Copy a database from template database

Sometimes, for data security, you need to copy and backup the database to be operated before operation. You can use CREATE DATABASE statement to copy a database, as follows:

CREATE DATABASE new_db
WITH TEMPLATE old_db;

This statement will copy old_db database to new_db database. The old_db database must be a template database. If it is not a template database, you can modify this database to be a template database using the ALTER DATABASE statement as follows:

ALTER DATABASE old_db WITH IS_TEMPLATE true;

This method can only be used within a PostgreSQL database server. If you want to copy your database between different PostgreSQL database servers, check out the PostgreSQL Backup and Recovery tutorial.

Conclusion

PostgreSQL allows you to copy a template database using the CREATE DATABASE statement.