Product Documentation

Appearance

ALTER DATABASE ​

Alter attributes of an existing physical database.

ALTER DATABASE name [ [ WITH ] ALLOW_CONNECTIONS { true | false }  ]
ALTER DATABASE name CONNECTION LIMIT number
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER DATABASE name RESET MAX_SIZE
ALTER DATABASE name SET HOT_STANDBY ON | OFF
ALTER DATABASE name SET MAX_SIZE [=] 'size'
ALTER DATABASE name SET MAX_SIZE DISABLE
ALTER DATABASE name SET READONLY ON [ WITH [ IMMEDIATE | TIMEOUT n { SECOND | SECONDS | MINUTE | MINUTES } ] ] | OFF

See also ALTER DATABASE ADD REPLICA, ALTER DATABASE ALTER REPLICA, and ALTER DATABASE DROP REPLICA.

ALLOW_CONNECTIONS

Specify whether the database accepts user connections: true or false.

CONNECTION LIMIT

Set a limit on the number of concurrent database connections by regular users (superuser connections do not count). Some of these connections may be used up by internal database services.

RENAME TO

Rename a database. You cannot rename the current database (the database you are connected to) or any database that is currently involved in replication.

OWNER TO

Change the owner of a database. See CURRENT_USER and SESSION_USER.

RESET MAX_SIZE

Disable the disk usage limit for the specified database. Similar to ALTER DATABASE name SET MAX_SIZE DISABLE.

SET HOT_STANDBY ON | OFF

ON: Prepare an empty database to serve as a target for a restore operation or replication. See HOT_STANDBY and READONLY Modes.

OFF: Take a database out of HOT_STANDBY mode because you no longer want to use it for incremental restores or replication.

SET MAX_SIZE [=] 'size'

Specify the maximum amount of space an individual database may use, where 'size' is the number of bytes. 'size' must be a positive integer with the possible inclusion of units: MB, GB, or TB. If units are included, the command will be accepted with and without a space between the integer and unit.

SET MAX_SIZE DISABLE

Disable the disk usage limit for the specified database. Similar to ALTER DATABASE name RESET MAX_SIZE.

SET READONLY ON | OFF

Put a database in READONLY mode and prevent new writes to the database. By default, the system waits for any running read-write transactions to complete. Optionally, you can specify WITH IMMEDIATE or a TIMEOUT of some number of seconds or minutes.

WITH IMMEDIATE causes any running write transactions to be rolled back. A TIMEOUT setting waits the specified number of seconds or minutes for read-write transactions to complete, then aborts any remaining read-write transactions. The database is set to READONLY immediately if no read-write transactions are running.

See HOT_STANDBY and READONLY Modes.

You can also set READONLY mode to OFF. The WITH IMMEDIATE and TIMEOUT options do not apply in this case.

The database mode is reflected in the results of ybsql \l command and logged to the sys.database view.

Note: If a restore operation is in progress for the database named in the ALTER DATABASE command, the command has to wait for an exclusive system catalog lock. The ALTER DATABASE command will proceed when the lock being held by the restore operation is released.

Examples ​

For example, attempt to alter the name of the current database, then switch to another database and retry.

shakespeare=# alter database shakespeare rename to shakes;
ERROR:  current database cannot be renamed
shakespeare=# \c premdb
You are now connected to database "premdb" as user "brumsby".
premdb=# alter database shakespeare rename to shakes;
ALTER DATABASE

Change the owner of the premdb database:

premdb=# alter database premdb owner to bobr;
ALTER DATABASE

Disable connections to the yellowbrick database:

premdb=# alter database yellowbrick allow_connections false;
ALTER DATABASE
premdb=# \c yellowbrick
FATAL:  database "yellowbrick" is not currently accepting connections
Previous connection kept

Put a database into HOT_STANDBY mode:

premdb=# alter database newpremdb set hot_standby on;
ALTER DATABASE

Put a database into READONLY mode after waiting for 10 minutes for read-write transactions to complete:

premdb=# alter database premdb_secure_all set readonly on with timeout 10 minutes;
ALTER DATABASE

Turn off HOT_STANDBY mode for a database that was in use for replication:

yellowbrick=# alter database premdb set hot_standby off;
WARNING:  This database will no longer accept restore and replication operations
ALTER DATABASE

Set a quota for the newpremdb database:

premdb=# alter database newprembd set max_size='1TB'; 
ALTER DATABASE

Parent topic:SQL Commands