Postgres: Best way to move data from public schema of one DB to new schema of another DB

Question

I am new to Postgres and just discovered that I cannot access data of different databases in one SQL query. And also learned the concept of schema in Postgres.

Now, I have two databases

db1 and db2

Both have tables with same name in their public schema.

Now, I want to create a new schema in db1 with name : new_schema

And move data from db2.public to db1.new_schema

What is the easiest way to do this ?

Answer 1

The simplest way to do that is to rename schemas. However you must be sure you are a sole user of db1 database.

First, hide your schema public in db1:

alter schema public rename to original_public;
create schema public;

Next, do the backup and restore:

$ pg_dump --format custom --file "my_backup" --schema "public" "db2"
$ pg_restore --dbname "db1" "my_backup"

Finally, recreate appropriate schema names:

alter schema public rename to my_schema;
alter schema original_public rename to public;

Another option is to use dblink. It enables accessing data of different databases.