Greenplum Create, Rename, Drop Database and Examples

  • Post author:
  • Post last modified:September 4, 2019
  • Post category:Greenplum
  • Reading time:5 mins read

You can create, drop or rename the database in Greenplum using respective commands. These are some important commands you should know if you are working as a Greenplum database administrator. In this article, we will check Greenplum create, drop, rename database commands and some of the examples.

Greenplum Create, Rename, Drop Database

Creating, altering or dropping database would be your daily job if you are a database administrator.

Below are some of the important commands that might help you.

Greenplum Create Database

CREATE DATABASE creates a new database. To create a database, you must be a superuser or have the special CREATEDB privilege.

For example, Below is the example to create database in Greenplum:

postgres=# create database test_db;
CREATE DATABASE

Greenplum Create Database Error

Sometimes, when you try to create new database in Greenplum, you may end up getting error something like “ERROR: source database “template1” is being accessed by other users“. This error is because default databases such as “Template0” or “Template1” are being used and you cannot create a new database with an active connection to default template databases as these databases are shared across all created databases.

Follow my other post Greenplum Create Database Error and Resolution to resolve the issue during greenplum database creation.

Greenplum Alter Database

ALTER DATABASE changes the attributes of a database. You can rename Greenplum database using this command.

Below are the syntax:

ALTER DATABASE name [ WITH CONNECTION LIMIT connlimit ]

ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }

ALTER DATABASE name RESET parameter

ALTER DATABASE name RENAME TO newname

ALTER DATABASE name OWNER TO new_owner

For example, rename already created database using alter database command:

postgres=# ALTER DATABASE test_db RENAME TO testdb;
ALTER DATABASE

Note that, You cannot alter database to which you are already connected. First, connect to a different database and then execute alter database command.

Greenplum List Databases

You can use ‘\l’ command to list all database available in Greenplum server.

For example, list all databases.

postgres=# \l
                  List of databases
   Name    |  Owner  | Encoding |  Access privileges
-----------+---------+----------+---------------------
 gpadmin   | gpadmin | UTF8     |
 postgres  | gpadmin | UTF8     |
 template0 | gpadmin | UTF8     | =c/gpadmin
                                : gpadmin=CTc/gpadmin
 template1 | gpadmin | UTF8     | =c/gpadmin
                                : gpadmin=CTc/gpadmin
 testdb    | gpadmin | UTF8     |
(5 rows)

Drop Database

You can drop database using DROP DATABASE command on the psql command line interface.

For example, drop above created database.

postgres=# DROP DATABASE testdb;
DROP DATABASE

Note that, You cannot drop database to which you are already connected. First, connect to a different database and then execute drop database command.

Related Article

Hope this helps 🙂

Tags: ,