Hey folks, so we have a use case (with Hasura, I’ll elaborate after explaining the problem) where we migrate a database from service X to Neon by:
1.) First exporting/restoring the schema
2.) Then exporting/restoring the data
In order to do #2, triggers have to be disabled (otherwise inserting data into some tables triggers unwanted data changes elsewhere). So something like:
pg_restore --dbname='postgres://username:firstname.lastname@example.org:5432/db' --data-only --no-privileges --no-owner --exit-on-error --disable-triggers --format=c "./data.sql"
This fails with an error:
pg_restore: error: could not execute query: ERROR: permission denied: "RI_ConstraintTrigger_a_84818" is a system trigger
Stackoverflow tells me I need to be a super user to avoid this problem. Is that possible?
The use case is migrating a database being used by Hasura. Hasura, specifically Hasura Events, create a situation which requires this separate schema/database migration style (as opposed to a full pg_dump then pg_restore).