I have a requirement of merging two databases (Database1 & Database2) to a common database in Postgresql.
Database1
Table1
Id - Value (Id is Primary key)
1 - a
2 - aa
3 - aaa
4 - aaaa
Database2
Table1
Id Value (Id is Primary key)
2 - bb
5 - bbbbb
I want my output as
OutPutDatabase
Table1
Id Value (Id is Primary key)
1 - a
2 - bb
3 - aaa
4 - aaaa
5 - bbbbb
How can I achieve this?
First, load the tables into two separate schemas in the same database.
CREATE SCHEMA db1;
CREATE TABLE db1.table1 (id INT PRIMARY KEY, value VARCHAR(10));
-- load the contents of Table1 @ Database1 into db1.table1
CREATE SCHEMA db2;
CREATE TABLE db2.table1 (id INT PRIMARY KEY, value VARCHAR(10));
-- load the contents of Table1 @ Database2 into db2.table1
Then you can merge the two, by prioritizing db2 over db1.
SELECT
DISTINCT ON (id)
id,
value
FROM (
SELECT
*,
1 AS db
FROM
db1.table1
UNION
SELECT
*,
2 AS db
FROM
db2.table1) AS mix
ORDER BY
id,
db DESC;
Not my idea, but one I have read in the past.
The source: Move table to new database
pg_dump -d db1 -t table1 |psql db2
then psql and do
insert into table2 (select * from table1);
I used the migrate_pkey_sequence() function defined here to migrate the primary keys of multiple tables (with the same schema but different values from different databases) to distinct ranges so that I could merge the tables.
This is the approximate sequence I followed to merge two databases, d1 and d2, each of which has a table example:
Dump d1 and d2 to the respective files d1.sql.gz and d2.sql.gz. This is the command I used to dump:
$ pg_dump --no-owner | gzip > d1.sql.gz
Create a new, empty database on a local PostgreSQL server.
d1.sql.gz.Migrate d1.example using:
# SELECT migrate_pkey_sequence('example', 'id', 'example_id_seq', 1);
INFO: 00000: UPDATE example SET id = nextval('example_id_seq') + 0
INFO: 00000: ALTER SEQUENCE example_id_seq RESTART WITH 1
INFO: 00000: UPDATE example SET id = DEFAULT
migrate_pkey_sequence
-----------------------
4
(1 row)
Record the value printed (4 in this example). That will be the start of the next sequence.
d1-new.sql.gz in the same way as above.Repeat steps 2 through 4 with d2.sql.gz but use the value from step 5 as the argument to migrate_pkey_sequence():
# SELECT migrate_pkey_sequence('example', 'id', 'example_id_seq', 4);
Dump to the file d2-new-without-schema.sql.gz without saving the schema and disable triggers. This is the command I used:
$ pg_dump --no-owner --data-only --disable-triggers | \
gzip > d2-new-without-schema.sql.gz
Create a new, empty database.
Load d1-new.sql.gz and d2-new-without-schema.sql.gz in order. If everything worked as expected, the primary keys should be disjoint and not conflict.
Dump the database if you want to load it into a remote server.
I have been able to easily merge 2 postgres database using pg_dump & pg_restore.
In my case, it was easy as I use UUID identifiers, so there are no duplicated keys.
Export (data-only, excluding some schemas and tables):
pg_dump --dbname=database_1 --username=${POSTGRES_USER} --no-owner --format=tar --data-only --exclude-schema=hdb_views --exclude-schema=hdb_catalog --exclude-table=app_configuration --exclude-table=migrations --file=/database_1.data-only.dump
Import (changing role if necessary):
pg_restore --username=$POSTGRES_USER --role=$POSTGRES_USER --no-owner --exit-on-error --verbose --dbname=database_2 /database_1.data-only.dump
