Postgres DB migration error due to replication

We got a weird error today when running the latest migration on a production Postgres DB.

cannot update table "schema_info" because it does not have a replica identity and publishes updates

The table schema_info is created when we run ruby Sequel migrations. This table stores just the version number of the latest migration applied under a column called version.

We had set up a replica database some time ago and it looked like the issue was because of this.

Initially, the thought was to stop the replica and then retry the migration. But that didn’t work.

Finally, a search revealed this insight –

It turns out that PostgreSQL does not like tables, even temp tables, that lack a primary key where replication is involved.

Per this Stackoverflow answer, we had to run from the psql prompt –

ALTER TABLE <table_name> REPLICA IDENTITY FULL;