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 ?

Star

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.

Finalize

Updating, about dblink, is better to use postgres_fdw. As guide say, it "provides roughly the same functionality using a more modern and standards-compliant infrastructure". – Chittagong

Answer 2

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.

Polymorphonuclear

/usr/lib/postgresql/14/bin/pg_dump: invalid option -- '0' – Fennell