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., Aug 04 '14 at 11:40
possible duplicate of [Postgresql : Merge 2 similar databases](http://stackoverflow.com/questions/9499227/postgresql-merge-2-similar-databases) — Vivek S., Aug 04 '14 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, Aug 04 '14 at 11:48
thanks but I think you miunderstood I dont need to union the data — Paul Taylor, Aug 04 '14 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, Aug 04 '14 at 13:26

score 0 · Accepted Answer · edited Aug 26 '14 at 13:14

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;

Simplest way to merge two postgres databases into one?

1 Answers1