Re: pg_dumpall --exclude-database option

On 12/19/18 3:55 PM, Andrew Dunstan wrote:
> On 12/18/18 11:53 PM, Michael Paquier wrote:
>> On Fri, Nov 30, 2018 at 04:26:41PM -0500, Andrew Dunstan wrote:
>>> On 11/18/18 1:41 PM, Andrew Dunstan wrote:
>>>> On 11/17/18 9:55 AM, Alvaro Herrera wrote:
>>>>> In the long run, I think we should add an option to processSQLNamePattern
>>>>> to use OR instead of AND, which would fix both this problem as well as
>>>>> pg_dump's.  I don't think that's important enough to stall this patch.
>> Agreed. This patch is useful in itself. This option would be nice to
>> have, and this routine interface would begin to grow too many boolean
>> switches to my taste so I'd rather use some flags instead.
>>
>> The patch is doing its work, however I have spotted an issue in the
>> format of the dumps generated. Each time an excluded database is
>> processed its set of SET queries (from _doSetFixedOutputState) as well
>> as the header "PostgreSQL database dump" gets generated. I think that
>> this data should not show up.
>
> I'll take a look.

I think you're mistaken. The following example shows this clearly -
there is nothing corresponding to the 20 excluded databases. What you're
referring to appears to be the statements that preceded the 'CREATE
DATABASE' statement. That's to be excpected.

cheers

andrew

andrew(at)emma:inst (pg_dumpall--exclude)*$ for x in `seq 1 20` ; do
bin/createdb ex$x; done
andrew(at)emma:inst (pg_dumpall--exclude)*$ bin/createdb inc
andrew(at)emma:inst (pg_dumpall--exclude)*$ bin/pg_dumpall
--exclude-database "ex*"
--
-- PostgreSQL database cluster dump
--

SET default_transaction_read_only = off;

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;

--
-- Roles
--

CREATE ROLE andrew;
ALTER ROLE andrew WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN
REPLICATION BYPASSRLS;

\connect template1

--
-- PostgreSQL database dump
--

-- Dumped from database version 12devel
-- Dumped by pg_dump version 12devel

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 client_min_messages = warning;
SET row_security = off;

--
-- PostgreSQL database dump complete
--

--
-- PostgreSQL database dump
--

-- Dumped from database version 12devel
-- Dumped by pg_dump version 12devel

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 client_min_messages = warning;
SET row_security = off;

--
-- Name: inc; Type: DATABASE; Schema: -; Owner: andrew
--

CREATE DATABASE inc WITH TEMPLATE = template0 ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';

ALTER DATABASE inc OWNER TO andrew;

\connect inc

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 client_min_messages = warning;
SET row_security = off;

--
-- PostgreSQL database dump complete
--

\connect postgres

--
-- PostgreSQL database dump
--

-- Dumped from database version 12devel
-- Dumped by pg_dump version 12devel

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 client_min_messages = warning;
SET row_security = off;

--
-- PostgreSQL database dump complete
--

--
-- PostgreSQL database cluster dump complete
--
 

--

Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services