How to check either table has been locked or not AND how to kill the process

 

SELECT

    l.locktype,

    l.mode,

    l.granted,

    l.pid,

    a.query,

    a.state,

    a.query_start

FROM pg_locks l

JOIN pg_stat_activity a ON l.pid = a.pid

WHERE l.relation = 'your_table_name'::regclass;

 

SELECT pg_terminate_backend(4868);

* 4868 is PID (Process ID)

Inspection Percentage Completion

select sum(a.selesai_lp ) as selesai_lp, sum(a.harta_keseluruhan) as harta_keseluruhan,
round(sum(a.selesai_lp )/sum(a.harta_keseluruhan)*100,2) as peratus_selesai_lp
from (
select 0 as harta_keseluruhan, count(*) as selesai_lp from t_harta_status ths
where ths.id_status_harta >= 6
union
select count(*) as harta_keseluruhan, 0 selesai_lp from stg_ntharta sn
) a;

Disconnect all client from database

 

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'dbname';

Export / Import PostgreSQL to .sql file

 

Export postgreSQL to .sql file (linux):
# pg_dump -U username -h localhost databasename >> sqlfile.sql

Import .sql file into PostgreSQL database (windows / linux):

> psql -h localhost -U postgres -d postdata -f D:\Backup\database.sql

PostgreSQL Database

1. Disconnect all clients from database
2. Export / Import PostgreSQL to .sql file
3. How to check either table has been locked or not AND how to kill the process
4. Inspection Percentage Completion