Skip to content

Skip dropping pg_stat_statements view when restoring backups

What does this MR do and why?

Restoring backups with pg_stat_statements extension enabled causes the process to fail.

DROP VIEW IF EXISTS "pg_stat_statements" CASCADE statement was causing an exception when gitlab::backup::restore was invoked:

Unpacking backup
rake aborted!
ActiveRecord::StatementInvalid: PG::DependentObjectsStillExist: ERROR:  cannot drop view pg_stat_statements because extension pg_stat_statements requires it
HINT:  You can drop extension pg_stat_statements instead.

Describe in detail what your merge request does and why.

Skips dropping VIEWS that are dependency for extensions when restoring db backups

Query 1 postgres.ai
SELECT pg_depend.*
FROM pg_depend
INNER JOIN pg_class ON pg_class.oid = pg_depend.objid
WHERE (pg_class.relkind IN ('r', 'v', 'm', 'p', 'f'))
AND (refclassid = 'pg_extension'::pg_catalog.regclass);
Time: 3.661 ms
  - planning: 1.339 ms
  - execution: 2.322 ms
    - I/O read: 0.891 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1217 (~9.50 MiB) from the buffer pool
  - reads: 16 (~128.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 13 (~104.00 KiB)
  - writes: 0
Query 2 postgres.ai
SELECT pg_depend.*
FROM pg_depend
INNER JOIN pg_class ON pg_class.oid = pg_depend.objid
WHERE (pg_class.relkind IN ('v', 'm'))
AND (refclassid = 'pg_extension'::pg_catalog.regclass);
Time: 48.596 ms
  - planning: 1.383 ms
  - execution: 47.213 ms
    - I/O read: 45.197 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1197 (~9.40 MiB) from the buffer pool
  - reads: 16 (~128.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 13 (~104.00 KiB)
  - writes: 0

How to set up and validate locally

Recreating the issue:

Please, backup your data!

On master branch:

  1. Enable pg_stat_statements in your local DB:
gdk psql

gitlabhq_development=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
  1. Create a new backup file. This will create a new backup in tmp/backups folder
bundle exec rake gitlab:backup:create
  1. Restore the backup:
bundle exec rake gitlab:backup:restore
  1. Restore should raise an error:
2023-04-26 20:32:40 UTC -- Cleaning the database ...
2023-04-26 20:32:40 UTC -- Deleting tar staging files ...
2023-04-26 20:32:40 UTC -- Cleaning up /Users/leonardodarosa/gdk/gitlab/tmp/backups/backup_information.yml
2023-04-26 20:32:40 UTC -- Cleaning up /Users/leonardodarosa/gdk/gitlab/tmp/backups/db
2023-04-26 20:32:40 UTC -- Cleaning up /Users/leonardodarosa/gdk/gitlab/tmp/backups/repositories
2023-04-26 20:32:40 UTC -- Cleaning up /Users/leonardodarosa/gdk/gitlab/tmp/backups/uploads.tar.gz
2023-04-26 20:32:40 UTC -- Cleaning up /Users/leonardodarosa/gdk/gitlab/tmp/backups/builds.tar.gz
2023-04-26 20:32:40 UTC -- Cleaning up /Users/leonardodarosa/gdk/gitlab/tmp/backups/artifacts.tar.gz
2023-04-26 20:32:40 UTC -- Cleaning up /Users/leonardodarosa/gdk/gitlab/tmp/backups/pages.tar.gz
2023-04-26 20:32:40 UTC -- Cleaning up /Users/leonardodarosa/gdk/gitlab/tmp/backups/lfs.tar.gz
2023-04-26 20:32:40 UTC -- Cleaning up /Users/leonardodarosa/gdk/gitlab/tmp/backups/terraform_state.tar.gz
2023-04-26 20:32:40 UTC -- Cleaning up /Users/leonardodarosa/gdk/gitlab/tmp/backups/packages.tar.gz
2023-04-26 20:32:40 UTC -- Deleting tar staging files ... done
2023-04-26 20:32:40 UTC -- Deleting backups/tmp ...
2023-04-26 20:32:40 UTC -- Deleting backups/tmp ... done
2023-04-26 17:32:40 -0300 -- Deleting backup and restore lock file

rake aborted!
ActiveRecord::StatementInvalid: PG::DependentObjectsStillExist: ERROR:  cannot drop view pg_stat_statements because extension pg_stat_statements requires it
HINT:  You can drop extension pg_stat_statements instead.

Validating the fix:

  1. Switch to this branch
git checkout -b 2469-cannot-restore-to-helm-installed-version origin/2469-cannot-restore-to-helm-installed-version
  1. Restore the backup:
bundle exec rake gitlab:backup:restore
  1. Backup should be restored without any errors.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to gitlab-org/charts/gitlab#2469 (closed)

Related to #351071

Edited by Leonardo da Rosa

Merge request reports

Loading