Postgres Admin

Postgresql Admin

1. Stop/Start
/usr/pgsql-12/bin/pg_ctl stop -D /var/lib/pgsql/12/data/

Connect to DB
psql --host=hostname --port=5432 --password —dbname=dbname —username=username 

2. Set search path

Login to user: say t_user
 SET search_path TO t_user, public;
 alter database dbname set search_path TO schema_main,public; 

alter user username set search_path=schema_name;

SELECT r.rolname, d.datname, rs.setconfig
FROM   pg_db_role_setting rs
LEFT   JOIN pg_roles      r ON r.oid = rs.setrole
LEFT   JOIN pg_database   d ON d.oid = rs.setdatabase
WHERE  r.rolname = 'role_name';

reset search_path
alter role role_name reset search_path;
 

3. Validate Session

SET TIMEZONE='America/Los_Angeles';
select pid as process_id, 
       usename as username, 
       datname as database_name, 
       client_addr as client_address, 
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity where datname='dbname';

select datname, usename, ssl, client_addr,application_name, backend_start,query_start ,state
 from pg_stat_ssl inner join pg_stat_activity on pg_stat_ssl.pid = pg_stat_activity.pid where ssl is true and usename not in ( 'rds');



	
 

4. Kill session

select pid as process_id, usename as username, datname as database_name, client_addr as client_address, application_name, backend_start, state, state_change from pg_stat_activity where datname=’aurorapg_dbname’; select datname, usename, ssl, client_addr,application_name, backend_start,query_start ,state from pg_stat_ssl inner join pg_stat_activity on pg_stat_ssl.pid = pg_stat_activity.pid where ssl is true and usename not in ( ‘rds’);