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.
