Pivotal Knowledge Base


FAQ - Greenplum Administration for DBA Part I (General)


Pivotal Greenplum Database (GPDB) all versions


In this article, we will take a look at some common frequently asked General DBA related question.


1. How to check distribution policy of a test table sales?

The Describe table sales shows the distribution details.

 psql>\d sales
 Column "  Type   " Modifiers
      id     " integer "    
      date   " date    "   
Distributed by: (id)

2. How many user schemas are there in the database?

Use "\dn" at psql prompt.

3. When are my tables last analyzed in Greenplum?

In 4.x check pg_stat_operations for all action-name performed on any object.

For example, a sales table:

gpdb=# select objname,actionname,statime from pg_stat_operations  where objname like 'sales';
objname " actionname "            statime                     
sales   " CREATE     " 2010-10-01 12:18:41.996244-07       
sales   " ANALYZE    " 2010-10-06 14:38:21.933281-07       
sales   " VACUUM     " 2010-10-06 14:38:33.881245-07

4. How to check the size of a table?

-- Table Level

psql> select pg_size_pretty(pg_relation_size('schema.tablename'));

Replace schema.tablename with your search table.

-- Table and Index

psql> select pg_size_pretty(pg_total_relation_size('schema.tablename'));   

Replace schema.tablename with your search table.

5. How to check the Schema size?

psql> select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Size_MB" from pg_tables where schemaname='SCHEMANAME' group by 1;

Replace SCHEMANAME with your schema name.

6. How to check the database size?

-- To see the size of a specific database,

psql> select pg_size_pretty(pg_database_size('DATBASE_NAME'));

Example: gpdb=# select pg_size_pretty(pg_database_size('gpdb'));
24 MB
(1 row)

-- To see all database sizes

psql> select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

7. How to check partitioned table size including indexes and partitions?

-- Table size with partitions The following SQL gives you employee_dailly table size, which includes partitions.

select schemaname,tablename,round(sum(pg_total_relation_size(schemaname "" '.' "" partitiontablename))/1024/1024) "MB"
from pg_partitions where tablename='employee_daily' group by 1,2;
schemaname " tablename      " MB
public     " employee_daily " 254

8. How do I get help with syntax to alter table?

In psql session type \h alter table which will display the syntax:

gpdb=# \h alter table

9. How to connect in utility mode? From master host

PGOPTIONS='-c gp_session_role=utility' psql -p  -h 

port is segment/ master database port.        
hostname is segment/master hostname.

10. Where/How to find db logs?

-- Master

Master gpdb logfile is located in the $MASTER_DATA_DIRECTORY/pg_log/ directory and the file name depends on the database "log_filename" parameter.

  1. $MASTER_DATA_DIRECTORY/pg_log/gpdb-yyyy-mm-dd_000000.csv -->Log file format with default installation.
  2. ~gpadmin/gpAdminLogs/ -->gpstart,gpstop,gpstate and other utility logs.

-- Segments

  1. primary segments run below SQL to see logfile location: select dbid,hostname,datadir""'/pg_log' from gp_configuration where content not in (-1) and isprimary is true;
  2. Mirror Segments run below SQL to see logfile location: select dbid,hostname,datadir""'/pg_log' from gp_configuration where content not in (-1) and isprimary is false;

11. How to see the list of available functions in Greenplum?

\df schemaname.functionname (schemaname and function name support wildcard characters)

test=# \df pub*.*test*
                       List of functions
 Schema "   Name      " Result data type " Argument data types
 public " bugtest     " integer          "
 public " test        " boolean          " integer
 public " test        " void             "
(3 rows)

12. How to check whether Greenplum server is up and running?

The gpstate is the utility to check gpdb status.

Use gpstate -Q to show a quick status. Refer to gpstate --help for more options.

Sample output

[gpadmin@stinger2]/export/home/gpadmin>gpstate -Q
gpadmin-[INFO]:-Obtaining GPDB array type, [Brief], please wait...
gpadmin-[INFO]:-Obtaining GPDB array type, [Brief], please wait...
gpadmin-[INFO]:-Quick Greenplum database status from Master instance only
gpadmin-[INFO]:-GPDB fault action value = readonly
gpadmin-[INFO]:-Valid count in status view = 4
gpadmin-[INFO]:-Invalid count in status view = 0

13. How to create a Database?

There are two ways to create gpdb database using psql session or the Greenplum "createdb utility.

-- Using psql session

gpdb=# \h create database
Description: create a new database
    [ [ WITH ] [ OWNER [=] dbowner ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ TABLESPACE [=] tablespace ]
           [ CONNECTION LIMIT [=] connlimit ] ]

-- Using createdb utility

Usage: $GPHOME/bin/createdb --help
  -D, --tablespace=TABLESPACE  default tablespace for the database
  -e, --echo                   show the commands being sent to the server
  -E, --encoding=ENCODING      encoding for the database
  -O, --owner=OWNER            database user to own the new database
  -T, --template=TEMPLATE      template database to copy
  --help                       show this help, then exit
  --version                    output version information, then exit

14. How do I get a list of databases in a greenplum cluster?

gpdb=# \ l  (lowercase letter "l")
       List of databases
   Name    "  Owner  " Encoding
 gpdb      " gpadmin " UTF8
 gpperfmon " gpadmin " UTF8
 postgres  " gpadmin " UTF8
 template0 " gpadmin " UTF8
 template1 " gpadmin " UTF8

Check below SQL for more details on dbs.

gpdb=# select * from pg_database;

15. How to delete/drop an existing database in Greenplum?

gpdb=# \h DROP Database
Command:     DROP DATABASE
Description: remove a database

Also check dropdb utility

$GPHOME/bin/dropdb --help
dropdb removes a PostgreSQL database.
  dropdb [OPTION]... DBNAME

16. Where can I get help on postgres psql commands?

In psql session

"\ ?"          - for all psql session help
"\h  "   For any SQL syntax help.

17. Are there scripts/tools available to manage Greenplum database?

Yes, you can find scripts at the location here and you can use support package tool mentioned here for additional Greenplum DBA checks.

18. Where is the location to download the GPDB products?

You can find all the product under the URL. If you need help on how to navigate to the site to download the products please refer to the article.

19. Where do you get GPDB products End of Life Cycle information?

Refer to the link for EOF of pivotal products 

Additional Information

Related articles:


  • Avatar
    olivier brun

    I think you have a problem of " | on this page:

    we have an error in these query:

    psql> select schemaname ,round(sum(pg_total_relation_size(schemaname""'.'""tablename))/1024/1024) "Size_MB"
    from pg_tables where schemaname='SCHEMANAME' group by 1;

    fixed by

    psql> select schemaname ,round(sum(pg_total_relation_size(schemaname||'.'||tablename))/1024/1024) "Size_MB"
    from pg_tables where schemaname='SCHEMANAME' group by 1;

  • Avatar
    Faisal Ali

    Thanks Olivier for the feedback , this has been fixed.

Powered by Zendesk