migrate
migrate copied to clipboard
Use DELETE FROM instead of TRUNCATE for pgx
Problem
In PostgreSQL, TRUNCATE statements cannot run during a db backup.
Deployment would be blocked by db_dump
if there would be migration to run.
db_dump
requires ACCESS SHARE
LOCK
Reference: https://www.postgresql.org/docs/current/app-pgdump.html
And will conflict with TRUNCATE
which requires ACCESS EXCLUSIVE
LOCK
Solution
Use DELETE FROM
instead of TRUNCATE
for clearing the table since there is only one row of record
Reference
Postgres
https://www.postgresql.org/docs/current/sql-truncate.html
Article about this scenario
Edits
Edit 28-Feb-2024
- Added support for pgx/v5 driver as well
I was facing the same issue too. Yes, the pg_dump operation is blocking the truncate, can refer to this article
Can we change from TRUNCATE
to DELETE
like @HeroSizy proposed in his PR?
coverage: 59.051%. remained the same when pulling e29809ab22a200fb396f722b1b5f1c2af9506135 on HeroSizy:pg-db-dump-truncate-fix into 0695426fbff745d89c554bbe260d0e2c6471ba9d on golang-migrate:master.
@dhui Any updates on getting this merged? Appreciate for your time.
@HeroSizy @shawnzxx Sorry for the extremely slow response. I'm onboard with the change. A similar change was already made to MySQL but for different reasons. Could you also make the change to the postgres driver?
Also, based on the article you linked, be aware that if there any migrations (e.g. DDL statements) that need to be applied, they'd still be blocked by the pg_dump run. SetVersion()
should only be called if there are migrations that need to be applied.
Hi @dhui , apologise for the slow response, I have just updated the isolation level. And also included pgx/v5