Linux x86-64 (RHEL 8)
Hrishikesh Gore Systems Engineer Dec 20, 2019
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.
1. ALTER DATABASE statements
a. Changing the owner of the database
b. Setting up the Template database
c. Restricting new connections for a specific database
d. Restricting the connection limit for a specific database
e. Changing the Default Tablespace for the database
2. RENAME DATABASE statements
3. DROP DATABASE statements
4. Copying database structure and data
This post looks at some of the available commands for managing PostgreSQL databases from PSQL prompt. We will review methods for altering, renaming, dropping, and copying a database.
Let's recap some basic PostgreSQL commands:
1. Database creation syntax:
CREATE DATABASE name [ [ WITH ] [ OWNER [=] user_name ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ LC_COLLATE [=] lc_collate ] [ LC_CTYPE [=] lc_ctype ] [ TABLESPACE [=] tablespace ] [ CONNECTION LIMIT [=] connlimit ] ]
2. To get a list of databases in the present cluster:
b. select * from pg_database ;
ALTER database statements
ALTER database statements can be used to change the existing attributes of the target database.
Changing the owner of the database
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.
ALTER DATABASE <database_name> OWNER TO <new_owner>;
ALTER DATABASE test OWNER TO user;
Setting up the template database
By default, we have only two template databases present in a cluster—template0 and template1. Other databases cannot be used as a template by default. In case you need to create a custom database template for your environment, you can use this option.
ALTER DATABASE <database_name> WITH IS_TEMPLATE = true;
ALTER DATABASE test WITH IS_TEMPLATE = true;
Restricting new connections for a specific database
This option is very useful for maintaining the database. You can restrict new connections to a specific database during the database maintenance window. To enable this option, no user is connected to the target database.
ALTER DATABASE <database_name> WITH ALLOW_CONNECTIONS = false;
ALTER DATABASE test2 WITH ALLOW_CONNECTIONS = false;
Restricting the connection limit for a specific database.
For security and optimal performance, you can limit the number of connections that can connect to the target database. By default, the value for this parameter is unlimited—i.e., -1—for every database.
ALTER DATABASE <database_name> WITH CONNECTION LIMIT <max_number_of_connections>;
ALTER DATABASE test2 WITH CONNECTION LIMIT 10;
Changing the default tablespace for the database
You can physically move default tablespace objects like tables and indexes from the default tablespace to a new custom tablespace. The new default tablespace must be empty for this database, and no one can be connected to the database during this activity.
Note: The owner of the database or a superuser must make this change, and the user must have CREATE privileges for the new tablespace.
ALTER DATABASE <database_name> SET TABLESPACE <custom_tablespace_name>;
ALTER DATABASE test SET TABLESPACE custom_tblspc;
RENAME database statements
You can change the current database’s name using RENAME. However, you cannot rename the database while it is being accessed by other connections.
Note: The owner of the database or superuser must make this change, and the user must have CREATEDB privileges.
ALTER DATABASE <old_database_name> RENAME TO <new_database_name>;
ALTER DATABASE test RENAME TO test123;
DROP Database statements
You can DROP the target database. This removes the catalog entries for the database and deletes the directory containing the data. It can only be executed by the database owner. Also, it cannot be executed while you or anyone else is connected to the target database.
Note: DROP DATABASE cannot be undone.
DROP DATABASE [ IF EXISTS ] <database_name>;
DROP DATABASE test2;
Copying database structure and data
You can copy the source database structure along with its database to the target database. No user should be connected to the source database during this activity.
CREATE DATABASE <target_database> WITH TEMPLATE <source_database>;
For Example: CREATE DATABASE test21 WITH TEMPLATE test2;