GP - Play with partition

List of tables that are partitioned and total number of partitions.

 SELECT distinct m.schemaname||'.'||m.tablename "Relation Name", CASE WHEN p.tolpart IS NULL THEN 0 ELSE p.tolpart END AS "Total Parition", CASE WHEN s.tolsubpart IS NULL THEN 0 ELSE s.tolsubpart END AS "Total Subpartitions" FROM pg_partitions m LEFT JOIN (SELECT schemaname,tablename,count(*) tolpart FROM pg_partitions WHERE parentpartitiontablename IS NULL group by schemaname,tablename ) p ON p.schemaname=m.schemaname AND p.tablename=m.tablename LEFT JOIN (SELECT schemaname,tablename,count(*) tolsubpart FROM pg_partitions WHERE parentpartitiontablename IS NOT NULL group by schemaname,tablename) s ON s.schemaname=m.schemaname AND s.tablename=m.tablename ORDER BY 1;

 If you are in need to get details of a specified table. Below sql will help in getting details;

 SELECT p.schemaname||'.'||p.tablename "Relation Name", p.partitionschemaname||'.'||p.partitiontablename "Partition Table", p.partitionname "Name", p.partitiontype "Type", c.columnname "Partition Column", p.partitionlevel "Level", p.partitionrank "Rank", p.partitionposition "Position", CASE WHEN p.partitionisdefault='t' then 'YES' ELSE 'NO' END AS "Default" FROM pg_partitions p , pg_partition_columns c WHERE c.schemaname=p.schemaname AND c.tablename=p.tablename AND c.partitionlevel=p.partitionlevel AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>');

If you are in need to get details of a table which is of `range` partition, Below sql will help in getting details;

 SELECT p.schemaname||'.'||p.tablename "Relation Name", p.partitionschemaname||'.'||p.partitiontablename "Partition Table", p.partitionname "Name", p.partitiontype "Type", c.columnname "Partition Column", p.partitionrangestart "Start Range", CASE WHEN p.partitionstartinclusive='t' then 'YES' ELSE 'NO' END AS "Start Include", p.partitionrangeend "End Range", CASE WHEN p.partitionendinclusive='t' then 'YES' ELSE 'NO' END AS "End Include", CASE WHEN p.partitionisdefault='t' then 'YES' ELSE 'NO' END AS "Default" FROM pg_partitions p, pg_partition_columns c WHERE c.schemaname=p.schemaname AND c.tablename=p.tablename AND  c.partitionlevel=p.partitionlevel AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>');

 If you are in need to get details of a table which is of `list` partition, Below sql will help in getting details;

 SELECT p.schemaname||'.'||p.tablename "Relation Name", p.partitionschemaname||'.'||p.partitiontablename "Partition Table", p.partitionname "Name", p.partitiontype "Type", c.columnname "Partition Column", p.partitionlistvalues "List Values", CASE WHEN p.partitionisdefault='t' then 'YES' ELSE 'NO' END AS "Default" FROM pg_partitions p , pg_partition_columns c WHERE c.schemaname=p.schemaname AND c.tablename=p.tablename AND  c.partitionlevel=p.partitionlevel AND (p.schemaname,p.tablename)=('<schemaname>','<tablename>');

Comments

Post a Comment

Popular posts from this blog

How to set Optimizer at database level in greenplum

How can I turn ON optimizer at Database level? You can set the optimier ON at database level using below command; ALTER DATABASE databasename SET OPTIMIZER = ON; Once this is done, kindly do reconnect to Database to see the change. How do I check if optimizer is set (ON/OFF) in a given database? You can use the show command as below to check psql -d databasename -c 'show optimizer'
Read more

Changing the gpmon Password

Greenplum Command Center manages the gpperfmon database using the gpmon database role. To change the gpmon password, follow these steps: 1. Log in to Greenplum Database as a superuser and change the gpmon password with the ALTER ROLE command: # ALTER ROLE gpmon WITH PASSWORD 'new_password'; 2. Update the password in the .pgpass file, which is used by Greenplum Command Center. The default location for this file is the gpadmin home directory (~/.pgpass). The .pgpass file contains a line with the gpmon password. Replace the existing password with the new password. *:5432:gpperfmon:gpmon:new_password The file should be owned by gpadmin and RW-accessible by gpadmin only. 3. Restart Greenplum Command Center with the gpcmdr utility. $ gpcmdr --restart
Read more

IPMI Tool | IPMI Cheatsheet

To have a better understanding about IPMI Explanation: The Intelligent Platform Management Interface (IPMI) is a standardized computer system interface used by System Administrators for out-of-band management of computer systems and monitoring of their operation. Out-of-band is also referred to the LOM (Lights-Out Management) involves the use of a dedicated management channel for device maintenance. A numbers of options are available to manage the device remotely. There are vendor-specific server management technologies available. HP’s Integrated Lights-out or iLO, Dell’s Dell Remote Access Controller or DRAC and IBM’s Remote Supervisor Adapter (RSA) are a few to list. The above listed modules are vendor specific and proprietary. IPMI whereas is vendor-neutral and cross-platform. It is available on Linux distros. The functionality of IPMI can be accessed via IPMItool. IPMItool is a command prompt, which is used to manage IPMI-enabled devices. IPMItool helps in managing the
Read more