Gathering Information for Greenplum Support

The gpdetective utility collects information from a running Greenplum Database system and creates a bzip2-compressed tar output file. You can then send the output file to Greenplum Customer Support to aid the diagnosis of Greenplum Database errors or system failures.

 Run gpdetective on your master host, for example:

[08:53 sachi@sachi ~] > su - gpadmin

Password: 

[gpadmin@sachi ~]$ gpdetective

Collecting information

  + getConfiguration

  + copyLogs

  +     get master logs

  +     get primary segment logs

  +     get gpAdminLogs

  +     get primary segment pg_changetracking FILEREP_CONFIG_LOG and FILEREP_LOG  logs

  +     get mirror segment pg_changetracking FILEREP_CONFIG_LOG and FILEREP_LOG  logs

  + systemInfo

  + checkCatalog

  + pg_dumpall

  + crashReport

    + localhost

ls: cannot access |/usr/libexec/abrt-hook-ccpp: No such file or directory

ls: cannot access *: No such file or directory

ls: cannot access *: No such file or directory

ls: cannot access *: No such file or directory

ls: cannot access *: No such file or directory

ls: cannot access *: No such file or directory

ls: cannot access *: No such file or directory

ls: cannot access localhost: No such file or directory

ls: cannot access postgres: No such file or directory

ls: cannot access 636f726500*: No such file or directory

    + sachi

  + makeTar

Done

[gpadmin@sachi ~]$ ls -ltr

total 111232

-r--r--r--. 1 root    root        7008 Oct 17  2012 README_INSTALL

-rwxr-xr-x. 1 root    root    55684023 Oct 17  2012 greenplum-db-4.2.2.4-build-1-CE-RHEL5-x86_64.bin

-rw-r--r--. 1 root    root    54377435 Apr 23  2013 greenplum-db-4.2.2.4-build-1-CE-RHEL5-x86_64.zip

-rwxr-xr-x. 1 gpadmin gpadmin        6 Apr 23  2013 hostlist_singlenode

drwxrwxr-x. 3 gpadmin gpadmin     4096 Oct 27 23:56 share

-rwxrwxr-x. 1 gpadmin gpadmin     1983 Oct 27 23:56 pgcrypto_install.sh

drwxrwxr-x. 3 gpadmin gpadmin     4096 Oct 27 23:56 lib

-rw-rw-r--. 1 gpadmin gpadmin   144092 Nov 17 18:09 0

-rw-rw-r--. 1 gpadmin gpadmin       81 Nov 18 08:40 table_list.txt

-rw-r--r--. 1 gpadmin gpadmin   501760 Nov 19 20:07 pgcrypto-1.1.3.0-4609.x86_64.tar

drwxrwxr-x. 2 gpadmin gpadmin     4096 Nov 23 13:15 sachi

-rw-rw-r--. 1 gpadmin gpadmin     4515 Nov 27 11:41 gpinitsystem_singlenode.orig

-rw-rw-r--. 1 gpadmin gpadmin      292 Nov 27 12:50 gpinitsystem_singlenode

drwxrwxr-x. 2 gpadmin gpadmin     4096 Jan 23 08:53 gpAdminLogs

-rw-rw-r--. 1 gpadmin gpadmin  3133719 Jan 23 08:54 gpdetective20140123085342.tar.bz2

[gpadmin@sachi ~]$ 

Let's look at the content of the file.

[gpadmin@sachi ~]$ tar jxf gpdetective20140123085342.tar.bz2

It will create a directory gpdetective20140123085342 and bunch of files and directories under it.

[gpadmin@sachi ~]$ cd gpdetective20140123085342

[[gpadmin@sachi gpdetective20140123085342]$ ls -ltr

total 1268

-rw-rw-r--. 1 gpadmin gpadmin     231 Jan 23 08:53 version_at_initdb.log

-rw-rw-r--. 1 gpadmin gpadmin     391 Jan 23 08:53 resqueue.log

-rw-rw-r--. 1 gpadmin gpadmin     433 Jan 23 08:53 pg_stat_resqueue.log

-rw-rw-r--. 1 gpadmin gpadmin     696 Jan 23 08:53 pg_stat_database.log

-rw-rw-r--. 1 gpadmin gpadmin    1152 Jan 23 08:53 pg_roles.log

-rw-rw-r--. 1 gpadmin gpadmin     133 Jan 23 08:53 pg_filespace.log

-rw-rw-r--. 1 gpadmin gpadmin     305 Jan 23 08:53 pg_filespace_entry.log

-rw-rw-r--. 1 gpadmin gpadmin     340 Jan 23 08:53 pgdatabase.log

-rw-rw-r--. 1 gpadmin gpadmin     251 Jan 23 08:53 mastermirror.log

-rw-rw-r--. 1 gpadmin gpadmin     133 Jan 23 08:53 interfaces.log

-rw-rw-r--. 1 gpadmin gpadmin       6 Jan 23 08:53 hosts

-rw-rw-r--. 1 gpadmin gpadmin     377 Jan 23 08:53 dbsizes.log

-rw-rw-r--. 1 gpadmin gpadmin     134 Jan 23 08:53 db_interfaces.log

-rw-rw-r--. 1 gpadmin gpadmin     659 Jan 23 08:53 config.log

-rw-rw-r--. 1 gpadmin gpadmin     109 Jan 23 08:53 config_history.log

drwxrwxr-x. 2 gpadmin gpadmin    4096 Jan 23 08:53 backupmaster

-rw-rw-r--. 1 gpadmin gpadmin     393 Jan 23 08:53 autovacuum.log

drwxrwxr-x. 4 gpadmin gpadmin    4096 Jan 23 08:53 sachi

drwxrwxr-x. 3 gpadmin gpadmin    4096 Jan 23 08:53 master

-rw-rw-r--. 1 gpadmin gpadmin     927 Jan 23 08:53 gpstate_mirrors.log

-rw-rw-r--. 1 gpadmin gpadmin    1352 Jan 23 08:53 gpstate_standby_master.log

-rw-rw-r--. 1 gpadmin gpadmin   76998 Jan 23 08:53 gpcheckos_master.log

-rw-rw-r--. 1 gpadmin gpadmin   71825 Jan 23 08:53 gpcheckos_segments.log

-rw-rw-r--. 1 gpadmin gpadmin     645 Jan 23 08:53 gpcheckcat_sachi.log

-rw-rw-r--. 1 gpadmin gpadmin     648 Jan 23 08:53 gpcheckcat_postgres.log

-rw-rw-r--. 1 gpadmin gpadmin     647 Jan 23 08:53 gpcheckcat_gpadmin.log

-rw-rw-r--. 1 gpadmin gpadmin    5854 Jan 23 08:54 pg_dumpall.log

-rw-rw-r--. 1 gpadmin gpadmin 1026325 Jan 23 08:54 catalog_q1.log

-rw-rw-r--. 1 gpadmin gpadmin    8552 Jan 23 08:54 error.log

Let's look at some of the important information included in this file.

[gpadmin@sachi gpdetective20140123085342]$ cat version_at_initdb.log

SQL: 

            SELECT * FROM pg_catalog.gp_version_at_initdb

        

schemaversion | productversion                    | 

--------------+-----------------------------------+

2             | 4.2.2.4 build 1 Community Edition | 

[gpadmin@sachi gpdetective20140123085342]$ cat resqueue.log

SQL: 

            SELECT * FROM pg_catalog.pg_resqueue

        

rsqname    | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit | 

-----------+---------------+--------------+---------------+--------------------+

perf_test  | 2.0           | 100000000.0  | t             | 100000.0           | 

pg_default | 20.0          | -1.0         | f             | 0.0                | 

[gpadmin@sachi gpdetective20140123085342]$ cat pg_stat_resqueue.log

SQL: 

            SELECT * FROM pg_catalog.pg_stat_resqueues            

        

queueid | queuename  | n_queries_exec | n_queries_wait | elapsed_exec | elapsed_wait | 

--------+------------+----------------+----------------+--------------+--------------+

16995   | perf_test  | 0              | 0              | 0            | 0            | 

6055    | pg_default | 0              | 0              | 0            | 0            | 

[gpadmin@sachi gpdetective20140123085342]$ cat pg_stat_database.log

SQL: 

            SELECT * FROM pg_catalog.pg_stat_database            

        

datid | datname   | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | 

------+-----------+-------------+-------------+---------------+-----------+----------+

16992 | gpadmin   | 1           | 0           | 0             | 0         | 0        | 

10900 | postgres  | 1           | 0           | 0             | 0         | 0        | 

16993 | sachi     | 0           | 0           | 0             | 0         | 0        | 

1     | template1 | 0           | 0           | 0             | 0         | 0        | 

10899 | template0 | 0           | 0           | 0             | 0         | 0        | 

[gpadmin@sachi gpdetective20140123085342]$ cat pg_roles.log

SQL: 

            SELECT * FROM pg_catalog.pg_roles

        

rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnlimit | rolpassword | rolvaliduntil | rolconfig | rolresqueue | oid   | rolcreaterextgpfd | rolcreaterexthttp | rolcreatewextgpfd | rolcreaterexthdfs | rolcreatewexthdfs | 

--------+----------+------------+---------------+-------------+--------------+-------------+--------------+-------------+---------------+-----------+-------------+-------+-------------------+-------------------+-------------------+-------------------+-------------------+

gpadmin | t        | t          | t             | t           | t            | t           | -1           | ********    | None          | None      | 6055        | 10    | t                 | t                 | t                 | t                 | t                 | 

sachi   | f        | t          | f             | f           | f            | t           | -1           | ********    | None          | None      | 6055        | 16994 | f                 | f                 | f                 | f                 | f                 | 

[gpadmin@sachi gpdetective20140123085342]$ cat pg_filespace.log

SQL: 

            SELECT * FROM pg_catalog.pg_filespace

        

fsname    | fsowner | 

----------+---------+

pg_system | 10      | 

[gpadmin@sachi gpdetective20140123085342]$ cat dbsizes.log

SQL: 

            SELECT datname,pg_size_pretty(pg_database_size( datname )) 

            FROM ( SELECT datname 

                   FROM pg_database 

                   WHERE datname NOT IN ('template0', 'postgres')) d

        

datname   | pg_size_pretty | 

----------+----------------+

gpadmin   | 9821 kB        | 

sachi     | 9796 kB        | 

template1 | 9805 kB        | 

[gpadmin@sachi gpdetective20140123085342]$ cat error.log

gpdetective version 1.01

TEST: getConfiguration

SQL: 

           SELECT hostname, fselocation 

           FROM   gp_segment_configuration

           INNER JOIN pg_filespace_entry on (fsedbid=dbid)

           INNER JOIN pg_filespace fs on (fs.oid = fsefsoid and fsname = 'pg_system')

           WHERE  content = -1  AND preferred_role = 'p'

        

SQL: 

           SELECT hostname, fselocation

           FROM   gp_segment_configuration

           INNER JOIN pg_filespace_entry on (fsedbid=dbid)

           INNER JOIN pg_filespace fs on (fs.oid = fsefsoid and fsname = 'pg_system')

           WHERE  content < 0 AND preferred_role != 'p'

        

SQL: 

           SELECT distinct hostname

           FROM   gp_segment_configuration

           WHERE  content >= 0

        

SQL: 

           SELECT dbid, hostname || ':' || fselocation

           FROM   gp_segment_configuration

           INNER JOIN pg_filespace_entry on (fsedbid=dbid)

           INNER JOIN pg_filespace fs on (fs.oid = fsefsoid and fsname = 'pg_system')

           WHERE  content >= 0  AND  preferred_role = 'p'

        

SQL: 

           SELECT dbid, hostname || ':' || fselocation

           FROM   gp_segment_configuration

           INNER JOIN pg_filespace_entry on (fsedbid=dbid)

           INNER JOIN pg_filespace fs on (fs.oid = fsefsoid and fsname = 'pg_system')

           WHERE  content >= 0  AND  preferred_role != 'p'

        

SQL: 

           SELECT datname FROM pg_database

           WHERE  datname not in ('template0', 'template1')

        

SQL: 

           SELECT * from gp_segment_configuration

        

SQL: 

           SELECT * from gp_configuration_history

        

SQL: 

            SELECT * FROM gp_configuration_history

        

SQL: 

            SELECT * FROM pg_catalog.pg_resqueue

        

SQL: 

            SELECT * FROM pg_catalog.gp_master_mirroring

        

SQL: 

            SELECT * FROM pg_catalog.gp_version_at_initdb

        

SQL: 

            SELECT * FROM pg_catalog.gp_interfaces

        

SQL: 

            SELECT * FROM pg_catalog.gp_db_interfaces

        

SQL: 

            SELECT * FROM pg_catalog.pg_autovacuum

        

SQL: 

            SELECT * FROM pg_catalog.gp_pgdatabase

        

SQL: 

            SELECT datname,pg_size_pretty(pg_database_size( datname )) 

            FROM ( SELECT datname 

                   FROM pg_database 

                   WHERE datname NOT IN ('template0', 'postgres')) d

        

SQL: 

            SELECT * FROM pg_catalog.pg_stat_resqueues            

        

SQL: 

            SELECT * FROM pg_catalog.pg_stat_database            

        

SQL: 

            SELECT * FROM pg_catalog.pg_roles

        

SQL: 

            SELECT * FROM pg_catalog.pg_filespace

        

SQL: 

            SELECT * FROM pg_catalog.pg_filespace_entry

        

TEST: copyLogs

TEST:     get master logs

localhost$ scp /home/gpmaster/gpsne-1/postgresql.conf master/postgresql.conf 

localhost$ scp /home/gpmaster/gpsne-1/global/pg_database master/pg_database 

localhost$ scp /home/gpmaster/gpsne-1/global/pg_auth master/pg_pgauth 

TEST:     get primary segment logs

localhost$ scp -q sachi:/disk2/gpdata2/gpsne1/postgresql.conf sachi/postgresql_gpsne1.conf 

localhost$ scp -q sachi:/disk1/gpdata1/gpsne0/postgresql.conf sachi/postgresql_gpsne0.conf 

TEST:     get gpAdminLogs

localhost$ cp -R /home/gpadmin/gpAdminLogs/ master/gpAdminLogs 

TEST:     get primary segment pg_changetracking FILEREP_CONFIG_LOG and FILEREP_LOG  logs

localhost$ mkdir sachi/pg_changetracking_gpsne1 

localhost$ scp -qr sachi:/disk2/gpdata2/gpsne1/pg_changetracking/FILEREP_CONFIG_LOG sachi/pg_changetracking_gpsne1/FILEREP_CONFIG_LOG 

scp: /disk2/gpdata2/gpsne1/pg_changetracking/FILEREP_CONFIG_LOG: No such file or directory

localhost$ scp -qr sachi:/disk2/gpdata2/gpsne1/pg_changetracking/FILEREP_LOG sachi/pg_changetracking_gpsne1/FILEREP_LOG 

scp: /disk2/gpdata2/gpsne1/pg_changetracking/FILEREP_LOG: No such file or directory

localhost$ mkdir sachi/pg_changetracking_gpsne0 

localhost$ scp -qr sachi:/disk1/gpdata1/gpsne0/pg_changetracking/FILEREP_CONFIG_LOG sachi/pg_changetracking_gpsne0/FILEREP_CONFIG_LOG 

scp: /disk1/gpdata1/gpsne0/pg_changetracking/FILEREP_CONFIG_LOG: No such file or directory

localhost$ scp -qr sachi:/disk1/gpdata1/gpsne0/pg_changetracking/FILEREP_LOG sachi/pg_changetracking_gpsne0/FILEREP_LOG 

scp: /disk1/gpdata1/gpsne0/pg_changetracking/FILEREP_LOG: No such file or directory

TEST:     get mirror segment pg_changetracking FILEREP_CONFIG_LOG and FILEREP_LOG  logs

localhost$ scp -qr sachi:/disk2/gpdata2/gpsne1/gp_transaction_files_filespace sachi/pg_changetracking_gpsne1/gp_transaction_files_filespace 

scp: /disk2/gpdata2/gpsne1/gp_transaction_files_filespace: No such file or directory

localhost$ scp -qr sachi:/disk2/gpdata2/gpsne1/gp_temporary_files_filespace sachi/pg_changetracking_gpsne1/gp_temporary_files_filespace 

scp: /disk2/gpdata2/gpsne1/gp_temporary_files_filespace: No such file or directory

localhost$ scp -qr sachi:/disk1/gpdata1/gpsne0/gp_transaction_files_filespace sachi/pg_changetracking_gpsne0/gp_transaction_files_filespace 

scp: /disk1/gpdata1/gpsne0/gp_transaction_files_filespace: No such file or directory

localhost$ scp -qr sachi:/disk1/gpdata1/gpsne0/gp_temporary_files_filespace sachi/pg_changetracking_gpsne0/gp_temporary_files_filespace 

scp: /disk1/gpdata1/gpsne0/gp_temporary_files_filespace: No such file or directory

localhost$ cp /home/gpmaster/gpsne-1/gp_transaction_files_filespace master/gp_transaction_files_filespace 

cp: cannot stat `/home/gpmaster/gpsne-1/gp_transaction_files_filespace': No such file or directory

localhost$ cp /home/gpmaster/gpsne-1/gp_temporary_files_filespace master/gp_temporary_files_filespace 

cp: cannot stat `/home/gpmaster/gpsne-1/gp_temporary_files_filespace': No such file or directory

TEST: systemInfo

localhost$ gpstate -m 

localhost$ gpstate -f 

localhost$ gpcheckos -vm -h localhost 

/usr/local/greenplum-db/./bin/gpcheckos:19: DeprecationWarning: The popen2 module is deprecated.  Use the subprocess module.

  import getopt, popen2, pickle, time

localhost$ gpcheckos -vf hosts 

/usr/local/greenplum-db/./bin/gpcheckos:19: DeprecationWarning: The popen2 module is deprecated.  Use the subprocess module.

  import getopt, popen2, pickle, time

TEST: checkCatalog

localhost$ /usr/local/greenplum-db-4.2.2.4/bin/lib/gpcheckcat -h localhost -p 5432 -U gpadmin gpadmin 

Error: option -h not recognized

localhost$ /usr/local/greenplum-db-4.2.2.4/bin/lib/gpcheckcat -h localhost -p 5432 -U gpadmin postgres 

Error: option -h not recognized

localhost$ /usr/local/greenplum-db-4.2.2.4/bin/lib/gpcheckcat -h localhost -p 5432 -U gpadmin sachi 

Error: option -h not recognized

TEST: pg_dumpall

localhost$ p g _ d u m p a l l   - - s c h e m a - o n l y   - - g p - s y n t a x   - h   l o c a l h o s t   - p   5 4 3 2   - U   g p a d m i n 

SQL: 

           SELECT 

             nspname || '.' || relname, 

             attname, 

             starelid,

             staattnum,

             stanullfrac,

             stawidth,

             stadistinct,

             stakind1,

             stakind2,

             stakind3,

             stakind4,

             staop1,

             staop2,

             staop3,

             staop4,

             array_to_string(stanumbers1, ','),

             array_to_string(stanumbers2, ','),

             array_to_string(stanumbers3, ','),

             array_to_string(stanumbers4, ','),

             array_to_string(stavalues1, ','),

             array_to_string(stavalues2, ','),

             array_to_string(stavalues3, ','),

             array_to_string(stavalues4, ',')

           FROM   pg_class c, 

                  pg_namespace n, 

                  pg_attribute a, 

                  pg_statistic s 

           WHERE  n.oid = c.relnamespace 

             AND  c.oid = a.attrelid 

             AND  c.oid = s.starelid 

             AND  a.attnum = s.staattnum 

          ORDER BY 1, 2

        

TEST: crashReport

localhost$ uname

localhost$ /sbin/sysctl -n kernel.core_pattern 

localhost$ /sbin/sysctl -n kernel.core_uses_pid 

localhost$ ls -1 |/usr/libexec/abrt-hook-ccpp /var/spool/abrt * * * * * * localhost postgres 636f726500*

localhost$ /usr/bin/gdb -batch -n -c /var/spool/abrt:

/usr/bin/gdb: symbol lookup error: /usr/bin/gdb: undefined symbol: PyUnicodeUCS4_FromEncodedObject

sachi$ uname

sachi$ /sbin/sysctl -n kernel.core_pattern 

sachi$ /sbin/sysctl -n kernel.core_uses_pid 

sachi$ ls -1 |/usr/libexec/abrt-hook-ccpp /var/spool/abrt * * * * * * sachi postgres 636f726500*

TEST: makeTar

[gpadmin@sachi gpdetective20140123085342]$