FAQ - Greenplum Administration for DBA Part I (General)

Products

VMware Tanzu Greenplum

Issue/Introduction

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

Resolution

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

The Describe table shows the distribution details:

 gpdb=# \d sales
 Table"public.sales"
 Column "  Type   " Modifiers
--------+---------+-----------
      id     " integer "    
      date   " date    "   
Distributed by: (id)

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

Use "\dn" at psql prompt.

3. When were my tables last analyzed in Greenplum?

Check pg_stat_operations for all actions performed on any object.

For example, the sales table:

gpdb=# SELECT * FROM pg_stat_operations WHERE objid = 'public.sales'::regclass;
 classname | objname | objid | schemaname | usestatus | usename | actionname | subtype  |            statime            
-----------+---------+-------+------------+-----------+---------+------------+----------+-------------------------------
 pg_class  | sales   | 16412 | public     | CURRENT   | gpadmin | CREATE     | TABLE    | 2024-04-22 14:57:45.645929+01
 pg_class  | sales   | 16412 | public     | CURRENT   | gpadmin | VACUUM     | TRUNCATE | 2024-04-22 15:48:12.334265+01
 pg_class  | sales   | 16412 | public     | CURRENT   | gpadmin | TRUNCATE   |          | 2024-04-22 15:48:12.334383+01
 pg_class  | sales   | 16412 | public     | CURRENT   | gpadmin | ANALYZE    |          | 2024-04-22 15:48:20.497711+01
(4 rows)

4. How to check the size of a table?

-- Table level

gpdb=# SELECT pg_size_pretty(pg_relation_size('schema.tablename'));

Replace schema.tablename with your search table.

-- Table size including size of indexes on the table

gpdb=# SELECT pg_size_pretty(pg_total_relation_size('schema.tablename'));

Replace schema.tablename with your search table.

5. How to check the Schema size?

gpdb=# 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:

gpdb=# SELECT pg_size_pretty(pg_database_size('DATABASE_NAME'));

Replace DATABASE_NAME with your database name.

Example:

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

-- To see all database sizes:

gpdb=# 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 reports table size for "public.employee_daily", which includes partitions.

gpdb=# SELECT schemaname, tablename, round(sum(pg_total_relation_size(schemaname "" '.' "" partitiontablename))/1024/1024) "MB"
FROM pg_partitions
WHERE tablename='employee_daily' AND schemaname = 'public'
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 PORT -h HOST

where PORT is the database port and HOST is hostname where the database is running.

10. Where and How to find db logs?

-- Master/Coordinator

Pre Greenplum 7.x : Master log files are located in the $MASTER_DATA_DIRECTORY/pg_log/

Greenplum 7.x and above: Coordinator log files are located in $COORDINATOR_DATA_DIRECTORY/log/

-- Segments

Find the hosts and locations of the logs with psql query: select content,hostname,datadir from gp_segment_configuration where role='p';

The "pg_log" or "log" directory in the reported "datadir" directory on the reported "host" will contain the logs for the segment.

The log file names depend on the "log_filename" GUC.

Also see the directory ~/gpAdminLogs/ directory on the hosts for logs of commands run on cluster.

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]:----------------------------------------------------------
gpadmin-[INFO]:-GPDB fault action value = readonly
gpadmin-[INFO]:-Valid count in status view = 4
gpadmin-[INFO]:-Invalid count in status view = 0
gpadmin-[INFO]:----------------------------------------------------------

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
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] dbowner ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ TABLESPACE [=] tablespace ]
           [ CONNECTION LIMIT [=] connlimit ] ]

-- Using createdb utility

Usage: $GPHOME/bin/createdb --help
  createdb [OPTION]... [DBNAME] [DESCRIPTION]
Options:
  -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
Syntax:DROP DATABASE [ IF EXISTS ] name

Also check dropdb utility:

$GPHOME/bin/dropdb --help
dropdb removes a PostgreSQL database.
Usage:
  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. Where is the location to download the GPDB products?

You can find all the products once you are logged into https://support.broadcom.com/ and click on "My Downloads".

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

Refer to the https://support.broadcom.com/group/ecx/productlifecycle for EOGS and EOL dates of Greenplum products.

Additional Information

For more detailed information please refer to the Greenplum Administrator's Guide.