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