Database backup: pg_schema option leads to inconsistent backup

The Settings.backup.pg_schema option can be set to target a specific Postgres schema only for backups. There are two options here:

1. pg_schema = nil - no specific schema target, run pg_dump --clean $db
2. pg_schema = some_schema - only dump some_schema with pg_dump --clean -n some_schema

I believe there are some issues with this:

1. pg_schema is always forced to nil in https://gitlab.com/gitlab-org/gitlab/blob/master/config/initializers/1_settings.rb#L650 - I suppose this should read Settings.backup['pg_schema'] ||= nil instead, so it defaults to nil if not otherwise set
2. With (1) fixed and pg_schema set to public, postgres extensions are not part of the backup (see below).
3. The restore rake task uses psql but without ON_ERROR_STOP=1 it cannot recognize restore errors (psql only reports non-zero exit code on script error if ON_ERROR_STOP=1 is set).

For (2): The SQL dump does not contain any trace of extensions enabled (neither DROP EXTENSION nor CREATE EXTENSION). In the case where the backup is restored into an existing database, this is not a problem (extensions already exist anyways). In the case where the backup is restored into an empty database, the extensions are missing and objects dependent on an extension fail to create.

In the case I have seen, this is about the trigram extension. Without it, we fail to create trigram indexes like so:

ERROR:  operator class "public.gin_trgm_ops" does not exist for access method "gin"

With issue (3) in mind, I suspect we don't notice this problem when executing the restoration through Rake.

Steps to reproduce

1. Apply the patch attached: 0001-Let-pg_schema-default-to-nil-if-not-otherwise-set.patch
2. Change backup.pg_schema in config/gitlab.yml to public
3. Run rake gitlab:backup:db:create
4. Create an empty database new
5. Restore into empty database: zcat tmp/backups/db/database.sql.gz | gdk psql -d new
6. Inspect the output and look for ERROR messages unrelated to dropping objects in the beginning of the script (that is expected)

What to expect instead

The database.sql.gz dump should contain DROP/CREATE EXTENSION statements for all postgres extensions present in the database (installed into the public schema or the one we target with pg_schema setting).

Impact

Pretty much all of this is not a problem, because pg_schema has been forced to nil all the times anyways (I believe). This means, we target the whole database anyways and this does not lead to the outlined issues. As soon as we fix the bug with pg_schema setting, we need to make sure extensions are being included properly.