Merging multple pg_dumps with columns using auto key I have two unique instances of Postgres using the same database schema but with independent datasets. One of the tables has a column setup for auto increment and it is a primary key. No other tables depend on this primary key.

Is there a way to merge these two databases together and have the import mechanism reassign new keys (the auto increment column) on this specific table only?
What I would do is,


Start a transaction.
Update all constraints on the database you're merging in to ON UPDATE CASCADE
Set all of the ids to be offset by the max(id) of the table you're merging into, with a comfortable margin (so you know they won't overlap at the time of merger)
Merge them in.
Restart the sequence at a number equal to or greater than the maxid of the table's column.


Sample

CREATE TABLE foo ( foo_id serial PRIMARY KEY );
INSERT INTO foo DEFAULT VALUES;
CREATE TABLE bar ( foo_id int REFERENCES foo );
INSERT INTO bar (foo_id) VALUES (1);


Now

BEGIN;
  ALTER TABLE bar
    DROP CONSTRAINT bar_foo_id_fkey ,
    ADD CONSTRAINT bar_foo_id_fkey
      FOREIGN KEY (foo_id)
      REFERENCES foo(foo_id)
      ON UPDATE CASCADE;
  UPDATE foo SET foo_id = foo_id + 1e6;
COMMIT;


Now you've got,

TABLE bar;
 foo_id  
---------
 1000001
(1 row)


Now when you load db2, set the max id to max(foo_id) on the seq.

SELECT setval('foo_foo_id_seq', (SELECT max(foo_id) FROM foo), true);
 setval  
---------
 1000001
(1 row)


And, now when the next row is inserted, it calls nextval (under the hood), and gets

SELECT nextval('foo_foo_id_seq');
 nextval 
---------
 1000002
(1 row)


If you're doing this kind of a stuff a lot, look at using UUID's instead of integer sequences.