Add --if-exists to pg_dump command-line in backup creation
Because we use the --clean argument to pg_dump
, the database backup
SQL file has many DROP commands that precede the section to restore the
tables and data. However, on instances with an empty database, these
DROP statements generate a lot of noise and make it difficult to filter
true error messages from noise.
In preparation of capturing error messages from stderr, we add this to the backup commands to make it easier to flag real errors.
Reference: https://www.postgresql.org/docs/11/app-pgdump.html
Before
--
-- PostgreSQL database dump
--
-- Dumped from database version 11.7
-- Dumped by pg_dump version 11.7
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
ALTER TABLE public.product_analytics_events_experimental DROP CONSTRAINT product_analytics_events_experimental_project_id_fkey;
ALTER TABLE ONLY public.u2f_registrations DROP CONSTRAINT fk_u2f_registrations_user_id;
ALTER TABLE ONLY public.timelogs DROP CONSTRAINT fk_timelogs_merge_requests_merge_request_id;
ALTER TABLE ONLY public.timelogs DROP CONSTRAINT fk_timelogs_issues_issue_id;
ALTER TABLE ONLY public.ci_builds_metadata DROP CONSTRAINT fk_rails_ffcf702a02;
<snip>
After
--
-- PostgreSQL database dump
--
-- Dumped from database version 11.7
-- Dumped by pg_dump version 11.7
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
ALTER TABLE IF EXISTS public.product_analytics_events_experimental DROP CONSTRAINT IF EXISTS product_analytics_events_experimental_project_id_fkey;
ALTER TABLE IF EXISTS ONLY public.u2f_registrations DROP CONSTRAINT IF EXISTS fk_u2f_registrations_user_id;
ALTER TABLE IF EXISTS ONLY public.timelogs DROP CONSTRAINT IF EXISTS fk_timelogs_merge_requests_merge_request_id;
ALTER TABLE IF EXISTS ONLY public.timelogs DROP CONSTRAINT IF EXISTS fk_timelogs_issues_issue_id;
ALTER TABLE IF EXISTS ONLY public.ci_builds_metadata DROP CONSTRAINT IF EXISTS fk_rails_ffcf702a02;
<snip>
Relates to #36405 (closed)
Edited by Stan Hu