--disable-triggers in pg_restore fails due to not being super user?

Support
1

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:password@my-neon-stuff.cloud.neon.tech: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).

Thanks much!

2 Likes
2

You can split the schema restoration into two parts: first restore the tables, then load the data, and finally restore the triggers . pg_restore provides a handy option for this:

  1. pg_restore --section=pre-data ...
  2. pg_restore --section=data ...
  3. pg_restore --section=post-data ...

I’m not sure why a full pg_restore is not possible with Hasura Events, but maybe the above is possible? Another workaround would be to DROP the triggers, and re-CREATE them afterwards.

(We don’t currently provide superuser-access, although we plan to allow that in the future.)

1 Like