Simplest way to merge two postgres databases into one?

Question

Currently I have two postgres 9 databases for two different datasources.

Database 1 is called musicbrainz, the main user is musicbrainz and tables are created in schema musicbrainz

Database 2 is called discogs, the main user is discogs and tables are created in schema discogs

I now need to do queries that involves tables from both databases, so I want to put all the tables in one single database. I'm happy to start again and load the data from the original data files but whats the easiest way to do this, specifically I'm a little confused between the relationship between username, schemaname and databasename and having a schema named the same as the database/ or user rather than using public.

Additional problem there are some tables with the same name in both databases (musicbrainz and discogs) although renaming one sense would be a possibility. Note the database is only for user by a single user (me)

Solution I've decided to do it this way:

Create new database musicdata
Create new database superuser that matches unix username
Create schema musicbrainz and discogs
Set users schema search path to musicbrainz
Import musicbrainz data into musicdata with schema musicbrainz
Set user schema search path to discogs
Import disocgs data into musicdata with schema discogs
Set users schema search path to musicbrainz, discogs
Check can see all tables with psql and \dt;

Working okay so far

not an answer but a suggestion : you can use dblink to access one database from another — Vivek S.
– Vivek S., Commented Aug 4, 2014 at 11:40
possible duplicate of Postgresql : Merge 2 similar databases — Vivek S.
– Vivek S., Commented Aug 4, 2014 at 11:42
They're already in different schemas. Just load them in a single DB. Then CREATE VIEW wrappers that just UNION ALL the two datasources, e.g. CREATE VIEW tracks AS SELECT col1, col2, col3 FROM musicbrainz.tracks UNION ALL SELECT col1, col2, col3 FROM discogs.tracks. Or use CREATE TABLE (LIKE ...) then INSERT INTO ... SELECT from the two origin tables. — Craig Ringer
– Craig Ringer, Commented Aug 4, 2014 at 11:48
thanks but I think you miunderstood I dont need to union the data — Paul Taylor
– Paul Taylor, Commented Aug 4, 2014 at 13:25
I think dblink would have performance problems, the question was really about how best to logically organize my data and I think my solution does the job. — Paul Taylor
– Paul Taylor, Commented Aug 4, 2014 at 13:26

Paul Taylor · Accepted Answer · 2014-08-26 13:14:02Z

0

Solution I've decided to do it this way:

Create new database musicdata
Create new database superuser that matches unix username
Create schema musicbrainz and discogs
Set users schema search path to musicbrainz
Import musicbrainz data into musicdata with schema musicbrainz
Set user schema search path to discogs
Import disocgs data into musicdata with schema discogs
Set users schema search path to musicbrainz, discogs
Check can see all tables with psql and \dt;

edited Aug 26, 2014 at 13:14

user330315

answered Aug 26, 2014 at 11:58

Paul Taylor

13.4k55 gold badges220 silver badges407 bronze badges

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

Collectives™ on Stack Overflow

Simplest way to merge two postgres databases into one?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related