CODEWITHSUNDEEP
databasepostgresql
natsuapo
natsuapo

Reputation: 613

Postgresql: Copy different databases into one database with different schema

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.

Upvotes: 0

Views: 171

Answers (2)

user330315
user330315

Reputation:

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.

Upvotes: 1

aschoerk
aschoerk

Reputation: 3593

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.

Upvotes: 1

Related Questions