Error cannot execute grant in a read only transaction - Исправление ошибок и поиск оптимальных решений проблем

I’m trying to setup the pgexercises data in my local machine. When I run: psql -U <username> -f clubdata.sql -d postgres -x I get the error: psql:clubdata.sql:6: ERROR: cannot execute CREATE SCHEMA in a read-only transaction.

Why did it create a read-only database on my local machine? Can I change this?

asked Jul 2, 2015 at 13:48

Normally the most plausible reasons for this kind of error are :

trying create statements on a read-only replica (the entire instance is read-only).
<username> has default_transaction_read_only set to ON
the database has default_transaction_read_only set to ON

The script mentioned has in its first lines:

CREATE DATABASE exercises;
c exercises
CREATE SCHEMA cd;

and you report that the error happens with CREATE SCHEMA at line 6, not before.

That means that the CREATE DATABASE does work, when run by <username>.
And it wouldn’t work if any of the reasons above was directly applicable.

One possibility that would technically explain this would be that default_transaction_read_only would be ON in the postgresql.conf file, and set to OFF for the database postgres, the one that the invocation of psql connects to, through an ALTER DATABASE statement that supersedes the configuration file.

That would be why CREATE DATABASE works, but then as soon as it connects to a different database with c, the default_transaction_read_only setting of the session would flip to ON.

But of course that would be a pretty weird and unusual configuration.

answered Jul 2, 2015 at 16:32

Daniel VéritéDaniel Vérité

56.1k15 gold badges125 silver badges150 bronze badges

Reached out to pgexercises.com and they were able to help me.

I ran these commands(separately):

psql -U <username> -d postgres
begin;
set transaction read write;
alter database exercises set default_transaction_read_only = off;
commit;
q

Then I dropped the database from the terminal dropdb exercises and ran script again psql -U <username> -f clubdata.sql -d postgres -x -q

answered Jul 4, 2015 at 4:45

ltrainprltrainpr

2,9653 gold badges28 silver badges40 bronze badges

I was having getting cannot execute CREATE TABLE in a read-only transaction, cannot execute DELETE TABLE in a read-only transaction and others.

They all followed a cannot execute INSERT in a read-only transaction. It was like the connection had switched itself over to read-only in the middle of my batch processing.

Turns out, I was running out of storage!

Write access was disabled when the database could no longer write anything. I am using Postgres on Azure. I don’t know if the same effect would happen if I was on a dedicated server.

alecxe

455k116 gold badges1061 silver badges1180 bronze badges

answered Jan 15, 2019 at 4:52

Brad MathewsBrad Mathews

1,4532 gold badges20 silver badges45 bronze badges

I had same issue for Postgre Update statement

SQL Error: 0, SQLState: 25006 ERROR: cannot execute UPDATE in a read-only transaction

Verified Database access by running below query and it will return either true or false

SELECT pg_is_in_recovery()

true -> Database has only Read Access

false -> Database has full Access

if returns true then check with DBA team for the full access and also try for ping in command prompt and ensure the connectivity.

ping <database hostname or dns>

Also verify if you have primary and standby node for the database

answered Jun 16, 2021 at 16:22

BharathirajaBharathiraja

6441 gold badge11 silver badges20 bronze badges

In my case I had a master and replication nodes, and the master node became replication node, which I believe switched it into hot_standby mode. So I was trying to write data into a node that was meant only for reading, therefore the «read-only» problem.

You can query the node in question with SELECT pg_is_in_recovery(), and if it returns True then it is «read-only», and I suppose you should switch to using whatever master node you have now.

I got this information from: https://serverfault.com/questions/630753/how-to-change-postgresql-database-from-read-only-to-writable.
So full credit and my thanks goes to Craig Ringer!

answered May 6, 2021 at 9:12

vladimirrorvladimirror

65014 silver badges7 bronze badges

Dbeaver: In my case

This was on.

answered Oct 4, 2022 at 4:00

This doesn’t quite answer the original question, but I received the same error and found this page, which ultimately led to a fix.

My issue was trying to run a function with temp tables being created and dropped. The function was created with SECURITY DEFINER privileges, and the user had access locally.

In a different environment, I received the cannot execute DROP TABLE in a read-only transaction error message. This environment was AWS Aurora, and by default, non-admin developers were given read-only privileges. Their server connections were thus set up to use the read-only node of Aurora (-ro- is in the connection url), which must put the connection in the read-only state. Running the same function with the same user against the write node worked.

Seems like a good use case for table variables like SQL Server has! Or, at least, AWS should modify their flow to allow temp tables to be created and dropped on read nodes.

answered May 7, 2020 at 15:35

ps2goatps2goat

7,8971 gold badge33 silver badges68 bronze badges

This occurred when I was restoring a production database locally, the database is still doing online recovery from the WAL records.

A little bit unexpected as I assumed pgbackgrest was creating instantly recoverable restores, perhaps not.

 91902 postgres  20   0 1445256  14804  13180 D   4.3  0.3   0:28.06 postgres: startup   recovering 000000010000001E000000A5

answered Dec 6, 2019 at 15:28

Kevin ParkerKevin Parker

16.8k20 gold badges76 silver badges103 bronze badges

If like me you are trying to create DB on heroku and are stuck as this message shows up on the dataclip tab

I did this,

Choose Resources from(Overview Resources Deploy Metrics Activity Access Settings)

Choose Settings out of (Overview, Durability, Settings, Dataclip)

Then in Administration->Database Credentials choose View Credentials...

then open terminal and fill that info here and enter

psql --host=***************.amazonaws.com --port=5432 --username=*********pubxl --password --dbname=*******lol

then it’ll ask for password, copy-paste from there and you can run Postgres cmds.

answered May 27, 2021 at 18:42

shekhar g hshekhar g h

1,1751 gold badge9 silver badges12 bronze badges

I suddenly started facing this error on postgres installed on my windows machine, when I was running alter query from dbeaver, all I did was deleted the connection of postgres from dbeaver and created a new connection

answered Apr 8, 2022 at 9:55

Muhammad AwaisMuhammad Awais

1,3501 gold badge17 silver badges20 bronze badges

I just had this error. My cause was not granting permission to the SEQUENCE

GRANT ALL ON SEQUENCE word_mash_word_cube_template_description_reference_seq TO ronshome_user;

answered Oct 13, 2022 at 16:18

Ron PiggottRon Piggott

7031 gold badge8 silver badges26 bronze badges

If you are facing this issue with an RDS instance cluster, please check your endpoint and use the Writer instance endpoint. Then it should work now.

answered Nov 23, 2022 at 11:46

Aditya YAditya Y

5015 silver badges9 bronze badges

Источник

In this article, we will see the solution for error like PostgreSQL Error: cannot execute in a read-only transaction.

ERROR:  cannot execute CREATE DATABASE in a read-only transaction
Error: cannot execute DROP DATABASE in a read-only transaction
ERROR:  cannot execute CREATE TABLE in a read-only transaction
Error: cannot execute DROP TABLE in a read-only transaction
cannot execute INSERT in a read-only transaction

Reason:

Reason is simple, PostgreSQL server is recovery(read-only) or standby mode.

Resolution:

1. We have to remove recovery(read-only) or standby mode.

2. Go to your data or main directory then remove standby.signal and recovery.signal files.

Before performing following action, take backup of these files in some other location.

File location in(Ubuntu change your respective version nuymber)

/var/lib/postgresql/13/main

3. After removing signal files, restart PostgreSQL cluster.

sudo systemctl restart postgresql

4. Then, you will able to connect to the server and perform all actions as usual.

Источник

++ flynn-host bootstrap --peer-ips=10.100.47.226,10.100.39.201,10.100.41.193 --from-backup /mnt/flynn-restore-source --timeout 1200
+ bootstrap_output='20:22:48.211367 check online-hosts
20:22:48.219524 run-app discoverd
20:23:30.796661 run-app flannel
20:23:32.016760 wait-hosts wait-hosts
20:23:34.031541 run-app postgres
20:24:31.041414 wait postgres-wait
20:24:36.281487 restore-postgres restore
SET
SET
SET
ERROR:  cannot execute DROP DATABASE in a read-only transaction
ERROR:  cannot execute DROP DATABASE in a read-only transaction
ERROR:  cannot execute DROP DATABASE in a read-only transaction
ERROR:  cannot execute DROP ROLE in a read-only transaction
ERROR:  cannot execute DROP ROLE in a read-only transaction
ERROR:  cannot execute DROP ROLE in a read-only transaction
ERROR:  cannot execute DROP ROLE in a read-only transaction
ERROR:  cannot execute DROP ROLE in a read-only transaction
ERROR:  cannot execute CREATE ROLE in a read-only transaction
ERROR:  cannot execute ALTER ROLE in a read-only transaction
ERROR:  cannot execute CREATE ROLE in a read-only transaction
ERROR:  cannot execute ALTER ROLE in a read-only transaction
ERROR:  cannot execute CREATE ROLE in a read-only transaction
ERROR:  cannot execute ALTER ROLE in a read-only transaction
ERROR:  cannot execute CREATE ROLE in a read-only transaction
ERROR:  cannot execute ALTER ROLE in a read-only transaction
ERROR:  cannot execute CREATE ROLE in a read-only transaction
ERROR:  cannot execute ALTER ROLE in a read-only transaction
ERROR:  cannot execute CREATE DATABASE in a read-only transaction
ERROR:  cannot execute CREATE DATABASE in a read-only transaction
ERROR:  cannot execute CREATE DATABASE in a read-only transaction
ERROR:  cannot execute REVOKE in a read-only transaction
ERROR:  cannot execute REVOKE in a read-only transaction
ERROR:  cannot execute GRANT in a read-only transaction
ERROR:  cannot execute GRANT in a read-only transaction
connect: FATAL:  database "051c5383b32ae3340526e2cb1f07a013" does not exist

20:24:37.206372 restore-postgres restore error: error running psql restore: exec: job exited with status 2 - "SETnSETnSETnERROR:  cannot execute DROP DATABASE in a read-only transactionnERROR:  cannot execute DROP DATABASE in a read-only transactionnERROR:  cannot execute DROP DATABASE in a read-only transactionnERROR:  cannot execute DROP ROLE in a read-only transactionnERROR:  cannot execute DROP ROLE in a read-only transactionnERROR:  cannot execute DROP ROLE in a read-only transactionnERROR:  cannot execute DROP ROLE in a read-only transactionnERROR:  cannot execute DROP ROLE in a read-only transactionnERROR:  cannot execute CREATE ROLE in a read-only transactionnERROR:  cannot execute ALTER ROLE in a read-only transactionnERROR:  cannot execute CREATE ROLE in a read-only transactionnERROR:  cannot execute ALTER ROLE in a read-only transactionnERROR:  cannot execute CREATE ROLE in a read-only transactionnERROR:  cannot execute ALTER ROLE in a read-only transactionnERROR:  cannot execute CREATE ROLE in a read-only transactionnERROR:  cannot execute ALTER ROLE in a read-only transactionnERROR:  cannot execute CREATE ROLE in a read-only transactionnERROR:  cannot execute ALTER ROLE in a read-only transactionnERROR:  cannot execute CREATE DATABASE in a read-only transactionnERROR:  cannot execute CREATE DATABASE in a read-only transactionnERROR:  cannot execute CREATE DATABASE in a read-only transactionnERROR:  cannot execute REVOKE in a read-only transactionnERROR:  cannot execute REVOKE in a read-only transactionnERROR:  cannot execute GRANT in a read-only transactionnERROR:  cannot execute GRANT in a read-only transactionn\connect: FATAL:  database "051c5383b32ae3340526e2cb1f07a013" does not existn"'
+ echo 'Bootstrap failed, collecting debugging info'
Bootstrap failed, collecting debugging info
++ flynn-host collect-debug-info
+ debug_info='t=2016-03-16T20:24:37+0000 lvl=info msg="uploading logs and debug information to a private, anonymous gist"
t=2016-03-16T20:24:37+0000 lvl=info msg="this may take a while depending on the size of your logs"
t=2016-03-16T20:24:37+0000 lvl=info msg="getting flynn-host logs"
t=2016-03-16T20:24:37+0000 lvl=info msg="getting job logs"
t=2016-03-16T20:24:37+0000 lvl=info msg="getting system information"
t=2016-03-16T20:24:37+0000 lvl=info msg="creating anonymous gist"
t=2016-03-16T20:24:38+0000 lvl=info msg="debug information uploaded to: https://gist.github.com/bcb5d2ce9bcc01586343"'
++ grep -o 'https://gist.github.com/.*'
++ echo 't=2016-03-16T20:24:37+0000 lvl=info msg="uploading logs and debug information to a private, anonymous gist"
t=2016-03-16T20:24:37+0000 lvl=info msg="this may take a while depending on the size of your logs"
t=2016-03-16T20:24:37+0000 lvl=info msg="getting flynn-host logs"
t=2016-03-16T20:24:37+0000 lvl=info msg="getting job logs"
t=2016-03-16T20:24:37+0000 lvl=info msg="getting system information"
t=2016-03-16T20:24:37+0000 lvl=info msg="creating anonymous gist"
t=2016-03-16T20:24:38+0000 lvl=info msg="debug information uploaded to: https://gist.github.com/bcb5d2ce9bcc01586343"'
+ gist='https://gist.github.com/bcb5d2ce9bcc01586343"'

Looks like a race between standing up the postgres server and starting the backup?

Источник

You could always double check your problem by issuing a command such as

/usr/bin/pgsql/bin/createdb test

You can frequently get better/clearer error messages and/or other information from the command line than from GUI tools such as pgAdmin III.

But, in order to change a system parameter, you have to reload the postgresql.conf file after making changes. You can do this by either

rebooting the server or
reloading the .conf file or
change on the client side

From here, some parameters always need a reboot and some can be dynamically changed by simply reloading.

I tried to find a clear list of those which required reboot and which required reload, but failed (grateful for input on this!).

For a system reboot,

pgctl restart

To reload, you could also try these commands (from here — or check the documentation page given above).

Option 1: From the command-line shell

su - postgres /path/to/pg_ctl reload

Option 2: Using SQL

SELECT pg_reload_conf();

You can verify that the setting has «taken» from the table pg_settings. Also of interest is this page.

For the default_transaction_read_only parameter, you can also set it directly from the client (see here). Check the links here for how to set all parameters — reboot, reload or client.

test=# SELECT name, setting FROM pg_settings WHERE name = 'default_transaction_read_only';
             name              | setting 
-------------------------------+---------
 default_transaction_read_only | on
(1 row)

test=# 
test=# `SET default_transaction_read_only = OFF;`
SET
test=# 
test=# SELECT name, setting FROM pg_settings WHERE name = 'default_transaction_read_only';
             name              | setting 
-------------------------------+---------
 default_transaction_read_only | off
(1 row)

test=# 
test=# SET default_transaction_read_only = ON;
SET

Источник

Normally the most plausible reasons for this kind of error are :

trying create statements on a read-only replica (the entire instance is read-only).
<username> has default_transaction_read_only set to ON
the database has default_transaction_read_only set to ON

The script mentioned has in its first lines:

CREATE DATABASE exercises;
c exercises
CREATE SCHEMA cd;

and you report that the error happens with CREATE SCHEMA at line 6, not before.

That means that the CREATE DATABASE does work, when run by <username>.
And it wouldn’t work if any of the reasons above was directly applicable.

That would be why CREATE DATABASE works, but then as soon as it connects to a different database with c, the default_transaction_read_only setting of the session would flip to ON.

But of course that would be a pretty weird and unusual configuration.

Reached out to pgexercises.com and they were able to help me.

I ran these commands(separately):

psql -U <username> -d postgres
begin;
set transaction read write;
alter database exercises set default_transaction_read_only = off;
commit;
q

Then I dropped the database from the terminal dropdb exercises and ran script again psql -U <username> -f clubdata.sql -d postgres -x -q

I had same issue for Postgre Update statement

SQL Error: 0, SQLState: 25006 ERROR: cannot execute UPDATE in a read-only transaction

Verified Database access by running below query and it will return either true or false

SELECT pg_is_in_recovery()

true -> Database has only Read Access

false -> Database has full Access

if returns true then check with DBA team for the full access and also try for ping in command prompt and ensure the connectivity.

ping <database hostname or dns>

Also verify if you have primary and standby node for the database

I was having getting cannot execute CREATE TABLE in a read-only transaction, cannot execute DELETE TABLE in a read-only transaction and others.

They all followed a cannot execute INSERT in a read-only transaction. It was like the connection had switched itself over to read-only in the middle of my batch processing.

Turns out, I was running out of storage! Write access was disabled when the database could no longer write anything. I am using Postgres on Azure. I don’t know if the same effect would happen if I was on a dedicated server.

Источник

Я пытаюсь настроить данные pgexercises на моем локальном компьютере. Когда я запускаю: psql -U <username> -f clubdata.sql -d postgres -x, я получаю сообщение об ошибке: psql:clubdata.sql:6: ERROR: cannot execute CREATE SCHEMA in a read-only transaction.

Почему на моем локальном компьютере была создана база данных, доступная только для чтения? Могу я это изменить?

5 ответов

Лучший ответ

Обратился к pgexercises.com, и они смогли мне помочь.

Я выполнил эти команды (отдельно):

psql -U <username> -d postgres
begin;
set transaction read write;
alter database exercises set default_transaction_read_only = off;
commit;
q

Затем я сбросил базу данных с терминала dropdb exercises и снова запустил скрипт psql -U <username> -f clubdata.sql -d postgres -x -q

ltrainpr
4 Июл 2015 в 04:45

Это произошло, когда я восстанавливал производственную базу данных локально, база данных все еще выполняет восстановление в оперативном режиме из записей WAL.

Немного неожиданно, поскольку я предположил, что pgbackgrest создает мгновенно восстанавливаемые восстановления, возможно, нет.

 91902 postgres  20   0 1445256  14804  13180 D   4.3  0.3   0:28.06 postgres: startup   recovering 000000010000001E000000A5

Kevin Parker
6 Дек 2019 в 15:28

Это не совсем ответ на исходный вопрос, но я получил ту же ошибку и нашел эту страницу, что в конечном итоге привело к исправлению.

Моя проблема заключалась в попытке запустить функцию с созданием и удалением временных таблиц. Функция была создана с правами SECURITY DEFINER, и пользователь имел доступ локально.

В другой среде я получил сообщение об ошибке cannot execute DROP TABLE in a read-only transaction. Этой средой была AWS Aurora, и по умолчанию разработчикам, не являющимся администраторами, были предоставлены права только для чтения. Таким образом, их серверные соединения были настроены на использование доступного только для чтения узла Aurora (-ro- находится в URL-адресе соединения), который должен перевести соединение в состояние только для чтения. Выполнение той же функции с тем же пользователем для узла записи сработало.

Похоже, хороший вариант использования табличных переменных, таких как SQL Server! Или, по крайней мере, AWS следует изменить свой поток, чтобы разрешить создание и удаление временных таблиц на узлах чтения.

ps2goat
7 Май 2020 в 15:35

Я получал cannot execute CREATE TABLE in a read-only transaction, cannot execute DELETE TABLE in a read-only transaction и другие.

Все они следовали cannot execute INSERT in a read-only transaction. Как будто соединение переключилось в режим только для чтения в середине моей пакетной обработки.

Оказывается, у меня закончилась память! Доступ для записи был отключен, когда база данных больше не могла ничего писать. Я использую Postgres в Azure. Я не знаю, случился бы такой же эффект, если бы я был на выделенном сервере.

Brad Mathews
15 Янв 2019 в 04:52

Обычно наиболее вероятными причинами такого рода ошибок являются:

попытка создания операторов на реплике, доступной только для чтения (весь экземпляр доступен только для чтения).
<username> имеет default_transaction_read_only значение ON
в базе данных установлено значение «default_transaction_read_only«

В первых строках упомянутого сценария:

CREATE DATABASE exercises;
c exercises
CREATE SCHEMA cd;

и вы сообщаете, что ошибка возникает с CREATE SCHEMA в строке 6, а не ранее.

Это означает, что CREATE DATABASE действительно работает, когда запускается <username>. И это не сработало бы, если бы любая из вышеперечисленных причин была прямо применима.

Одна из возможностей, которая технически объяснила бы это, заключалась в том, что default_transaction_read_only будет ON в файле postgresql.conf и установлен на OFF для базы данных postgres, той самой к которому подключается вызов psql через оператор ALTER DATABASE, заменяющий файл конфигурации.

Вот почему CREATE DATABASE работает, но как только он подключается к другой базе данных с помощью c, параметр default_transaction_read_only сеанса переключается на ON.

Но, конечно, это была бы довольно странная и необычная конфигурация.

Daniel Vérité
2 Июл 2015 в 16:37

Источник

Здравствуйте! Настроил реплику БД postgres по инструкции https://www.8host.com/blog/replikaciya-baz-dannyx-…

Теперь возникла необходимость создать на Slave машине в той же БД таблицу, в которую slave мог бы делать инсёрты. Как это возможно? При попытке создать таблицу, получаю ошибку:

ERROR: cannot execute CREATE TABLE in a read-only transaction

P.S. На мастер сервер эта таблица не должна передаваться. Она нужна только локально на slave машине.

Вопрос задан

более трёх лет назад
526 просмотров

Пригласить эксперта

На slave это сделать нельзя.

На slave это сделать нельзя.

Потому что это slave база.
Придётся вам завести на слейве дополнительную БД, в которую будет писать Слейв.

Показать ещё
Загружается…

09 февр. 2023, в 15:13

2000 руб./за проект

09 февр. 2023, в 15:06

2000 руб./за проект

09 февр. 2023, в 15:02

12000 руб./за проект

Минуточку внимания

Источник

CHANGE HISTORY

Release Series	History
10.6 Enterprise	Present starting in MariaDB Enterprise Server 10.6.4-1 as error number 1792
10.6 Community	Present starting in MariaDB Community Server 10.6.0 as error number 1792
10.5 Enterprise	Present starting in MariaDB Enterprise Server 10.5.3-1 as error number 1792
10.5 Community	Present starting in MariaDB Community Server 10.5.0 as error number 1792
10.4 Enterprise	Present starting in MariaDB Enterprise Server 10.4.6-1 as error number 1792
10.4 Community	Present starting in MariaDB Community Server 10.4.0 as error number 1792
10.3 Enterprise	Present starting in MariaDB Enterprise Server 10.3.16-1 as error number 1792
10.3 Community	Present starting in MariaDB Community Server 10.3.0 as error number 1792
10.2 Enterprise	Present starting in MariaDB Enterprise Server 10.2.25-1 as error number 1792
10.2 Community	Present starting in MariaDB Community Server 10.2.0 as error number 1792

Release Series	History
10.6 Enterprise	Present starting in MariaDB Enterprise Server 10.6.4-1 as error number 1792
10.5 Enterprise	Present starting in MariaDB Enterprise Server 10.5.3-1 as error number 1792
10.4 Enterprise	Present starting in MariaDB Enterprise Server 10.4.6-1 as error number 1792

Источник

PostgreSQL: Script to Create a Read-Only Database User

Many users has asked me how they can create READ-ONLY user, READ-ONLY Database or How they can create backup user which can be use only for backup (no modification)

Well answer is in parameter called default_transaction_read_only.

If you want to make a user READ-ONLY, then you can follow steps given below:

1. CREATE normal user.

2. Use ALTER USER command to set this parameter for this user as given below:

ALTER USER  set default_transaction_read_only = on;

3. GRANT SELECT to this user for tables/object using GRANT. And you are good to go.
Below is snapshot:

postgres=# create user readonly password 'test';
CREATE ROLE
postgres=# alter user readonly set default_transaction_read_only = on;
ALTER ROLE
postgres=# GRANT select on employees to readonly;
GRANT
edbs-MacBook-Pro:data postgres$ psql -U readonly -W
Password for user readonly:
psql (9.1.1)
Type "help" for help.

postgres=> select * from employees ;
 employee_name | entity_name
---------------+-------------
 Smith         | HR
 Jones         | HR
 Taylor        | SALES
 Brown         | SALES
(4 rows)

postgres=> CREATE table test_readonly(id numeric);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
postgres=>

Similarly, If you want to make a Database READ-ONLY, then you can use following command

ALTER DATABASE  set default_transaction_read_only=on;
Below is snapshot:

postgres=# CREATE database readonly;
CREATE DATABASE
postgres=# alter database readonly set default_transaction_read_only = on;
ALTER DATABASE
postgres=# q
edbs-MacBook-Pro:data postgres$ psql readonly
psql (9.1.1)
Type "help" for help.

readonly=# create table test_readonly(id numeric);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
readonly=#

Now, if you want a seperate backup user which you want to use for Online/Hot Backup,Logical Backup (using pg_dump), then you can create a super user with default_transaction_read_only = on and can use it for backup purpose. As given below:

CREATE USER backupuser SUPERUSER  password 'backupuser';
ALTER USER backupuser set default_transaction_read_only = on;

Using default_transaction_read_only parameter, user can also make a Session Readonly by executing following command:

set default_transaction_read_only=on;

I hope above would help someone who is interested in having READ-ONLY user,database or backupuser.

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

Method 1 You can check the mode of the server using «pg_controldata». [pgsql@test~]$ pg_controldata /usr/local/pgsql/data84/ Database cluster state: in archive recovery —> This is Standby Database Database cluster state: in production —> This is Production Database [Master] Method 2 You can use pg_is_in_recovery() which returns True if recovery is still in progress(so the server is running in standby mode or slave) postgres=# select pg_is_in_recovery(); pg_is_in_recovery ——————- t (1 row) If Return false so the server is running in primary mode or master postgres=# select pg_is_in_recovery(); pg_is_in_recovery ——————- f (1 row)

How to Return a Result Set from a PostgreSQL Stored Procedure

Both stored procedures and user-defined functions are created with CREATE FUNCTION statement in PostgreSQL. To return one or more result sets (cursors in terms of PostgreSQL), you have to use refcursor return type. Quick Example : — Procedure that returns a single result set (cursor) CREATE OR REPLACE FUNCTION show_cities ( ) RETURNS refcursor AS $$ DECLARE ref refcursor; — Declare a cursor variable BEGIN OPEN ref FOR SELECT city , state FROM cities; — Open a cursor RETURN ref; — Return the cursor to the caller END ; $$ LANGUAGE plpgsql; Overview : Return Multiple Result Sets Cursor Lifetime Until the end of transaction Auto-commit Must be off Transaction must be active so the caller can see a result set Important Note : The cursor remains open until the end of transaction, and since PostgreSQL works

PostgreSQL New version upgrade

In this blog we are going to upgrade the postgresql server from 9.5 to 11.3 . We are upgraded the postgres server by using pg_upgrade utility as well as logical backup method , you can follow anyone of the method . Collect the server details before proceed upgrade activity. Title PostgreSQL VERSION 9.5 PostgreSQL VERSION 11.3 DATA DIRECTORY /data_9.5 /data_11.3 BIN path /opt/postgresql/9.5 /opt/postgresql/11.3 PORT 50000 5432 PREREQUEST : =========== Step 1. Check the application backend connection if any application connected , Disconnect the application from DB server. Below command will be helpful to checking backend connection : template1=# select datname, count(*) from pg_stat_activity group by datname; datname | count ————+——- testdb | 1 template1 | 0 (2 rows) template1=# select datname, numbackends from pg_stat_database; datname | numbackends ————+————- testdb | 1 template1 | 0 templ

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

In this post, I am sharing few important function for finding the size of database, table and index in PostgreSQL. Finding object size in postgresql database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in GB. The scripts have been formatted to work very easily with PUTTY SQL Editor. 1. Checking table size excluding table dependency: SELECT pg_size_pretty(pg_relation_size(‘mhrordhu_shk.mut_kharedi_audit’)); pg_size_pretty —————- 238 MB (1 row) 2. Checking table size including table dependency: SELECT pg_size_pretty(pg_total_relation_size(‘mhrordhu_shk.mut_kharedi_audit’)); pg_size_pretty —————- 268 MB (1 row) 3. Finding individual postgresql database size SELECT pg_size_pretty(pg_database_size(‘db_name’)); 4. Finding individual table size for postgresql database -including dependency index: SELECT pg_size_pretty(pg_total_rel

Linux ctime,mtime,atime,cmin,amin,mmin

Unix and therefore Linux uses (at least) 3 different timestamps on modern file systems (see File systems Table for info) in order to date any files. You can use these information to search for files, check logs, manage your backup and more… that’s why it is a must for any sysadmin to clearly understand this mechanism. This page is aimed at exposing basics knowledge to understand and use files timestamps. 1) Definitions Here are some time stamps related definitions. 1.1 atime This is the Access time : atime is updated whenever file’s data is accessed (a read, write or any other access); this could be done by a system process, interactively by CLI or by a script. 1.2 mtime This is the Modification time : mtime is updated whenever the file’s content changes. This time stamp is not updated by a change of files permissions (e.g : through a chown command). It is usually used for tracking the file content changes (see the Linux time related tools section below for more inf

Источник

5 ответов

Минуточку внимания

CHANGE HISTORY

PostgreSQL: Script to Create a Read-Only Database User

Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

How to Return a Result Set from a PostgreSQL Stored Procedure

PostgreSQL New version upgrade

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

Linux ctime,mtime,atime,cmin,amin,mmin