PostgreSQL DBA helpful stuff!

PostgreSQL commands

# to get help on psql 

psql --help

# Examples of psql connection to DB using various switches/flags

psql postgres postgres -h 192.168.0.25 -p 5432 (-h flag to specify hostname)
psql -U postgres -h 192.168.0.25 -p 5432 -W  (-U flag to specify db user)

psql -U postgres -d db1 -h 192.168.0.25 -p 5432 (-d flag to specify db)

#to know current user

select current_user;

#to get current date

select current_date;

#to know user details

select * from pg_user;

#to know about databases

select * from pg_database;

#to know about tables

select * from pg_class;

#to know about roles

select * from pg_roles;

#to know relative file path

select pg_relation_filepath('course');

#get details of user connections

select state, count(*) from pg_stat_activity group by state;

select * from pg_stat_activity where state='idle';

SELECT pg_terminate_backend(pid);

#shortcut commands on psql

postgres=# \l -- list db

postgres=#\c dbname -- to connect to a particular db

# to know dead tuples in a table

postgres=#select relname, n_dead_tup from pg_stat_user_tables;

#to vacuum manually

postgres=# vacuum (verbose, analyze);

#to know size of tables

select relname as "tableName", pg_size_pretty(pg_table_size(pgc.oid)) as "spaceused"

from pg_class as pgc left join pg_namespace as pgns on (pgns.oid = pgc.relnamespace)

where nspname not in ('pg_catalog', 'information_schema') and nspname !='pg_toast' and relkind in ('r')

order by pg_table_size(pgc.oid) desc;

#Indexes Info

postgres=# \di

postgres=# \di+

postgres=# \dis

# to get index, table its on & columns its created on

select row_number() over (order by c.relname), c.relname as index, t.relname as table, array_to_string(array_agg(a.attname), ', ') as column_names from pg_class c join pg_index i on c.oid = i.indexrelid and c.relkind='i' and c.relname not like 'pg_%' join pg_class t on t.oid= i.indrelid left join pg_attribute a on a.attrelid = t.oid and a.attnum = ANY(i.indkey) group by t.relname, c.relname order by c.relname;

#when last auto vacuum happened

select relname, last_vacuum, last_autovacuum from pg_stat_user_tables;

#to vacuum table

vacuum full tab1; -- tab1 is table name

#to know details about Archiving

postgres=# select name, setting, unit from pg_settings where name in

('wal_level', 'wal_keep_size', 'wal_keep_segments', 'wal_segment_size', 'archive_mode', 'archive_command', 'archive_timeout');

#to Check number of connections, super user connections, max connections

select * from (select count(*) used from pg_stat_activity) q1, (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) q2, (select setting::int max_conn from pg_settings where name=$$max_connections$$) q3;

#to take online backups

#can take backups as postgres user or create user with replication role

pg_basebackup -U username -D /location/wheretoplacebackups

pg_basebackup -v -U username -D /location/wheretoplacebackups

pg_basebackup -U username -D /location/wheretoplacebackups -h localhost

pg_basebackup -U username -D /location/wheretoplacebackups -Ft -z -P (-Ft - format tar file, z for compression P for progress)

#for upgrade

pg_upgrade

--old-datadir '/pg/9.x/data' --new-datadir '/pg/10.x/data'

--old-bindir '/pg/9.x/bin' --new-bindir '/pg/10.x/bin'

pg_upgrade -d oldCluster/data -D newCluster/data -b oldCluster/bin - B newCluster/bin

or you can export

$export PGDATAOLD = oldCluster/data

$export PGDATANEW = newCluster/data

$export PGBINOLD = oldCluster/bin

$export PGNEWOLD = newCluster/bin

$pg_upgrade

#to create db user

create user user1;

# to know the process tree for postgresql processes

pstree -p 7080 (where 7080 is postmaster pid)

PG Background Process

logger -- writes messages to error logs

checkpointer -- flushes dirty buffer to files (periodically)

background writer -- writes dirty buffer to files

wal writer -- writes wal buffer to wal file

autovaccum launcher -- performs vacuum operations

archiver-- for archiving

stats collector -- DBMS usage stats (session info, table usage stats)..etc.

PG Memory pools

shared memory -- db caching & transaction log caching

shared buffer -- frequently used blocks cached

wal buffer -- changes to db

postmaster process -- first to start, initialize memory, run background process, helps in recovery, creates background process for a client connection

maintenance_work_mem - for vacuum & create index, joins

temp_buffers -- temp tables area