# Postgres Master Slave

* Slave Conflicts Query

select * from pg_stat_database_conflicts;

減少衝突
1. hot_standby_feedback
2. vacuum_defaer_clean_up

* Slave Delay Time

SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

什麼情況需要重新建立?
- wal_keep_segments太小，來不及備份就被蓋掉。(ex.設定64，64*16MB = 1GB)

Slave Data Keeping Time(資料斷掉後保留時間為考量)
1. min_wal
2. wal_keep_segments

2020/12/22
hot_standby_feedback
vacuum_defaer_clean_up
或是延長query time(但會延長同步時間)

https://blog.csdn.net/dazuiba008/article/details/104966409

觀察每個Table 需要vacuum的部分
https://chenhuajun.github.io/2017/08/15/PostgreSQL%E7%9A%84%E8%A1%A8%E8%86%A8%E8%83%80%E5%8F%8A%E5%AF%B9%E7%AD%96.html

SELECT
      schemaname||'.'||relname,
      n_dead_tup,
      n_live_tup,
      round(n_dead_tup * 100 / (n_live_tup + n_dead_tup),2) AS dead_tup_ratio
  FROM
      pg_stat_all_tables
  WHERE
      n_dead_tup >= 10000
  ORDER BY dead_tup_ratio DESC
  LIMIT 10;

裝server
1. postgresql 先檢查 df -h 先看看未來postgresql 指定的root位置，space夠不夠
2. 裝postgresql 12，調整設定擋postgresql.conf，pg_hba.conf，create database [v4];
3. 給所有user, slave user
4. 裝apt-get install pgagent(但不要在[v4]extend)，然後跑migrate all，並監測有沒有ERROR
5. pgagent /root 底下pg check機制設定上去

DB LOCK
1. 看誰Lock Lock什麼

 SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  blockeda.pid AS blocked_pid, blockeda.query as blocked_query,
  blockedl.mode as blocked_mode, blockinga.pid AS blocking_pid,
  blockinga.query as blocking_query, blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
    (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted;

2. 查看 ps axu 看誰執行久
3. 看誰再執行

SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE now() - pg_stat_activity.query_start > interval '5 minutes';

4. 未來要改善

select * from pg_stat_statements pss where temp_blks_read > 0 order by mean_time desc

## 原裡
https://www.796t.com/content/1549241130.html

## 設定
https://mrpolo17.medium.com/set-up-postgresql-12-master-slave-a494e1a60356