On Sun, 20 Jun 2021 at 22:49, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote:
> > > On Sun, 20 Jun 2021 at 22:17, Karsten Hilbert <karsten.hilb...@gmx.net> > 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
