Re: pg_dump and public schema

On 3/5/20 6:57 AM, Олег Самойлов wrote:
> Thanks. I expected that the database restored from its dump must be exactly the same. As it was before. But something in PostgresQL changes and not always for the good.

From what I see:

1) pg_dump -C -U postgres -d sch_test

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

2) select oid, datname, datallowconn from pg_database;

13297 | template0 | f

update pg_database set datallowconn = 't' where oid = 13297;

3) test=# \c template0
You are now connected to database "template0" as user "postgres".

template0=# \d

Did not find any relations.
template0=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

template0=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)

So the script for restoring the database starts with using template0 as
the template. This is done to start with an 'empty' database that the
rest of the script can populate. The exception is the presence of the
public schema. Obviously, at this point, there is nothing that tracks
the presence of the public schema in the database being dumped and then
drops it from the newly created version if it was not present in the
original.

>
>> 4 марта 2020 г., в 19:19, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> написал(а):
>>
>> I believe this is the latest information on public schema handling:
>>
>> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5955d934194c3888f30318209ade71b53d29777f
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com