How to alter, rename, drop, and copy databases in PostgreSQL

 

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;