a developer

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 拿到新的物件, 但其實是同一筆資料

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; 
    } 
}

定義 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

Define a template and the content

<ng-template #myTemplate> 
    <div>Hello</dic>
</ng-template>

Output the template

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

Provide Context Data Binding

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>

Use the defined template and provide data for binding

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

Keep in mind

Expressions need to be idempontent
Can NOT access objects defined outside of the template's component, and in particular, templates can't access the global namespace.
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"

]

}

]

```

Start Kafka by docker-compose

docker-compose.yml

services:  
  zookeeper-server:
    image: bitnami/zookeeper:latest
    ports:
    - "2181:2181"
    environment:
      - ALLOW_ANONYMOUS_LOGIN=yes
    
  kafka-server:
    image: bitnami/kafka:latest
    ports:
    - "9092:9092"
    environment:
      - ALLOW_PLAINTEXT_LISTENER=yes
      - KAFKA_CFG_ZOOKEEPER_CONNECT=zookeeper-server:2181
      - KAFKA_CFG_ADVERTISED_LISTENERS=PLAINTEXT://localhost:9092

Start Kafka

docker-compose up -d

Stop kafka

docker-compose down

Full code: https://github.com/shooeugenesea/study-practice/blob/kafka/docker-compose.yml

Kafka Client assign and reset offset

1. Producer send messages

```

private static void sendMessages(Properties kafkaProps, String topic) {

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

try (KafkaProducer<String, String> producer = new KafkaProducer<>(kafkaProps)) {

ProducerRecord<String, String> record = new ProducerRecord<>(topic, "mykey", "myval" + i);

System.out.println("offset:" + producer.send(record).get(10, TimeUnit.SECONDS).offset());

} catch (Throwable ex) {

ex.printStackTrace();

}

```

2. Assign topic to consumer

```

try (KafkaConsumer<String, String> consumer = new KafkaConsumer<>(kafkaConsumerProps("localhost:9092"))) {

consumer.assign(consumer.partitionsFor(TOPIC).stream().map(p -> new TopicPartition(TOPIC, p.partition())).collect(Collectors.toList()));

resetOffset(consumer); // will NOT trigger java.lang.IllegalStateException: No current assignment for partition

```

3. Consume messages

```

private static int consumeAllMessages(KafkaConsumer<String, String> consumer) {

int total = 0;

while (true) {

ConsumerRecords<String, String> records = consumer.poll(Duration.ofSeconds(5));

if (records.count() > 0) {

total += records.count();

System.out.println("receive:" + records.count() + " messages.");

} else {

System.out.println("receive no message");

break;

}

return total;

}

```

4. Reset offset

```

private static void resetOffset(KafkaConsumer<String, String> consumer) {

consumer.partitionsFor(TOPIC).forEach(p -> {

System.out.println("Seek offset 0 in partition " + p.partition());

consumer.seek(new TopicPartition(TOPIC, p.partition()), 0);

});

}

```

5. Consume all messages

```