pg_restore drops extension plpgsql

Question

We face a situation where pg_restore drops extension plpgsql and then recreates it. This is done by a CI job we have but i could not reproduce it. These are the commands we use:

• pg_dump -d $DATABASE_URL -j 8 -Z 0 -Fd -f ${backupDir}
• psql -d $DATABASE_URL -c "DROP SCHEMA IF EXISTS public CASCADE;
• psql $DATABASE_URL -c "CREATE SCHEMA public; GRANT ALL ON SCHEMA public TO public;
• pg_restore --verbose --clean --no-owner -d $DATABASE_URL -j 8 --format=d ${backupDir}

And these are the logs:

Answer 1

The aws_s3 extension seems to be buggy: it lacks the dependency on the plpgsql extension. You should report that as a bug to Amazon. As a workaround, don't use the --clean option, but restore to a new, empty database.