<marquee>How To Monitoring EnterpriseDB 10 With Console On Centos 7</marquee>

IP XX EDB MASTER

[root@Marmer-Node1 ~]# su - enterprisedb

Last login: Kam Agu 16 10:57:32 WIB 2018 on pts/0

-bash-4.2$ psql -p 5444 -d edb

Password:

psql.bin (10.1.5)

Type "help" for help.

edb=# select datname as db, pg_size_pretty(pg_database_size(datname)) as size from pg_database order by pg_database_size(datname) desc;

edb=# select pid, application_name, pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) sending_lag, pg_wal_lsn_diff(sent_lsn, flush_lsn) receiving_lag, pg_wal_lsn_diff(flush_lsn, replay_lsn) replaying_lag, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) total_lag from pg_stat_replication;

pid | application_name | sending_lag | receiving_lag | replaying_lag | total_lag
-------+------------------+-------------+---------------+---------------+-----------
99987 | walreceiver | 0 | 0 | 0 | 0
99989 | walreceiver | 0 | 0 | 0 | 0
99991 | walreceiver | 0 | 0 | 0 | 0
(3 rows)

edb=# select client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,backend_start from pg_stat_replication;

-[ RECORD 1 ]-+---------------------------------

client_addr | 10.20.2.43

state | streaming

sent_lsn | 0/32EBB268

write_lsn | 0/32EBB268

flush_lsn | 0/32EBB268

replay_lsn | 0/32EBB268

backend_start | 13-AUG-18 09:12:33.328919 +07:00

-[ RECORD 2 ]-+---------------------------------

client_addr | 10.20.2.42

state | streaming

sent_lsn | 0/32EBB268

write_lsn | 0/32EBB268

flush_lsn | 0/32EBB268

replay_lsn | 0/32EBB268

backend_start | 13-AUG-18 09:12:33.355563 +07:00

-[ RECORD 3 ]-+---------------------------------

client_addr | 10.20.2.44

state | streaming

sent_lsn | 0/32EBB268

write_lsn | 0/32EBB268

flush_lsn | 0/32EBB268

replay_lsn | 0/32EBB268

backend_start | 13-AUG-18 09:12:33.3665 +07:00

edb=# select pg_current_wal_lsn();

-[ RECORD 1 ]------+-----------

pg_current_wal_lsn | 0/32EBB268

edb=# select pg_walfile_name(pg_current_wal_lsn());

-[ RECORD 1 ]---+-------------------------

pg_walfile_name | 000000010000000000000032

edb=# select pg_ls_logdir();

-[ RECORD 1 ]+---------------------------------------------------------------

pg_ls_logdir | (logedbnode1-2018-08-13.log,5903,"13-AUG-18 09:12:31 +07:00")

-[ RECORD 2 ]+---------------------------------------------------------------

pg_ls_logdir | (logedbnode1-2018-08-14.log,0,"14-AUG-18 00:00:00 +07:00")

-[ RECORD 3 ]+---------------------------------------------------------------

pg_ls_logdir | (logedbnode1-2018-08-15.log,243,"15-AUG-18 08:16:15 +07:00")

-[ RECORD 4 ]+---------------------------------------------------------------

pg_ls_logdir | (logedbnode1-2018-08-16.log,852,"16-AUG-18 11:16:49 +07:00")

edb=# select pg_export_snapshot() ;

-[ RECORD 1 ]------+--------------------

pg_export_snapshot | 00000004-00011F54-1

-- on main compare WAL status using lsn diff

edb=# select pg_wal_lsn_diff('0/32EBB268','0/32EBB262');

-[ RECORD 1 ]---+--

pg_wal_lsn_diff | 6

edb=# select * from pg_replication_slots;

(0 rows)

edb=# SELECT pid, now() - query_start as "runtime", usename, datname, wait_event, state, query FROM pg_stat_activity WHERE now() - query_start > '2 minutes'::interval and state = 'active' ORDER BY runtime DESC;

(0 rows)

edb=# select * from pg_stat_user_indexes;

relid | indexrelid | schemaname | relname | indexrelname | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+---------+---------------+----------+--------------+---------------
16384 | 16387 | public | dept | dept_pk | 31 | 31 | 31
16384 | 16389 | public | dept | dept_dname_uq | 0 | 0 | 0
16391 | 16395 | public | emp | emp_pk | 17 | 17 | 17
16402 | 16406 | public | jobhist | jobhist_pk | 0 | 0 | 0
(4 rows)

edb=# select * from pg_stat_database;

-[ RECORD 1 ]---+---------------------------------

datid | 15710

datname | edb

numbackends | 1

xact_commit | 37639

xact_rollback | 16

blks_read | 2484

blks_hit | 1758549

blks_icache_hit | 0

tup_returned | 32520049

tup_fetched | 221821

tup_inserted | 321

tup_updated | 28

tup_deleted | 8

conflicts | 0

temp_files | 0

temp_bytes | 0

deadlocks | 0

blk_read_time | 0

blk_write_time | 0

stats_reset | 03-AUG-18 14:34:48.046807 +07:00

-[ RECORD 2 ]---+---------------------------------

datid | 16455

datname | testing1

numbackends | 0

xact_commit | 139778

xact_rollback | 338

blks_read | 15362

blks_hit | 11279179

blks_icache_hit | 0

tup_returned | 17071091

tup_fetched | 1911181

tup_inserted | 184026

tup_updated | 253587

tup_deleted | 0

conflicts | 0

temp_files | 0

temp_bytes | 0

deadlocks | 0

blk_read_time | 0

blk_write_time | 0

stats_reset | 10-AUG-18 17:22:50.28873 +07:00

edb=# SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL;

edb=# SELECT pg_stat_get_backend_pid(s.backendid) AS pid,pg_stat_get_backend_activity(s.backendid) AS query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

-[ RECORD 1 ]-----------------------------------------------------------

pid | 99937

query | <command string not enabled>

-[ RECORD 2 ]-----------------------------------------------------------

pid | 99941

query | <command string not enabled>

-[ RECORD 3 ]-----------------------------------------------------------

pid | 99940

query | <command string not enabled>

-[ RECORD 4 ]-----------------------------------------------------------

pid | 32759

query | SELECT pg_stat_get_backend_pid(s.backendid) AS pid, +

| pg_stat_get_backend_activity(s.backendid) AS query +

| FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;

PURI_MARMER_FILES_TEST=# SELECT pg_size_pretty( pg_database_size('PURI_MARMER_FILES_TEST') );

-[ RECORD 1 ]--+-------

pg_size_pretty | 944 GB

PURI_MARMER_FILES_TEST=# SELECT pg_size_pretty( pg_total_relation_size('detail_penjualan') );

-[ RECORD 1 ]--+--------

pg_size_pretty | 1020 MB

postgres=# SELECT pg_database.datname AS database_name, pg_database_size(pg_database.datname) AS database_size_bytes, pg_size_pretty(pg_database_size(pg_database.datname)) AS database_size FROM pg_database UNION ALL SELECT 'TOTAL' AS database_name, sum(pg_database_size(pg_database.datname)) AS database_size_bytes, pg_size_pretty(sum(pg_database_size(pg_database.datname))) AS database_size FROM pg_database ORDER BY database_size_bytes ASC;

database_name | database_size_bytes | database_size

------------------------------------+---------------------------------+---------------

template0 | 7668227 | 7489 kB

postgres | 7804391 | 7621 kB

template1 | 7804391 | 7621 kB

testing1 | 26023399 | 25 MB

PURI_MARMER_FILES_TEST | 1197589288423 | 1115 GB

PURI_MARMER_FILES | 1556767888871 | 1450 GB

TOTAL | 2754414096262 | 2565 GB

(8 rows)

PURI_MARMER_FILES_TEST=# \c PURI_MARMER_FILES

You are now connected to database "PURI_MARMER_FILES" as user "postgres".

PURI_MARMER_FILES=# SELECT stats.relname AS table, pg_size_pretty(pg_relation_size(statsio.relid)) AS table_size, pg_size_pretty(pg_total_relation_size(statsio.relid) - pg_relation_size(statsio.relid)) AS related_objects_size, pg_size_pretty(pg_total_relation_size(statsio.relid)) AS total_table_size, stats.n_live_tup AS live_rows FROM pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS stats USING (relname) WHERE stats.schemaname = current_schema --Replace with any schema name UNION ALL SELECT 'TOTAL' AS table, pg_size_pretty(sum(pg_relation_size(statsio.relid))) AS table_size, pg_size_pretty(sum(pg_total_relation_size(statsio.relid) - pg_relation_size(statsio.relid))) AS related_objects_size, pg_size_pretty(sum(pg_total_relation_size(statsio.relid))) AS total_table_size, sum(stats.n_live_tup) AS live_rows FROM pg_catalog.pg_statio_user_tables AS statsio JOIN pg_stat_user_tables AS stats USING (relname) WHERE stats.schemaname = current_schema --Replace with any schema name ORDER BY live_rows ASC;

table | table_size | related_objects_size | total_table_size| live_rows

-----------------------------------+----------------+-------------------------+----------------+-----------

penjualan_detail | 0 bytes | 16 kB | 16 kB | 0

penjualan_activity_1803 | 29 MB | 7065 MB | 7094 MB | 0

penjualan_contract | 0 bytes | 16 kB | 16 kB | 0

TOTAL | 29 MB | 7065 MB | 7065 MB | 0

(3 rows)

PURI_MARMER_FILES=#

===============================================================================================================================================

IP XX EDB REPLICATION

edb=# \x

Expanded display is on.

edb=# select pg_last_wal_receive_lsn() "receive_lsn", pg_last_wal_replay_lsn() "replay_lsn", pg_is_in_recovery() "recovery_status";

-[ RECORD 1 ]---+-----------

receive_lsn | 0/32EBB268

replay_lsn | 0/32EBB268

recovery_status | t

edb=# select pg_last_wal_receive_lsn();

-[ RECORD 1 ]-----------+-----------

pg_last_wal_receive_lsn | 0/32EBB268

edb=# select pid, wait_event, wait_event_type, state, query from pg_stat_activity;

edb=# SELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted

edb-# FROM pg_locks l, pg_stat_all_tables t

edb-# WHERE l.relation = t.relid ORDER BY relation asc;

edb=# SELECT pid, now() - query_start as "runtime", usename, datname, wait_event, state, query

FROM pg_stat_activity

WHERE now() - query_start > '2 minutes'::interval and state = 'active'

ORDER BY runtime DESC;

(0 rows)

edb=# select pg_is_in_recovery();

-[ RECORD 1 ]-----+--

pg_is_in_recovery | t

Database Administrator Postgres

Search This Blog

How To Monitoring EnterpriseDB 10 With Console On Centos 7

Comments

Post a Comment