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.
The first step is to make a dump of your PostgreSQL database. This can be done with the binary
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
We can new rename the current schema of the database in your favorite editor (like Navicat, Dbweaver, Datagrip, etc.) , or CLI.
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.