分工不設限

 

前言

去年一些巧合, 跟團隊幾個人接手一個案子, 這個案子原本算是服務客戶特定需求的 POC, 但由於客戶愈來愈依賴這個工具, 因此交到我們手上.

PS. 在我加入之前, 已經有人辛苦耕耘了好一陣子. 不過也因緣際會離開這個案子. 一個案子要成功, 從來不是誰可以獨立勝任. 享受合作的當下, 同時也要記得這是很多默默付出的人辛勞的結果, 而非甚麼簡單的水到渠成.. 

挑戰

1. 技術背景不同
1. 案子用到的技術是 NodeJS, Angular, MongoDB
2. 成員技術背景是 Java, Cassandra
2. 需求大: 要把 MongoDB 換成 PostgreSQL
3. 技術債: 由於是個 POC 的案子, 所以程式結構沒有特別維護, 整組就是典型的 callback hell, 經常性的 callback 到第四第五層
4. 沒有統一的 build flow: 原本交付的方式就是從某個工程師的電腦打包出一個 image 給客戶安裝, 在 POC 也還合理, 但要產品化就無法接受了
5. 概括承受 bug, 當前客戶在用的系統如果遇到問題, 就需要與 technical support, customer support 一起討論與解決問題

接受挑戰

初期

1. Study: 由於技術不熟悉, 所以大家還是花時間學一下 NodeJS, Angular, MongoDB.
2. List features: 一起把功能使用一番, 列出來

規劃

Thread-1-1: 換資料庫

1. 一開始我們把所有的 table 列出來, 想要"縱切"來分開發方式. 也就是一個 table 一個 table 的把程式從面對 MongoDB 改為面對 PostgreSQL
2. 做一段時間後, 深感部分 Data Access layer 面對 MongoDB, 部分面對 PostgreSQL, 常常碰到一些因為資料不一致出現的錯誤.
   這種錯誤讓人無法確認現在的開發是好了沒有, 所以我就決定開始衝刺把所有的 Data Access Layer 通通轉成面對 PostgreSQL, 而不理會功能是否損毀.
3. 同時間, 另一個成員深受 callback hell 的困擾, 因此在討論後, 開始大幅度的將 callback hell 改為 controller -> service -> dao 這樣的三層式架構

Thread-1-2: build script

團隊內的大神基於興趣, 接手了 build flow 的規劃. 將原本由工程師在自己電腦上 build image 的方式, 轉為

1. 準備 docker 環境
2. 在 docker 內準備好 CentOS, PostgreSQL, NodeJS, Angular 的環境
3. 安裝好後 build 出客戶需要的 vmware image
4. 準備 CLI 讓客戶裝好 image 後, console 上能跳出 setup 的 CLI.
5. 支援 join 第二台 PostgreSQL
6. 在 Jenkins 上執行 build script

Thread-1-3: nginx

1. 專案原本用 openresty 在 nginx 寫程式 access MongoDB
2. 我們認為這設計導致不容易管理資料庫由誰存取
3. 因此規劃改寫 openresty 從直接存取資料庫, 改為存取 API 來存取資料.

Thread-2-1: data migration

在 Data Access Layer 改為面向 PostgreSQL 之後, 接下來需要處理怎麼從 MongoDB 轉移過來, 因此規劃了

1. 把 MongoDB 資料轉出
2. 建構 PostgreSQL 的 migration script, 包含建立 schema 的 DDL
3. 提供 user import MongoDB data 的功能

Thread-2-2: licensing

原本的案子沒有 license 的功能, 因此也照公司其他產品的方式規劃了 license

Thread-3-1: documentation

慢慢功能都快補好了, 開始需要與 document team 溝通與準備需要的文件

Thread-3-2: unit test

過去缺乏 unit test, 因此大量地補上

成就與心得

1. 像這樣分很多個 thread 做了非常多的事情, 一切只發生在大概五個月內, 而且我們成員就五個人, 且大多都還有其他在進行的專案.
2. 通常一個案子在進行的時候, 很多人可能會想要把要做的事情都規範好, 希望能夠營造一個 "我都規畫好了, 你照做就沒問題"
3. 但我們的作法則是: "目標就這些, 一起來看怎麼處理"
4. 在執行過程中, 每個人都在貢獻自己能做的事情, 而且當遇到覺得有問題的地方, 提出來後大家就能一起討論做決定, 接著繼續分頭進行
5. 由於能夠參與決定, 大家就更有 ownership, 就會提出非常多很好的意見
6. 我自己就很開心能享受到架構與流程的改善, 而且完全是大家自發性的改變, 回想這一段時間, 很短, 但很充實愉快

後記

這個案子, 因為一些因素, 人員變動, 在我們做完之後暫時告一段落.

大家繼續各自去忙不同的案子.

後來公司找了外包團隊.

所以接下來我還會一次與公司突然開始加入的外包團隊, functional QA & performance QA 一起合作.

正在如火如荼, 再次經歷一段美好的團隊合作經驗.

應用 XOR 特性取出相字元或數字

1. XOR 的特性, 相同的值 XOR 會變成 0

0^0=0

1^0=1

0^1=1

1^1=0

// code

int n = 0;

for (int i = 0; i < 10000; i++) {

    n ^= i;

}

for (int i = 0; i < 10000; i++) {

    if (i == 999) continue;

    n ^= i;

}

System.out.println(n); // print 999

2. 應用: 兩個字串只有一個字元不同的時候, 可以用來找出該不同的字元為何.
   (https://leetcode.com/problems/find-the-difference/)

class Solution {

    // ex. s = "abc", t="ab", then ch = 'c'

    public char findTheDifference(String s, String t) {

        char ch = 0;

        for (Character c: s.toCharArray()) {

            ch ^= c;

        }

        for (Character c: t.toCharArray()) {

            ch ^= c;

        }

        return ch;

    }

}

Explain - LeetCode 525 Contiguous Array

題目: Given a binary array nums, return the maximum length of a contiguous subarray with an equal number of 0 and 1

解法:

1. 當遇到 0 就 -1, 遇到 1 就 +1, 計算每個陣列位置的加總
2. 最重要的概念就是: 當遇到相同的加總數字, 表示中間經歷了相同的 1 & 0.
3. 因此解法就是:
1. 走過所有的陣列, 計算每個位子的 count. 遇到 0 就 -1, 遇到 1 就 +1
2. 最重要的概念是: 當過程中出現相同的 count (不管正負) 就表示過程中有相同的 0 與 1
3. 因此解法就是一邊算與紀錄 count, 如果遇到相同的 count 就算距離, 把最長的距離記錄下來

Code

public class Solution {

    public static void main(String[] args) {

        new Solution().findMaxLength(new int[]{0,0,1,0,0,0,1,1});

    }

    public int findMaxLength(int[] nums) {

        Map<Integer, Integer> map = new HashMap<>();

        map.put(0, -1);

        int maxlen = 0, count = 0;

        for (int i = 0; i < nums.length; i++) {

            count = count + (nums[i] == 1 ? 1 : -1);

            if (map.containsKey(count)) { 2. 如果以前有過跟現在相同的 count, 表示過程中經歷了相同的 0 & 1

                maxlen = Math.max(maxlen, i - map.get(count)); // 3. 以此計算距離

            } else {

                map.put(count, i); // 1. 紀錄目前的 count 的位置

            }

        }

        return maxlen;

    }

}

PostgreSQL version schema performance comparison

Requirement

1. Developers keep releasing new software, the version format was {major}.{minor}.{micro}.{build}
2. When a device ask for upgrade information, we need figure out the latest versions of software
3. But not only the latest one, we may need to know following version information, so that we can provide suitable recommendation
1. is there are 3 newer versions of software?
2. How many newer software versions?

Challenge

1. Database sort text by "Natural Sorting"
2. These 2 versions, the older version will be treated as the newer one if we use natural sorting
1. 1.2.3.100 => Will be treated as older because 2 > 1
2. 1.2.3.20 => So the version 20 will be treated as newer

Ideas

1. Persist major, minor, micro, build in different columns
2. Calculate versions to be a number, which can be sorted correctly
3. Merge versions to be a single text, and need to be sorted correctly (We need padLeft 0 to let all versions become the same length)
4. Persist major, minor, micro, build in a byte array (BLOB),
   assume the natural sorting will compare BLOB from the first element of an array.

Test Steps

1. Prepare TestContainer for PostgreSQL testing
2. Generate 10000 records
1. major 0
2. minor 0-9
3. micro 0-9
4. build 0-99
3. Compare with a specified version: 0.6.7.58
4. Compare query plan and query result (Query the latest 3 records)

Code for the test

https://github.com/axxdeveloper/postgresql-schema-testing/tree/master/src/test/java/com/shooeugenesea/dao

Tables

Version1

DDL

CREATE TABLE version1 (

id uuid PRIMARY KEY,

major int,

minor int,

micro int,

build int

);

CREATE INDEX version1_major_idx ON version1 (major DESC);

CREATE INDEX version1_major_minor_idx ON version1 (major DESC, minor DESC);

CREATE INDEX version1_major_minor_micro_idx ON version1 (major DESC, minor DESC, micro DESC);

CREATE INDEX version1_major_minor_micro_build_idx ON version1 (major DESC, minor DESC, micro DESC, build DESC);

SQL

String sql = "SELECT * FROM Version1 " +

"WHERE (major > 0) " +

"OR (major = 0 AND minor > 6) " +

"OR (major = 0 AND minor = 6 AND micro > 7) " +

"OR (major = 0 AND minor = 6 AND micro = 7 AND build > 58) " +

"ORDER BY major DESC, minor DESC, micro DESC, build DESC " +

"LIMIT 3";

Query Plan

QUERY PLAN: Limit (cost=0.29..1.16 rows=3 width=32) (actual time=0.065..0.193 rows=3 loops=1)

QUERY PLAN: -> Index Scan using version1_major_minor_micro_build_idx on version1 (cost=0.29..982.84 rows=3366 width=32) (actual time=0.051..0.073 rows=3 loops=1)

QUERY PLAN: Filter: ((major > 0) OR ((major = 0) AND (minor > 6)) OR ((major = 0) AND (minor = 6) AND (micro > 7)) OR ((major = 0) AND (minor = 6) AND (micro = 7) AND (build > 58)))

QUERY PLAN: Planning Time: 0.920 ms

QUERY PLAN: Execution Time: 0.307 ms

QUERY RESULT: {major=0, minor=9, micro=9, build=99, id=7e5878ff-9d25-46dc-a0c0-79196fd8c5d3}

QUERY RESULT: {major=0, minor=9, micro=9, build=98, id=ee2097ef-fe86-491e-869b-afda5976a354}

QUERY RESULT: {major=0, minor=9, micro=9, build=97, id=ada64c48-72e2-4a39-a6b0-5eabda135d0d}

Version2

DDL

CREATE TABLE version2 (

id uuid PRIMARY KEY,

build int

);

CREATE INDEX version2_order_idx ON version2 (build DESC);

SQL

String sql = "SELECT * FROM Version2 " +

"WHERE build > " + getVersion2Number(0,6,7,58) +

" ORDER BY build DESC" +

" LIMIT 3";

Query Plan

QUERY PLAN: Limit (cost=0.29..0.61 rows=3 width=20) (actual time=0.053..0.124 rows=3 loops=1)

QUERY PLAN: -> Index Scan using version2_order_idx on version2 (cost=0.29..391.76 rows=3627 width=20) (actual time=0.037..0.060 rows=3 loops=1)

QUERY PLAN: Index Cond: (build > 6758)

QUERY PLAN: Planning Time: 0.388 ms

QUERY PLAN: Execution Time: 0.195 ms

QUERY RESULT: {build=9999, id=e32b8067-9cae-4c49-b776-372e8a2137e4}

QUERY RESULT: {build=9998, id=c2902963-6579-48d3-bb91-deac6a8528bd}

QUERY RESULT: {build=9997, id=de28843c-b245-4b46-8e2d-7e549f0b862e}

Version3

DDL

CREATE TABLE version3 (

id uuid PRIMARY KEY,

build bytea

);

CREATE INDEX version3_order_idx ON version3 (build DESC);

SQL

String sql = "SELECT * FROM Version3 " +

"WHERE build > ?" +

" ORDER BY build DESC" +

" LIMIT 3";

Query Plan

QUERY PLAN: Limit (cost=0.28..0.77 rows=3 width=48) (actual time=0.063..0.130 rows=3 loops=1)

QUERY PLAN: -> Index Scan using version3_order_idx on version3 (cost=0.28..368.24 rows=2283 width=48) (actual time=0.047..0.068 rows=3 loops=1)

QUERY PLAN: Index Cond: (build > '\x000506073a'::bytea)

QUERY PLAN: Planning Time: 0.329 ms

QUERY PLAN: Execution Time: 0.214 ms

[0, 9, 9, 99]

[0, 9, 9, 98]

[0, 9, 9, 97]

Version4

DDL

CREATE TABLE version4 (

id uuid PRIMARY KEY,

build VARCHAR

);

CREATE INDEX version4_build_idx ON version4 (build DESC);

SQL

String sql = "SELECT * FROM Version4 " +

"WHERE build > '" + getVersion4Text(0, 6, 7, 58) + "'" +

" ORDER BY build DESC" +

" LIMIT 3";

Query Plan

QUERY PLAN: Limit (cost=0.29..0.80 rows=3 width=48) (actual time=0.074..0.137 rows=3 loops=1)

QUERY PLAN: -> Index Scan using version4_build_idx on version4 (cost=0.29..512.72 rows=2996 width=48) (actual time=0.060..0.079 rows=3 loops=1)

QUERY PLAN: Index Cond: ((build)::text > '0000000600070058'::text)

QUERY PLAN: Planning Time: 0.313 ms

QUERY PLAN: Execution Time: 0.256 ms

QUERY RESULT: {build=0000000900090099, id=29f9f1a0-40eb-4a31-873f-ff04868fa3d1}

QUERY RESULT: {build=0000000900090098, id=1ead5f4d-73d9-4cda-b899-f745b90f8598}

QUERY RESULT: {build=0000000900090097, id=bc9a4dfe-12c9-4c34-8925-836e8c4a7ad3}

Comparison

Options	cost	Pros & Cons
Version1 (split columns)	(cost=0.29..982.84 rows=3366 width=32)	Pros: Flexible, can change SQL easily Cons: Slow
Version2 (calculate to number)	(cost=0.29..391.76 rows=3627 width=20)	Pros: Fast Cons: Need calculate before persist and may need migrate if the logic to compare changed Sorting will be broken if the number exceed max number
Version3 (calculate to string)	(cost=0.28..368.24 rows=2283 width=48)	Pros: Fast and don't have max number issue Cons:  Need calculate before persist and may need migrate if the logic to compare changed
Version4 (calculate to byte array)	(cost=0.29..512.72 rows=2996 width=48)	Pros: Don't need extra calculation Cons: Easy to exceed max byte number, so still need extra calculation Slow

Kafka - Pick a transaction.id

Intention: Why I need Kafka Transaction

• 需求中收到 message 並處理之後, application 需要另外傳送訊息出去給多個 topic.
• 不管遇到任何錯誤, 我都希望訊息就不要送出去.
• 除此之外, 原本 consume 的訊息也不要收下來

How it works

1. Producer initTransaction with transaction.id
2. TransactionCoordinator close existing pending transactions with same transaction.id
3. Producer send message, the message will be wrote to topic
4. Producer commitTransaction, the TransactionCoordinator will start the 2 phase commit process
1. Write PREPARE_COMMIT to the "transactionLog" topic
2. Mark "commit" status in topic partitions
3. write COMMITTED to transactionLog
5. After these steps, transaction was pretty much to be finished

Note: transactionLog 是 internal topic, 用 transaction.id 作為 partition key, 因此可以保證狀態的順序

如果有兩個 Producer 共用 transaction.id, 當 Kafka 發現有相同的 transaction.id 存在的時候, 就會把先前的 transaction close,

因此如果 transaction.id 沒有規劃好就會遇到 transaction 莫名的被 abort 的 error.

Atomic Read-Process-Write

• Consumer 收訊息下來後透過呼叫 commit offset 來標記已經處理完
• 這個 offset 其實也是個 topic
• 藉由前面介紹的 transaction 處理機制, 可以讓 "commitOffset" 也只是發訊息到一個 topic, 也可以被包在同一個 transaction 中

適當的 transaction.id

• transaction.id 需要夠 unique, 如此才能避免 Producer 共用 transaction.id 而被 close
• 由於每個 transaction 都需要一些額外的 request 才能完成, 所以如果 transaction.id 定太細導致一堆 transaction 會使效能大幅降低
• tx-{consumeTopic}-{consumePartition} 是一個折衷的 transactionid, 因為一個 consumer 只會對應到一個 topic 以及 partition.
  在 read-process-write 的 pattern 下, 這個 transaction.id 會被該 topic & partition 的 consumer 使用, 不會有 multi-thread producer with same transaction.id 的情況.
  也不會過於分散 (同樣是在 read-process-write pattern 下, 一個 consumer thread 會一個一個訊息處理, 每個 consumed message 都值得一個 transaction (id).

KafkaTemplate 有個設定: producerPerConsumerPartition 就是拿來建立 tx-{consumeTopic}-{consumePartition} 這樣的 transaction.id.

它的做法就是在接收訊息的時候, 把 topic & partition 記錄下來, 好在發送訊息的時候 append 到 transaction.id prefix 後面

SpringBoot + Flyway + Kafka + PostgreSQL + Testcontainers

Source Code: https://github.com/axxdeveloper/study-practice/tree/testcontainer

在一個 sharing session 分享如何使用 testcontainer 輔助 SpringBoot application 開發測試 Kafka & PostgresSQL  相關的邏輯.

用 TestContainer 沒甚麼問題, 主要是多個 test class 開關 Kafka & Postgres 之後要重新讓 SpringBoot 連線比較麻煩, 這時候用了 DirtiesContexts

 

用 protobuf Any 來 parse byte array

第一次錄影片分享技術議題.

Source code: https://github.com/axxdeveloper/study-practice/tree/gpb 

主要其實就是之後可以用 Any.pack( gpbEntity ).toByteArray 傳送出去.
接收端也適用 Any.parseFrom( byteArray ).unpack( gpbEntity.class ).

這樣可以用 Any.parseFrom (byteArray).is( gpbEntity.class ) 來判斷應該要用哪個 gpbEntity 來讀資料.

PostgreSQL Replication

 

• WAL - Write Ahead Log, or xlog, or transaction log.
• WAL 就像是 Cassandra 的 CommitLog, 會先被存起來, 再寫進資料庫, 使 Postgres 不管何時被關閉, 重啟後都可以恢復資料.
• WAL 存在 pg_wal folder 下
• Postgres 13, 放在 /var/lib/pgsql/13/data/pg_wal
• WAL 檔案預設 16MB
• WAL 是用 binary format 寫入的
• Checkpoint
• 用來清除 WAL. 確認 WAL 已經寫入 data 就可以把 WAL 清掉了
• 由系統自動驅動, 不過可以在 postgresql.conf 裡面設定驅動的參數.
  例如可以指定 Checkpoint 之間的週期, 或是 wal 檔案大小的區間.

# - Checkpoints -

#checkpoint_timeout = 5min # range 30s-1d

max_wal_size = 1GB

min_wal_size = 80MB

#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0

#checkpoint_flush_after = 256kB # measured in pages, 0 disables

#checkpoint_warning = 30s # 0 disables

• WAL file 會被排序過再寫到硬碟, 來增加寫入的效能
• 比較長的 Checkpoint 區間會減少 WAL file 的數量
• PITR: point-in-time-recovery
• 做 HA 需要 PITR backup/restore, 因為 standby node 在剛啟動的時候需要先有一個從 primary node 建立的 base backup
• PITR 的設定在 postgresql.conf (擷取部分)

#------------------------------------------------------------------------------

# WRITE-AHEAD LOG

#------------------------------------------------------------------------------

wal_level = replica

#------------------------------------------------------------------------------

# REPLICATION

#------------------------------------------------------------------------------

max_wal_senders = 10

max_replication_slots = 10

• wal_level = replica (https://docs.postgresql.tw/server-administration/server-configuration/write-ahead-log) 預設 replica 使能夠準備足夠的 transaction log 給 PITR restore 使用, 如果設定成
• max_wal_senders = 10 (https://docs.postgresql.tw/server-administration/server-configuration/replication) 最大的寫量, 注意不能大於 max_connection
• max_replication_slots = 10 (https://docs.postgresql.tw/server-administration/server-configuration/replication) 指定最大的 replication slot, 就是一份寫入的資料, 要 replica 到幾個 node 才可以 (或是 replica 到所有的 standby)
• Archive transaction log
• 設定在 postgresql.conf

# - Archiving -

archive_mode = on # enables archiving; off, on, or always

# (change requires restart)

archive_command = 'cp "%p" "/var/lib/pgsql/archivedir/%f"' # command to use to archive a logfile segment

# placeholders: %p = path of file to archive

# %f = file name only

# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

#archive_timeout = 0 # force a logfile segment switch after this

# number of seconds; 0 disables

• 以這預設值, Postgres 會持續把 WAL 寫入 /var/lib/pgsql/archivedir/%f

[admin@rnd1 ~]$ sudo ls -l /var/lib/pgsql/13/data/pg_wal

total 32772

-rw------- 1 postgres postgres 337 Jun 9 08:40 000000010000000000000002.00000028.backup

-rw------- 1 postgres postgres 16777216 Jun 10 04:21 000000010000000000000003

-rw------- 1 postgres postgres 16777216 Jun 9 08:40 000000010000000000000004

drwx------ 2 postgres postgres 59 Jun 9 08:45 archive_status

[admin@rnd1 ~]$ sudo ls -l /var/lib/pgsql/archivedir/

total 49156

-rw------- 1 postgres postgres 16777216 Jun 9 08:40 000000010000000000000001

-rw------- 1 postgres postgres 16777216 Jun 9 08:40 000000010000000000000002

-rw------- 1 postgres postgres 337 Jun 9 08:40 000000010000000000000002.00000028.backup

-rw------- 1 postgres postgres 16777216 Jun 9 08:32 000000010000000000000003

[admin@rnd1 ~]$

• pg_hba.conf (host-based authentication)
• 如果要用 pg_basebackup 就需要設定 pg_hba.conf

local database user auth-method [auth-options]

host database user address auth-method [auth-options]

hostssl database user address auth-method [auth-options]

hostnossl database user address auth-method [auth-options]

hostgssenc database user address auth-method [auth-options]

hostnogssenc database user address auth-method [auth-options]

host database user IP-address IP-mask auth-method [auth-options]

hostssl database user IP-address IP-mask auth-method [auth-options]

hostnossl database user IP-address IP-mask auth-method [auth-options]

hostgssenc database user IP-address IP-mask auth-method [auth-options]

hostnogssenc database user IP-address IP-mask auth-method [auth-options]

Ex. 從 local or 127.0.0.1 or ::1 來的, 用來 replication 的 user 為 postgres 的連線都一律通過

# Allow replication connections from localhost, by a user with the

# replication privilege.

local replication postgres trust

host replication postgres 127.0.0.1/32 trust

host replication postgres ::1/128 trust

• https://www.postgresql.org/docs/13/auth-pg-hba-conf.html
• 執行 pg_basebackup
• https://www.postgresql.org/docs/13/app-pgbasebackup.html
• 這段指令會把 Postgres 的資料複製到 /some_target_dir 下

pg_basebackup -D /some_target_dir -h localhost --checkpoint=fast --wal-method=stream

• PITR restore
• 指定 restore_command 與 recovery_target_timeline

restore_command = 'cp /mnt/server/archivedir/%f "%p"'

recovery_target_timeline = latest

• 啟動後等關鍵字 "consistent recovery state reached"
• Ex. 用 pg_basebackup 要求 standby 去 sync data

pg_basebackup -D /target -h master.example.com --checkpoint=fast --wal-method=stream -R

• 使用 pg_basebackup 的時候可以 -R, 如此會把 standby configuration 寫進 postgresql.conf

standby_mode = on

primary_conninfo = ' ...'

• Debug
• pg_stat_replication ( *_lsn, lsn 是 location 的意思, 所以 sent_lsn 就是 sent_location )

select * from pg_stat_replication;

pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_l

ag | sync_priority | sync_state | reply_time

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

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

13594 | 16385 | repl | 10.206.79.240 | 10.206.79.240 | | 50462 | 2021-06-09 10:21:35.997181+00 | | streaming | 0/30255F8 | 0/30255F8 | 0/30255F8 | 0/30255F8 | | |

| 0 | async | 2021-06-13 18:06:50.870681+00

(1 row)

• pg_stat_wal_receiver (可以在 standby 查)

select * from pg_stat_wal_receiver;

pid | status | receive_start_lsn | receive_start_tli | written_lsn | flushed_lsn | received_tli | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time | slot_name | send

er_host | sender_port | conninfo

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

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

--------------------------------------------------------------------------------------------------

1598 | streaming | 0/3000000 | 1 | 0/30255F8 | 0/30255F8 | 1 | 2021-06-13 18:12:41.476929+00 | 2021-06-13 18:12:41.477835+00 | 0/30255F8 | 2021-06-10 04:21:07.190989+00 | 10_206_79_240 | 10.20

6.79.197 | 5432 | user=repl passfile=/var/lib/pgsql/.pgpass channel_binding=prefer dbname=replication host=10.206.79.197 port=5432 application_name=10.206.79.240 fallback_application_name=walreceiver sslmode=prefer sslcompression=

0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

(1 row)

• Timeline
• 一開始 Postgres primary 的 timeline 是 1, transaction log 的檔名像是0000000100000000000000F5
• 當 standby 被 promote 了, timeline 會變成 2, transaction log 的檔名像是0000000200000000000000F5
• hot_standby_feedback = off
• 由於 primary 與 standby 的狀態是單方面從 primary sync to standby, 所以如果 standby 做了一些 transaction 相關的事情, 會因為 primary 同時間也有動作而產生錯誤.
• 為了能夠修正這個錯誤, 透過 hot_standby_feedback = on, 使 standby 可以定期傳"最後一筆 transaction log" 給 primary, 如此 primary 就有機會可以補救 (例如因為注意到 standby 的 transaction log 有差距而避免 delete data)
• 不過為了避免 standby 長時間的 transaction 影響效能, hot_standby_feedback 是被關掉的 (相較於 transaction log streaming 效能好)
• anyway, 如果在 OLTP 的情況下, select 花很久時間, 就可以考慮改這個設定

Angular - Built-in directives

ngIf

true => Host div will be included in the HTML elements

false => Host div will be excluded in the HTML elements

<div *ngIf="needShow(4)" class="bg-info p-2 mt-1"> Check need to show if pass 4 </div>

<div *ngIf="doNotShow()" class="bg-info p-2 mt-1"> Expect do not show this element </div>

ngSwitch

Same, only matched element will be included in the HTML elements.

However the ngSwitch host element will always be included in HTML element.

<div class="bg-info p-2 mt-1" [ngSwitch]="howManyPeople()"> 

    <span *ngSwitchCase="2">There are two people</span> 

    <span *ngSwitchCase="5">There are five people</span> 

    <span *ngSwitchDefault>This is the default</span> 

</div>

Need double quote when comparing string

<div class="bg-info p-2 mt-1" [ngSwitch]="whatIsYourName()">

    <span *ngSwitchCase="'Anderson'">I know you!!</span>

    <span *ngSwitchCase="'QQ'">Hi, nice to meet you</span>

    <span *ngSwitchDefault>Hello?</span>

</div>

ngFor

repeats a section of content for each object in an array, providing the template equivalent of a foreach loop.

Template variables: index (zero based number), odd (boolean), even (boolean), first (boolean), last (boolean)

<tr *ngFor="let item of getProducts(); let i = index; let odd = odd; let even = even"> 

    <td>{{item.name}}</td> 

    <td>{{item.category}}</td>

    <td>{{item.price}}</td> 

</tr>

由於 ngFor 需要 iterate data, 當 data source 改變的時候, ngFor 需要重新跑一次 data source 會讓效能變差.

為了提升效能, 可以在 component 訂一個 method, 例如 getKey (注意 signature 第一個參數是 index, 第二個參數是 object), 然後在 ngFor 裡面定義 trackBy:getKey, 如此就能讓 ngFor 了解雖然是從 data source 拿到新的物件, 但其實是同一筆資料

1. define getKey method

import { ApplicationRef, Component } 

from "@angular/core"; 

import { Model } from "./repository.model"; 

import { Product } from "./product.model"; 

@Component({ 

    selector: "app", 

    templateUrl: "template.html" 

})

export class ProductComponent { 

model: Model = new Model(); 

    // ...constructor and methods omitted for brevity... 

    getKey(index: number, product: Product) { 

        return product.id; 

    } 

}

2. 定義 trackBy:getKey

<tr *ngFor="let item of getProducts();let i = index;let odd = odd;let even = even;trackBy:getKey">

    <td>{{item.name}}</td>

    <td>{{item.category}}</td>

    <td>{{item.price}}</td>

</tr>

ngTemplateOutlet

Used to repeat a block of content in a specified location

1. Define a template and the content

<ng-template #myTemplate> 

    <div>Hello</dic>

</ng-template>

2. Output the template

<ng-template [ngTemplateOutlet]="myTemplate"></ng-template>

<div>KKKKK</div>

<ng-template [ngTemplateOutlet]="myTemplate"></ng-template>

Provide Context Data Binding

1. Define template with context. We defined "text" variable in template, and the value will be the expression result of "title"
   (Use let- to define a variable)

<ng-template #myTemplate let-text="title"> 

    <h4 class="p-2 bg-success text-white">{{text}}</h4> 

</ng-template>

2. Use the defined template and provide data for binding

<ng-template [ngTemplateOutlet]="myTemplate" [ngTemplateOutletContext]="{title: 'Header'}"> </ng-template>

Keep in mind

1. Expressions need to be idempontent
2. Can NOT access objects defined outside of the template's component, and in particular, templates can't access the global namespace.
3. Global namespace must be provided by component, acting as on behalf of the template

import { ApplicationRef, Component }

from "@angular/core";

import { Model } from "./repository.model";

import { Product } from "./product.model";

@Component({

    selector: "app",

    templateUrl: "template.html"

})

export class ProductComponent {

model: Model = new Model();

    // ...constructor and methods omitted for brevity...

    getNumber(): number {

        return Math.floow(1); // The Math can't be accessed by template

    }

    getKey(index: number, product: Product) {

        return product.id;

    }

}

init mongodb data in docker

 1. Given file in project folder ./mongo/docker-entrypoint-initdb.d:/initdb.sh

```

echo '=====================================>'

mongo --eval 'db.getSiblingDB("testqq").createUser({"user": "admin", "pwd": "admin", roles: [{"role": "readWrite","db": "testqq"}]});'

mongo --eval 'db.getSiblingDB("testqq").users.insert({"username" : "admin", "password" : "admin", "email" : "admin@gmail.com"});'

echo '<======================================'

```

2. Given docker-compose.yml

```

services:

  mongo:

    image: mongo:4.4

    ports:

      - "27017:27017"

    volumes:

    - "./mongo/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d"

```

3. Start docker-compose

```

$ docker-compose up

```

4. Check log, can find following log

```

mongo_1  | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/initdb.sh

mongo_1  | =====================================>

mongo_1  | MongoDB shell version v4.4.3

mongo_1  | connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb

mongo_1  | {"t":{"$date":"2021-01-25T16:48:42.786+00:00"},"s":"I",  "c":"NETWORK",  "id":22943,   "ctx":"listener","msg":"Connection accepted","attr":{"remote":"127.0.0.1:46062","connectionId":2,"connectionCount":1}}

mongo_1  | {"t":{"$date":"2021-01-25T16:48:42.787+00:00"},"s":"I",  "c":"NETWORK",  "id":51800,   "ctx":"conn2","msg":"client metadata","attr":{"remote":"127.0.0.1:46062","client":"conn2","doc":{"application":{"name":"MongoDB Shell"},"driver":{"name":"MongoDB Internal Client","version":"4.4.3"},"os":{"type":"Linux","name":"Ubuntu","architecture":"x86_64","version":"18.04"}}}}

mongo_1  | Implicit session: session { "id" : UUID("14d8434a-7c74-4509-a5de-d55a4c63bdf4") }

mongo_1  | MongoDB server version: 4.4.3

mongo_1  | {"t":{"$date":"2021-01-25T16:48:42.835+00:00"},"s":"I",  "c":"STORAGE",  "id":20320,   "ctx":"conn2","msg":"createCollection","attr":{"namespace":"admin.system.users","uuidDisposition":"generated","uuid":{"uuid":{"$uuid":"e8923719-dd5a-4a94-a417-bb32a7ca4ebc"}},"options":{}}}

mongo_1  | {"t":{"$date":"2021-01-25T16:48:42.854+00:00"},"s":"I",  "c":"INDEX",    "id":20345,   "ctx":"conn2","msg":"Index build: done building","attr":{"buildUUID":null,"namespace":"admin.system.users","index":"_id_","commitTimestamp":{"$timestamp":{"t":0,"i":0}}}}

mongo_1  | {"t":{"$date":"2021-01-25T16:48:42.854+00:00"},"s":"I",  "c":"INDEX",    "id":20345,   "ctx":"conn2","msg":"Index build: done building","attr":{"buildUUID":null,"namespace":"admin.system.users","index":"user_1_db_1","commitTimestamp":{"$timestamp":{"t":0,"i":0}}}}

mongo_1  | Successfully added user: {

mongo_1  | "user" : "admin",

mongo_1  | "roles" : [

mongo_1  | {

mongo_1  | "role" : "readWrite",

mongo_1  | "db" : "testqq"

mongo_1  | }

mongo_1  | ]

mongo_1  | }

mongo_1  | {"t":{"$date":"2021-01-25T16:48:42.859+00:00"},"s":"I",  "c":"NETWORK",  "id":22944,   "ctx":"conn2","msg":"Connection ended","attr":{"remote":"127.0.0.1:46062","connectionId":2,"connectionCount":0}}

mongo_1  | MongoDB shell version v4.4.3

mongo_1  | connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb

mongo_1  | {"t":{"$date":"2021-01-25T16:48:42.926+00:00"},"s":"I",  "c":"NETWORK",  "id":22943,   "ctx":"listener","msg":"Connection accepted","attr":{"remote":"127.0.0.1:46064","connectionId":3,"connectionCount":1}}

mongo_1  | {"t":{"$date":"2021-01-25T16:48:42.927+00:00"},"s":"I",  "c":"NETWORK",  "id":51800,   "ctx":"conn3","msg":"client metadata","attr":{"remote":"127.0.0.1:46064","client":"conn3","doc":{"application":{"name":"MongoDB Shell"},"driver":{"name":"MongoDB Internal Client","version":"4.4.3"},"os":{"type":"Linux","name":"Ubuntu","architecture":"x86_64","version":"18.04"}}}}

mongo_1  | Implicit session: session { "id" : UUID("b0df77be-4789-4cb7-a1d0-6b64717b0207") }

mongo_1  | MongoDB server version: 4.4.3

mongo_1  | {"t":{"$date":"2021-01-25T16:48:42.937+00:00"},"s":"I",  "c":"STORAGE",  "id":20320,   "ctx":"conn3","msg":"createCollection","attr":{"namespace":"testqq.users","uuidDisposition":"generated","uuid":{"uuid":{"$uuid":"fb550551-8a86-4f98-9a93-b324e057084d"}},"options":{}}}

mongo_1  | {"t":{"$date":"2021-01-25T16:48:42.950+00:00"},"s":"I",  "c":"INDEX",    "id":20345,   "ctx":"conn3","msg":"Index build: done building","attr":{"buildUUID":null,"namespace":"testqq.users","index":"_id_","commitTimestamp":{"$timestamp":{"t":0,"i":0}}}}

mongo_1  | WriteResult({ "nInserted" : 1 })

mongo_1  | {"t":{"$date":"2021-01-25T16:48:42.957+00:00"},"s":"I",  "c":"NETWORK",  "id":22944,   "ctx":"conn3","msg":"Connection ended","attr":{"remote":"127.0.0.1:46064","connectionId":3,"connectionCount":0}}

mongo_1  | <======================================

```

5. Access mongo and check db

```

cds % docker exec -it cds_mongo_1 bash

root@186eb2a0f48d:/# mongo

MongoDB shell version v4.4.3

connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb

Implicit session: session { "id" : UUID("59729a8b-ecb1-439a-ab9f-63cc06f30e54") }

MongoDB server version: 4.4.3

Welcome to the MongoDB shell.

For interactive help, type "help".

For more comprehensive documentation, see

https://docs.mongodb.com/

Questions? Try the MongoDB Developer Community Forums

https://community.mongodb.com

---

The server generated these startup warnings when booting:

        2021-01-25T16:52:03.600+00:00: Using the XFS filesystem is strongly recommended with the WiredTiger storage engine. See http://dochub.mongodb.org/core/prodnotes-filesystem

        2021-01-25T16:52:04.496+00:00: Access control is not enabled for the database. Read and write access to data and configuration is unrestricted

---

---

        Enable MongoDB's free cloud-based monitoring service, which will then receive and display

        metrics about your deployment (disk utilization, CPU, operation statistics, etc).

        The monitoring data will be available on a MongoDB website with a unique URL accessible to you

        and anyone you share the URL with. MongoDB may use this information to make product

        improvements and to suggest MongoDB products and deployment options to you.

        To enable free monitoring, run the following command: db.enableFreeMonitoring()

        To permanently disable this reminder, run the following command: db.disableFreeMonitoring()

---

> db.getSiblingDB('testqq').getUsers()

[

{

"_id" : "testqq.admin",

"userId" : UUID("886d3d0f-7457-4724-8ae6-d9494382bce4"),

"user" : "admin",

"db" : "testqq",

"roles" : [

{

"role" : "readWrite",

"db" : "testqq"

}

],

"mechanisms" : [

"SCRAM-SHA-1",

"SCRAM-SHA-256"

]

}

]

>

```