PostgreSQL commands
psql -U postgres -h 192.168.0.25 -p 5432 -W (-U flag to specify db user)
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 query parameters
SELECT name as "Parameter name", setting as value, short_desc FROM pg_settings WHERE name LIKE '%ssss%';
#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;
#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;
#Query to check is SSL is being used by sessions
SELECT datname as "Database name", usename as "User name", ssl, client_addr, application_name, backend_type
FROM pg_stat_ssl JOIN pg_stat_activity ON pg_stat_ssl.pid = pg_stat_activity.pid ORDER BY ssl;
#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
No comments:
Post a Comment