<marquee>How To Monitoring Postgresql 10 With Console Linux</marquee>

In here I want to tell you monitoring postgresql with centos 7, so in here I can to tell you step by step monitoring postgresql 10 or EnterpriseDB 10.

Ok now you must login ssh IP XX EDB MASTER and then you can move to user enterprisedb if you use location on root.

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

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

-bash-4.2$

After move to user enterprisedb you can call database postgresql and move to database, you must use the command psql, but in here I use custom the port database postgres, if you custom port, you must call function “-p”, but if you use port default, no problem if you dont be called “-p”. ok you can do that command in here.

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

Password:

psql.bin (10.1.5)

Type "help" for help.

edb=#

Next, if you want see the size database, so you can do the query command.

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

db | size

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

testing1 | 35 MB

edb | 12 MB

postgres | 12 MB

template1 | 12 MB

template0 | 12 MB

(5 rows)

Step two, if you want the show process receiving data and sending or replaying data process in other application to use database you can see processing wal with the do query command.

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;

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

99987 | walreceiver | 0 | 0 | 0 | 0

99989 | walreceiver | 0 | 0 | 0 | 0

99991 | walreceiver | 0 | 0 | 0 | 0

(3 rows)

Ok next step, if you want the show the syncron data and the show ip replication, and start database until running streaming replication. You can do the query command.

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

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

client_addr | IP_B_Replication

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 | IP_C_Replication

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 | IP_D_Replication

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

But if you just wanna current wal syncron data in primary, you can do function “ pg_current_wal_lsn”. You can do query command.

edb=# select pg_current_wal_lsn();

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

pg_current_wal_lsn | 0/32EBB268

And if you wanna the show last file name in live to the wal archive. You can do query command.

edb=# select pg_walfile_name(pg_current_wal_lsn());

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

pg_walfile_name | 000000010000000000000032

So, if you wanna to the show log data from the beginning to the present. You can do the query command.

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")

And then, if you wanna see export snapshot on the database postgresql with query command.

edb=# select pg_export_snapshot() ;

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

pg_export_snapshot | 00000004-00011F54-1

So, if you wanna main compare WAL status using lsn diff with query command on postgresql. And if you wanna the status slot postgresql replication with the command “replicatyion slots”.

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)

Next step you wanna monitoring the show query lock to the exceed until 2 minutes on status postgres activity with query command short by descending. The query command.

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;

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

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)

And the next step, if you wanna show all status postgresql database with function query command.

edb=# select datname,numbackends,xact_commit,xact_rollback,blks_read,blks_hit,tup_inserted, tup_updated, tup_deleted 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 datname, pid, usename, application_name, client_addr, client_port, backend_start, query_start, state_change, wait_event, state, query FROM pg_stat_activity WHERE wait_event is NOT NULL;

pid | wait_event_type | wait_event

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

99937 | Activity | AutoVacuumMain

99941 | Activity | LogicalLauncherMain

99940 | Extension | Extension

99987 | Activity | WalSenderMain

99989 | Activity | WalSenderMain

99991 | Activity | WalSenderMain

99935 | Activity | BgWriterHibernate

99934 | Activity | CheckpointerMain

99936 | Activity | WalWriterMain

(9 rows)

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;

And if you wanna to the show size database just one database, you can do the query command.

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

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

pg_size_pretty | 944 GB

And if you wanna to the show size just one database or size all table on database, you can do the query command.

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=#

And if you wanna show row on all table for one database with query, you can do the query command.

PURI_MARMER_FILES=# SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;

schemaname | relname | n_live_tup

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

public | penjualan_activity_1803 | 204218

public | penjualan_detail | 0

public | penjualan_contract | 0

(3 rows)

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

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;

pid | wait_event | wait_event_type | state | query

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

44800 | | | active | select pid, wait_event, wait_event_type, state, query from pg_stat_activity;

86819 | RecoveryWalAll | Activity | |

86823 | BgWriterHibernate | Activity | |

86822 | CheckpointerMain | Activity | |

86861 | WalReceiverMain | Activity | |

(5 rows)

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;

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

pg_class | relation | | 2/41015 | 44800 | AccessShareLock | t

pg_index | relation | | 2/41015 | 44800 | AccessShareLock | t

pg_namespace | relation | | 2/41015 | 44800 | AccessShareLock | t

(3 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 pg_is_in_recovery();

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

pg_is_in_recovery | t

Database Administrator Postgres

Search This Blog

How To Monitoring Postgresql 10 With Console Linux

Comments

Post a Comment