Greenplum Create Database Error and Resolution

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

Pivotal Greenplum is one of widely used MPP relational databases. You can handle huge amount of data with Greenplum. Sometimes, it is very difficult to create a database in the Greenplum server because of connection issue. In this article, we will check how to resolve Greenplum create database error and steps required to resolve.

Greenplum Create Database Error

Below are the most common database errors that you may face while creating a database on Greenplum server.

  • psql: FATAL: database “gpadmin” does not exist
  • ERROR: source database “template1” is being accessed by other users

Now let us check what are these errors in detail.

psql: FATAL: database “gpadmin” does not exist

The first error is because, by default, “gpadmin”, database is not created when you set up the Greenplum server. So when you try to connect to non-existent Greenplum ‘gpadmin’ database either using an application or psql client, you will end up getting database not found fatal error.

ERROR: source database “template1” is being accessed by other users

You will probably get this kind of error when you try to create any new database from psql. There will be three databases already created for you, template0, template1, or postgres.

Initially, when you try to connect to Greenplum using the psql client, you should provide a database name to which you are trying to connect. You may end up getting “source database “template1” is being accessed by other users” when you try to create new a database by connecting to template1 database.

Database template1 exists only to provide structure to create another empty database. You should never log on to template1, otherwise you will have problems.

Below are the steps that you can follow to resolve this issue.

  • Restart Greenplum server. When the server is up connect to Postgres database and create new database.
  • If restarting is not an option, you can use another emergency template database: template0.
  • If the second option fails, it might, probably. then Third option would be kill the sessions that are connected to template0, or template1.

Kill Greenplum Sessions

You can follow below commands to kill Greenplum sessions that are connected to either template0 or template1.

  • Query pg_stat_activity table to identify the active or idle sessions. For example
postgres=# select datname, procpid  FROM pg_stat_activity  WHERE usename='gpadmin';
  datname  | procpid
-----------+---------
 postgres  |   24949
 template1 |   29232
 template1 |   29233
  • Kill background sessions using pg_terminate_backend. Connect to postgres database and kill other sessions.
 postgres=# select pg_terminate_backend(24967);
 pg_terminate_backend
----------------------
 t
(1 row)

postgres=# select pg_terminate_backend(24973);
 pg_terminate_backend
----------------------
 t
(1 row)
  • Now, you can create new database

Related Articles

Hope this helps 🙂

Tags: ,