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.

You can prepare an empty database to be a target for restore operations by creating it as follows:

premdb=# create database premdb_restored hot_standby on;
CREATE DATABASE

As long as a database remains empty, you can alter it to a 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.

A 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

A 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.

After you have put a 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

The following table summarizes the attributes of each type of database:


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.