Search Postgresql Archives

Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Sun, 20 Jun 2021 at 22:49, Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx> wrote:


On Sun, 20 Jun 2021 at 22:17, Karsten Hilbert <Karsten.Hilbert@xxxxxxx> wrote:
Dear all,

I am testing the pg_restore of a database with
default_transaction_read_only=on.

It would seem the restore script lacks a

        SET default_transaction_read_only TO 'off';

in the setup section after re-connecting to the DB following
the ALTER DATABASE section ?

Thanks,
Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B



i just tested as below.

postgres@db:~/playground/logical_replication$ rm -rf example
postgres@db:~/playground/logical_replication$ initdb -D example 2>/dev/null >/dev/null
postgres@db:~/playground/logical_replication$ vim example/postgresql.conf
postgres@db:~/playground/logical_replication$ printf "default_transaction_read_only=on\n" >> example/postgresql.conf  #global config setting as on
postgres@db:~/playground/logical_replication$ pg_ctl -D example -l logfile start
waiting for server to start.... done
server started
postgres@db:~/playground/logical_replication$ psql
psql (14beta1)
Type "help" for help.

postgres=# show default_transaction_read_only;  -- validate it is on
 default_transaction_read_only
-------------------------------
 on
(1 row)

postgres=# \q
postgres@db:~/playground/logical_replication$ pg_dumpall -f dump.sql
postgres@db:~/playground/logical_replication$ grep default_transaction_read_only dump.sql -- check what gets dumped
SET default_transaction_read_only = off;

but this is 14beta1.

Did I simulate your concern correctly ?


ok i do not know if that is a bug ? 
but i am able to reproduce your concern now alter database

postgres@db:~/playground/logical_replication$ rm -rf example
postgres@db:~/playground/logical_replication$ initdb -D example 2>/dev/null >/dev/null
postgres@db:~/playground/logical_replication$ pg_ctl -D example -l logfile start
waiting for server to start.... done
server started
postgres@db:~/playground/logical_replication$ createdb example
postgres@db:~/playground/logical_replication$ psql example -c 'create table t(id int); insert into t select 1; '
INSERT 0 1
postgres@db:~/playground/logical_replication$ psql example -c 'show default_transaction_read_only;'
 default_transaction_read_only
-------------------------------
 off
(1 row)

postgres@db:~/playground/logical_replication$ psql example -c 'ALTER DATABASE example SET default_transaction_read_only TO ''on'';'
ALTER DATABASE
postgres@db:~/playground/logical_replication$ psql example -c 'show default_transaction_read_only;'
 default_transaction_read_only
-------------------------------
 on
(1 row)

postgres@db:~/playground/logical_replication$ pg_dumpall -f dump.sql
postgres@db:~/playground/logical_replication$ grep default_transaction_read_only dump.sql
SET default_transaction_read_only = off;
ALTER DATABASE example SET default_transaction_read_only TO 'on';
postgres@db:~/playground/logical_replication$ dropdb example
postgres@db:~/playground/logical_replication$ psql < dump.sql

ERROR:  cannot execute CREATE TABLE in a read-only transaction
ERROR:  cannot execute ALTER TABLE in a read-only transaction
ERROR:  relation "public.t" does not exist
invalid command \.
You are now connected to database "postgres" as user "postgres".
ERROR:  syntax error at or near "1"

so the table did not get restored, as default_transaction_read_only = on.
so this is the same in 14 as well.

you can load sections via pg_restore and skip this TOC
 --section=SECTION            restore named section (pre-data, data, or post-data)

maybe you know that, and are just asking if this is a feature or a bug.




--
Thanks,
Vijay
Mumbai, India

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux