# Virtual Machines (Compute Cloud)

Создание группы виртуальных машин:
https://cloud.yandex.ru/docs/compute/quickstart/ig

## Создание виртуальной машины:
name vm: otus-db-pg-vm-1
disk: SSD 20
2CPU x 8MEM
Цена: 3 910,20 ₽ в месяц

Создать сеть:
Каталог: default
Имя: otus-vm-db-pg-net-1

Создать подсеть:
Имя: otus-vm-db-pg-subnet-1
CIDR: 10.0.0.0/24

Доступ
username: otus

Сгенерировать ssh-key:
```bash
cd ~/Otus/PostgreSQL_Cloud/PG_Yandex_Cloud/demo

ssh-keygen -t rsa -b 2048 -f ~/Otus/PostgreSQL_Cloud/PG_Yandex_Cloud/demo/otus_key
name ssh-key: otus_key
# #passphrase: pasS$12345
# chmod 400 ~/Otus/PostgreSQL_Cloud/PG_Yandex_Cloud/demo/otus_key.pub
cat otus_key.pub
```

## Подключение к VM:
https://cloud.yandex.ru/docs/compute/operations/vm-connect/ssh

cd ~/Otus/PostgreSQL_Cloud/PG_Yandex_Cloud/demo
ssh -i otus_key otus@51.250.77.113

## Установка Postgres (Ubuntu 22.04):
```bash
sudo apt update \
    && sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' \
    && wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - \
    && sudo apt update \
    && sudo apt -y install postgresql-15 \
    && sudo apt -y install unzip mc nano

sudo apt install postgresql-15-wal2json     # for ETL
sudo -u postgres psql -c "SELECT version();"
pg_lsclusters
```

## Установить пароль для Postgres:
```bash
sudo -u postgres psql
\password   # 123
\q
```

## Добавить сетевые правила для подключения к Postgres:
```bash
cd /etc/postgresql/15/main/
sudo nano /etc/postgresql/15/main/postgresql.conf
#listen_addresses = 'localhost'
listen_addresses = '*'
wal_level = logical     # for ETL

sudo nano /etc/postgresql/15/main/pg_hba.conf
#host    all             all             127.0.0.1/32            md5 password
host    all             all             0.0.0.0/0               scram-sha-256 

sudo pg_ctlcluster 15 main restart
pg_lsclusters
```

## Подключение к Postgres:
```bash
cd ~/Otus/PostgreSQL_Cloud/PG_Yandex_Cloud/demo
PGPASSWORD=123 psql -h51.250.77.113 -p5432 -Upostgres -dpostgres

\c postgres
\l
\du
\dt
\dt+
\d table
\q

exit
```

## Тестирование (pgbench)
### inizialization
export PGPASSWORD=123
psql -h51.250.77.113 -p5432 -Upostgres -dpostgres -c "CREATE DATABASE benchmark;"
pgbench -h51.250.77.113 -p5432 -Upostgres -i -s 15 benchmark    -- 1 500 000 rows

### run test
<!-- pgbench -h51.250.77.113 -p5432 -Upostgres -c 50 -j 2 -P 60 -t 10000 benchmark   -- 10k транзакций -->
pgbench -h51.250.77.113 -p5432 -Upostgres -c 50 -j 2 -P 60 -T 180 benchmark     -- 3 минуты


# Yandex Managed Service for PostgreSQL
Цена: 5 141,80 ₽ в месяц

Начало работы с интерфейсом командной строки (CLI)

# yc init
# yc config list

token: 111111111111111
cloud-id: 22222222222222222
folder-id: b1gackfhe17va0567u1t
compute-default-zone: ru-central1-a

## Создание
https://cloud.yandex.ru/docs/managed-postgresql/operations/cluster-create
https://cloud.yandex.ru/docs/compute/concepts/disk#disks_types

```bash
cd ~/Otus/PostgreSQL_Cloud/PG_Yandex_Cloud/demo
yc vpc network list

yc vpc network create --name otus-db-pg-net-1 \
    --description "otus-db-pg-net-1"
yc vpc network list

yc vpc subnet create --name otus-db-pg-subnet-a-1 \
  --description "otus-db-pg-subnet-a-1" \
  --folder-id b1gackfhe17va0567u1t \
  --network-id enpt6g298oqc852vvch8 \
  --zone ru-central1-a \
  --range 10.135.0.0/24

yc vpc subnet create --name otus-db-pg-subnet-b-1 \
  --description "otus-db-pg-subnet-b-1" \
  --folder-id b1gackfhe17va0567u1t \
  --network-id enpt6g298oqc852vvch8 \
  --zone ru-central1-b \
  --range 10.136.0.0/24

yc vpc subnet list

yc managed-postgresql cluster create --help
yc managed-mongodb resource-preset list | grep s2.micro

yc managed-postgresql cluster create \
   --name otus-db-pg-msp-1 \
   --environment production \
   --network-name otus-db-pg-net-1 \
   --host zone-id=ru-central1-a,subnet-id=e9bn4lrf8s1e4l4c609g,assign-public-ip \
   --host zone-id=ru-central1-b,subnet-id=e2lscu4ll7jdj2u74vmc,assign-public-ip \
   --resource-preset s2.micro \
   --user name=otus,password=pasS@12345 \
   --database name=otus_db,owner=otus \
   --disk-size 20 \
   --disk-type  network-ssd \
   --datatransfer-access \
   --datalens-access \
   --websql-access \
   --serverless-access \
   --postgresql-version 14 --async

--done (6m38s)

yc managed-postgresql cluster list
yc managed-postgresql database list \
     --cluster-name otus-db-pg-msp-1
```

## Подключение
https://cloud.yandex.ru/docs/managed-postgresql/operations/connect

```bash
cd ~/Otus/PostgreSQL_Cloud/PG_Yandex_Cloud/demo

# curl "https://storage.yandexcloud.net/cloud-certs/CA.pem" -o ya_ca.crt
# chmod 0644 ya_ca.crt

yc managed-postgresql host add --help
yc managed-postgresql host list --cluster-name otus-db-pg-msp-1

psql "host=rc1a-5yk3sl3ea4eyi3or.mdb.yandexcloud.net,rc1b-usqsijgoxiq96du9.mdb.yandexcloud.net \
      port=6432 \
      sslmode=verify-full \
      sslrootcert=ya_ca.crt \
      dbname=otus_db \
      user=otus \
      password=pasS@12345 \
      target_session_attrs=read-write"
\conninfo

psql "host=rc1b-usqsijgoxiq96du9.mdb.yandexcloud.net \
      port=6432 \
      sslmode=verify-full \
      sslrootcert=ya_ca.crt \
      dbname=otus_db \
      user=otus \
      password=pasS@12345 \
      target_session_attrs=read-write"

psql "host=rc1b-usqsijgoxiq96du9.mdb.yandexcloud.net \
      port=6432 \
      sslmode=verify-full \
      sslrootcert=ya_ca.crt \
      dbname=otus_db \
      user=otus \
      password=pasS@12345 \
      target_session_attrs=any"

# \password #pasS@12345
```

```sql
SELECT * FROM pg_replication_slots \gx
SELECT * FROM pg_stat_replication \gx
-- select * from pg_settings where name like '%port%';
```

## Тестирование (pgbench)
### inizialization
export PGPASSWORD=pasS@12345
psql -hrc1a-5yk3sl3ea4eyi3or.mdb.yandexcloud.net -p6432 -Uotus -dotus_db -c "CREATE DATABASE benchmark;"
pgbench -hrc1a-5yk3sl3ea4eyi3or.mdb.yandexcloud.net -p6432 -Uotus -i -s 15 benchmark                    -- 1 500 000 rows

### run test
<!-- pgbench -h51.250.77.113 -p5432 -Upostgres -c 50 -j 2 -P 60 -t 10000 benchmark   -- 10k транзакций -->
pgbench -hrc1a-5yk3sl3ea4eyi3or.mdb.yandexcloud.net -p6432 -Uotus -c 50 -j 2 -P 60 -T 180 benchmark     -- 3 минуты
psql -hrc1a-5yk3sl3ea4eyi3or.mdb.yandexcloud.net -p6432 -Uotus -dbenchmark

# Yandex Data Transfer 
https://cloud.yandex.ru/docs/data-transfer/concepts/?from=int-console-help-center-or-nav

# Источник (VM)
```bash
cd ~/Otus/PostgreSQL_Cloud/PG_Yandex_Cloud/demo
export PGPASSWORD=123
psql -h51.250.77.113 -p5432 -Upostgres -dpostgres -c "CREATE DATABASE otus_db;"
psql -h51.250.77.113 -p5432 -Upostgres -dotus_db -c "create table test as SELECT generate_series(1,1000) AS id,md5(random()::text) AS text;"
psql -h51.250.77.113 -p5432 -Upostgres -dotus_db -c "ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY (id);"

psql -h51.250.77.113 -p5432 -Upostgres -dotus_db -c "SELECT * FROM public.test LIMIT 10;"
psql -h51.250.77.113 -p5432 -Upostgres -dotus_db -c "SELECT count(*) FROM public.test;"

psql -h51.250.77.113 -p5432 -Upostgres -dotus_db -c "SHOW wal_level;"

export PGPASSWORD=123
psql -h51.250.77.113 -p5432 -Upostgres -dotus_db -c "INSERT INTO public.test VALUES (1001,'test');"
# sudo nano /etc/postgresql/15/main/postgresql.conf
# wal_level = logical
# sudo pg_ctlcluster 15 main restart
# psql -h51.250.77.113 -p5432 -Upostgres -dotus_db -c "select pg_reload_conf();"

Имя: otus-vm-db-pg
```

# Приемник (DBaaS)
```bash
export PGPASSWORD=pasS@12345
psql -hrc1a-5yk3sl3ea4eyi3or.mdb.yandexcloud.net -p6432 -Uotus -dotus_db -c "SELECT count(*) FROM public.test;"
psql -hrc1a-5yk3sl3ea4eyi3or.mdb.yandexcloud.net -p6432 -Uotus -dotus_db -c "SELECT * FROM public.test WHERE id = 1001;"

Имя: otus-db-pg
# table=public.test
```

# Трансфер
Имя: otus-db-pg-tr


# Yandex Database
https://www.youtube.com/watch?v=MlSdUq5RIN8
https://cloud.yandex.ru/docs/ydb/
https://cloud.yandex.ru/docs/ydb/pricing/ru-yql
https://ydb.tech/ru/docs/yql/reference/builtins/basic
Name: otus-db-ydb-1

```sql
CREATE TABLE series
(
    series_id Uint64,
    title Utf8,
    series_info Utf8,
    release_date Uint64,
    PRIMARY KEY (series_id)
);

CREATE TABLE seasons
(
    series_id Uint64,
    season_id Uint64,
    title Utf8,
    first_aired Uint64,
    last_aired Uint64,
    PRIMARY KEY (series_id, season_id)
);

CREATE TABLE episodes
(
    series_id Uint64,
    season_id Uint64,
    episode_id Uint64,
    title Utf8,
    air_date Uint64,
    PRIMARY KEY (series_id, season_id, episode_id)
);

ALTER TABLE episodes ADD COLUMN is_deleted Bool;
ALTER TABLE episodes DROP COLUMN is_deleted;

DROP TABLE actors;

REPLACE INTO episodes (series_id, season_id, episode_id, title) VALUES (1, 1, 1, "Yesterday's Jam");
REPLACE INTO episodes (series_id, season_id, episode_id, title) VALUES (1, 1, 2, "Calamity Jen");

REPLACE INTO series (series_id, title, release_date, series_info)
VALUES
    (
        1,
        "IT Crowd",
        CAST(Date("2006-02-03") AS Uint64),
        "The IT Crowd is a British sitcom produced by Channel 4, written by Graham Linehan, produced by Ash Atalla and starring Chris O'Dowd, Richard Ayoade, Katherine Parkinson, and Matt Berry."
    ),
    (
        2,
        "Silicon Valley",
        CAST(Date("2014-04-06") AS Uint64),
        "Silicon Valley is an American comedy television series created by Mike Judge, John Altschuler and Dave Krinsky. The series focuses on five young men who founded a startup company in Silicon Valley."
    )
    ;

REPLACE INTO seasons (series_id, season_id, title, first_aired, last_aired)
VALUES
    (1, 1, "Season 1", CAST(Date("2006-02-03") AS Uint64), CAST(Date("2006-03-03") AS Uint64)),
    (1, 2, "Season 2", CAST(Date("2007-08-24") AS Uint64), CAST(Date("2007-09-28") AS Uint64)),
    (2, 1, "Season 1", CAST(Date("2014-04-06") AS Uint64), CAST(Date("2014-06-01") AS Uint64)),
    (2, 2, "Season 2", CAST(Date("2015-04-12") AS Uint64), CAST(Date("2015-06-14") AS Uint64))
;

REPLACE INTO episodes (series_id, season_id, episode_id, title, air_date)
VALUES
    (1, 1, 1, "Yesterday's Jam", CAST(Date("2006-02-03") AS Uint64)),
    (1, 1, 2, "Calamity Jen", CAST(Date("2006-02-03") AS Uint64)),
    (2, 1, 1, "Minimum Viable Product", CAST(Date("2014-04-06") AS Uint64)),
    (2, 1, 2, "The Cap Table", CAST(Date("2014-04-13") AS Uint64))
;

UPSERT INTO episodes
(
    series_id,
    season_id,
    episode_id,
    title,
    air_date
)
VALUES
(
    2,
    1,
    3,
    "Test Episode",
    CAST(Date("2018-08-27") AS Uint64)
)
;

SELECT
    series_id,
    title AS series_title,
    CAST (release_date AS Date) AS release_date
FROM series;


/* Параметризированный запрос
DECLARE $seriesId AS Uint64;
DECLARE $seasonId AS Uint64;

$seriesId = 1;
$seasonId = 2;

SELECT sa.title AS season_title, sr.title AS series_title
FROM seasons AS sa
INNER JOIN series AS sr
ON sa.series_id = sr.series_id
WHERE sa.series_id = $seriesId AND sa.season_id = $seasonId;
*/
```

# Yandex Database Работа с SDK
https://cloud.yandex.ru/docs/ydb/quickstart/yql-api/ydb-sdk#connect-to-db

cd ~/Otus/PostgreSQL_Cloud/PG_Yandex_Cloud/demo
git clone https://github.com/yandex-cloud/ydb-nodejs-sdk.git
# cd ydb-nodejs-sdk/examples
# npm install
npm run build





=============================================================================================================================

# Test Outputs
## VM
vpopov@MacBook-Pro-Vitaly ~ % pgbench -h51.250.77.113 -p5432 -Upostgres -i -s 15 benchmark
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
1500000 of 1500000 tuples (100%) done (elapsed 12.78 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 32.07 s (drop tables 0.04 s, create tables 0.16 s, client-side generate 25.46 s, vacuum 0.33 s, primary keys 6.08 s).
vpopov@MacBook-Pro-Vitaly ~ % pgbench -h51.250.77.113 -p5432 -Upostgres -c 50 -j 2 -P 60 -T 180 benchmark
pgbench (15.3)
starting vacuum...end.
progress: 60.0 s, 116.2 tps, lat 376.435 ms stddev 153.718, 0 failed
progress: 120.0 s, 128.6 tps, lat 389.085 ms stddev 177.993, 0 failed
progress: 180.0 s, 123.1 tps, lat 406.160 ms stddev 190.876, 0 failed
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 15
query mode: simple
number of clients: 50
number of threads: 2
maximum number of tries: 1
duration: 180 s
number of transactions actually processed: 22122
number of failed transactions: 0 (0.000%)
latency average = 391.027 ms
latency stddev = 175.839 ms
initial connection time = 7207.923 ms
tps = 127.536536 (without initial connection time)

## DBaaS
vpopov@MacBook-Pro-Vitaly demo % pgbench -hrc1a-5yk3sl3ea4eyi3or.mdb.yandexcloud.net -p6432 -Uotus -i -s 15 benchmark
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
1500000 of 1500000 tuples (100%) done (elapsed 1.32 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 41.70 s (drop tables 0.04 s, create tables 0.20 s, client-side generate 33.45 s, vacuum 2.33 s, primary keys 5.68 s).
vpopov@MacBook-Pro-Vitaly demo % pgbench -hrc1a-5yk3sl3ea4eyi3or.mdb.yandexcloud.net -p6432 -Uotus -c 50 -j 2 -P 60 -T 180 benchmark
pgbench (15.3, server 14.8 (Ubuntu 14.8-201-yandex.52909.0e5cd5ea67))
starting vacuum...end.
progress: 60.0 s, 108.9 tps, lat 420.630 ms stddev 210.896, 0 failed
progress: 120.0 s, 117.2 tps, lat 426.487 ms stddev 226.829, 0 failed
progress: 180.0 s, 118.1 tps, lat 423.084 ms stddev 220.289, 0 failed
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 15
query mode: simple
number of clients: 50
number of threads: 2
maximum number of tries: 1
duration: 180 s
number of transactions actually processed: 20709
number of failed transactions: 0 (0.000%)
latency average = 423.624 ms
latency stddev = 219.727 ms
initial connection time = 4781.388 ms
tps = 117.759222 (without initial connection time)


=============================================================================================================================
2CPU x 8MEM

Architecture:            x86_64
  CPU op-mode(s):        32-bit, 64-bit
  Address sizes:         40 bits physical, 48 bits virtual
  Byte Order:            Little Endian
CPU(s):                  2
  On-line CPU(s) list:   0,1
Vendor ID:               GenuineIntel
  Model name:            Intel Xeon Processor (Cascadelake)
    CPU family:          6
    Model:               85
    Thread(s) per core:  2
    Core(s) per socket:  1
    Socket(s):           1
    Stepping:            6
    BogoMIPS:            4190.15
    Flags:               fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl xtopology nonstop_tsc cpuid tsc
                         _known_freq pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch cpuid_fault invpcid_single
                          pti ssbd ibrs ibpb stibp fsgsbase bmi1 hle avx2 smep bmi2 erms invpcid rtm avx512f avx512dq rdseed adx smap clflushopt clwb avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 arat pku osp
                         ke avx512_vnni md_clear
Virtualization features:
  Hypervisor vendor:     KVM
  Virtualization type:   full
Caches (sum of all):
  L1d:                   64 KiB (2 instances)
  L1i:                   64 KiB (2 instances)
  L2:                    4 MiB (1 instance)
  L3:                    16 MiB (1 instance)
NUMA:
  NUMA node(s):          1
  NUMA node0 CPU(s):     0,1
Vulnerabilities:
  Itlb multihit:         KVM: Mitigation: VMX unsupported
  L1tf:                  Mitigation; PTE Inversion
  Mds:                   Mitigation; Clear CPU buffers; SMT Host state unknown
  Meltdown:              Mitigation; PTI
  Mmio stale data:       Vulnerable: Clear CPU buffers attempted, no microcode; SMT Host state unknown
  Retbleed:              Mitigation; IBRS
  Spec store bypass:     Mitigation; Speculative Store Bypass disabled via prctl and seccomp
  Spectre v1:            Mitigation; usercopy/swapgs barriers and __user pointer sanitization
  Spectre v2:            Mitigation; IBRS, IBPB conditional, STIBP conditional, RSB filling, PBRSB-eIBRS Not affected
  Srbds:                 Not affected
  Tsx async abort:       Mitigation; Clear CPU buffers; SMT Host state unknown


=============================================================================================================================
# Test Outputs 202311
## VM
pgbench -h51.250.77.113 -p5432 -Upostgres -i -s 15 benchmark
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
1500000 of 1500000 tuples (100%) done (elapsed 14.28 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 35.17 s (drop tables 0.04 s, create tables 0.21 s, client-side generate 28.48 s, vacuum 0.36 s, primary keys 6.07 s).
vpopov@MacBook-Pro-Vitaly demo % pgbench -h51.250.77.113 -p5432 -Upostgres -c 50 -j 2 -P 60 -T 180 benchmark
pgbench (16.1, server 15.5 (Ubuntu 15.5-1.pgdg22.04+1))
starting vacuum...end.
progress: 60.0 s, 103.1 tps, lat 420.181 ms stddev 179.481, 0 failed
progress: 120.0 s, 119.6 tps, lat 417.472 ms stddev 195.584, 0 failed
progress: 180.0 s, 119.0 tps, lat 420.393 ms stddev 182.549, 0 failed
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 15
query mode: simple
number of clients: 50
number of threads: 2
maximum number of tries: 1
duration: 180 s
number of transactions actually processed: 20555
number of failed transactions: 0 (0.000%)
latency average = 419.654 ms
latency stddev = 186.898 ms
initial connection time = 7719.539 ms
tps = 118.804008 (without initial connection time)


## DBaaS
pgbench -hrc1a-5yk3sl3ea4eyi3or.mdb.yandexcloud.net -p6432 -Uotus -i -s 15 benchmark
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
1500000 of 1500000 tuples (100%) done (elapsed 1.18 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 45.03 s (drop tables 0.05 s, create tables 0.22 s, client-side generate 37.39 s, vacuum 0.99 s, primary keys 6.38 s).
vpopov@MacBook-Pro-Vitaly demo % pgbench -hrc1a-5yk3sl3ea4eyi3or.mdb.yandexcloud.net -p6432 -Uotus -c 50 -j 2 -P 60 -T 180 benchmark
pgbench (16.1, server 14.9 (Ubuntu 14.9-201-yandex.52963.cda84d2acd))
starting vacuum...end.
progress: 60.0 s, 85.6 tps, lat 521.855 ms stddev 253.372, 0 failed
progress: 120.0 s, 95.2 tps, lat 525.452 ms stddev 286.806, 0 failed
progress: 180.0 s, 93.4 tps, lat 534.738 ms stddev 305.696, 0 failed
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 15
query mode: simple
number of clients: 50
number of threads: 2
maximum number of tries: 1
duration: 180 s
number of transactions actually processed: 16506
number of failed transactions: 0 (0.000%)
latency average = 527.785 ms
latency stddev = 283.696 ms
initial connection time = 6050.596 ms
tps = 94.470727 (without initial connection time)