Postgresql – Merging multple pg_dumps with columns using auto key

importpg-dumppg-restorepostgresqlprimary-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?

Best Answer

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.

Related Solutions

Mysql – Primary key auto increment best practices

You're using a BIGINT which is 8 bytes. The maximum value you can store in a BIGINT is 18446744073709551615 if unsigned. At your rate of insertion of approximately 100 thousand rows per day, it will take you 459445680541 years to overflow.

In short, you have nothing to worry about.

In fact, you should consider changing this field to INT which is 4 bytes, with maximum value of 4294967295, which you can use for approximately 110 years before overflowing.

MySQL – Unable to Convert Primary Key to Auto Increment

Your post intrigued me. There is a "workaround" here, but I was curious, so I performed the following tests. You might like to try what worked for me below before trying the workaround. If there are FOREIGN KEYs, and potential conflicts (duplicates and/or KEY violations), then this won't work. It may be the reason for your error.

I created a table (bill) as follows.

mysql> CREATE TABLE bill(fred INT, paul VARCHAR(10));
Query OK, 0 rows affected (0.31 sec)

Then inserted some sample values.

mysql> INSERT INTO bill VALUES(1, 'adsfd');
    Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO bill VALUES(2, 'adsfd');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO bill VALUES(3, 'xxxx');
Query OK, 1 row affected (0.05 sec)

I then performed a SHOW CREATE TABLE My_Table\G (the \G gives clear output that can be cut and pasted).

mysql> SHOW CREATE TABLE bill\G
*************************** 1. row ***************************
       Table: bill
Create Table: CREATE TABLE `bill` (
  `fred` int(11) DEFAULT NULL,
  `paul` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Just to see the results of my INSERTs

mysql> SELECT * FROM bill;
+------+-------+
| fred | paul  |
+------+-------+
|    1 | adsfd |
|    2 | adsfd |
|    3 | xxxx  |
+------+-------+
3 rows in set (0.00 sec)
mysql>

Now, issue your ALTER TABLE statement.

ALTER TABLE bill MODIFY COLUMN fred INT AUTO_INCREMENT NOT NULL PRIMARY KEY;

Recheck the definition of your table.

mysql> SHOW CREATE TABLE bill\G
*************************** 1. row ***************************
       Table: bill
Create Table: CREATE TABLE `bill` (
  `fred` int(11) NOT NULL AUTO_INCREMENT,
  `paul` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`fred`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

And the field fred is indeed now a PRIMARY KEY and also an AUTO_INCREMENT.

And to check that it's working.

mysql> INSERT INTO bill (paul) VALUES('yyyy');
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM bill;
+------+-------+
| fred | paul  |
+------+-------+
|    1 | adsfd |
|    2 | adsfd |
|    3 | xxxx  |
|    4 | yyyy  |
+------+-------+
4 rows in set (0.00 sec)

So, it's also AUTO_INCREMENTing correctly. I'm not sure why your original statement gave the strange ALTER TABLE causes auto_increment resequencing error - maybe a bug of some sort? While performing these tests, I made a number of small errors - MySQL appears to be very fussy about the exact order of steps and the particular syntax of the ALTER TABLE statement.

Best Answer

Related Solutions

Mysql – Primary key auto increment best practices

MySQL – Unable to Convert Primary Key to Auto Increment

Related Question