Postgresql: Copy different databases into one database with different schema

Question

I have three postgresql databases in a server: db1,db2,db3. Now I want to save the tables in each db to a new db dbnew and distinguish it with schemas. Which means that there are three schemas in this database dbnew.db1,dbnew.db2,dbnew.db3. What is the easiest method to realize it?

(p.s.: I have dumped the database to local files.)

I test the answer from @a_horse_with_no_name, it seems to work but there is a new problem as follows:

If in db1 there is an extension like postgis, then I load db1 with psql and rename public to db1 and create a new schema named public. I could not create the extension postgis for public schema as it shows that extension postgis already exists. However if I use the command \dx, it shows that the extension is in schema db1 not in dbnew. Even if I use create EXTENSION postgis with schema public it doesn't work.

aschoerk · Accepted Answer · 2017-07-19 06:42:16Z

1

Based on backup/plain and restore using psql

I assume, that the schemas in dbnew are created and the rights to schema are set to the user doing all restoring using psql.

Somehow create sql files (backup plain in pgadmin3), the resulting files can be loaded into the dbnew database using psql, but they should piped through sed or grep, so that all tablenames get the schema prefix.

With somehow I meant, backup from original or first restore your backups into a temporary db, if they are no sql yet, and backup them as plain text.

answered Jul 19, 2017 at 6:42

aschoerk

3,6632 gold badges17 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2017-07-19 06:58:47Z

1

Assuming all tables are stored in the public schema in the source databases

Import the first dump (by running psql ... -f dumpfile.sql), then rename the public schema to the new name:

alter schema schema public rename to db1;

Then re-create the public schema:

create schema public;

Now do the same thing for the other two database scripts.

answered Jul 19, 2017 at 6:58

user330315

2 Comments

aschoerk Over a year ago

The solution is great, to get the binary backups into the new database, that is very fast. But the second rename will fail, because the schema already exists. What will work is to use "alter table public.<tablename> set schema dbnew;" for each table in the public schema.

natsuapo Over a year ago

I found if I rename schema with extension, and if I want to create extension in public schema, it fails.

Collectives™ on Stack Overflow

Postgresql: Copy different databases into one database with different schema

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related