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:
a. \l+
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.
Syntax
ALTER DATABASE <database_name> OWNER TO <new_owner>;
Example
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.
Syntax
ALTER DATABASE <database_name> WITH IS_TEMPLATE = true;
Example
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.
Syntax
ALTER DATABASE <database_name> WITH ALLOW_CONNECTIONS = false;
Example
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.
Syntax
ALTER DATABASE <database_name> WITH CONNECTION LIMIT <max_number_of_connections>;
Example
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.
Syntax
ALTER DATABASE <database_name> SET TABLESPACE <custom_tablespace_name>;
Example
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.
Syntax
ALTER DATABASE <old_database_name> RENAME TO <new_database_name>;
Example
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.
Syntax
DROP DATABASE [ IF EXISTS ] <database_name>;
Example
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.
Syntax:
CREATE DATABASE <target_database> WITH TEMPLATE <source_database>;
For Example: CREATE DATABASE test21 WITH TEMPLATE test2;
