MySQL to Postgres data migration

Question

I'm in the process of migrating a Ruby on Rails application from MySQL to Postgres. Is there a recommended way to keep the deleted data, like all the deleted records (their IDs at least) from MySQL?

In testing a dump-and-restore didn't seem to keep deleted records.

Also, in the event that I manage to keep the records where they are, what'll happen with the blank ones in Postgres? Will they be skipped over or used?

Example

Say I have a user with an ID of 101 and I've deleted users up to 100. I need 101 to stay at 101.

If data is deleted, it doesn't exist in the DB anymore, so you can't keep it. This doesn't make sense. Are you talking about AUTO_INCREMENT keys and PostgreSQL SERIAL columns? — Craig Ringer
– Craig Ringer, Commented Sep 19, 2014 at 1:17

Craig Ringer · Accepted Answer · 2014-09-19 01:25:07Z

2

So you don't want to reassign the IDs assigned to records where you generated keys.

That should be the default in any sane migration. When you copy the data rows over - say, exporting from MySQL with SELECT ... INTO OUTFILE and importing into PostgreSQL with COPY tablename FROM 'filename.csv' WITH (FORMAT CSV), the IDs won't change.

All you'll need to do is to set the next ID to be generated in the sequence on the PostgreSQL table afterwards. So, say you have the table:

CREATE TABLE users
(
    id serial primary key,
    name text not null,
    ...
);

and you've just copied a user with id = 101 into it.

You'll now just assign a new value to the key generation sequence for the table, e.g.:

SELECT setval('users_id_seq', (SELECT max(id) FROM users)+1);

To learn more about sequences and key generation in PostgreSQL, see SERIAL in the numeric types documentation, the documentation for CREATE SEQUENCE, the docs for setval, etc. The default name for a key generation sequence is tablename_columnname_seq.

answered Sep 19, 2014 at 1:25

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

2 Comments

t56k Over a year ago

Oh good! I started to panic when I thought this might be a problem. Will I have to assign new values to the key generation sequence for every table individually?

Craig Ringer Over a year ago

@emm Yep. I'd script it personally. The above assumes you're migrating the data table-by-table as CSV. If you're using some kind of Rails-level data migration then everything might be different.

Collectives™ on Stack Overflow

MySQL to Postgres data migration

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related