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"