PostgresSQL 로 데이터 마이그레이션 후 Sequence 중복 오류가 뜰때

PostgreSQL로 Data를 pg_dump를 사용하여 Dump 하고 그 결과를 Restore 할 때 Sequence 가 제대로 마이그레이션이 되지 않는 경우가 있다. 

보통 Bigserial이나 Serial과 같은 DataType을 쓰는 경우에 이런 문제가 발생했고, 새로운 Data를 Insert 할 때 Sequence 가 이미 있어 중복 오류가 발생하여 이 상황을 확인할 수 있었다. 

duplicate key value violates unique constraint "vouchers_pk"; 
nested exception is io.r2dbc.postgresql.ExceptionFactory$PostgresqlDataIntegrityViolationException: 
[23505] duplicate key value violates unique constraint "vouchers_pk"*

예를 들어 테이블 vouchers 가 다음과 같은 pk를 가지고 있을 때 이런 현상이 발생했다. 

create table schema.vouchers
(
    id                bigserial
        constraint vouchers_pk
            primary key
);

PostgreSQL 은 pg_dump를 사용하여 데이터를 dump 할 경우 자동증가값이 아닌 그 값자체로 컬럼에 데이터를 Set 하기 때문에 이와 같은 현상이 발생한다. 

the pg_dump sets the iID column to integer rather than serial, which disabled the auto-incrementation.

이 부분은 MySQL과는 다른 상황이다. 

MySQL 은 Sequence 가 아닌 Table의 컬럼 옵션에 auto_increment라는 설정이 있어서 이런 차이를 보이는 것 같다. 

아무튼 이 상황이 꽤나 번거로운 상황이라 다음과 급하게 시퀀스를 재설정하는 방법을 찾았었다. 

-- 새로운 id값 가져오기 
select max(id) + 1 from schema.vouchers;

-- 위에서 가져온 값이 9라면 
alter sequence "vouchers_id_seq" restart with 9

이렇게 해주면 되는데, 문제는 with 뒤의 9를 쿼리로 가져올 수 없다는 것이었다. 

mysql처럼 변수를 설정하는 방법으로 방향을 잡고, PostgreSQL에서 해당 방법을 찾았으나, alter sequence에서는 도저히 쓸 수가 없었다. 

하지만, 결국 다음과 같은 방법으로 찾았다. 

select setval('vouchers_id_seq', (select max(id)+1 from schema.vouchers, true);

잘 동작해서 우왓 했는데, vouchers_id_seq이라는 시퀀스 정보를 알아내는 것이 규칙성이 있기는 하지만 걸렸는데, StackOverflow에서 다음과 같은 쿼리를 찾았다. 

select setval(pg_get_serial_sequence('schema.vouchers', 'id'), max(id) + 1) from schema.vouchers;

테이블 정보와 column을 알면 동일하게 처리할 수 있다. 

출처 - https://stackoverflow.com/a/70103242

pg_dump setting of sequences