Make a Database Read Only

Jim Knicely

The YugabyteDB YSQL (PostgreSQL compatble) API supports the creation of multiple databases. You can create a new database using the CREATE DATABASE command.

				
					yugabyte=# CREATE DATABASE my_database;
CREATE DATABASE

yugabyte=# \c my_database
You are now connected to database "my_database" as user "yugabyte".

my_database=# CREATE TABLE test (c1 INT);
CREATE TABLE

my_database=# INSERT INTO test VALUES (1), (2), (3);
INSERT 0 3

There are varous reasons why you may want one or more databases to be read only.

Examples include:

  1. Data Integrity and Security

  2. Backup and Archival

  3. Reporting and Analytics

  4. Load Balancing: 

  5. Compliance

  6. Testing and Development

  7. Disaster Recovery

We can make a make a database read only by setting the default_transaction_read_only configuration parameter to true or on.

				
					yugabyte=# ALTER DATABASE my_database SET default_transaction_read_only = on;
ALTER DATABASE

my_database=# INSERT INTO test VALUES (1), (2), (3);
INSERT 0 3

Wait, why was I able to run that INSERT? It’s because the ALTER DATABASE command does not affect any sessions already connected to the database, only new connections.

				
					my_database=# \c my_database;
You are now connected to database "my_database" as user "yugabyte".

my_database=# INSERT INTO test VALUES (1), (2), (3);
ERROR:  cannot execute INSERT in a read-only transaction

If you decide to allow the database to start accepting writes again, simply set the default_transaction_read_only configuration parameter to false or off.

				
					my_database=# ALTER DATABASE my_database SET default_transaction_read_only = off;
ERROR:  cannot execute ALTER DATABASE in a read-only transaction
Oops! If you want to alter the read only database in which you are connected, you first have to set the default_transaction_read_only configuration parameter to false or off at the session level. 
				
					my_database=# SET default_transaction_read_only = off;
SET

my_database=# ALTER DATABASE my_database SET default_transaction_read_only = off;
ALTER DATABASE

my_database=# INSERT INTO test VALUES (1), (2), (3);
INSERT 0 3

Have Fun!