Read Only Database
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Read Only Database
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.
Example of making a DB read-only:
That INSERT worked, however, all new sessions will be READ ONLY:
To set the database back to READ WRITE, you first have to change the SESSION CHARACTERISTICS:
That is, this command will fail:
So you gotta do this:
Thanks!
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)
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)
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
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
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
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.