PostgreSQL¶

Installation¶

MacにPostgreSQLをインストール - Qiita

Commands¶

connect use database form psql
- psql <DB_NAME>
- psql -U <USER_NAME> <DB_NAME>
exit database \q
list databases \l
connect database \c <DB_NAME> \connect <DB_NAME>
list tables \d \dt
show shemas \d <TABLE_NAME> \dn
show table permission \z <TABLE_NAME>
show view \dv
show view definition select definition from pg_views where viewname = '<VIEW_NAME>';
show current user select current_user;
show user roles \du
show user info select * from pg_user;
grant access to user
- grant select, insert, update, delete on <TABLE_NAME> to <USER_NAME>;
revoke access to user
- revoke select, insert, update, delete on <TABLE_NAME>from <USER_NAME>;

References¶

PostgreSQLの基本的なコマンド - Qiita

cache hit ratioがよくない¶

まずは postgresql.conf まわりでtuning
- shared_buffers
  - 単位: 8KB
  - 目安: 2GB以下: 全メモリの20%, 32GB以下: 全メモリの25%, 32GB以上: 8GB, 全メモリの 1/4 - 1/2
    - OSの上限値 (shmmax) を上げないと増やせないのでOSをいじれないRDSではそんなに増やせない
- work_mem
  - 単位: KB
  - 目安: 最初は32MB程度から始め、log_temp_filesで吐かれたログを見つつ、倍々に増やしていく, work_mem * connection最大数が全メモリの1/4を超えないぐらいにしておく
- maintenance_work_mem
  - 単位: KB
  - 目安: システムメモリの10%。最大1GB, もしVACUUMの問題があればもっと大きく
- effective_cache_size
  - 単位: 8KB
  - 目安: 全メモリの 1/2 程度
- ref: PostgresのRDSチューニング - Qiita
tuneしても即座にcache_hit ratioには反映されないので，pg_stat_reset()を実行して統計情報をリセットする

zabbix=# select datname, blks_hit * 100.0 / (blks_read + blks_hit) AS cache_hit, stats_reset from pg_stat_database where datname = 'zabbix';
 datname |      cache_hit      |          stats_reset
---------+---------------------+-------------------------------
 zabbix  | 78.4450092005849089 | 2020-07-26 18:50:13.592914+09
(1 row)

zabbix=# select pg_stat_reset();
 pg_stat_reset
---------------

(1 row)

zabbix=# select datname, blks_hit * 100.0 / (blks_read + blks_hit) AS cache_hit, stats_reset from pg_stat_database where datname = 'zabbix';
 datname |      cache_hit      |          stats_reset
---------+---------------------+-------------------------------
 zabbix  | 99.8402555910543131 | 2020-08-17 08:32:21.216764+09
(1 row)

backup¶

pg_dump -cU $POSTGRES_USER $POSTGRES_DB' > `date +%Y%m%d-%H%M%S`_`hostname`_pg_dump_${POSTGRES_DB}.sql
pg_dump -cU $POSTGRES_USER $POSTGRES_DB' | gzip > `date +%Y%m%d-%H%M%S`_`hostname`_pg_dump_${POSTGRES_DB}.sql.gz
sudo -u postgres pg_dumpall > `date +%Y%m%d-%H%M%S`_`hostname`_pg_dump.sql
sudo -u postgres pg_dumpall | gzip > `date +%Y%m%d-%H%M%S`_`hostname`_pg_dump.sql.gz

restore¶

cat backup.sql | psql -U $POSTGRES_USER