Read-only PostgreSQL instance

[gdsotirov]

Hello,

For the purpose of migration I need to make a Cloud SQL PostgreSQL instance read-only. I so far tried running the following statement:

ALTER SYSTEM SET default_transaction_read_only TO on;

in the following ways:

• as the postgres user (which fails with ERROR: must be superuser to execute ALTER SYSTEM command); and
• by importing (e.g. gcloud sql import sql) a file (with the statement above) from a bucket (which fails with ERROR: ALTER SYSTEM cannot run inside a transaction block).

So what is the correct way to make a PostgreSQL instance read-only in Cloud SQL?

Best regards,
--
Georgi

[anjelisa]

Hi @gdsotirov,

Welcome to Google Cloud Community!

Remove the ALTER SYSTEM and run this command instead,

set default_transaction_read_only=on;

You can only use ALTER when you have a user/role with SUPERUSER privilege and to grant a SUPERUSER attribute one must also possess the said role.
I just also want to let you know that alert system set default_transaction_read_only=off; and set default_transaction_read_only=off; will put a PostgreSQL primary instance into a “read-only” mode temporarily.

Let me know if I have addressed your problem.

[gdsotirov]

Hello @anjelisa,

Thanks for answering my question. I tried what you suggest (i.e. imported set default_transaction_read_only=on;), but even though the import passed without error when I then ran the following:

postgres=> SHOW default_transaction_read_only;
default_transaction_read_only
-------------------------------
off
(1 row)

It did not seem the change was applied, so can you please, clarify what exactly you meant by "temporarily"?

Best regards,
--
Georgi

[anjelisa]

Hi @gdsotirov,

Try it from the cloud shell, use command 

gcloud sql connect your-instance --user=postgres

then connect as the postgres user:

What I meant by temporarily is it will set the characteristics of the current transaction and has no effect on any subsequent transactions.

[gdsotirov]

OK, so what you suggest is setting default_transaction_read_only on session level, which does not answer my need. I'd like to make the whole instance read-only and apparently this is not possible.

[pivanof]

You can set default_transaction_read_only on a database or user level using `ALTER DATABASE <db> SET default_transaction_read_only TO on` or `ALTER ROLE <user> SET default_transaction_read_only TO on`.

[gdsotirov]

Yes, (I tried it and) I can, but I'm searching for a way to make the whole instance read only, instead of making all the databases and users read only.

[kade_chen]

May I ask what the follow-up progress is