Unexpected statements in the backup from a Essential-x Database is causing local `pg_restore` to fail

Issue

I'm working on coping my Essential-x database over to my local Postgres environment, but when I restore the backup using pg_restore, the process failures due to the following trigger statements:

pg_restore: from TOC entry 4326; 3466 691225 EVENT TRIGGER extension_before_drop heroku_admin
pg_restore: error: could not execute query: ERROR:  schema "_heroku" does not exist
Command was: CREATE EVENT TRIGGER extension_before_drop ON ddl_command_start
   EXECUTE FUNCTION _heroku.extension_before_drop();


pg_restore: creating EVENT TRIGGER "log_create_ext"
pg_restore: from TOC entry 4327; 3466 691226 EVENT TRIGGER log_create_ext heroku_admin
pg_restore: error: could not execute query: ERROR:  schema "_heroku" does not exist
Command was: CREATE EVENT TRIGGER log_create_ext ON ddl_command_end
   EXECUTE FUNCTION _heroku.create_ext();


pg_restore: creating EVENT TRIGGER "log_drop_ext"
pg_restore: from TOC entry 4328; 3466 691227 EVENT TRIGGER log_drop_ext heroku_admin
pg_restore: error: could not execute query: ERROR:  schema "_heroku" does not exist
Command was: CREATE EVENT TRIGGER log_drop_ext ON sql_drop
   EXECUTE FUNCTION _heroku.drop_ext();


pg_restore: creating EVENT TRIGGER "validate_extension"
pg_restore: from TOC entry 4329; 3466 691228 EVENT TRIGGER validate_extension heroku_admin
pg_restore: error: could not execute query: ERROR:  schema "_heroku" does not exist
Command was: CREATE EVENT TRIGGER validate_extension ON ddl_command_end
   EXECUTE FUNCTION _heroku.validate_extension();


pg_restore: warning: errors ignored on restore: 4

Resolution

The _heroku schema is an internal schema that'll exist in our Essential databases that are built on Amazon Aurora and it shouldn't impact with the actual data restore.

However, there is a workaround which is essentially using the pg_restore --use-list argument to filter out event triggers

The first step is to get a list from the dump file via pg_restore -l database.dump > unfiltered.list

Which would like something like the following:

2; 3079 31308 EXTENSION - pg_stat_statements 
4304; 0 0 COMMENT - EXTENSION pg_stat_statements 
4149; 3466 31303 EVENT TRIGGER - extension_before_drop heroku_admin
4150; 3466 31304 EVENT TRIGGER - log_create_ext heroku_admin
4151; 3466 31305 EVENT TRIGGER - log_drop_ext heroku_admin
4152; 3466 31306 EVENT TRIGGER - validate_extension heroku_admin

Then you would comment out the event triggers with a semicolon:

sed -e '/EVENT TRIGGER/ s/./;&/' unfiltered.list > filtered.list

Which the results would look something like the following:

2; 3079 31308 EXTENSION - pg_stat_statements 
4304; 0 0 COMMENT - EXTENSION pg_stat_statements 
;4149; 3466 31303 EVENT TRIGGER - extension_before_drop heroku_admin
;4150; 3466 31304 EVENT TRIGGER - log_create_ext heroku_admin
;4151; 3466 31305 EVENT TRIGGER - log_drop_ext heroku_admin
;4152; 3466 31306 EVENT TRIGGER - validate_extension heroku_admin

Then you can run the restore using the --use-list argument:

pg_restore -d foo --use-list filtered.list --no-owner database.dump

Which results with the following where there are no errors when restoring in the local database:

pg_restore --verbose -d foo --use-list filtered.list --no-owner database.dump
pg_restore: connecting to database for restore
pg_restore: creating EXTENSION "pg_stat_statements"
pg_restore: creating COMMENT "EXTENSION pg_stat_statements"