Also, it cannot be executed while you or anyone else is connected to the target database. It can only be executed by the database owner. This removes the catalog entries for the database and deletes the directory containing the data. Syntax ALTER DATABASE RENAME TO Įxample ALTER DATABASE test RENAME TO test123 Note: The owner of the database or superuser must make this change, and the user must have CREATEDB privileges. However, you cannot rename the database while it is being accessed by other connections. You can change the current database’s name using RENAME.
Syntax ALTER DATABASE SET TABLESPACE Įxample ALTER DATABASE test SET TABLESPACE custom_tblspc Note: The owner of the database or a superuser must make this change, and the user must have CREATE privileges for the new tablespace. The new default tablespace must be empty for this database, and no one can be connected to the database during this activity. You can physically move default tablespace objects like tables and indexes from the default tablespace to a new custom tablespace. Syntax ALTER DATABASE WITH CONNECTION LIMIT Įxample ALTER DATABASE test2 WITH CONNECTION LIMIT 10 Ĭhanging the default tablespace for the database By default, the value for this parameter is unlimited-i.e., -1-for every database.
Restricting the connection limit for a specific database.įor security and optimal performance, you can limit the number of connections that can connect to the target database. Syntax ALTER DATABASE WITH ALLOW_CONNECTIONS = false ĪLTER DATABASE test2 WITH ALLOW_CONNECTIONS = false To enable this option, no user is connected to the target database. You can restrict new connections to a specific database during the database maintenance window. This option is very useful for maintaining the database. Restricting new connections for a specific database Syntax ALTER DATABASE WITH IS_TEMPLATE = true Įxample ALTER DATABASE test WITH IS_TEMPLATE = true In case you need to create a custom database template for your environment, you can use this option. Other databases cannot be used as a template by default. Syntax ALTER DATABASE OWNER TO īy default, we have only two template databases present in a cluster-template0 and template1. To alter the owner of the database, the connected user should be the owner of the database and also be a direct or indirect member of the new owning role, and the connected user must have the CREATEDB privilege. To get a list of databases in the present cluster:ĪLTER database statements can be used to change the existing attributes of the target database. Database creation syntax: CREATE DATABASE nameĢ. Let's recap some basic PostgreSQL commands:ġ. We will review methods for altering, renaming, dropping, and copying a database. This post looks at some of the available commands for managing PostgreSQL databases from PSQL prompt. Changing the Default Tablespace for the database Restricting the connection limit for a specific databaseĮ.
Restricting new connections for a specific databaseĭ. SUMMARY: This article covers the ALTER DATABASE, RENAME DATABASE, and DROP DATABASE statements and using the CREATE DATABASE WITH TEMPLATE statement to copy a database.Ĭ.
0 kommentar(er)
