Редовно одржување на базите на податоци, бекап и реставрација

---

Редовно одржување на базите на податоци (тестирање на сценарио бр.1 од претходна фаза со 10 000 конкурентни барања)

• Parameter Tuning
  • Max connections – to handle more concurrent connections
  • Shared buffers – allocate more memory for caching data
  • Work mem – allow more memory for each session (helpful for sorting and other memoryintensive operations)
  • Maintenance work mem – allocate more memory for tasks like index creation or vacuuming
  • Checkpoint segments and checkpoint timeout – this can help improve write operations
  • Commit delay – to allow multiple transactions to be batched together which can reduce the I/O load
  • Max wal size – to allow more WAL space for heavy write workloads
  • Max parallel workers – set the number of parallel workers
  • Мax parallel workers per gather – set the number of parallel workers per query
  • Effective cache size – helps the query planner estimate the amount of system memory available for caching data, it provides a hint to the query planner about the amount of cache that can be used for storing frequently accessed data
• Потребно е да се направи промена на вредностите на претходно наведените параметри за да се овозможи базата на податоци да ги обработи сите 10 000 барања паралелно
• При промена на вредностите треба да се внимава да не се надминат ресурсите кои ги нуди серверот каде се наоѓа базата на податоци
  • Пр. Доколку имаме 8GB РАМ меморија, треба да се внимава сите параметри кои што се поврзани со меморијата да не надминуваат повеќе од 8GB

---

Тестирање на сценарио бр.1 од претходна фаза со 10 000 нишки

---

Резултат од тестирањето

Бекап

• Навигирајте во фолдерот ‘bin’ каде што ви е инсталирана PostgreSQL преку CMD (Run as Administrator)
  
• Извршете ја следната команда: pg_dump -U username -d dbname > dbname.sql
  
  • На местото на “username” напишете го корисничкото име кое ги има сите привилегии, во случајот би било postgres
    
  • На местото на “dbname” напишете го името на базата за која сакате да креирате бекап
    
  • Опционално: може да специфицирате каде точно да се зачува dbname.sql датотеката, во случајот ќе се зачува во ‘bin’ фолдерот
    
• Во ‘bin’ фолдерот, ќе се креира SQL датотека во која што се наоѓаат сите команди кои треба да се извршат за да се рекреира вашата база до моментот кога е направен бекап-от
  
• За да креирате бекап и на корисниците, извршете ја следнава команда: pg_dumpall -U username –database=dbname –roles-only –no-owner -f roles_backup.sql
  
  • На местото на “username” напишете го корисничкото име кое ги има сите привилегии, во случајот би било postgres
    
  • На местото на “dbname” напишете го името на базата за која сакате да креирате бекап на корисниците
    
• Командата pg_dumpall, може да служи и за да се креира целосен бекап на сите бази
  
  • Командата има исто така и опции, преку кои може да специфицирате на што точно сакате да направите бекап
    
  • На официјалната документација има повеќе информации за секоја опција која што може да ја искористите со оваа командa
    (​https://www.postgresql.org/docs/current/app-pg-dumpall.html)

Реставрација

• За да направите реставрација на базата за која што креиравте бекап потребно е да ја избришете базата, па да ја рекреирате
  
• Логирајте се на SQL Shell (psql за PostgreSQL)
  
• Извршете ги следниве команди:
  
  • drop database dbname;
    
  • create database dbname;
    
  • На местото на “dbname” напишете го името на базата за која креиравте бекап
    
• Навигирајте во фолдерот ‘bin’ каде што ви е инсталирана PostgreSQL преку CMD (Run as Administrator)
  
• Извршете ја следнава команда: psql -U username -d dbname < dbname.sql
  
  • На местото на “username” напишете го корисничкото име кое ги има сите привилегии, во случајот би било postgres
    
  • На местото на “dbname” напишете го името на базата за која креиравте бекап
    
  • Доколку SQL датотеката не се наоѓа во ‘bin’ фолдерот, тогаш ќе треба да ја напишете целосната патека каде што се наоѓа датотеката
    
• Откако ќе ја извршите претходната команда, ќе се направи реставрација на базата и ќе се додадат сите табели и записи кои постоеле до моментот кога е направен бекап-от. Сите додавања, промени и бришења кои се направени после бекап-от нема да бидат вратени
  
• За да извршите реставрација и на корисниците, извршете ја следнава команда: psql -U username -d dbname -f roles_backup.sql
  
  • На местото на “username” напишете го корисничкото име кое ги има сите привилегии, во случајот би било postgres
    
  • На местото на “dbname” напишете го името на базата за која креиравте бекап на корисници
    

PITR (Point-in-time-recovery)

• Навигирајте во фолдерот на PostgreSQL (C:\Program Files\PostgreSQL\14)
  
• Креирајте фолдер ‘pgbasebackup’ и ‘wal_archive’
  
  • Внатре во фолдерот ‘pgbasebackup’, креирајте два подфолдери ‘bck1’ и ‘data.before_recovery’
    
• Додадете пермисии за сите акции врз сите фолдери во PostgreSQL
  
• Во postgresql.conf датотеката, направете ги следните промени:
  
  • wal_level = replica
    
  • full_page_writes = on
    
  • archive_mode = on
    
  • archive_command = ‘copy “%p” “C:\Program Files\PostgreSQL\14\wal_archive\%f”’
    
  • archive_timeout = 300
    
• Рестартирајте го PostgreSQL сервисот преку Services
  
• Навигирајте во ‘bin’ фолдерот преку командна линија во PostgreSQL фолдерот
  
• Извршете ја следната команда
  
  • pg_basebackup --host=localhost --port=5432 --username=postgres --pgdata="C:\Program Files\PostgreSQL\14\pgbasebackup\bck1"
    
• Извршете некоја промена во одредена табела од базата
  
• Стопирајте го PostgreSQL сервисот преку Services
  
• Навигирајте во ‘bin’ фолдерот преку командна линија во PostgreSQL фолдерот
  
• Извршете ги следниве команди (првата команда креира бекап на моменталната база, а со втората команда ја бришеме целосно базата)
  
  • xcopy /O/X/E/H/K "C: \\Program Files\\PostgreSQL\\14\\data" "C: \\Program Files\\PostgreSQL\\14\\pgbasebackup\\data.before_recovery"
    
  • rmdir /S/Q "C: \\Program Files\\PostgreSQL\\14\\data"
    
• Креирајте нов фолдер со име ‘data’ во PostgreSQL фолдерот (C:\Program Files\PostgreSQL\14)
  
• Извршете ги следниве команди (првата команда ги копира бекап фајловите назад во ‘data’ фолдерот, втората команда ги брише логовите)
  
  • xcopy /O/X/E/H/K "C: \\Program Files\\PostgreSQL\\14\\pgbasebackup\\bck1" "C: \\Program Files\\PostgreSQL\\14\\data"
    
  • del /S/F/Q "C:\Program Files\PostgreSQL\\14\\data\\log"
    
• Во postgresql.conf датотеката додадете ги следниве параметри
  
  • restore_command = 'copy "C: \\Program Files\\PostgreSQL\\14\\wal_archive\\%f" "%p"'
    
  • recovery_target_time = 'YYYY-MM-DD HH:MM:SS'
    
  • Во recovery_target_time наведете го времето до кое сакате да ја вратите базата
    
• Вклучете го PostgreSQL сервисот преку Services и бекапот ќе се направи автоматски до наведеното време
  

Автоматизација

• Доколку сакаме процесот да биде автоматизиран, може да креираме таскови во Windows оперативниот систем, при што ке му предадеме скрипта која ќе ги извршува командите за бекап
• На таскот може да му се специфицира на колку време да се извршува

---