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 ?
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.
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.
/usr/lib/postgresql/14/bin/pg_dump: invalid option -- '0'