Postgresql – postgres: Upgraded RDS Postgres from 9.4 – 9.6, id fields went from SERIAL to INT

postgresql

So, on my RDS postgres, that is currently running as a DMS CDC from our previous DB, our ID fields lost the serial type, and it got moved to INT without a NEXTVAL. So, using the example of our table public.pageviews, I tried

ALTER TABLE public.pageviews ALTER COLUMN id TYPE SERIAL;

and it is giving me that SERIAL is not a valid data type.

When I run 

ALTER TABLE public.pageviews ALTER COLUMN id SET DEFAULT nextval('pageviews_id_seq');

I am getting the following:

ERROR: null value in column "c_key" violates not-null constraint
DETAIL: Failing row contains (null, 2018-01-03 14:17:28.952708,
master, 1528336, ALTER TABLE, 0, , public, ALTER TABLE
public.pageviews ALTER COLUMN id SET DEFAULT nextval…). CONTEXT:
SQL statement "insert into public.awsdms_ddl_audit
values
(
default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
)" PL/pgSQL function awsdms_intercept_ddl() line 6 at SQL statement

********** Error **********

ERROR: null value in column "c_key" violates not-null constraint SQL
state: 23502 Detail: Failing row contains (null, 2018-01-03
14:17:28.952708, master, 1528336, ALTER TABLE, 0, , public, ALTER
TABLE public.pageviews ALTER COLUMN id SET DEFAULT nextval…).
Context: SQL statement "insert into public.awsdms_ddl_audit
values
(
default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
)" PL/pgSQL function awsdms_intercept_ddl() line 6 at SQL statement

On that, I am not a PG DBA by trade (MSSQL that got lucked into this job) so I am not familiar enough with how to really make my way around things, on pgadmin, I have tried to set the default value for ID to a nextval, but I am not getting anywhere with that.

I'm on windows and have really no experience with psql commands (learning them, but the curve is steep)

Best Answer

While it appears that DMS supports migrating the SERIAL datatype, DMS doesn't support migrating default values like your SET DEFAULT nextval('pageviews_id_seq')

If you are running ongoing replication, then all the data in the read replica target is created in the source database, that is where the nextval from that sequence will be applied.

It kind of makes sense, because DMS is trying to explicity insert a value into that id column, and you shouldn't explicitly set a column that is supposed to have its value generated by a sequence.

If you're using DMS to migrate a database with the intention to switch off the old one then you'll have to

  • switch off DMS, then on the new database:
  • create the sequence object pageviews_id_seq, setting the sequence to start at a value higher than the max id in the table, so it's nextval doesn't collide with an existing id
  • alter the column to apply the SET DEFAULT nextval('pageviews_id_seq')

In your specific case, there is a trigger on the source database (see link below to docs) which looks like this

CREATE OR REPLACE FUNCTION <objects_schema>.awsdms_intercept_ddl()
  RETURNS event_trigger
LANGUAGE plpgsql
SECURITY DEFINER
  AS $$
  declare _qry text;
BEGIN
  if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE') then
         SELECT current_query() into _qry;
         insert into <objects_schema>.awsdms_ddl_audit
         values
         (
         default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
         );
         delete from <objects_schema>.awsdms_ddl_audit;
end if;
END;
$$;

It is trying to insert the default value for the first column in the table awsdms_ddl_audit.

According to the docs the table should look like this

create table <objects_schema>.awsdms_ddl_audit
(
  c_key    bigserial primary key,
  c_time   timestamp,    -- Informational
  c_user   varchar(64),  -- Informational: current_user
  c_txn    varchar(16),  -- Informational: current transaction
  c_tag    varchar(24),  -- Either 'CREATE TABLE' or 'ALTER TABLE' or 'DROP TABLE'
  c_oid    integer,      -- For future use - TG_OBJECTID
  c_name   varchar(64),  -- For future use - TG_OBJECTNAME
  c_schema varchar(64),  -- For future use - TG_SCHEMANAME. For now - holds current_schema
  c_ddlqry  text         -- The DDL query associated with the current DDL event
)
  • ckey is supposed to be a bigserial, so this should be inserting an incremented bigint automatically for you ( similar to IDENTITY on MSSQL ). The error says that it is failing when trying to insert a NULL, so can you check the datatype for that c_key column, I suspect in your DB the datatype isn't right.

!! Note that in the docs it says you need to do these thing in order 1. create the aws_ddl_audit table 2. create the trigger function 3. attatch the trigger to the ddl_command_end then execute it

The trigger monitors all create , update and drop table statements, which would include creating the aws_ddl_audit table if you create the table after the trigger.

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.Limitations

Related Question