Symfony Doctrine migration breaks primary key sequence in PostgreSQL

Question

I have been migrating from MariaDB to PostgreSQL using pgloader

If I execute pgloader to migrate database and load my program everything works fine; I can insert a new Post as an example

then I try to sync doctrine with the new database using

docker exec -ti app php bin/console doctrine:migrations:sync-metadata-storage
docker exec -ti app php bin/console make:migration
docker exec -ti app php bin/console doctrine:migrations:migrate 

After migration, If I try inserting a new Post I get the error:

SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "idx_16660_primary"
DETAIL: Key (id)=(1) already exists.

I'm not sure what could be causing this.

Looking into the migration file, I can see this:

        $this->addSql('ALTER TABLE post ALTER id TYPE INT');
        $this->addSql('ALTER TABLE post ALTER id DROP DEFAULT');
        $this->addSql('ALTER TABLE post ALTER id ADD GENERATED BY DEFAULT AS IDENTITY');

If I remove the last two lines from the migration file ALTER id DROP DEFAULT and ALTER id ADD GENERATED BY DEFAULT AS IDENTITY then it works, but my database is no in sync anymore

here is a typical id declaration on my entities

    /**
     * @var int
     */
    #[ORM\Column(name: 'id', type: 'integer', nullable: false)]
    #[Id]
    #[ORM\GeneratedValue(strategy: 'IDENTITY')]
    private $id;

Answer 1

The statement "ALTER TABLE post ALTER id ADD GENERATED BY DEFAULT AS IDENTITY" is setting the initial value of as its default of 1. You apparently need to reset this value after migration. Accomplish this with:

ALTER TABLE <table name> 
   ALTER COLUMN <column_name> RESTART WITH <value>;

If you have several tables or anticipate needing to do so multiple times then create a procedure to do so (see demo for example of each).

create or replace procedure post_migration_resync(table_name_in text
                                                 , id_column_in text
                                                 )
 language plpgsql
as $$
declare 
    k_select_max_id constant text = 
        'select max(%I) from %I'; 
       
    k_alter_key_seq constant text =
        'alter table %I alter column %I restart with %s'; 
       
    l_current_max integer;   
    l_statement   text; 
   
begin 
    l_statement = format(k_select_max_id,id_column_in,table_name_in); 
    raise notice '%',E'GET CURRENT MAX:\n ' || l_statement; 
    execute l_statement into l_current_max; 
   
    l_current_max = l_current_max + 10;  -- set for new sequence
    l_statement = format(k_alter_key_seq,table_name_in,id_column_in,l_current_max);
    raise notice '%',E'SET NEW SEQUENCE:\n ' || l_statement; 
    execute l_statement;
   
    commit; 
     
end;
$$;