Remap Schema in PostgreSQL

In this blogpost I will discuss how can you remap database objects you would like to move from one schema to another.

The method i will be using is through backup dump:

In a database “company_sales”, I have tables under the default “PUBLIC” schema:

// I will first create the destination schema:

create schema schema1;

// then, i will exit the psql shell and execute the following backup command of the databaase:

pg_dump -d company_sales -f sales_db_backup.sql

// after that i will replace the schema definition of the generated SQL file from public—->schema1

sed -i ‘s/public/schema1/g’ sales_db_backup.sql

// i will perform a restore of the tables

psql company_sales < sales_db_backup.sql postgres

// to verify connect to the database and query the dictionary

select * from pg_tables;

select * from schema1.company_employees;