Postgresql: Command runbook for DBA

Postgresql runbook for a DBA:

Steps to perform "cluster" command in postgres.... after brief data collection

Ensure to enable putty log...

1) Connect to postgres user:

command:

su - postgres

2) Find out the postgres process running:

command:

ps -ef|grep -i postgres

3) echo $PATH and $PGDATA variable

command:

echo $PATH

echo $PGDATA

4) Find out the location of psql

command:

which psql

5) Collect the cron job details

command:

crontab -l

6) Collect df -h output

command:

df -h

7) Connect to psql

command:

psql

8) Collect the db list

command:

\l

or 

select a.oid

,a.datname

,b.rolname owner

,a.encoding

,a.dattablespace

,a.datcollate

,a.datctype

,a.datacl

from pg_database a

,pg_authid b

where b.oid=a.datdba;

9) Connect to the db where the table is located

command:

\c <dbname>

10) View the schema list

command:

select * from pg_catalog.pg_namespace;

or

\dn+

11) show your search path first

command:

show search_path;

12) Set the search path the candidate schema

command:

SET search_path TO <candidate_schema>;

13) show your search path again [remember not to disconnect or exit this connection]

command:

show search_path;

14) List the tables along with their sizes

command:

\d+

15) Collect more details on the table

command:

select schemaname,tablename,tableowner,hasindexes from pg_tables where schemaname='<candidate_schema>' order by 1;

select relname table_name

,relnamespace schema_name

,reltype relation_type

,relowner owner

,relpages pages_or_blocks

,reltuples numrows

,relhasindex has_index

from pg_class

where relnamespace=(select oid from pg_namespace where nspname=<candidate_schema>')

and relname='<candidate_table>' order by 2,1;

16) Take description details

command:

\d <candidate table_name>;

17) Let us examine the index status

command:

select c.schemaname

,c.tablename

,c.indexname

,b.indisunique

,b.indisprimary

,b.indisclustered

,b.indisvalid

,b.indisready

,b.indislive

from pg_class a

,pg_index b

,pg_indexes c

,pg_namespace d

where d.nspname='<candidate_schema>'

and c.schemaname=d.nspname

and c.tablename='<candidate_table>'

and c.indexname=a.relname

and a.oid=b.indexrelid

and a.relnamespace=d.oid

;

18) Now secure backup of the candidate table [in case needed, we can revert both table structure and data]

command:

pg_dump -U <candidate_username> -d <candidate_database> -t <candidate_schema>.<candidate_table> > <candidate_mountspace>/pgtst_schema_pgtst_tbl2.sql

19) Let us run cluster command: << outage begins here

command:

Connect back to the same db;

SET search_path TO <candidate_schema>;

cluster verbose <candidate_schemaname>.<candidate_tablename> using <candidate_index>;

20) Let us run analyze

command:

Connect back to the same db;

SET search_path TO <candidate_schema>;

analyze verbose <candidate_schemaname>.<candidate_tablename>;

20) Let us examine the index status again

command:

Connect back to the same db;

SET search_path TO <candidate_schema>;

select c.schemaname

,c.tablename

,c.indexname

,b.indisunique

,b.indisprimary

,b.indisclustered

,b.indisvalid

,b.indisready

,b.indislive

from pg_class a

,pg_index b

,pg_indexes c

,pg_namespace d

where d.nspname='<candidate_schema>'

and c.schemaname=d.nspname

and c.tablename='<candidate_table>'

and c.indexname=a.relname

and a.oid=b.indexrelid

and a.relnamespace=d.oid

;

21) Let us examine the table status again

command:

Connect back to the same db;

SET search_path TO <candidate_schema>;

select schemaname,tablename,tableowner,hasindexes from pg_tables where schemaname='<candidate_schema>' order by 1;

select relname table_name

,relnamespace schema_name

,reltype relation_type

,relowner owner

,relpages pages_or_blocks

,reltuples numrows

,relhasindex has_index

from pg_class

where relnamespace=(select oid from pg_namespace where nspname='pgtst_schema')

and relname='<candidate_table>' order by 2,1;

\dn+

This closes the command summary for clustering operation.