# DB - Schedule

1. 監控 (linux指令/監控工具/pg_stat/lock/trobule shooting 文件/分析資料) 10/21
2. 裝db (設定/migrate/檢查/備份還原/master-slave切換) 10/21
3. 日常routine (開表/開欄位/檢視架構/熟悉migrate規則/新舊資料相容/上版流程注意)
4. 報表 (procedure及trigger/架構/資料校正及檢驗/確認排程) 10/24 ~ 10/28
v5 統計資訊
v4 統計資訊
5. 大功能
v4 彩票 (新增彩種流程/db結算) 10/24 ~ 10/28

## Permission
https://docs.google.com/presentation/d/1IjgW6XxJcIlZmsccGn9iUhZahZ_Ok_kMKriPhlBZjJA/edit#slide=id.gdb16580b8b_0_162
## Database Installation
- timezone
- shared_preload_libraries
- listen_addresses
- PG_TUNE
- Linux Disk
- pg_hba(host-based authentication)

### Backup-restore
pg_dump
pg_backup

Import
### Master-Slave
- Replication user
- Replication setting
- Check replication
https://mrpolo17.medium.com/set-up-postgresql-12-master-slave-a494e1a60356

- Exception & Adjustment

## Monitor & Analyze

Considereate(what's the bottleneck?)
- pg_stat_activity(https://help.aliyun.com/document_detail/171076.html)
- pg_stat_statements
- OS CPU Usage
- Duration time & Error Log

## Routine

## 日常Routine
上板流程
加欄位
修改資料
報表處理
## Utilities

-- 資料表大小
SELECT nspname || '.'||  relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) desc

## Database Version Upgrade
Reference: https://www.gushiciku.cn/pl/pSMA/zh-tw

### Little Version 12.x -> 12.12
#### Package upgrade

   SELECT version(); -- check version

service postgresql stop
apt-get upgrade postgresql-12*
service postgresql start 

### Main Version
Have not searched cluster! Need to rebuild cluster either pg_dump or pg_upgrade(https://www.paulox.net/2022/04/28/upgrading-postgresql-from-version-13-to-14-on-ubuntu-22-04-jammy-jellyfish/
)
#### Mehtod 1: pg_dump
1. pg_dump all of database
2. Stop database service
3. Update package
4. Setting pg_hba, postgresql.conf
5. Start new database service & create database
6. Import database

#### Method 2: pg_upgrade
Reference: https://www.postgresql.org/docs/current/pgupgrade.html
1. Search the specific pg_upgrate tool path(We will know the new version path!)

## Ubuntu20.04 Postgresql 12
/usr/lib/postgresql/12/bin/pg_upgrade 

## CentOS Postgresql 13
/usr/pgsql-13/bin/pg_upgrade

2. Install New Postgresql Version
3. Drop the extension from old database
To avoid extension error on checking

4. Check all of databases can be connected exclude template0

SELECT datname, datallowconn FROM pg_database;

To avoid database connection error on checking
Reference: https://gist.github.com/johand/1224125b1b55629346855d9d21b99a24

6. Stop old version postgresql & Stop new version postgresql & close cluster
7. Check version upgrade

su postgres
cd ~

/usr/lib/postgresql/14/bin/pg_upgrade  -d /var/lib/postgresql/12/main/  -D /var/lib/postgresql/14/main/  -b /usr/lib/postgresql/12/bin/  -B /usr/lib/postgresql/14/bin/  -o '-c config_file=/etc/postgresql/12/main/postgresql.conf' -O '-c config_file=/etc/postgresql/14/main/postgresql.conf'  --check

Error: such of errors
Check successfully

8. Upgrade database with 'link'

/usr/lib/postgresql/14/bin/pg_upgrade  -d /var/lib/postgresql/12/main/  -D /var/lib/postgresql/14/main/  -b /usr/lib/postgresql/12/bin/  -B /usr/lib/postgresql/14/bin/  -o '-c config_file=/etc/postgresql/12/main/postgresql.conf' -O '-c config_file=/etc/postgresql/14/main/postgresql.conf'  --link 

Upgrade successfully database 40G 40G total 100G (31 min)

9. Check new database pg_hba, postresql
Restart new version postgresql and check version

10. Execute analyze(40G 40G total 100G (40) min))

./analyze_new_cluster.sh

11. Rebuild cluster
12. remove old directory