DBA Hub

📋Steps in this guide1/2

How to make a postgres database readonly - DBACLASS DBACLASS

default_transaction_read_only controls whether the database is in read only or not . Use the steps mentioned in the article to

postgresql configurationintermediate
by PostgreSQL
14 views
1

For making single db read only.( DB_NAME – > edbstore)

–connect to database other than the edbstore ( here i connected to default postgres db) — Now restart the postgres cluster(either using pg_ctl or service_name) — Now login to database and check: We can see it is not allowing write operations. Now if you wish to remove the database from read only mode, then

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
[enterprisedb@localhost ~]$ psql -d postgres
psql (12.4.5)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "enterprisedb" via socket in "/tmp" at port "5444".


postgres=# alter database edbstore set <span style="color: #ff0000;"><strong>default_transaction_read_only=on</strong></span>;
ALTER DATABASE

[root@localhost ~]# systemctl stop edb-as-12
[root@localhost ~]# systemctl start edb-as-12

edbstore=# \conninfo
You are connected to database "edbstore" as user "enterprisedb" via socket in "/tmp" at port "5444".
edbstore=# create table test as select  * from pg_settings;
ERROR:  cannot execute CREATE TABLE AS in a read-only transaction

postgres=# \conninfo
You are connected to database "postgres" as user "enterprisedb" via socket in "/tmp" at port "5444".


postgres=# alter database edbstore set <strong><span style="color: #ff0000;">default_transaction_read_only=off</span></strong>;
ALTER DATABASE

[root@localhost ~]# systemctl stop edb-as-12
[root@localhost ~]# systemctl start edb-as-12
2

For making all the databases of the postgres cluster read only:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
postgres=# show <span style="color: #ff0000;"><strong>default_transaction_read_only</strong></span>;
 default_transaction_read_only 
-------------------------------
 off
(1 row)

postgres=# alter system set <span style="color: #ff0000;"><strong>default_transaction_read_only=on</strong></span>;
ALTER SYSTEM

-- Restart the pg cluster:
[root@localhost ~]# systemctl stop edb-as-12
[root@localhost ~]# systemctl start edb-as-12

Comments (0)

Please to add comments

No comments yet. Be the first to comment!