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:
-
pg_schema = nil
- no specific schema target, runpg_dump --clean $db
-
pg_schema = some_schema
- only dumpsome_schema
withpg_dump --clean -n some_schema
I believe there are some issues with this:
-
pg_schema
is always forced tonil
in https://gitlab.com/gitlab-org/gitlab/blob/master/config/initializers/1_settings.rb#L650 - I suppose this should readSettings.backup['pg_schema'] ||= nil
instead, so it defaults tonil
if not otherwise set - With (1) fixed and
pg_schema
set topublic
, postgres extensions are not part of the backup (see below). - The restore rake task uses
psql
but withoutON_ERROR_STOP=1
it cannot recognize restore errors (psql
only reports non-zero exit code on script error ifON_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
- Apply the patch attached: 0001-Let-pg_schema-default-to-nil-if-not-otherwise-set.patch
- Change
backup.pg_schema
inconfig/gitlab.yml
topublic
- Run
rake gitlab:backup:db:create
- Create an empty database
new
- Restore into empty database:
zcat tmp/backups/db/database.sql.gz | gdk psql -d new
- 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.