Very new user.
Problem: Server Status has no data in Activity, Locks, Prepared
Transactions windows. 100% repeatable though restarts, etc.
Ubuntu 15.10
PostgreSQL 9.4 and 9.5
pgadminIII 1.20 and 1.22
logfile window does work, per proper log set up in postgresql.conf
SQL Editor query: SELECT * FROM pg_stat_activity returns expected
connection information. So the connections are there.
I have seen a few similar posts in 2015, with no response, or
resolutions. Nothing more from internet searches. No clues from 9.4
manual.
My experience in other forums is such a problem has such an obvious
solution that it is staring me right in the face.
Is there some setting, plugin, permission, role I am missing?
Other than that I have the databases working with a higher level app,
successfully backup and restore, moving data around; not bragging, but
to indicate I have been successful at something.
I have attached the log output that appears synced to the server status
refresh.
QUERY : 08:54:01 PM: Set query (localhost:5432): SELECT transaction::text,
gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY 2 ASC
QUERY : 08:54:06 PM: Set query (localhost:5432): SELECT p.pid AS pid,
application_name, datname, usename,
CASE WHEN client_port=-1 THEN 'local pipe' WHEN length(client_hostname)>0 THEN
client_hostname||':'||client_port ELSE
textin(inet_out(client_addr))||':'||client_port END AS client,
date_trunc('second', backend_start) AS backend_start, CASE WHEN state='active'
THEN date_trunc('second', query_start)::text ELSE '' END AS query_start,
date_trunc('second', xact_start) AS xact_start, state, date_trunc('second',
state_change) AS state_change, backend_xid::text, backend_xmin::text, (SELECT
min(l1.pid) FROM pg_locks l1 WHERE GRANTED AND (relation IN (SELECT relation
FROM pg_locks l2 WHERE l2.pid=p.pid AND NOT granted) OR transactionid IN
(SELECT transactionid FROM pg_locks l3 WHERE l3.pid=p.pid AND NOT granted))) AS
blockedby,
query AS query,
CASE WHEN query_start IS NULL OR state<>'active' THEN false ELSE query_start <
now() - '10 seconds'::interval END AS slowquery
FROM pg_stat_activity p ORDER BY 1 ASC
QUERY : 08:54:06 PM: Set query (localhost:5432): SELECT
pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE
oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS
class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user,
pgl.virtualxid::text, pgl.virtualtransaction::text AS transaction, pgl.mode,
pgl.granted, date_trunc('second', pg_stat_get_backend_activity_start(svrid)) AS
query_start, pg_stat_get_backend_activity(svrid) AS query FROM
pg_stat_get_backend_idset() svrid, pg_locks pgl LEFT JOIN pg_class pgc ON
pgl.relation=pgc.oid WHERE pgl.pid = pg_stat_get_backend_pid(svrid) ORDER BY 1
ASC
QUERY : 08:54:06 PM: Set query (localhost:5432): SELECT transaction::text,
gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY 2 ASC
QUERY : 08:54:06 PM: Set query (localhost:5432): SELECT
pg_file_length('pg_log/postgresql-2016-01-29_150234.log') AS len
QUERY : 08:54:06 PM: Scalar query (localhost:5432): SHOW log_directory
QUERY : 08:54:06 PM: Query result: pg_log
QUERY : 08:54:06 PM: Set query (localhost:5432): SELECT filename, filetime
FROM pg_logdir_ls() AS A(filetime timestamp, filename text)
ORDER BY filetime DESC
QUERY : 08:54:11 PM: Set query (localhost:5432): SELECT transaction::text,
gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY 2 ASC
QUERY : 08:54:16 PM: Set query (localhost:5432): SELECT p.pid AS pid,
application_name, datname, usename,
CASE WHEN client_port=-1 THEN 'local pipe' WHEN length(client_hostname)>0 THEN
client_hostname||':'||client_port ELSE
textin(inet_out(client_addr))||':'||client_port END AS client,
date_trunc('second', backend_start) AS backend_start, CASE WHEN state='active'
THEN date_trunc('second', query_start)::text ELSE '' END AS query_start,
date_trunc('second', xact_start) AS xact_start, state, date_trunc('second',
state_change) AS state_change, backend_xid::text, backend_xmin::text, (SELECT
min(l1.pid) FROM pg_locks l1 WHERE GRANTED AND (relation IN (SELECT relation
FROM pg_locks l2 WHERE l2.pid=p.pid AND NOT granted) OR transactionid IN
(SELECT transactionid FROM pg_locks l3 WHERE l3.pid=p.pid AND NOT granted))) AS
blockedby,
query AS query,
CASE WHEN query_start IS NULL OR state<>'active' THEN false ELSE query_start <
now() - '10 seconds'::interval END AS slowquery
FROM pg_stat_activity p ORDER BY 1 ASC
QUERY : 08:54:16 PM: Set query (localhost:5432): SELECT
pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE
oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS
class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user,
pgl.virtualxid::text, pgl.virtualtransaction::text AS transaction, pgl.mode,
pgl.granted, date_trunc('second', pg_stat_get_backend_activity_start(svrid)) AS
query_start, pg_stat_get_backend_activity(svrid) AS query FROM
pg_stat_get_backend_idset() svrid, pg_locks pgl LEFT JOIN pg_class pgc ON
pgl.relation=pgc.oid WHERE pgl.pid = pg_stat_get_backend_pid(svrid) ORDER BY 1
ASC
QUERY : 08:54:16 PM: Set query (localhost:5432): SELECT transaction::text,
gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY 2 ASC
QUERY : 08:54:16 PM: Set query (localhost:5432): SELECT
pg_file_length('pg_log/postgresql-2016-01-29_150234.log') AS len
QUERY : 08:54:16 PM: Scalar query (localhost:5432): SHOW log_directory
QUERY : 08:54:16 PM: Query result: pg_log
QUERY : 08:54:16 PM: Set query (localhost:5432): SELECT filename, filetime
FROM pg_logdir_ls() AS A(filetime timestamp, filename text)
ORDER BY filetime DESC
QUERY : 08:54:21 PM: Set query (localhost:5432): SELECT transaction::text,
gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY 2 ASC
QUERY : 08:54:26 PM: Set query (localhost:5432): SELECT p.pid AS pid,
application_name, datname, usename,
CASE WHEN client_port=-1 THEN 'local pipe' WHEN length(client_hostname)>0 THEN
client_hostname||':'||client_port ELSE
textin(inet_out(client_addr))||':'||client_port END AS client,
date_trunc('second', backend_start) AS backend_start, CASE WHEN state='active'
THEN date_trunc('second', query_start)::text ELSE '' END AS query_start,
date_trunc('second', xact_start) AS xact_start, state, date_trunc('second',
state_change) AS state_change, backend_xid::text, backend_xmin::text, (SELECT
min(l1.pid) FROM pg_locks l1 WHERE GRANTED AND (relation IN (SELECT relation
FROM pg_locks l2 WHERE l2.pid=p.pid AND NOT granted) OR transactionid IN
(SELECT transactionid FROM pg_locks l3 WHERE l3.pid=p.pid AND NOT granted))) AS
blockedby,
query AS query,
CASE WHEN query_start IS NULL OR state<>'active' THEN false ELSE query_start <
now() - '10 seconds'::interval END AS slowquery
FROM pg_stat_activity p ORDER BY 1 ASC
QUERY : 08:54:26 PM: Set query (localhost:5432): SELECT
pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE
oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS
class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user,
pgl.virtualxid::text, pgl.virtualtransaction::text AS transaction, pgl.mode,
pgl.granted, date_trunc('second', pg_stat_get_backend_activity_start(svrid)) AS
query_start, pg_stat_get_backend_activity(svrid) AS query FROM
pg_stat_get_backend_idset() svrid, pg_locks pgl LEFT JOIN pg_class pgc ON
pgl.relation=pgc.oid WHERE pgl.pid = pg_stat_get_backend_pid(svrid) ORDER BY 1
ASC
QUERY : 08:54:26 PM: Set query (localhost:5432): SELECT transaction::text,
gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY 2 ASC
QUERY : 08:54:26 PM: Set query (localhost:5432): SELECT
pg_file_length('pg_log/postgresql-2016-01-29_150234.log') AS len
QUERY : 08:54:26 PM: Scalar query (localhost:5432): SHOW log_directory
QUERY : 08:54:26 PM: Query result: pg_log
QUERY : 08:54:26 PM: Set query (localhost:5432): SELECT filename, filetime
FROM pg_logdir_ls() AS A(filetime timestamp, filename text)
ORDER BY filetime DESC
QUERY : 08:54:31 PM: Set query (localhost:5432): SELECT transaction::text,
gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY 2 ASC
QUERY : 08:54:36 PM: Set query (localhost:5432): SELECT p.pid AS pid,
application_name, datname, usename,
CASE WHEN client_port=-1 THEN 'local pipe' WHEN length(client_hostname)>0 THEN
client_hostname||':'||client_port ELSE
textin(inet_out(client_addr))||':'||client_port END AS client,
date_trunc('second', backend_start) AS backend_start, CASE WHEN state='active'
THEN date_trunc('second', query_start)::text ELSE '' END AS query_start,
date_trunc('second', xact_start) AS xact_start, state, date_trunc('second',
state_change) AS state_change, backend_xid::text, backend_xmin::text, (SELECT
min(l1.pid) FROM pg_locks l1 WHERE GRANTED AND (relation IN (SELECT relation
FROM pg_locks l2 WHERE l2.pid=p.pid AND NOT granted) OR transactionid IN
(SELECT transactionid FROM pg_locks l3 WHERE l3.pid=p.pid AND NOT granted))) AS
blockedby,
query AS query,
CASE WHEN query_start IS NULL OR state<>'active' THEN false ELSE query_start <
now() - '10 seconds'::interval END AS slowquery
FROM pg_stat_activity p ORDER BY 1 ASC
QUERY : 08:54:36 PM: Set query (localhost:5432): SELECT
pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE
oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS
class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user,
pgl.virtualxid::text, pgl.virtualtransaction::text AS transaction, pgl.mode,
pgl.granted, date_trunc('second', pg_stat_get_backend_activity_start(svrid)) AS
query_start, pg_stat_get_backend_activity(svrid) AS query FROM
pg_stat_get_backend_idset() svrid, pg_locks pgl LEFT JOIN pg_class pgc ON
pgl.relation=pgc.oid WHERE pgl.pid = pg_stat_get_backend_pid(svrid) ORDER BY 1
ASC
QUERY : 08:54:36 PM: Set query (localhost:5432): SELECT transaction::text,
gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY 2 ASC
QUERY : 08:54:36 PM: Set query (localhost:5432): SELECT
pg_file_length('pg_log/postgresql-2016-01-29_150234.log') AS len
QUERY : 08:54:36 PM: Scalar query (localhost:5432): SHOW log_directory
QUERY : 08:54:36 PM: Query result: pg_log
QUERY : 08:54:36 PM: Set query (localhost:5432): SELECT filename, filetime
FROM pg_logdir_ls() AS A(filetime timestamp, filename text)
ORDER BY filetime DESC
QUERY : 08:54:41 PM: Set query (localhost:5432): SELECT transaction::text,
gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY 2 ASC
QUERY : 08:54:46 PM: Set query (localhost:5432): SELECT p.pid AS pid,
application_name, datname, usename,
CASE WHEN client_port=-1 THEN 'local pipe' WHEN length(client_hostname)>0 THEN
client_hostname||':'||client_port ELSE
textin(inet_out(client_addr))||':'||client_port END AS client,
date_trunc('second', backend_start) AS backend_start, CASE WHEN state='active'
THEN date_trunc('second', query_start)::text ELSE '' END AS query_start,
date_trunc('second', xact_start) AS xact_start, state, date_trunc('second',
state_change) AS state_change, backend_xid::text, backend_xmin::text, (SELECT
min(l1.pid) FROM pg_locks l1 WHERE GRANTED AND (relation IN (SELECT relation
FROM pg_locks l2 WHERE l2.pid=p.pid AND NOT granted) OR transactionid IN
(SELECT transactionid FROM pg_locks l3 WHERE l3.pid=p.pid AND NOT granted))) AS
blockedby,
query AS query,
CASE WHEN query_start IS NULL OR state<>'active' THEN false ELSE query_start <
now() - '10 seconds'::interval END AS slowquery
FROM pg_stat_activity p ORDER BY 1 ASC
QUERY : 08:54:46 PM: Set query (localhost:5432): SELECT
pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE
oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS
class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user,
pgl.virtualxid::text, pgl.virtualtransaction::text AS transaction, pgl.mode,
pgl.granted, date_trunc('second', pg_stat_get_backend_activity_start(svrid)) AS
query_start, pg_stat_get_backend_activity(svrid) AS query FROM
pg_stat_get_backend_idset() svrid, pg_locks pgl LEFT JOIN pg_class pgc ON
pgl.relation=pgc.oid WHERE pgl.pid = pg_stat_get_backend_pid(svrid) ORDER BY 1
ASC
QUERY : 08:54:46 PM: Set query (localhost:5432): SELECT transaction::text,
gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY 2 ASC
QUERY : 08:54:46 PM: Set query (localhost:5432): SELECT
pg_file_length('pg_log/postgresql-2016-01-29_150234.log') AS len
QUERY : 08:54:46 PM: Scalar query (localhost:5432): SHOW log_directory
QUERY : 08:54:46 PM: Query result: pg_log
QUERY : 08:54:46 PM: Set query (localhost:5432): SELECT filename, filetime
FROM pg_logdir_ls() AS A(filetime timestamp, filename text)
ORDER BY filetime DESC
QUERY : 08:54:51 PM: Set query (localhost:5432): SELECT transaction::text,
gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY 2 ASC
QUERY : 08:54:56 PM: Set query (localhost:5432): SELECT p.pid AS pid,
application_name, datname, usename,
CASE WHEN client_port=-1 THEN 'local pipe' WHEN length(client_hostname)>0 THEN
client_hostname||':'||client_port ELSE
textin(inet_out(client_addr))||':'||client_port END AS client,
date_trunc('second', backend_start) AS backend_start, CASE WHEN state='active'
THEN date_trunc('second', query_start)::text ELSE '' END AS query_start,
date_trunc('second', xact_start) AS xact_start, state, date_trunc('second',
state_change) AS state_change, backend_xid::text, backend_xmin::text, (SELECT
min(l1.pid) FROM pg_locks l1 WHERE GRANTED AND (relation IN (SELECT relation
FROM pg_locks l2 WHERE l2.pid=p.pid AND NOT granted) OR transactionid IN
(SELECT transactionid FROM pg_locks l3 WHERE l3.pid=p.pid AND NOT granted))) AS
blockedby,
query AS query,
CASE WHEN query_start IS NULL OR state<>'active' THEN false ELSE query_start <
now() - '10 seconds'::interval END AS slowquery
FROM pg_stat_activity p ORDER BY 1 ASC
QUERY : 08:54:56 PM: Set query (localhost:5432): SELECT
pg_stat_get_backend_pid(svrid) AS pid, (SELECT datname FROM pg_database WHERE
oid = pgl.database) AS dbname, coalesce(pgc.relname, pgl.relation::text) AS
class, pg_get_userbyid(pg_stat_get_backend_userid(svrid)) as user,
pgl.virtualxid::text, pgl.virtualtransaction::text AS transaction, pgl.mode,
pgl.granted, date_trunc('second', pg_stat_get_backend_activity_start(svrid)) AS
query_start, pg_stat_get_backend_activity(svrid) AS query FROM
pg_stat_get_backend_idset() svrid, pg_locks pgl LEFT JOIN pg_class pgc ON
pgl.relation=pgc.oid WHERE pgl.pid = pg_stat_get_backend_pid(svrid) ORDER BY 1
ASC
QUERY : 08:54:56 PM: Set query (localhost:5432): SELECT transaction::text,
gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY 2 ASC
QUERY : 08:54:56 PM: Set query (localhost:5432): SELECT
pg_file_length('pg_log/postgresql-2016-01-29_150234.log') AS len
QUERY : 08:54:56 PM: Scalar query (localhost:5432): SHOW log_directory
QUERY : 08:54:56 PM: Query result: pg_log
QUERY : 08:54:56 PM: Set query (localhost:5432): SELECT filename, filetime
FROM pg_logdir_ls() AS A(filetime timestamp, filename text)
ORDER BY filetime DESC
--
Sent via pgadmin-support mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
