Blog

How to copy all objects of one schema to other schema with in same PostgreSQL database?

Published on: March 24, 2022 by Vinayan C V

How to copy all objects of one schema to other schema with in same PostgreSQL database?

Scenario:

You can follow the following steps to copy all objects of one schema to another schema within the same PostgreSQL database:

1.Login to PostgreSQL using the following command

psql -h <host> -U <username> -d <databasename> -p <port no.>

2.Create the new schema using the following command

CREATE SCHEMA <new-schema-name>;

3.Check the relations of the source schema using the following command:

\dt <source-schema-name>.*

4.Rename the target schema to a different name and then rename the source schema to the target schema name. You can use the following commands:

ALTER SCHEMA <target-schema-name> RENAME TO <different-schema-name>;
ALTER SCHEMA <source-schema-name> RENAME TO <target-schema-name>;

5.Now log out from the PostgreSQL command line and take the dump of source schema which is renamed to target schema now. The following command can be used:

pg_dump -h <host> -U <username> -p <port> -n <target-schema-name> -d <databasename> >> <dump filename>
OR
pg_dump -U <username> -h <host> –schema=<target-schema-name> -d <databasename> -f <dump filename.backup>

6.Now login into PostgreSQL again and rename the source schema and target schema to their previous names.

ALTER SCHEMA <target-schema-name> RENAME TO <source-schema-name>;
ALTER SCHEMA <different-schema-name> RENAME TO <target-schema-name>;

7.Restore the database by using the below command

<blockquote>psql -U <username> -h <host> -d <databasename> -f <dump filename.backup</blockquote>

8.You can verify the same after login to PostgreSQL and running the following commands:

\dt <source-schema-name>.*

\dt <target-schema-name>.*

Category : PostgreSQL

Vinayan C V

Vinayan C V

You may also read:

Comments

Add new commentSIGN IN

Let's Connect

Categories

Your Cart

Cart is empty.

Subtotal
₹0.00
APPLY
0
Send this to a friend