HOT_STANDBY and READONLY Modes
A target database for restore operations or replication must be either created in
HOT_STANDBY
mode or set to HOT_STANDBY
mode. This mode
preserves the database in a state that allows it to accept incremental restores but not
other writes. If HOT_STANDBY
mode is turned off, the database no longer
accepts incremental restores.
premdb=# create database premdb_restored hot_standby on;
CREATE DATABASE
HOT_STANDBY
database:premdb=# alter database newpremdb set hot_standby on;
ALTER DATABASE
premdb=# \c newpremdb
You are now connected to database "newpremdb" as user "yb100".
newpremdb=# \d
No relations found.
newpremdb=# create table a(b int);
ERROR: cannot execute CREATE TABLE on a hot standby database
When you restore a database under a new name, and a database with that name does not exist
on the target system, the restore automatically creates the new database in
HOT_STANDBY
mode. You can use the --hot-standby OFF
option in the ybrestore
command if you want to override the default
behavior and take the database out of HOT_STANDBY
mode when the restore
operation is complete.
HOT_STANDBY
database is available for subsequent incremental restores
and replication cycles but not for other write operations. The database is also available
for read operations (it can be queried). When HOT_STANDBY
mode is turned
off (with ybrestore --hot-standby OFF
or an ALTER DATABASE
command), the database defaults to the behavior of a regular database. A
HOT_STANDBY
database cannot be
dropped:yellowbrick=# drop database premdb_restored;
ERROR: Database 'premdb_restored' is a hot standby
READONLY
database is similar to a HOT_STANDBY
database
but blocks restore operations in addition to other writes. A READONLY
database can be the source for backup and restore operations but not the target. You cannot
create a database in READONLY
mode, but you can alter a database to put it
into READONLY
mode. You should only change a database to this state when
you want to lock it down for some reason. A READONLY
database continues to
be useful for read query access but is frozen in time. HOT_STANDBY
database into HOT_STANDBY
OFF
or READONLY
state, you cannot alter it back to
HOT_STANDBY
state. For
example:premdb=# alter database premdb_restored set hot_standby on;
ERROR: A hot standby database must be empty when initialized
A single database can be in both states at once: HOT_STANDBY
and
READONLY
, in which case the overall restrictions on the database are
greater.
Operations Available in Each Mode
Operation | HOT_STANDBY State | READONLY State | Both States |
---|---|---|---|
DML: INSERT, DELETE, UPDATE, TRUNCATE | No | No | No |
DDL: CREATE, ALTER, DROP objects) | No | No* | No |
Create temporary objects | Yes | Yes | Yes |
CREATE EXTERNAL TABLE | No | Yes | No |
GRANT, REVOKE on objects | Yes | No | No |
GRANT, REVOKE on roles, users | Yes | Yes | Yes |
NEXTVAL function and ALTER SEQUENCE commands | No | No | No |
Statistics generation | Yes | No | No |
Backups | No | Yes | No |
Full restores | Yes (initial restore only) | No | No |
Incremental restores | Yes | No | No |
SELECT queries | Yes | Yes | Yes |
Replication reads | No | Yes | No |
Replication writes | Yes | No | No |
*When the current database is READONLY
, CREATE DATABASE
and DROP DATABASE
commands are allowed.