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
orfalse
. - 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 ofHOT_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 specifyWITH IMMEDIATE
or aTIMEOUT
of some number of seconds or minutes.
See HOT_STANDBY and READONLY Modes.WITH IMMEDIATE
causes any running write transactions to be rolled back. ATIMEOUT
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 toREADONLY
immediately if no read-write transactions are running.You can also set
READONLY
mode toOFF
. TheWITH IMMEDIATE
andTIMEOUT
options do not apply in this case.The database mode is reflected in the results of
ybsql \l
command and logged to thesys.database
view.
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
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
premdb
database:premdb=# alter database premdb owner to bobr;
ALTER DATABASE
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
HOT_STANDBY
mode:
premdb=# alter database newpremdb set hot_standby on;
ALTER DATABASE
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
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
newpremdb
database:premdb=# alter database newprembd set max_size='1TB';
ALTER DATABASE