2020年07月04日

PostgreSQL 接続数を確認

PostgreSQL 接続数を確認する
SELECT * FROM pg_stat_activity
SELECT count(*) FROM pg_stat_activity


同時接続数の設定値
show  max_connections;

アクセス頻度の高いテーブル

select
    relname,
    coalesce(seq_tup_read,0)+coalesce(idx_tup_fetch,0)+
    coalesce(n_tup_ins,0)+coalesce(n_tup_upd,0)+coalesce(n_tup_del,0) as total,
    coalesce(seq_tup_read,0)+coalesce(idx_tup_fetch,0) as select,
    coalesce(n_tup_ins,0) as insert,
    coalesce(n_tup_upd,0) as update,
    coalesce(n_tup_del,0) as delete
from pg_stat_user_tables
order by total desc;



I/O&キャッシュヒット率

select
    *,
    (heap_blks_hit*100) / (heap_blks_read+heap_blks_hit) as ritu
  from pg_statio_all_tables
 where heap_blks_hit >= 1
   and schemaname = 'public' order by ritu desc;


インデックス利用回数
select * from pg_stat_user_indexes ;


統計情報をクリア
select pg_stat_reset();




select datname from pg_database;
select * from pg_database;



PostgreSQLが遅い原因を今すぐ監視して調査する方法




ログ
postgresql.conf  の logging_collectorの値がoffの場合はonに変更

/var/lib/psql/12/data/log









posted by a23 at 09:31| Comment(0) | PostgreSQL

2020年07月03日

pgAdmin4

pgadmin4-4.23-x86.exe を Windows7 32 bit  にインストールしようとして

err.png

---------------------------
Error
---------------------------
Runtime Error (at 12:609):


Internal error: Cannot access 64-bit registry keys on this version of Windows.
---------------------------
OK   
---------------------------

内部エラー:このバージョンのWindowsでは64ビットのレジストリキーにアクセスできません。

posted by a23 at 17:32| Comment(0) | PostgreSQL

2020年06月24日

PostgreSQL12 CentOS8 dnf Install

PostgreSQL12 Install


Linux downloads (Red Hat family)

https://www.postgresql.org/download/linux/redhat/
1.png

 
1.
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm2.
2.png
2.
dnf -qy module disable postgresql
3.png
3.
dnf install -y postgresql12-server
4.png
4.
/usr/pgsql-12/bin/postgresql-12-setup initdb5.
5.png
5.
systemctl enable postgresql-126.
systemctl start postgresql-12
6.png

6. \l
7.png








# Install the repository RPM:
dnf -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable the built-in PostgreSQL module:
dnf -qy module disable postgresql

# Install PostgreSQL:
dnf -y install postgresql12-server

# Optionally initialize the database and enable automatic start:
/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable postgresql-12
systemctl start postgresql-12

 su postgres
 psql
 \l
 alter role postgres with password 'dbPasswd123';
 create database testdb1;
 \q

firewall-cmd --add-port=5432/tcp --zone=public --permanent
firewall-cmd --reload
systemctl restart postgresql-12

posted by a23 at 15:44| Comment(0) | PostgreSQL