Gathering Information for Greenplum Support

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