Linux և սերվերներՄիջին12 րոպե ընթերցումԻմ ստեկում

Տվյալների բազաների գործարկում սեփական սերվերների վրա

MySQL-ի, PostgreSQL-ի և Redis-ի տեղադրում, պաշտպանություն, կարգավորում, backup և իրական restore։

Install & tune
Users & access
Backups
Replicas

Քայլ 1/5

Ամեն գիշեր ժամը 02:00-ին, երբ բոլորը քնած են, բազան backup է անում և պատճենն ուղարկում NAS։

30 վայրկյանում

Քո տվյալների բազան պահում է միակ տվյալները, որոնք չես կարող վերակառուցել, ուստի այն արժանի է ավելի շատ խնամքի, քան սերվերի ցանկացած այլ ծառայություն։ Այն ինքնուրույն գործարկելը նշանակում է տեղադրել այն, պահել public ինտերնետից դուրս, յուրաքանչյուր հավելվածի տալ միայն իրեն անհրաժեշտ հասանելիությունը, սահմանել հիշողության մի քանի կարգավորում և, ամենից առաջ, անել backup-ներ, որոնք պահվում են այլ տեղում և պարբերաբար restore են արվում՝ ապացուցելու, որ աշխատում են։ Managed տվյալների բազաները դրա մեծ մասն անում են քո փոխարեն՝ որոշակի գնով։

Ինչու եմ օգտագործում

Production սերվերների վրա գործարկում եմ MySQL/MariaDB, PostgreSQL և Redis, ուստի հասանելիության, կարգավորման և backup-ների ճիշտ կազմակերպումը իմ ամենօրյա պատասխանատվության մասն է։
Backup-ը իրական եմ համարում միայն restore-ի ստուգումից հետո․ այդ կանոնը ավելի շատ սթրես է խնայել, քան ցանկացած գործիք։
Public port-ով լսող տվյալների բազան փոքր ընկերությունների տվյալների արտահոսքի ամենատարածված ձևերից է, ուստի այն localhost-ին կամ private ցանցին փակելը իմ checklist-ի առաջին կետն է։

Ինչպես է աշխատում

01Որտեղ պետք է գտնվի տվյալների բազան

Կա տեղադրման երեք տարածված տարբերակ։ Նույն սերվերի վրա, ինչ հավելվածը՝ ամենապարզն ու ամենաարագը (ցանցային hop չկա), նորմալ է փոքր և միջին նախագծերի համար, բայց հավելվածն ու տվյալների բազան մրցում են RAM-ի համար, և սերվերի կորուստը նշանակում է երկուսի կորուստ։ Առանձին սերվերի վրա private ցանցում՝ app սերվերները կարող են scale լինել անկախ, և տվյալների բազան ստանում է ամբողջ հիշողությունը։ Որպես managed ծառայություն (Amazon RDS, DigitalOcean Managed Databases, Google Cloud SQL և այլն)՝ provider-ը հոգում է տեղադրումը, patch-երը, backup-ները, failover-ը և point-in-time recovery-ն։ Ինչ էլ ընտրես, նույն կանոններն են գործում․ տվյալների բազան երբեք հասանելի չէ ինտերնետից, յուրաքանչյուր հավելված ունի իր օգտատերը նվազագույն իրավունքներով, և մեքենայից դուրս կա backup-ի պատճեն։ Սկսիր ամենապարզ տեղադրումից, որը բավարարում է քո recovery-ի պահանջները, և տեղափոխիր, երբ հիշողության ճնշումը կամ հասանելիության պահանջները դա պահանջեն։

02Տեղադրում և bind localhost-ին կամ private ցանցին

Ubuntu-ում apt install postgresql, mysql-server (կամ mariadb-server) և redis-server-ը տալիս են աշխատող ծառայություններ։ Ubuntu 26.04-ը գալիս է PostgreSQL 18-ով․ vendor-ների պաշտոնական repository-ներն առաջարկում են այլ տարբերակներ, եթե պետք է համապատասխանեցնել այլ տեղի production-ին։ Ամենակարևոր կարգավորումն այն է, թե որտեղ է լսում ծառայությունը։ Ubuntu-ում MySQL-ը և Redis-ը արդեն լռելյայն լսում են 127.0.0.1-ում, իսկ PostgreSQL-ը՝ localhost-ում, բայց միշտ ստուգիր ss -tlnp-ով։ Եթե app սերվերները միանում են private ցանցով, bind արա միայն private ինտերֆեյսի հասցեին, firewall-ում թույլատրիր այդ subnet-ը, իսկ PostgreSQL-ի համար pg_hba.conf-ում ավելացրու համապատասխան տող scram-sha-256 authentication-ով։ Redis-ը պահում է protected-mode-ը միացված և պետք է պահանջի գաղտնաբառ կամ ACL օգտատեր նույնիսկ localhost-ում։ Երբեք ufw-ում 3306, 5432 կամ 6379 մի բացիր ամբողջ աշխարհի համար։

ini

1# /etc/mysql/mysql.conf.d/mysqld.cnf
2[mysqld]
3bind-address = 127.0.0.1
4 
5# /etc/postgresql/18/main/postgresql.conf
6listen_addresses = 'localhost'        # or 'localhost,10.0.0.5' for a private NIC
7 
8# /etc/postgresql/18/main/pg_hba.conf (private subnet only)
9host  app_db  app_user  10.0.0.0/24  scram-sha-256
10 
11# /etc/redis/redis.conf
12bind 127.0.0.1 -::1
13protected-mode yes
14requirepass change_me_long_random_value
15 
16# Verify what is listening and where
17sudo ss -tlnp | grep -E '3306|5432|6379'

03Օգտատերեր և least privilege

Երբեք թույլ մի տուր հավելվածին միանալ որպես root (MySQL) կամ postgres (PostgreSQL)։ Յուրաքանչյուր հավելվածի համար ստեղծիր մեկ տվյալների բազա և մեկ օգտատեր և տուր միայն այն, ինչ այդ հավելվածին պետք է այդ տվյալների բազայի վրա։ Շատ թիմեր նաև ստեղծում են առանձին migration օգտատեր, որը կարող է փոխել schema-ն, և runtime օգտատեր, որը կարող է միայն կարդալ և գրել տողեր, ինչպես նաև read-only օգտատեր հաշվետվությունների կամ support-ի համար։ Օգտագործիր երկար պատահական գաղտնաբառեր, որոնք պահվում են հավելվածի environment ֆայլում կամ secrets manager-ում, ոչ թե Git-ում։ PostgreSQL 15-ից և ավելի նորերում սովորական օգտատերերը լռելյայն այլևս չեն կարող օբյեկտներ ստեղծել public schema-ում, ուստի տվյալների բազայի տերը պետք է լինի հավելվածի կամ migration-ի role-ը։ Ժամանակ առ ժամանակ վերանայիր օգտատերերին և հեռացրու հեռացած մարդկանց և հավելվածների հաշիվները։

sql

1-- PostgreSQL
2CREATE ROLE app_user LOGIN PASSWORD 'long_random_password';
3CREATE DATABASE app_db OWNER app_user;
4CREATE ROLE report_ro LOGIN PASSWORD 'another_random_password';
5GRANT CONNECT ON DATABASE app_db TO report_ro;
6\c app_db
7GRANT USAGE ON SCHEMA public TO report_ro;
8GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_ro;
9ALTER DEFAULT PRIVILEGES FOR ROLE app_user IN SCHEMA public GRANT SELECT ON TABLES TO report_ro;
10 
11-- MySQL / MariaDB
12CREATE DATABASE app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
13CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'long_random_password';
14GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'localhost';

04Հիմնական կարգավորում՝ տվյալների բազային տուր իր հիշողությունը

Տվյալների բազաներն արագ են, երբ «տաք» տվյալները տեղավորվում են RAM-ում։ Լռելյայն արժեքները միտումնավոր փոքրիկ են, ուստի մի քանի կարգավորում մեծ տարբերություն է տալիս։ MySQL/InnoDB-ի համար innodb_buffer_pool_size-ը cache է անում տվյալներն ու index-ները․ առանձին տվյալների բազայի սերվերի վրա տիպիկ է RAM-ի 50-ից 70 տոկոսը, շատ ավելի քիչ, երբ հավելվածը կիսում է մեքենան։ PostgreSQL-ի համար shared_buffers-ը սովորաբար RAM-ի մոտ 25 տոկոսն է, effective_cache_size-ը (հուշում, ոչ թե հատկացում)՝ 50-ից 75 տոկոսը, իսկ work_mem-ը մնում է փոքր, որովհետև այն հատկացվում է յուրաքանչյուր sort կամ hash գործողության համար՝ յուրաքանչյուր միացման վրա։ max_connections-ը պահիր համեստ և օգտագործիր PgBouncer-ի նման pooler, երբ շատ app պրոցեսներ են միանում։ Redis-ի համար սահմանիր maxmemory և eviction policy, երբ այն cache է․ օգտագործիր noeviction, երբ այն պահում է queue-եր, որոնք չես կարող կորցնել։ Հետո չափիր․ միացրու slow query log-ը և օգտագործիր EXPLAIN՝ մինչև ավելի շատ hardware գնելը։

ini

1# MySQL, 8 GB server shared with the app
2[mysqld]
3innodb_buffer_pool_size = 3G
4max_connections = 200
5slow_query_log = 1
6long_query_time = 1
7 
8# PostgreSQL, 8 GB dedicated server
9shared_buffers = 2GB
10effective_cache_size = 6GB
11work_mem = 16MB
12maintenance_work_mem = 512MB
13log_min_duration_statement = 1000
14 
15# Redis used as a cache
16maxmemory 512mb
17maxmemory-policy allkeys-lru

05Backup-ներ՝ logical ընդդեմ physical, լոկալ ընդդեմ off-site

Logical backup-ը SQL-ի dump է կամ կառուցվածքային արխիվ (mysqldump, pg_dump)։ Այն portable է տարբերակների միջև, հեշտ է դրանից մեկ աղյուսակ restore անելը և կատարյալ է փոքր և միջին տվյալների բազաների համար, բայց դանդաղ է շատ մեծերի համար։ Physical backup-ը պատճենում է հենց տվյալների ֆայլերը (pg_basebackup, pgBackRest, WAL-G, Percona XtraBackup, mariabackup)։ Write-ahead log-ի կամ binary log-ի շարունակական արխիվացման հետ համատեղ այն թույլ է տալիս point-in-time recovery՝ restore անել մինչև այն րոպեն, երբ ինչ-որ մեկը ջնջեց աղյուսակը։ Provider-ի server snapshot-ները օգտակար են, բայց միայնակ բավարար չեն․ դրանք գտնվում են նույն հաշվում և կարող են տվյալների բազան բռնել գրելու կեսին։ Հետևիր 3-2-1-ին՝ երեք պատճեն, պահեստավորման երկու տեսակ, մեկը՝ off-site։ Գաղտնագրիր backup-ները, պահիր դրանք այլ հաշվում կամ provider-ի մոտ և հետևիր, որ job-ն իսկապես աշխատել է։

bash

1#!/usr/bin/env bash
2# /usr/local/bin/db-backup.sh  (cron: 30 2 * * * root /usr/local/bin/db-backup.sh)
3set -euo pipefail
4STAMP=$(date +%F_%H%M)
5DIR=/var/backups/db
6mkdir -p "$DIR"
7 
8# PostgreSQL: compressed custom format, restorable per table
9sudo -u postgres pg_dump -Fc app_db > "$DIR/pg_app_db_$STAMP.dump"
10 
11# MySQL/MariaDB: consistent InnoDB snapshot without locking (credentials in /root/.my.cnf)
12mysqldump --single-transaction --routines --triggers app_db | gzip > "$DIR/my_app_db_$STAMP.sql.gz"
13 
14# Copy off the server to an S3-compatible bucket configured in rclone
15rclone copy "$DIR" offsite:db-backups/
16 
17# Keep 14 days locally
18find "$DIR" -type f -mtime +14 -delete

06Restore-ի ստուգումներ՝ միակ ապացույցը, որ backup-ն աշխատում է

Backup-ները ձախողվում են լուռ․ լցված սկավառակ, ժամկետանց credential, dump, որն աշխատել է սխալ տվյալների բազայի վրա, script, որը դադարել է սերվերի տեղափոխությունից հետո։ Իմանալու միակ ձևը պարբերաբար restore անելն է, իդեալում՝ ավտոմատ։ Պարզ restore test-ը վերջին dump-ը բեռնում է ժամանակավոր տվյալների բազայում (կամ առանձին test սերվերի վրա), գործարկում է մի քանի sanity query, օրինակ՝ տողերի քանակը հիմնական աղյուսակներում և ամենանոր գրառման ամսաթիվը, և հետո ջնջում է այն։ Չափիր նաև տևողությունը․ դա քո իրական recovery time-ն է։ Քայլերը գրի առ որպես կարճ runbook, որովհետև incident-ի ժամանակ ոչ ոք չի ուզում հայտնաբերել pg_restore-ի ճիշտ flag-երը։ Ամբողջական փորձարկում, ներառյալ upload-ների և հավելվածի config-ի restore-ը, արա տարեկան առնվազն մի քանի անգամ։

bash

1#!/usr/bin/env bash
2set -euo pipefail
3LATEST=$(ls -t /var/backups/db/pg_app_db_*.dump | head -1)
4 
5sudo -u postgres dropdb --if-exists restore_test
6sudo -u postgres createdb restore_test
7time sudo -u postgres pg_restore --no-owner -d restore_test "$LATEST"
8 
9# Sanity checks: data exists and is recent
10sudo -u postgres psql -d restore_test -c "SELECT count(*) FROM orders;"
11sudo -u postgres psql -d restore_test -c "SELECT max(created_at) FROM orders;"
12 
13sudo -u postgres dropdb restore_test

07Managed, թե self-hosted

Managed տվյալների բազաները RAM-ի մեկ գիգաբայթի համար ավելի թանկ են, բայց դու գնում ես ժամանակ և անվտանգություն․ ավտոմատ backup-ներ point-in-time recovery-ով, minor տարբերակների patch-եր, մեկ կտտոցով replica-ներ, failover դեպի standby և մետրիկաներ առանց լրացուցիչ կարգավորման։ Schema-ի դիզայնը, index-ները, օգտատերերը և query-ների արտադրողականությունը դեռ քոնն են։ Self-hosting-ը ավելի էժան է և լրիվ վերահսկողություն է տալիս տարբերակների, extension-ների և կոնֆիգուրացիայի վրա, և այն միանգամայն ողջամիտ է փոքր թիմերի համար, եթե backup-ները, monitoring-ը և թարմացումները script արված և ստուգված են։ Լավ կանոն՝ եթե տվյալների բազայի մի քանի ժամվա downtime-ը կամ մեկ օրվա կորած տվյալները լրջորեն կվնասեն բիզնեսին, և թիմում ոչ ոք չի սիրում DBA լինել, օգտագործիր managed տվյալների բազա։ Եթե նախագիծը փոքր է, բյուջեի նկատմամբ զգայուն, և թիմը հարմարավետ է Linux-ում, զգուշորեն self-host արա և վերանայիր որոշումը, երբ նախագիծն աճի։

Օգտագործեք, երբ

Self-host արա app սերվերի վրա փոքր և միջին նախագծերի համար, որտեղ գինը կարևոր է, և կարճ outage-ը ընդունելի է։
Տվյալների բազան տեղափոխիր իր private սերվեր, երբ այն մրցում է հավելվածի հետ RAM-ի կամ CPU-ի համար։
Օգտագործիր managed տվյալների բազա, երբ քեզ պետք են high availability, point-in-time recovery և քիչ գործառնական ջանք։
Redis-ը օգտագործիր SQL-ի կողքին cache-ի, session-ների, rate limit-երի և queue-երի համար, ոչ թե որպես տվյալների հիմնական պահոց։

Մի՛ օգտագործեք, երբ

Մի self-host արա, եթե ոչ ոք պատասխանատու չի լինելու patch-երի, monitoring-ի և restore-ի փորձարկումների համար։
Տվյալների բազայի port-ը public մի բացիր GUI client-ը աշխատեցնելու համար․ փոխարենը օգտագործիր SSH tunnel։
Provider-ի snapshot-ների վրա մի հենվիր որպես միակ backup։

Այլընտրանքներ

Managed SQL (RDS, Cloud SQL, DigitalOcean)
Backup-ները, failover-ը և patch-երը հոգում է provider-ը․ ավելի բարձր գին, ավելի քիչ low-level վերահսկողություն։
Serverless Postgres (Neon, Supabase և նմանատիպեր)
Արագ մեկնարկ, branching և scale-to-zero․ հետևիր միացումների սահմանափակումներին և գնագոյացմանը մեծ մասշտաբով։
Docker Compose-ով տվյալների բազա
Հարմար և վերարտադրելի․ տվյալները պահիր named volume-ների վրա և երբեք port-ը մի publish արա 0.0.0.0-ին։
SQLite
Զրո ադմինիստրավորում փոքր, մեկ սերվերով հավելվածների համար․ սահմանափակ զուգահեռ գրառումներ։

Լավագույն պրակտիկաներ

Յուրաքանչյուր տվյալների բազա bind արա localhost-ին կամ private ինտերֆեյսին և հաստատիր ss -tlnp-ով։
Մեկ տվյալների բազա և մեկ least-privilege օգտատեր յուրաքանչյուր հավելվածի համար․ երբեք հավելվածները մի միացրու որպես root կամ postgres։
Buffer pool-ը կամ shared_buffers-ը սահմանիր գիտակցված և առաջին օրվանից միացրու slow query logging-ը։
Ավտոմատացրու գիշերային dump-երը կամ physical backup-ները WAL/binlog արխիվացմամբ՝ գաղտնագրված և պատճենված off-site։
Restore-ները ստուգիր ժամանակացույցով և գրանցիր, թե որքան են դրանք տևում։
Հետևիր սկավառակի տեղին, միացումներին, replication lag-ին և backup job-երի հաջողությանը։
Major տարբերակները նախ upgrade արա պատճենի վրա՝ ստուգված rollback պլանով։

Տարածված սխալներ

3306-ը կամ 5432-ը բացել ինտերնետին, հաճախ ժամանակավորապես, և մոռանալ փակել։
Backup-ները պահել միայն նույն սերվերի կամ նույն cloud հաշվի վրա, ինչ տվյալների բազան։
Երբեք backup-ը restore չանել մինչև այն օրը, երբ այն պետք է, և հետո պարզել, որ այն դատարկ է կամ վնասված։
Ծանրաբեռնված InnoDB տվյալների բազայի վրա mysqldump գործարկել առանց --single-transaction-ի՝ առաջացնելով lock-եր կամ անհետևողական dump-եր։
Queue-ի տվյալների համար Redis-ն օգտագործել eviction policy-ով՝ հիշողության ճնշման տակ լուռ կորցնելով job-եր։

Խորը վերլուծություն

08Կապերը թանկ են. pooling PgBouncer-ով

PostgreSQL-ի յուրաքանչյուր կապ առանձին օպերացիոն համակարգի process է՝ իր սեփական հիշողությամբ, ուստի PHP-FPM worker-ներից, queue worker-ներից և cron job-երից եկող հարյուրավոր անգործ կապեր վատնում են RAM և դանդաղեցնում սերվերը, և max_connections-ին հասնում եք շատ ավելի շուտ, քան CPU-ն կզբաղվի։ MySQL-ը օգտագործում է thread-ներ և ավելի լավ է դիմանում, բայց նույն խնդիրը հայտնվում է մասշտաբի դեպքում։ Pooler-ը կանգնում է հավելվածների և database-ի միջև և բազմաթիվ client կապեր multiplex է անում մի քանի server կապի վրա։ PgBouncer-ը transaction mode-ում client-ին server կապ է տալիս միայն մեկ transaction-ի տևողությամբ, ինչն ամենաարդյունավետ կարգավորումն է։ Գինն այն է, որ session state-ը չի պահպանվում. SET հրամանները, transaction-ների միջև պահվող advisory lock-երը, LISTEN/NOTIFY-ն և ժամանակավոր աղյուսակները կոտրվում են։ PgBouncer 1.21-ից max_prepared_statements-ը թույլ է տալիս protocol-level prepared statement-ներին աշխատել transaction mode-ում։ Pool-ի չափը ընտրեք ըստ այն բանի, թե database-ը իրականում որքան կարող է զուգահեռ կատարել, մոտավորապես CPU core-երի քանակի փոքր բազմապատիկ։

ini

1; /etc/pgbouncer/pgbouncer.ini
2[databases]
3app_db = host=127.0.0.1 port=5432 dbname=app_db
4 
5[pgbouncer]
6listen_addr = 127.0.0.1
7listen_port = 6432
8auth_type = scram-sha-256
9auth_file = /etc/pgbouncer/userlist.txt
10pool_mode = transaction
11max_client_conn = 1000
12default_pool_size = 20
13max_prepared_statements = 100

09Write-ahead log-եր, archiving և point-in-time recovery

Երկու engine-ներն էլ յուրաքանչյուր փոփոխություն գրում են հաջորդական log-ում՝ նախքան data ֆայլերին դիպչելը. PostgreSQL-ում՝ WAL-ը, MySQL-ում՝ redo log-ը գումարած binary log-ը։ Crash-ից հետո database-ը վերարտադրում է log-ը՝ հետևողական վիճակի հասնելու համար։ Նույն log-ը replication-ի և point-in-time recovery-ի հիմքն է. վերականգնեք ֆիզիկական base backup, հետո վերարտադրեք արխիվացված log segment-ները մինչև ընտրված timestamp։ PostgreSQL-ի դեպքում pgBackRest-ի նման գործիքը կառավարում է base backup-ները, WAL-ի archiving-ը դեպի object storage, retention-ը, սեղմումը և գաղտնագրումը։ archive_command-ը ուղարկում է յուրաքանչյուր ավարտված WAL segment. եթե այն սկսում է ձախողվել, PostgreSQL-ը segment-ները պահում է լոկալ, և pg_wal-ը մեծանում է, մինչև disk-ը լցվի, ուստի alert դրեք archive-ի ձախողումների վրա (pg_stat_archiver-ը ցույց է տալիս դրանք)։ MySQL-ում binary log-երը պահեք GTID-ները միացված և պատճենեք սերվերից դուրս. ամբողջական backup-ը վերականգնելուց հետո mysqlbinlog-ը կարող է դրանք վերարտադրել մինչև որոշակի դիրք կամ ժամանակ։

ini

1# postgresql.conf
2wal_level = replica
3archive_mode = on
4archive_command = 'pgbackrest --stanza=main archive-push %p'
5 
6# /etc/pgbackrest/pgbackrest.conf (S3 credentials kept in a root-only include)
7[global]
8repo1-type=s3
9repo1-s3-bucket=example-db-backups
10repo1-s3-endpoint=s3.example.com
11repo1-s3-region=eu-central-1
12repo1-retention-full=2
13repo1-cipher-type=aes-256-cbc
14 
15[main]
16pg1-path=/var/lib/postgresql/18/main

10Major տարբերակների upgrade առանց դրամայի

Minor release-ները (18.0-ից 18.1) պահանջում են միայն package update և restart։ Major տարբերակները փոխում են disk-ի վրա պահման ձևաչափը և պլան են պահանջում։ Ubuntu-ում pg_upgradecluster-ը փաթաթում է pg_upgrade-ը և cluster-ի կառուցվածքը. pg_upgrade-ը --link-ով խուսափում է data ֆայլերի պատճենումից և ավարտվում է րոպեներում՝ նույնիսկ մեծ database-ների համար, բայց հին cluster-ը դրանից հետո այլևս չի կարող գործարկվել, ուստի նախ backup վերցրեք։ PostgreSQL 18-ը նաև upgrade-ի ընթացքում տեղափոխում է planner-ի վիճակագրությունը, ուստի performance-ը միանգամից լավն է, բայց ANALYZE աշխատեցնելը դեռ էժան ապահովագրություն է։ Գրեթե զրոյական downtime-ի համար օգտագործեք logical replication. կառուցեք նոր տարբերակը կողքին, replicate արեք աղյուսակները հին primary-ից, թողեք, որ հասնի, հետո հավելվածը փոխեք վայրկյանների ընթացքում։ Sequence-ները և schema-ի փոփոխությունները չեն replicate լինում, ուստի cutover-ի ժամանակ սառեցրեք migration-ները և sync արեք sequence-ները։ Ինչ մեթոդ էլ լինի, փորձարկեք վերականգնված պատճենի վրա, դրա դեմ աշխատեցրեք հավելվածի test suite-ը և նախ ստուգեք extension-ները, քանի որ բացակայող extension տարբերակը upgrade-ի դասական խոչընդոտն է։

11Դանդաղ query-ների հայտնաբերում և աղյուսակների առողջության պահպանում

pg_stat_statements-ը ագրեգացնում է յուրաքանչյուր նորմալացված query՝ կանչերի քանակով և ընդհանուր ժամանակով։ Դասավորելը ըստ ընդհանուր ժամանակի, ոչ թե միջինի, գտնում է այն, ինչն իրականում բեռնում է սերվերը. միլիոն անգամ կանչվող 2 ms query-ն հաճախ ավելի կարևոր է, քան 3 վայրկյանանոց հաշվետվությունը։ MySQL-ում performance_schema-ն և sys schema-ն տալիս են նույն պատկերը։ PostgreSQL-ի MVCC-ն պահում է տողերի հին տարբերակները, մինչև VACUUM-ը դրանք վերադարձնի։ Autovacuum-ը սովորաբար կառավարում է սա, բայց շատ մեծ կամ հաճախ թարմացվող աղյուսակները պահանջում են ավելի խիստ per-table շեմեր, իսկ երկարատև transaction-ը կամ մոռացված replication slot-ը արգելափակում է մաքրումը ամբողջ cluster-ի համար՝ առաջացնելով bloat և disk-ի աճող օգտագործում։ Հետևեք dead tuple-ներին, ամենահին transaction-ի տարիքին և ամենամեծ աղյուսակների չափին։ Առողջ database-ը հիմնականում ձանձրալի մոնիթորինգ է գումարած ճիշտ index-ներ՝ ստուգված EXPLAIN (ANALYZE, BUFFERS)-ով։

sql

1-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements' (restart)
2CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
3 
4SELECT calls,
5       round(total_exec_time::numeric) AS total_ms,
6       round(mean_exec_time::numeric, 1) AS mean_ms,
7       left(query, 80) AS query
8FROM pg_stat_statements
9ORDER BY total_exec_time DESC
10LIMIT 10;
11 
12-- Tables that need vacuum attention
13SELECT relname, n_dead_tup, last_autovacuum
14FROM pg_stat_user_tables
15ORDER BY n_dead_tup DESC
16LIMIT 10;

Իրական իրավիճակներ

Հանգիստ database-ում disk-ը լցվում է գիշերվա ընթացքում

Խնդիր

Disk-ի օգտագործման alert-ները գործարկվում են գիշերվա ժամը 3-ին, թեև տրաֆիկը ցածր է։ Data directory-ն ինքը շատ չի մեծացել, բայց pg_wal-ը շատ գիգաբայթ է և շարունակում է աճել։

Ինչպես լուծել

WAL-ը աճում է, երբ PostgreSQL-ին թույլ չի տրվում այն հեռացնել։ Ստուգեք երկու պատճառ։ Առաջին՝ SELECT * FROM pg_stat_archiver. ձախողվող archive_command-ը (ժամկետանց storage credentials, լցված bucket) յուրաքանչյուր segment պահում է լոկալ։ Ուղղեք credentials-ը, և archiving-ը կհասնի։ Երկրորդ՝ SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) FROM pg_replication_slots. հեռացված replica-ից կամ CDC գործիքից մնացած ոչ ակտիվ slot-ը WAL-ը պահում է ընդմիշտ։ Եթե այն իսկապես չի օգտագործվում, ջնջեք pg_drop_replication_slot-ով։ Կրկնությունը կանխեք max_slot_wal_keep_size-ով, archive-ի ձախողումների alert-ով և disk alert-ով 80 տոկոսում՝ 95-ի փոխարեն։

DELETE առանց WHERE production-ում

Խնդիր

11:58-ին ինչ-որ մեկը orders աղյուսակի վրա ձեռքով DELETE աշխատեցրեց առանց WHERE-ի։ Հավելվածը դեռ աշխատում է, և նոր պատվերներ շարունակում են գալ։

Ինչպես լուծել

Մի վերականգնեք production-ի վրա, քանի որ դա կկորցնի այդ պահից ի վեր տրված բոլոր պատվերները։ Վերջին base backup-ը վերականգնեք առանձին instance-ում և WAL-ը վերարտադրեք մինչև սխալից անմիջապես առաջ, օրինակ՝ pgbackrest restore --type=time՝ 11:57:30 target-ով։ Այնտեղ ստուգեք տողերի քանակը, հետո հետ պատճենեք միայն ջնջված տողերը՝ այդ աղյուսակի dump-ով կամ INSERT ... SELECT-ով postgres_fdw-ի միջոցով՝ բաց թողնելով արդեն գոյություն ունեցող տողերը։ Incident report-ի համար գրանցեք վերականգնման ճշգրիտ քայլերը և ծախսված ժամանակը։ Դրանից հետո հեռացրեք մարդկանց ուղիղ write access-ը production-ում, ձեռքով ուղղումների համար պահանջեք transaction-ներ և restore drill-երը պահեք օրացույցում։

«Too many connections» worker-ները մեծացնելուց հետո

Խնդիր

Թիմը կրկնապատկեց queue worker-ները և ավելացրեց երկրորդ app սերվեր։ Հիմա պիկի ժամանակ PostgreSQL-ը մերժում է կապերը «sorry, too many clients already»-ով, թեև CPU-ն ընդամենը 40 տոկոս է։

Ինչպես լուծել

Հաշվեք կապերի բյուջեն. PHP-FPM-ի max_children-ը մեկ սերվերի համար բազմապատկած սերվերների քանակով, գումարած queue worker-ները, գումարած cron-ը և ադմին գործիքները։ Այն հիմա գերազանցում է max_connections-ը։ max_connections-ը հազարների բարձրացնելը սխալ լուծում է, քանի որ յուրաքանչյուր կապ արժե հիշողություն և contention։ Դրեք PgBouncer transaction mode-ով յուրաքանչյուր app սերվերում կամ database-ի կողքին, հավելվածներն ուղղեք port 6432-ին և իրական pool-ը պահեք մոտ 20-ից 40 server կապ։ Ստուգեք կոդը session-level ֆունկցիաների համար, ինչպես advisory lock-երը կամ SET հրամանները, որոնք transaction pooling-ը կոտրում է։ Հետո մոնիթորինգ արեք pool-ի սպասումները. եթե client-ները հերթ են կանգնում, bottleneck-ը database-ն ինքն է, և query-ները օպտիմալացման կարիք ունեն։

Արագ ուղեցույց

PostgreSQL cluster-ների ցանկը Ubuntu-ում: pg_lsclusters
Տեսնել աշխատող query-ները (PostgreSQL): SELECT pid, now() - query_start AS age, state, left(query, 60) FROM pg_stat_activity WHERE state <> 'idle';
Չեղարկել կամ kill անել query: SELECT pg_cancel_backend(12345); -- or pg_terminate_backend(12345)
Database-ի չափը: SELECT pg_size_pretty(pg_database_size('app_db'));
Զուգահեռ վերականգնել custom-format dump: pg_restore -j 4 --no-owner -d app_db app_db.dump
Replication lag PostgreSQL replica-ում: SELECT now() - pg_last_xact_replay_timestamp();
Աշխատող query-ներ (MySQL / MariaDB): SHOW FULL PROCESSLIST;
Replica-ի status (MySQL 8.0.22+): SHOW REPLICA STATUS\G
Սահմանափակել binary log-երի պահպանումը (MySQL): binlog_expire_logs_seconds = 604800
Redis-ի հիշողությունը և մեծ key-երը: redis-cli INFO memory && redis-cli --bigkeys

Իմ իրական նախագծերում

Շահագործում եմ MariaDB/MySQL hosting պանելով սերվերների վրա և PostgreSQL Redis-ի հետ առանձին SaaS սերվերի վրա, որտեղ backup script-երը setup-ի մասն են։
Իմ անվտանգության baseline-ը յուրաքանչյուր տվյալների բազա պահում է bind արված localhost-ին կամ private ցանցին, երբեք՝ public։
Իմ ստանդարտը server snapshot-ներն են ստուգված restore-ներով, իսկ ընթացիկ պլանս backup-ները տեղափոխում է սերվերից դուրս։

Հարցեր և պատասխաններ

Q1Ո՞րն է տարբերությունը logical և physical backup-ի միջև։

Logical backup-ը տվյալները export է անում որպես SQL հրահանգներ կամ արխիվ՝ pg_dump-ի կամ mysqldump-ի նման գործիքներով։ Այն portable է տարբերակների միջև և թույլ է տալիս restore անել մեկ աղյուսակ, բայց մեծ տվյալների բազաների համար դանդաղ է ստեղծվում և restore արվում։ Physical backup-ը պատճենում է տվյալների բազայի ֆայլերը, օրինակ՝ pg_basebackup-ով, pgBackRest-ով կամ Percona XtraBackup-ով։ Այն շատ ավելի արագ է մեծ տվյալների համար և, WAL-ի կամ binary log-ի շարունակական արխիվացման հետ համատեղ, հնարավոր է դարձնում point-in-time recovery։ Լուրջ setup-ների մեծ մասը recovery-ի համար օգտագործում է physical backup-ներ log-երի արխիվացմամբ և ժամանակ առ ժամանակ logical dump-եր՝ portability-ի և առանձին աղյուսակներ restore անելու համար։

Q2Ինչպե՞ս ես համոզվում, որ տվյալների բազան բաց չէ ինտերնետին։

Մի քանի շերտ։ Առաջին՝ ծառայությունը bind է արված 127.0.0.1-ին կամ private ցանցային ինտերֆեյսին, ինչը ստուգում եմ ss -tlnp-ով։ Երկրորդ՝ host firewall-ը լռելյայն մերժում է մուտքային traffic-ը և թույլատրում միայն 22, 80 և 443․ եթե app սերվերներին հասանելիություն է պետք, թույլատրում եմ միայն private subnet-ը։ Երրորդ՝ cloud provider-ի firewall-ը կրկնում է այդ կանոնները։ Չորրորդ՝ authentication-ը դեռ խիստ է․ remote root login չկա, PostgreSQL-ում scram-sha-256, ուժեղ գաղտնաբառեր յուրաքանչյուր հավելվածի համար։ Laptop-ից admin հասանելիության համար օգտագործում եմ SSH tunnel՝ port բացելու փոխարեն։ Docker-ի դեպքում publish արված port-երը bind եմ անում 127.0.0.1-ին, որովհետև Docker-ը կարող է շրջանցել ufw-ի կանոնները։

Q3Ինչպե՞ս կկարգավորես PostgreSQL-ը կամ MySQL-ը նոր սերվերի վրա։

Սկսում եմ հիշողությունից։ MySQL-ի համար innodb_buffer_pool_size-ը վերցնում է RAM-ի մեծ մասը առանձին սերվերի վրա, մոտ 50-ից 70 տոկոսը, և ավելի քիչ, եթե հավելվածը կիսում է մեքենան։ PostgreSQL-ի համար shared_buffers-ը մոտ RAM-ի 25 տոկոսը, effective_cache_size-ը մոտ 50-ից 75 տոկոսը և համեստ work_mem, որովհետև այն բազմապատկվում է ըստ գործողության և միացման։ max_connections-ը պահում եմ ողջամիտ և շատ client-ների համար ավելացնում եմ PgBouncer։ Հետո միացնում եմ slow query logging-ը, ամենավատ query-ները նայում եմ EXPLAIN-ով և index-ներ ավելացնում։ Արտադրողականության իրական շահերի մեծ մասը գալիս է query-ներից և index-ներից, ոչ թե էկզոտիկ կոնֆիգուրացիոն flag-երից։

Q4Ի՞նչ են RPO-ն և RTO-ն, և ինչպե՞ս են դրանք ազդում backup-ի ռազմավարության վրա։

RPO-ն՝ recovery point objective-ը, այն է, թե որքան տվյալ կարող ես քեզ թույլ տալ կորցնել՝ չափված ժամանակով։ RTO-ն՝ recovery time objective-ը, այն է, թե որքան կարող ես անջատված մնալ restore-ի ընթացքում։ Գիշերային dump-ը տալիս է մինչև 24 ժամ RPO․ եթե բիզնեսը կարող է կորցնել միայն րոպեներ, քեզ պետք է WAL-ի կամ binlog-ի շարունակական արխիվացում կամ managed տվյալների բազա point-in-time recovery-ով։ RTO-ն կախված է տվյալների չափից և պրակտիկայից․ 200 GB logical restore-ը կարող է ժամեր տևել, մինչդեռ replica-ն promote անելը՝ րոպեներ։ Այս թվերը նախ համաձայնեցնում եմ բիզնեսի հետ, հետո ընտրում եմ գործիքները և երկուսն էլ ստուգում եմ ժամաչափված restore-ի փորձարկումներով։

Q5Պե՞տք է օգտագործենք managed տվյալների բազա, թե գործարկենք մերը։

Managed-ը ավելի անվտանգ լռելյայն ընտրություն է, երբ downtime-ը կամ տվյալների կորուստը թանկ են, և թիմը չունի տվյալների բազաների մասնագետ․ ստանում ես ավտոմատ backup-ներ, point-in-time recovery, patch-եր, replica-ներ և failover։ Փոխզիջումներն են գինը, տարբերակների և extension-ների ավելի քիչ վերահսկողությունը և որոշ vendor lock-in։ Self-hosting-ը ավելի էժան է և լիովին ճկուն, և լավ է աշխատում փոքր կամ միջին նախագծերի համար, երբ backup-ները, monitoring-ը և թարմացումները ավտոմատացված են, և restore-ները ստուգված են։ Հաճախ խորհուրդ եմ տալիս MVP-ի համար սկսել self-hosted տարբերակից լավ կարգավորված սերվերի վրա և անցնել managed-ի, երբ եկամուտը կամ հասանելիության պահանջներն արդարացնեն դա, քանի որ migration-ը պլանավորված dump և restore է։

Q6Ինչպե՞ս ես developer-ին կամ analyst-ին անվտանգ read-only հասանելիություն տալիս։

Ստեղծում եմ առանձին read-only role՝ SELECT-ով միայն անհրաժեշտ schema-ի կամ աղյուսակների վրա, գումարած default privileges, որպեսզի նոր աղյուսակներն էլ ներառվեն։ Միացումներն անցնում են SSH tunnel-ով կամ bastion-ով, երբեք՝ public port-ով։ Իդեալում role-ը ուղղված է read replica-ին կամ վերջերս restore արված պատճենին, որպեսզի ծանր հաշվետվությունները չկարողանան դանդաղեցնել production-ը։ Եթե տվյալները ներառում են անձնական տեղեկություններ, սյուները սահմանափակում եմ view-ներով կամ տրամադրում եմ անանունացված պատճեն։ Հասանելիությունը անձնական է, ոչ թե ընդհանուր, ուստի կարելի է revoke անել առանձին, իսկ statement logging-ը կամ audit extension-ները գրանցում են, թե ինչ է գործարկվել։ Հաշիվը հեռացվում է, երբ մարդուն այն այլևս պետք չէ։

Սենիոր մակարդակի հարցեր

S1Ինչպե՞ս եք նախագծում database failover, և ինչպե՞ս եք խուսափում split-brain-ից։

Failover-ը նշանակում է standby-ը promote անել, երբ primary-ն մեռնում է, հետո հավելվածն ուղղել դրան։ Վտանգավոր դեպքը split-brain-ն է. հին primary-ն ընդամենը անհասանելի է, ոչ թե մեռած, և երկուսն էլ ընդունում են գրառումներ՝ ստեղծելով տարամիտված տվյալներ, որոնք ցավոտ է միավորել։ Կանխարգելումը պահանջում է consensus կամ fencing մեխանիզմ։ Patroni-ն etcd-ով PostgreSQL-ի համար, կամ managed ծառայությունը, պահում է leader lock և promote անելուց առաջ demote կամ fence է անում հին primary-ն։ Հավելվածները միանում են կայուն endpoint-ով, օրինակ՝ virtual IP-ով, proxy-ով կամ cluster-ի կողմից կառավարվող DNS-ով։ Synchronous replication-ը կարող է երաշխավորել տվյալների զրոյական կորուստ՝ գրառման latency-ի և հասանելիության հաշվին, եթե standby-ը ձախողվի։ Փոքր թիմերի համար managed database-ը սովորաբար ավելի լավ է, քան սա ճիշտ կառուցելը։

S2Redis-ը պահում է մեր հերթերը և session-ները։ Ինչպե՞ս եք կարգավորում persistence-ը։

Redis-ն առաջարկում է RDB snapshot-ներ՝ պարբերական point-in-time dump-եր, որոնք կոմպակտ են և արագ բեռնվում, բայց կորցնում են վերջին snapshot-ից հետո եղած գրառումները, և AOF՝ յուրաքանչյուր գրառման append-only log։ appendfsync everysec-ով, որը սովորական կարգավորումն է, crash-ը կորցնում է առավելագույնը մոտ մեկ վայրկյան։ Հերթերի և session-ների համար միացնում եմ AOF գումարած RDB, սահմանում եմ maxmemory-policy noeviction, որպեսզի Redis-ը սխալներ վերադարձնի, փոխանակ job-երը լուռ դեն նետելու, և alert եմ դնում հիշողության վրա սահմանից շատ առաջ։ Նաև բաժանում եմ պատասխանատվությունները. cache instance՝ allkeys-lru-ով և առանց persistence-ի, և queue instance՝ durability-ով։ Եթե նույնիսկ մեկ վայրկյանի job-երի կորուստն անընդունելի է, job-ի source of truth-ը պետք է լինի SQL database-ը, իսկ Redis-ը՝ միայն որպես transport։

S3Ինչպե՞ս եք պաշտպանում backup-ները ransomware-ից կամ compromised սերվերից։

Ենթադրեք, որ database սերվերում root ունեցող հարձակվողը կփորձի ջնջել նաև backup-ները։ Ուստի սերվերը պետք է կարողանա գրել backup-ներ, բայց ոչ ջնջել կամ վերագրել դրանք. օգտագործեք bucket առանձին account-ում՝ object lock-ով կամ versioning-ով գումարած retention policy, և credentials, որոնք թույլ են տալիս միայն upload։ Գաղտնագրեք backup-ները client-side՝ սերվերից դուրս պահվող key-երով, որպեսզի արտահոսած bucket-ը ոչինչ չբացահայտի։ Պահեք առնվազն մեկ պատճեն այլ provider-ի մոտ։ Մոնիթորինգ արեք բացակայող կամ անսովոր փոքր backup-ները, քանի որ լուռ ձախողումը նույնն է թվում, ինչ հարձակումը։ Վերջապես, restore drill-եր անցկացրեք off-site պատճենից՝ առանց սկզբնական սերվերի հասանելիության, ինչը հենց incident-ից հետո եղած իրավիճակն է։

S4PostgreSQL, թե՞ MySQL նոր պրոդուկտի համար։

Երկուսն էլ գերազանց են, և թիմի փորձը հաճախ ավելի կարևոր է, քան ֆունկցիաները։ PostgreSQL-ն առաջարկում է ավելի հարուստ SQL, ուժեղ JSONB index-ներով, partial և expression index-ներ, PostGIS-ի և pgvector-ի նման extension-ներ, transactional DDL և խիստ ճշտություն, ինչը այն դարձնում է իմ լռելյայն ընտրությունը բարդ domain-ների և analytics-ով ծանրաբեռնված հավելվածների համար։ MySQL-ը InnoDB-ով պարզ է սպասարկել, ունի շատ հասուն replication, hosting-ի հսկայական էկոհամակարգ և սյունակների ակնթարթային ավելացում schema-ի շատ փոփոխությունների համար, իսկ Laravel-ի նման framework-ները նրա վրա նույնքան լավ են աշխատում։ Գործառնական տարբերությունները նույնպես կարևոր են. PostgreSQL-ը ավելի շուտ է պահանջում connection pooling և vacuum-ի գիտակցում, MySQL-ը պահանջում է զգուշություն մեծ աղյուսակների վրա online DDL-ի հետ։ Ընտրում եմ PostgreSQL, եթե միայն գոյություն ունեցող համակարգերը, hosting-ը կամ թիմի հմտությունները MySQL-ի օգտին չեն։

Բառարան

WAL: Write-ahead log. PostgreSQL-ի յուրաքանչյուր փոփոխության հաջորդական գրանցումը, որը գրվում է data ֆայլերից առաջ և օգտագործվում crash recovery-ի և replication-ի համար։
Binary log: MySQL-ի commit արված փոփոխությունների log-ը, որն օգտագործվում է replication-ի և backup վերականգնելուց հետո իրադարձությունները վերարտադրելու համար։
PITR: Point-in-time recovery. base backup-ի վերականգնում և log-երի վերարտադրում մինչև ճշգրիտ պահ, օրինակ՝ սխալից անմիջապես առաջ։
Replication slot: PostgreSQL-ի էջանիշ, որը պահում է WAL-ը, մինչև replica-ն կամ consumer-ը ստանա այն. մոռացված slot-երը լցնում են disk-երը։
Replication lag: Որքան է replica-ն հետ primary-ից՝ ժամանակով կամ բայթերով. նրանից կարդացածը կարող է մի փոքր հնացած լինել։
Connection pooler: Proxy, ինչպես PgBouncer-ը, որը մի քանի database կապ կիսում է բազմաթիվ application client-ների միջև։
MVCC: Multi-version concurrency control. կարդացողները տեսնում են snapshot, իսկ գրողները ստեղծում են տողերի նոր տարբերակներ, ուստի հազվադեպ են արգելափակում միմյանց։
VACUUM: PostgreSQL-ի սպասարկում, որը վերադարձնում է մեռած տողերի տարբերակների տարածքը և թարմացնում visibility տվյալները. սովորաբար աշխատեցնում է autovacuum-ը։
Buffer pool: InnoDB-ի հիմնական հիշողության քեշը data և index էջերի համար. դրա չափը մեծապես որոշում է MySQL-ի performance-ը։
GTID: Global transaction identifier MySQL-ում. յուրաքանչյուր transaction-ի տալիս է եզակի ID, որպեսզի replica-ները և failover-ը հուսալի հետևեն դիրքին։

Շարունակեք սովորել

Պրոդուկտի գաղափար ունե՞ք։Եկեք ստեղծենք այն։

Պատմեք ձեր գաղափարի, պրոդուկտի կամ թիմի մասին։ Սովորաբար պատասխանում եմ մեկ աշխատանքային օրվա ընթացքում։

Սկսել զրույցը