Обсуждение: pg_dump and public schema
Hi all. PostgresQL 12.1. Strange behaviour with pg_dump and absent public schema.
I droped public schema and I work under "username" schema.
=> \dn
List of schemas
Name | Owner
-------+-------
olleg | olleg
(1 row)
Dump now
pg_dump -U postgres -C olleg >dump.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 12.1
-- Dumped by pg_dump version 12.1
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;
--
-- Name: olleg; Type: DATABASE; Schema: -; Owner: olleg
--
CREATE DATABASE olleg WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'ru_RU.UTF-8' LC_CTYPE = 'ru_RU.UTF-8';
ALTER DATABASE olleg OWNER TO olleg;
\connect olleg
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;
--
-- Name: olleg; Type: SCHEMA; Schema: -; Owner: olleg
--
CREATE SCHEMA olleg;
ALTER SCHEMA olleg OWNER TO olleg;
--
-- PostgreSQL database dump complete
--
recreate DB from the dump:
psql postgres postgres -f dump.sql
And now I see public schema, which must be absent.
psql olleg olleg
=> \dn
List of schemas
Name | Owner
--------+----------
olleg | olleg
public | postgres
(2 rows)
Hi,
I think "create database" always creates the "public" schema. So, all
is well. All you have to do is drop it after running the dump.sql
script.
Cheers,
Paul
On Wed, Mar 4, 2020 at 2:43 PM Олег Самойлов <splarv@ya.ru> wrote:
>
> Hi all. PostgresQL 12.1. Strange behaviour with pg_dump and absent public schema.
>
> I droped public schema and I work under "username" schema.
>
> => \dn
> List of schemas
> Name | Owner
> -------+-------
> olleg | olleg
> (1 row)
>
> Dump now
>
> pg_dump -U postgres -C olleg >dump.sql
>
> --
> -- PostgreSQL database dump
> --
>
> -- Dumped from database version 12.1
> -- Dumped by pg_dump version 12.1
>
> 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;
>
> --
> -- Name: olleg; Type: DATABASE; Schema: -; Owner: olleg
> --
>
> CREATE DATABASE olleg WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'ru_RU.UTF-8' LC_CTYPE =
'ru_RU.UTF-8';
>
>
> ALTER DATABASE olleg OWNER TO olleg;
>
> \connect olleg
>
> 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;
>
> --
> -- Name: olleg; Type: SCHEMA; Schema: -; Owner: olleg
> --
>
> CREATE SCHEMA olleg;
>
>
> ALTER SCHEMA olleg OWNER TO olleg;
>
> --
> -- PostgreSQL database dump complete
> --
>
> recreate DB from the dump:
>
> psql postgres postgres -f dump.sql
>
> And now I see public schema, which must be absent.
>
> psql olleg olleg
>
> => \dn
> List of schemas
> Name | Owner
> --------+----------
> olleg | olleg
> public | postgres
> (2 rows)
>
On 3/4/20 5:42 AM, Олег Самойлов wrote:
> Hi all. PostgresQL 12.1. Strange behaviour with pg_dump and absent public schema.
>
> I droped public schema and I work under "username" schema.
>
> => \dn
> List of schemas
> Name | Owner
> -------+-------
> olleg | olleg
> (1 row)
>
> Dump now
>
> pg_dump -U postgres -C olleg >dump.sql
>
> --
> -- PostgreSQL database dump
> --
>
> -- Dumped from database version 12.1
> -- Dumped by pg_dump version 12.1
>
> 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;
>
> --
> -- Name: olleg; Type: DATABASE; Schema: -; Owner: olleg
> --
>
> CREATE DATABASE olleg WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'ru_RU.UTF-8' LC_CTYPE =
'ru_RU.UTF-8';
>
>
> ALTER DATABASE olleg OWNER TO olleg;
>
> \connect olleg
>
> 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;
>
> --
> -- Name: olleg; Type: SCHEMA; Schema: -; Owner: olleg
> --
>
> CREATE SCHEMA olleg;
>
>
> ALTER SCHEMA olleg OWNER TO olleg;
>
> --
> -- PostgreSQL database dump complete
> --
>
> recreate DB from the dump:
>
> psql postgres postgres -f dump.sql
>
> And now I see public schema, which must be absent.
>
> psql olleg olleg
>
> => \dn
> List of schemas
> Name | Owner
> --------+----------
> olleg | olleg
> public | postgres
> (2 rows)
>
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
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. > 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
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