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
inPostgresQL 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.klaver@aklaver.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.klaver@aklaver.com
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com