Back to post list

Duplicate a PostgreSQL schema

25-11-2019

In this post we'll learn how to duplicate a PostgreSQL schema. This might sound like a trivial operation, but there's no built-in solution to perform this safely. The following steps are easy to follow and reproduce. This process can be useful to make a backup of the database as a different schema name.

1. Dump current structure and data

The first step is to make a dump of your PostgreSQL database. This can be done with the binary pg_dump.

The following command will export the database database_name, from the host localhost with a user postgres and schema public to a file dump.sql. The password will be asked during the command execution.

pg_dump -d database_name -h localhost -U postgres -n public > dump.sql

2. Rename the schema

We can new rename the current schema of the database in your favorite editor (like Navicat, Dbweaver, Datagrip, etc.) , or CLI.

3. Import back the dump'ed file

The last step is to import back the dump file into the database using the following command:

psql -h localhost -U postgres -d database_name -f dump.sql

This command uses the psql binary.

We now have 2 schemas with the same data. One that we renamed, and one that we imported back into the database.

Exporting Relations diagram from Sequel Pro Automatically set Authentication tokens in Postman requests

Show comments