Category: Postgres

check active connections

SELECT 
    pid
    ,datname as database
    ,usename as username
    ,application_name
    ,client_addr as client_address 
    ,client_hostname
    ,client_port
    ,backend_start
    ,query_start
    ,query
    ,state
FROM pg_stat_activity
WHERE state = 'active'
order by datname;

close connections

SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
-- don't kill my own connection!
pid <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname = 'medical_center_sm'
;


 

SELECT a.datname,
l.relation::regclass,
l.transactionid,
l.mode,
l.GRANTED,
a.usename,
a.query,
a.query_start,
age(now(), a.query_start) AS “age”,
a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start;
— SELECT pg_terminate_backend(14832 );

sudo -u postgres /Library/PostgreSQL/11/bin/pg_ctl -D /Library/PostgreSQL/11/data start

sudo -u postgres /Library/PostgreSQL/11/bin/pg_ctl -D /Library/PostgreSQL/11/data stop

 

To remove postgres find the root postgres directory normally like

/Library/PostgreSQL/9.6

run : uninstall-postgresql

will remain the data folder and you have to remove manually.

start new installation (change your version/folder number):

sudo /Library/PostgreSQL/10/bin/pg_ctl -D  /Library/PostgreSQL/10/data start

stop postgres:

sudo /Library/PostgreSQL/10/bin/pg_ctl -D  /Library/PostgreSQL/10/data stop