Read Only Database

Moderator: NorbertKrupa

Post Reply
User avatar
Josh
Intermediate
Intermediate
Posts: 106
Joined: Thu Jan 26, 2012 9:38 pm

Read Only Database

Post by Josh » Tue Jan 31, 2017 8:14 pm

Is there a way to make a Vertica database read only?
Thank you!
Joshua

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Read Only Database

Post by JimKnicely » Wed Feb 01, 2017 3:59 am

We were just talking about this at work the other day ...

There is a configuration parameter named TransactionMode that can be set at the Database or Node level telling Vertica whether a transaction is read/write or read-only.

Code: Select all

dbadmin=> SELECT parameter_name, database_value, allowed_levels, description
dbadmin->   FROM configuration_parameters WHERE parameter_name = 'TransactionMode';
parameter_name  | database_value | allowed_levels |                                        description
-----------------+----------------+----------------+-------------------------------------------------------------------------------------------
TransactionMode | READ WRITE     | NODE, DATABASE | Determines whether the transaction is read/write or read-only.  Read/write is the default
(1 row)
Example of making a DB read-only:

Code: Select all

dbadmin=> SELECT database_name FROM databases;
database_name
---------------
sfdc
(1 row)

dbadmin=> ALTER DATABASE sfdc SET TransactionMode = 'READ ONLY';
ALTER DATABASE

dbadmin=> INSERT INTO jim VALUES(1);
OUTPUT
--------
      1
(1 row)
That INSERT worked, however, all new sessions will be READ ONLY:

Code: Select all

dbadmin=> \q

[dbadmin@s18384357 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> INSERT INTO jim VALUES(1);
ERROR 2448:  Cannot issue this command in a read-only transaction
To set the database back to READ WRITE, you first have to change the SESSION CHARACTERISTICS:

That is, this command will fail:

Code: Select all

dbadmin=> ALTER DATABASE sfdc SET TransactionMode = 'READ WRITE';
ROLLBACK 2448:  Cannot issue this command in a read-only transaction
So you gotta do this:

Code: Select all

dbadmin=> SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED READ WRITE;
SET

dbadmin=> ALTER DATABASE sfdc SET TransactionMode = 'READ WRITE';
ALTER DATABASE
Thanks!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
Josh
Intermediate
Intermediate
Posts: 106
Joined: Thu Jan 26, 2012 9:38 pm

Re: Read Only Database

Post by Josh » Thu Feb 02, 2017 3:46 pm

Thanks, Jim! This is what I needed.
Thank you!
Joshua

Post Reply

Return to “New to Vertica Database Administration”