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.aiSELECT 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.aiSELECT 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!
master
branch:
On - Enable
pg_stat_statements
in your local DB:
gdk psql
gitlabhq_development=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
- Create a new backup file. This will create a new backup in
tmp/backups
folder
bundle exec rake gitlab:backup:create
- Restore the backup:
bundle exec rake gitlab:backup:restore
- 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:
- Switch to this branch
git checkout -b 2469-cannot-restore-to-helm-installed-version origin/2469-cannot-restore-to-helm-installed-version
- Restore the backup:
bundle exec rake gitlab:backup:restore
- 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.
-
I have evaluated the MR acceptance checklist for this MR.
Related to gitlab-org/charts/gitlab#2469 (closed)
Related to #351071
Edited by Leonardo da Rosa