https://cloud.ru/ru/docs/vdc/ug/topics/faq/common-questions/common-questions__where-is-dc.html https://sbertech.ru/documents/platform-v-pangolin-se/5.1.0 # Virtual Machines (Elastic Cloud Server) ## Создание виртуальной машины: ECS Name: otus-ecs-db-pg-1 Username: root Password: pas#1Word Flavor Name: c6.large.4 disk: SSD 20 2CPU x 8MEM Цена: 4.261 ₽/hour ## Подключение к VM: cd ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo ssh root@94.139.253.129 ## Установка Postgres: ```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_Sber_Cloud/demo PGPASSWORD=123 psql -h94.139.253.129 -p5432 -Upostgres -dpostgres \c postgres \l \du \dt \dt+ \d table \q exit ``` ## Тестирование (pgbench) ### inizialization export PGPASSWORD=123 psql -h94.139.253.129 -p5432 -Upostgres -dpostgres -c "CREATE DATABASE benchmark;" pgbench -h94.139.253.129 -p5432 -Upostgres -i -s 15 benchmark -- 1 500 000 rows ### run test pgbench -h94.139.253.129 -p5432 -Upostgres -c 50 -j 2 -P 60 -T 180 benchmark -- 3 минуты # Relational Database Service for MySQL cd ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/terraform/ # terraform init terraform plan terraform apply # -auto-approve # Relational Database Service for PostgreSQL ## Создание DB Instance Name: rds-otus-pg-01 Administrator: root Administrator Password: pas#1Word Цена: 5.98 ₽/hour ## Привязать Elastic Assign EIP ## Подключение к Postgres: ```bash cd ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo PGPASSWORD=pas#1Word psql -h94.139.253.129 -p5432 -Uroot -dpostgres \c postgres \l \du \dt \dt+ \d table \q exit ``` ## Тестирование (pgbench) ### inizialization export PGPASSWORD=pas#1Word psql -h94.139.253.129 -p5432 -Uroot -dpostgres -c "CREATE DATABASE benchmark;" pgbench -h94.139.253.129 -p5432 -Uroot -i -s 15 benchmark -- 1 500 000 rows ### run test pgbench -h94.139.253.129 -p5432 -Uroot -c 50 -j 2 -P 60 -T 180 benchmark -- 3 минуты ## Резервное копирование cd ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo export PGPASSWORD=pas#1Word psql -h94.139.253.129 -p5432 -Uroot -dpostgres -c "DROP DATABASE benchmark;" psql -h94.139.253.129 -p5432 -Uroot -dpostgres -c "CREATE DATABASE otus_db;" psql -h94.139.253.129 -p5432 -Uroot -dotus_db -c "create table test as SELECT generate_series(1,1000) AS id,md5(random()::text) AS text;" psql -h94.139.253.129 -p5432 -Uroot -dotus_db -c "ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY (id);" psql -h94.139.253.129 -p5432 -Uroot -dotus_db -c "SELECT * FROM public.test LIMIT 10;" psql -h94.139.253.129 -p5432 -Uroot -dotus_db -c "SELECT count(*) FROM public.test;" psql -h94.139.253.129 -p5432 -Uroot -dotus_db -c "select version();" # psql -h94.139.253.129 -p5432 -Uroot -dotus_db -c "SHOW wal_level;" ### pg_dump mkdir -p ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/pg_dump rm -r ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/pg_dump/* pg_dump -h94.139.253.129 -p5432 -Uroot -dotus_db --table=test > ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/pg_dump/pg_dump.sql less ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/pg_dump/pg_dump.sql ### restore from file mkdir -p ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/restore/ rm -r ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/restore/* ! move file into ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/restore/ chmod -R 777 ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/restore/ # user must be postgres # mv -f /Users/vpopov/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/restore/pg_wal /Users/vpopov/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/restore/base/ nano /Users/vpopov/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/restore/base/postgresql.conf ------------------------------------------------------------------------------------- # rds_superuser_funcs # conn_privilege_user_list # rds_manager_user # switch_sync_allow_flush_lag shared_preload_libraries = 'passwordcheck.so, pg_stat_statements, auth_delay' # (change requires restart) ssl = off # (change requires restart) # log_directory = '/var/lib/pgsql/log/error' # directory where log files are written, # unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories # (change requires restart) # unix_socket_group = 'Ruby' # (change requires restart) # unix_socket_permissions = 0700 # begin with 0 to use octal notation # restore_command = '/usr/pgsql/bin/rds_wal_restore %f %p' # archive_command = '/usr/pgsql/bin/rds_wal_archive_lz4 %p' # command to use to archive a logfile segment ------------------------------------------------------------------------------------- nano /Users/vpopov/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/restore/base/pg_hba.conf ------------------------------------------------------------------------------------- local replication rdsBackup md5 hostssl postgres rdsMetric 172.16.0.0/12 md5 hostssl replication rdsBackup 172.16.0.0/12 md5 hostssl postgres rdsRepl 172.16.0.0/12 md5 hostssl replication rdsRepl 172.16.0.0/12 md5 hostssl all rdsAdmin 0.0.0.0/0 reject hostssl all rdsBackup 0.0.0.0/0 reject hostssl all rdsMetric 0.0.0.0/0 reject hostssl all rdsRepl 0.0.0.0/0 reject host all rdsAdmin 0.0.0.0/0 reject host all rdsBackup 0.0.0.0/0 reject host all rdsMetric 0.0.0.0/0 reject host all rdsRepl 0.0.0.0/0 reject hostssl all all 0.0.0.0/0 md5 hostssl replication root 0.0.0.0/0 md5 ------------------------------------------------------------------------------------- # nano /Users/vpopov/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/restore/base/backup_manifest # Удалить из файла backup_manifest строку # { "Path": "pg_replslot/pg_basebackup_133469/state", "Size": 200, "Last-Modified": "2022-07-31 16:43:25 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "1fc1e99a" } rm ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/restore/base/backup_label rm -r ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/restore/base/pg_replslot/* # touch ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/restore/base/recovery.signal docker compose ps docker compose up -d docker compose logs otus-db psql -hlocalhost -p5432 -Uroot -dotus_db -c "SELECT count(*) FROM public.test;" psql -hlocalhost -p5432 -Uroot -dotus_db -c "SELECT * FROM public.test limit 10;" # SHOW data_directory; http://localhost:8080/ docker compose down rm -r ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/restore/* ### pg_basebackup mkdir -p ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/pg_basebackup rm -r ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/pg_basebackup/* pg_basebackup -h94.139.253.129 -p5432 -Uroot -R -D ~/Otus/PostgreSQL_Cloud/PG_Sber_Cloud/demo/pg_basebackup ls -lh pg_basebackup # docker compose ps # docker compose up -d # docker compose logs otus-db # http://localhost:8080/ # docker compose down ------------------------------------------------------------------------------------------------ # Links Pangolin https://platformv.sbertech.ru/products/pangolin ------------------------------------------------------------------------------------------------ # Distributed Database Middleware Name: ddm-otus rds-otus Create Account User: otus Password: qy1T6Ls_728Y931Gn Create Schemas Name: otusdb mysql -h 94.139.253.129 -P 5066 -Dotusdb -uotus -pqy1T6Ls_728Y931Gn show databases; create table t1 (id int, name varchar(50)); insert t1 values (1,'Ivan'),(2,'Dima'),(3,'Petya'),(4,'Alex'),(5,'Vasia'); select * from t1; ================================================================================================ ## HTTP API Availability Zone: AZ1 ru-moscow-1a ru-moscow-1 AZ2 ru-moscow-1b ru-moscow-1 AZ3 ru-moscow-1c ru-moscow-1 Regions and Endpoints: Relational Database Service RU-Moscow ru-moscow-1 rds.ru-moscow-1.hc.sbercloud.ru HTTPS Request URI consists of the following: {URI-scheme}://{Endpoint}/{resource-path}?{query-string} https://iam.sa-brazil-1.myhuaweicloud.com/v3/auth/tokens curl --request POST 'https://rds.ru-moscow-1.hc.sbercloud.ru/v3/auth/tokens' curl --request POST 'https://rds.ru-moscow-1.hc.sbercloud.ru/v3/auth/tokens' \ --header 'Content-Type: application/json' \ -d '{ "auth": { "identity": { "methods": [ "password" ], "password": { "user": { "name": "username", "password": "********", "domain": { "name": "domainname" } } } }, "scope": { "project": { "name": "xxxxxxxxxxxxxxxxxx" } } } }' curl --request POST 'https://rds.ru-moscow-1.hc.sbercloud.ru/v3/4523542345324654gsdfg4526/instances' \ --header 'Content-Type: application/json' \ --header 'X-Auth-Token: sbdsfdsgdsfg4625hrsrt2yujke54fw45g55t4tb' \ -d '{ "name": "rds-instance-rep2", "datastore": { "type": "MySQL", "version": "5.6" }, "flavor_ref": "rds.mysql.s1.large", "volume": { "type": "ULTRAHIGH", "size": 100 }, "disk_encryption_id": "2gfdsh-844a-4023-a776-fc5c5fb71fb4", "region": "aaa", "availability_zone": "bbb", "vpc_id": "490a4a08-ef4b-44c5-94be-3051ef9e4fce", "subnet_id": "0e2eda62-1d42-4d64-a9d1-4e9aa9cd994f", "security_group_id": "2a1f7fc8-3307-42a7-aa6f-42c8b9b8f8c5", "port": 8635, "backup_strategy": { "start_time": "08:15-09:15", "keep_days": 12 }, "charge_info": { "charge_mode": "postPaid" }, "password": "Test@12345678", "configuration_id": "452408-ef4b-44c5-94be-305145fg", "enterprise_project_id": "fdsa-3rds", "time_zone": "UTC+04:00" }' ============================================================================================================================= # Test Outputs ## VM vpopov@MacBook-Pro-Vitaly ~ % pgbench -h87.242.127.90 -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 1.22 s, remaining 0.00 s) vacuuming... creating primary keys... done in 8.06 s (drop tables 0.03 s, create tables 0.14 s, client-side generate 7.08 s, vacuum 0.25 s, primary keys 0.57 s). vpopov@MacBook-Pro-Vitaly ~ % pgbench -h87.242.127.90 -p5432 -Upostgres -c 50 -j 2 -P 60 -T 180 benchmark pgbench (15.3) starting vacuum...end. progress: 60.0 s, 132.5 tps, lat 334.133 ms stddev 131.871, 0 failed progress: 120.0 s, 150.2 tps, lat 332.858 ms stddev 133.333, 0 failed progress: 180.1 s, 148.0 tps, lat 336.888 ms stddev 139.285, 0 failed transaction type: 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: 25910 number of failed transactions: 0 (0.000%) latency average = 335.154 ms latency stddev = 135.849 ms initial connection time = 6632.755 ms tps = 148.910057 (without initial connection time) ## DBaaS vpopov@MacBook-Pro-Vitaly demo % pgbench -h87.242.127.90 -p5432 -Uroot -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.26 s, remaining 0.00 s) vacuuming... creating primary keys... done in 9.22 s (drop tables 0.04 s, create tables 0.14 s, client-side generate 7.34 s, vacuum 0.26 s, primary keys 1.44 s). vpopov@MacBook-Pro-Vitaly demo % pgbench -h87.242.127.90 -p5432 -Uroot -c 50 -j 2 -P 60 -T 180 benchmark pgbench (15.3, server 14.6) starting vacuum...end. progress: 60.0 s, 135.8 tps, lat 328.466 ms stddev 131.162, 0 failed progress: 120.0 s, 154.5 tps, lat 323.635 ms stddev 119.880, 0 failed progress: 180.0 s, 153.4 tps, lat 325.656 ms stddev 120.465, 0 failed transaction type: 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: 26674 number of failed transactions: 0 (0.000%) latency average = 325.892 ms latency stddev = 123.746 ms initial connection time = 6295.843 ms tps = 153.143217 (without initial connection time) ============================================================================================================================= 2CPU x 8MEM root@otus-ecs-db-pg-1:~# lscpu Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Address sizes: 42 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(R) Xeon(R) Gold 6266C CPU @ 3.00GHz CPU family: 6 Model: 85 Thread(s) per core: 2 Core(s) per socket: 1 Socket(s): 1 Stepping: 7 BogoMIPS: 6000.00 Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss 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 invpcid_single ssbd ibr s ibpb stibp ibrs_enhanced fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm mpx avx512f avx512dq rdseed adx smap clflushopt clwb avx512cd avx512bw avx512vl xsaveopt xsavec xget bv1 arat avx512_vnni md_clear flush_l1d arch_capabilities Virtualization features: Hypervisor vendor: KVM Virtualization type: full Caches (sum of all): L1d: 32 KiB (1 instance) L1i: 32 KiB (1 instance) L2: 1 MiB (1 instance) L3: 30.3 MiB (1 instance) NUMA: NUMA node(s): 1 NUMA node0 CPU(s): 0,1 Vulnerabilities: Itlb multihit: KVM: Mitigation: VMX unsupported L1tf: Not affected Mds: Not affected Meltdown: Not affected Mmio stale data: Vulnerable: Clear CPU buffers attempted, no microcode; SMT Host state unknown Retbleed: Mitigation; Enhanced 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; Enhanced IBRS, IBPB conditional, RSB filling, PBRSB-eIBRS SW sequence Srbds: Not affected Tsx async abort: Vulnerable: Clear CPU buffers attempted, no microcode; SMT Host state unknown ======================================================================================================== # Test Outputs ## VM pgbench -h94.139.253.129 -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 1.33 s, remaining 0.00 s) vacuuming... creating primary keys... done in 9.17 s (drop tables 0.04 s, create tables 0.15 s, client-side generate 8.09 s, vacuum 0.29 s, primary keys 0.60 s). vpopov@MacBook-Pro-Vitaly demo % pgbench -h94.139.253.129 -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, 114.0 tps, lat 381.146 ms stddev 162.382, 0 failed progress: 120.0 s, 122.4 tps, lat 408.620 ms stddev 204.302, 0 failed progress: 180.0 s, 128.0 tps, lat 390.493 ms stddev 170.424, 0 failed transaction type: 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: 21911 number of failed transactions: 0 (0.000%) latency average = 393.795 ms latency stddev = 180.620 ms initial connection time = 7626.560 ms tps = 126.630291 (without initial connection time) ## DBaaS pgbench -h94.139.253.129 -p5432 -Uroot -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.24 s, remaining 0.00 s) vacuuming... creating primary keys... done in 9.69 s (drop tables 0.04 s, create tables 0.15 s, client-side generate 7.87 s, vacuum 0.27 s, primary keys 1.35 s). vpopov@MacBook-Pro-Vitaly demo % pgbench -h94.139.253.129 -p5432 -Uroot -c 50 -j 2 -P 60 -T 180 benchmark pgbench (16.1, server 14.8) starting vacuum...end. progress: 60.0 s, 118.8 tps, lat 365.235 ms stddev 142.973, 0 failed progress: 120.0 s, 133.1 tps, lat 375.759 ms stddev 161.803, 0 failed progress: 180.0 s, 128.9 tps, lat 387.769 ms stddev 180.117, 0 failed transaction type: 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: 22897 number of failed transactions: 0 (0.000%) latency average = 376.626 ms latency stddev = 163.017 ms initial connection time = 7723.037 ms tps = 132.464012 (without initial connection time)