5

I recently migrated a self hosted Postgres database to AWS RDS using the AWS Migration Service.
Postgres Version: 10.6

I have noticed that all of my primary keys are no longer set to "sequence", and when I attempt to manually add a sequence it starts at 1 instead of continuing the count that is already set.

I use Rails with the database, so my sql skills are pretty low. I can generally find my way around inserts and updates, but this is not a realm I have much experience in.

My question has 2 parts:

  1. How do I fix a single table? I want to understand and test what I am doing before moving on.
  2. Is there a way I can apply this fix to every table I have without needing to manually modify each table?
Jon Heckman
  • 420
  • 2
  • 7
  • 18
  • 1
    Are the sequences still there? Are the sequences still attached to the sequences, i.e. do they have a `default nextval('...')` option? –  Apr 11 '19 at 13:01
  • 1
    They are not, checking under "information_schema" >> "sequences", that is empty Compared to the old database which lists everything I had before – Jon Heckman Apr 11 '19 at 13:08
  • Maybe the migration service changed the `serial` columns to `identity` columns - their sequences are not listed in `information_schema`. Can you check `pg_sequences` instead? Or check for one column by running: `select pg_get_serial_sequence('some_table', 'pk_column_name');` –  Apr 11 '19 at 13:12
  • Just checked `pg_sequence` and `pg_sequences` and its empty. – Jon Heckman Apr 11 '19 at 13:15
  • Did not see the edit in time, when I ran `select pg_get_serial_sequence('some_table', 'id');` with a table I have, I get `null`... If I run it against my old database I get "public.TABLEENAME_id_seq" – Jon Heckman Apr 11 '19 at 13:17
  • The clearly that migration tool failed to migrate those defaults. –  Apr 11 '19 at 13:29
  • Yeah that is what it looks like. Thank you though! This at least gives me more information to go back to them with and I learned something new. – Jon Heckman Apr 11 '19 at 13:31

4 Answers4

6

After @a_horse_with_no_name pointed me in the right direction and chatting with AWS I am able to answer my own question, at least if you are using AWS Database Migration Service (DMS).

The problem is, DMS only focuses on the data itself and not really the schema (which to me seems like a major oversight, especially if your using the same database technology but that is another issue). So the schema itself is not migrated. The documentation does not really make this clear.

To fix this issue:

  1. Stop (if it still exists) the existing AWS DMS migration
  2. Drop the existing migrated database, and create a new empty schema to use
  3. Follow the steps here https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html to install and setup the Amazon Schema Conversation Tool (SCT)
  4. Once you are connected to both databases, follow the steps here https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Converting.html to "convert" your schema (I did the entire "public" schema for this database to ensure everything is covered
  5. Create or modify your AWS DMS Migration, ensuring Target Table Preparation Mode = "TRUNCATE" and disable foreign keys on the target database. If modifying, make sure when asked you "RESTART" not resume

What I have not yet tested is how to handle the fact that I am migrating a live database. So the sequences may be out of date on the target database when the migration is done. I believe I can just later go into SCT and only migrate the sequences but I have not tested this yet.

Jon Heckman
  • 420
  • 2
  • 7
  • 18
2

Unless you missed something and the migration could be processed once more (with different parameters? I have never used AWS Migration Service but I presume it should preserve the serial-iness of your columns...) you'll need to re-create the sequences too.


I encountered a similar situation a year or so ago, and wrote this answer on SO.

Here's the gist of it:

CREATE SEQUENCE foo_a_seq OWNED BY foo.a;
SELECT setval('foo_a_seq', coalesce(max(a), 0)) FROM foo;
ALTER TABLE foo ALTER COLUMN a SET DEFAULT nextval('foo_a_seq'); 

This would create a sequence foo_a_seq, with its nextval being one higher than the max value in foo.a (or 1 if there is no foo record).

I also went ahead and set up a Function to quickly apply to all the tables/columns in need:

CREATE OR REPLACE FUNCTION make_into_serial(table_name TEXT, column_name TEXT) RETURNS INTEGER AS $$
DECLARE
    start_with INTEGER;
    sequence_name TEXT;
BEGIN
    sequence_name := table_name || '_' || column_name || '_seq';
    EXECUTE 'SELECT coalesce(max(' || column_name || '), 0) + 1 FROM ' || table_name
            INTO start_with;
    EXECUTE 'CREATE SEQUENCE ' || sequence_name ||
            ' START WITH ' || start_with ||
            ' OWNED BY ' || table_name || '.' || column_name;
    EXECUTE 'ALTER TABLE ' || table_name || ' ALTER COLUMN ' || column_name ||
            ' SET DEFAULT nextVal(''' || sequence_name || ''')';
    RETURN start_with;
END;
$$ LANGUAGE plpgsql VOLATILE;

Use it like so:

SELECT make_into_serial('foo', 'a');
ccjmne
  • 9,333
  • 3
  • 47
  • 62
  • 1
    Disclaimer: I think there is a better way than monkey-patching your migrated DB, and I think you're in better hands with @a_horse_with_no_name, but in case he can't figure out what happened exactly, I'll just leave that there. If that's not useful, just let me know and I'll remove this answer! – ccjmne Apr 11 '19 at 13:20
  • 1
    I don't know that Amazon tool, so I can't help with that. But you can shorten the code by running a single statement: `alter table foo alter pk_column add generated always as identity (start with xxx);` –  Apr 11 '19 at 13:40
  • @a_horse_with_no_name Thank you! I just started reading about the Identity columns that you mentioned in the comments to the question. I'm gonna have to brush up my skills and upgrade my databases :) – ccjmne Apr 11 '19 at 13:43
1

For any folks using macOS and aren't able to use the SchemaConversionTool suggested in one of the answers above, follow the steps below:

  1. Empty the schema in the target database in order to start fresh

    DROP SCHEMA public;

  2. Generate a SQL script which contains all the DDL statements from the source database's schema. If you use dbeaver then right click on schema name and click on Generate SQL generate sql

  3. Run the commands in the generated SQL script from step 2 in the target database so that the schemas are now in sync with each other

  4. Go to AWS DMS and start the task with target table preparation mode set to truncate

0

Sequence, Index, and Constraint are not migrated and it is mentioned in the official docs on AWS.

You can use this source. This will help you to migrate Sequence, Index, and Constraint at once.

sinwoobang
  • 138
  • 10