Postgres: the best way to move data from an open schema of one database to a new schema of another database

Question

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

I now have two databases

db1 and db2

Both have tables with the same name in their public schema.

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

And move the data from db2.public to db1.new_schema

What is the easiest way to do this?

Answer 1

The easiest way is to rename the schemas. However, you must be sure that you are the only user of the db1 database.

First hide the public schema in db1:

alter schema public rename to original_public; create schema public; 

Then back up and restore:

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

Finally, recreate the appropriate schema names:

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

Another option is to use dblink. It allows you to access data from different databases.

Answer 2

Export "public" from db2 (grant grant and ownership):

 pg_dump -xO -n public db2 > db2.sql 

The exported file will set the search path (somewhere near the top):

 SET search_path = public, pg_catalog; 

change it to:

 CREATE SCHEMA IF NOT EXISTS new_schema; SET search_path = new_schema, pg_catalog; 

Import to db1 as usual:

 psql db1 < db2.sql 

You probably want to transfer everything from the public to the new schema in db1 first.

If the circuit is already configured in db1, you can do the transfer in one go:

 pg_dump -xO -n public db2 | sed 's/search_path = public/search_path = new_schema/' | psql db1 

I would not recommend this without a lot of tests, of course.