DBARefGPDBlib - Over 200 utility functions. Smarter way to manage and use Greenplum database. - Coming Soon!!!!! 

Greenplum DBA References

  • Finding Bad table name in Greenplum Due to some bug in the older version of greenplum some of table contains (-) or (.) in the name.  You can find all those tables names with following SQL.select relname ...
    Posted May 27, 2022, 11:58 AM by Sachchida Ojha
  • Understanding Greenplum Bloat on heap, AO and AO columnar tables by examples and how to remove it. Step 1: Create heap, ao and ao columnar (column oriented) table drop table IF EXISTS sachi.vac_test_heap; drop table IF EXISTS sachi.vac_test_ao; drop table IF ...
    Posted Apr 22, 2022, 3:14 PM by Sachchida Ojha
  • MADLib and MADLib pivot function Apache MADlib is a SQL-based open source library for scalable in-database analytics that supports Greenplum Database. The library offers data scientists numerous distributed implementations of mathematical, statistical and ...
    Posted Oct 6, 2016, 12:25 PM by Sachchida Ojha
  • Checking list of security definer functions in GPDB testdb=# \d pg_proc       Table "pg_catalog.pg_proc"     Column     |   Type    | Modifiers ----------------+-----------+----------- proname        | name      | not null pronamespace   | oid       | not null proowner       | oid       | not null prolang        | oid       | not null proisagg       | boolean ...
    Posted May 11, 2016, 4:55 AM by Sachchida Ojha
  • Connecting as another user without their password In Oracle we use to run (from privileged user)alter session set current_schema=abc;Lets discuss how we can achieve same thing in Greenplum using SET ROLE and with ...
    Posted Jan 15, 2016, 12:41 AM by Sachchida Ojha
  • Enable PQO for a specific user/role gpadmin=# select * from pg_roles where rolname='sachi';-[ RECORD 1 ]-----+---------rolname           | sachirolsuper          | frolinherit        | trolcreaterole     | frolcreatedb       | frolcatupdate      | frolcanlogin       | frolconnlimit      | -1rolpassword       | ********rolvaliduntil     | rolconfig         | rolresqueue ...
    Posted May 20, 2016, 5:53 PM by Sachchida Ojha
  • Enable PQO (Pivotal Query Optimizer) at the database level Step 1: Check the status of the optimizergpadmin=# show optimizer; optimizer ----------- off(1 row)gpadmin=# show optimizer_control; optimizer_control ------------------- on(1 row)Step 1: Check state of the ...
    Posted May 20, 2016, 5:52 PM by Sachchida Ojha
  • Select runs but insert failing in Greenplum database Few days back I faced a unique problem. In one environment of Greenplum database system, inserts on a table (ITAS) was failing but select was not. When I executed the ...
    Posted May 20, 2016, 5:52 PM by Sachchida Ojha
  • New Parameters in GPDB 4.3 and onward There were 22 new parameters introduced in Greenplum starting GPDB 4.3.0. Mostly these parameters are related to specific features. Click here to learn more about these 22 parameters
    Posted May 27, 2015, 4:13 PM by Sachchida Ojha
  • List Append Only / Append Organized table details in Greenplum sachi=# SELECT n.nspname AS schema_name ,c.relname AS table_name FROM pg_catalog.pg_appendonly a ,pg_catalog.pg_class c ,pg_catalog.pg_namespace n WHERE c ...
    Posted Sep 14, 2018, 10:48 AM by Sachchida Ojha
  • Spill or workfile configuration parameters Greenplum introduced 2 server configuration parameter. 1. gp_workfile_limit_per_query – This sets the maximum disk size (in kilobytes) an individual query is allowed to use for creating temporary ...
    Posted May 25, 2015, 9:17 AM by Sachchida Ojha
  • Summary Of New Features in Greenplum Database 4.3.X.X Summary of new Features (Family 4.3.X.X)For details, visit http://www.greenplumdba.com/greenplum-database---new-features 1. Query Optimizer and Management of query execution (4.3 ...
    Posted May 20, 2016, 5:25 PM by Sachchida Ojha
Showing posts 1 - 12 of 219. View more »

Find freelance Oracle, PostgreSQL, Greenplum DBA, consultants and get your Job done remotely online and save upto 50%. 

Backup and Restore

  • gpcrondump and gpdbrestore [gpadmin@mdw ~]$ gpcrondump --help COMMAND NAME: gpcrondump Writes out a database to SQL script files. The script files can be used to restore the database using the gpdbrestore utility. The ...
    Posted Oct 17, 2016, 10:40 AM by Sachchida Ojha
  • How to copy the gpperfmon data from DCA1 to DCA2 Task:  copy the gpperfmon V1 data from DCA1 V1 to DCA2  V2.  Both DCA's are accessible from a commodity server.Here are the very simple steps. Since those tables ...
    Posted May 20, 2016, 5:48 PM by Sachchida Ojha
  • Dump files created during (gp_dump) greenplum parallel backup command Note: gp_dump is deprecated in later versions. use gpcrondump. Dump File Description In the master    gp_catalog_1_<dbid>_timestamp> System catalog tables  gp_cdatabase_1_<dbid>_timestamp> CREATE ...
    Posted May 20, 2016, 5:49 PM by Sachchida Ojha
  • GPDBRESTORE Example 1- backup set of tables and restore it to a new database a) Backup list of tables using gpcrondump and a specified location[gpadmin@sachi ~]$ gpcrondump  -c --table-file="tablist" -u /disk4/backup20150111:17:23:47:022685 gpcrondump:sachi:gpadmin-[INFO ...
    Posted May 20, 2016, 5:49 PM by Sachchida Ojha
  • Exploring gpdbrestore command Get the syntax[gpadmin@sachi ~]$ gpdbrestore --helpCOMMAND NAME: gpdbrestoreA wrapper utility around gp_restore. Restores a database from a set of dump files generated by gpcrondump.*****************************************************SYNOPSIS*****************************************************gpdbrestore ...
    Posted May 20, 2016, 5:50 PM by Sachchida Ojha
Showing posts 1 - 5 of 14. View more »