14

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 ?

Mandeep Singh
  • 7,674
  • 19
  • 62
  • 104

2 Answers2

17

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.

klin
  • 112,967
  • 15
  • 204
  • 232
  • Updating, about *dblink*, is better to use [*postgres_fdw*](https://www.postgresql.org/docs/current/postgres-fdw.html). As guide say, it "provides roughly the same functionality using a more modern and standards-compliant infrastructure". – Peter Krauss Mar 09 '20 at 02:09
4

Export "public" from db2 (skipping grants and ownership):

pg_dump -xO -n public db2 > db2.sql

The exported file will set up 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'll probably want to move everything from public to a new schema in db1, first.

If the schema is already set up 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

Wouldn't recommend that without a lot of testing, of course.

Dmitri
  • 8,999
  • 5
  • 36
  • 43