0

Is any simple function/command for PostgreSQL 9.3 to set whole database to read only mode and then back to RW mode?

For example same as for MySQL:

FLUSH TABLES WITH READ LOCK;
LOCK TABLES customers READ;
UNLOCK TABLES;

or

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = 1;
SET GLOBAL read_only = 0;
UNLOCK TABLES;

Thanks.

3
  • 1
    Why would you want to do that? What problem are you trying to solve with that? Btw: 9.3 is no longer supported anyway. Why stay on such an old version?
    – user1822
    Jul 26, 2020 at 21:33
  • @a_horse_with_no_name I need fix problem due migration, because there are lot of new orders due this migration, so when DNS will be changed, I need stop activity about orders on the web and do dump/restore of actual state from old DB, then turn on writing to DB. I know PG 9.3 is old version, but unfortunately application is too old, tested on higher version, but it is not working.
    – genderbee
    Jul 26, 2020 at 21:47
  • How can you "restore" anything, if the instance is set to "read only"? But you could simply disallow connections. Either through pg_hba.conf or through altering the database in question
    – user1822
    Jul 27, 2020 at 5:29

1 Answer 1

3

default_transaction_read_only might work for your case.

The command below will make new sessions start their transactions in read-only mode:

ALTER DATABASE name_of_database SET default_transaction_read_only to ON;

and to go back to normal:

ALTER DATABASE name_of_database SET default_transaction_read_only to OFF;

This takes effect for new sessions, not for sessions that are already connected.

The effect can also be obtained at the user level (which might be more convenient in some cases):

ALTER USER name_of_user SET default_transaction_read_only to ON;

While in read-only mode, a session can always issue SET default_transaction_read_only to OFF; to get out of this mode, or start any individual transaction with BEGIN read write;. But in general programs don't do that, so that default_transaction_read_only is sufficient to deny them the ability to write.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.