Nainštalujte a nakonfigurujte PostgreSQL v systéme CentOS

V tomto článku nainštalujeme databázu DBMS PostgreSQL 11 v systéme Linux CentOS 7, vykonáme základnú konfiguráciu servera a DBMS, vezmeme do úvahy hlavné parametre konfiguračného súboru, ako aj spôsoby ladenia výkonu. PostgreSQL je populárny bezplatný objektovo-relačný systém správy databáz. Aj keď nie je taký rozšírený ako MySQL / MariDB, je to najprofesionálnejší.

Silné stránky PostgreSQL:

  • Úplný súlad s normami SQL;
  • Vysoký výkon prostredníctvom viacrozmerného riadenia súbežnosti (MVCC);
  • Škálovateľnosť (široko používaná vo vysoko zaťažených prostrediach);
  • Podpora mnohých programovacích jazykov;
  • Spoľahlivé mechanizmy transakcií a replikácie;
  • Podpora údajov JSON.

obsah:

  • Nainštalujte PostgreSQL na CentOS / RHEL
  • Pripojte sa k PostgreSQL, vytvorte databázu, užívateľ
  • Základné možnosti konfiguračného súboru PostgreSQL
  • Zálohovanie a obnova databázy v PostgreSQL
  • Optimalizácia a ladenie PostgreSQL

Nainštalujte PostgreSQL na CentOS / RHEL

Aj keď PostgreSQL je možné nainštalovať zo základného úložiska CentOS, úložisko nainštalujeme od vývojárov, pretože má vždy najnovšiu verziu balíka.

Prvým krokom je inštalácia úložiska PosgreSQL (v súčasnosti je nainštalovaná nasledovne):

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Toto úložisko obsahuje najnovšie verzie PostgreSQL aj staršie verzie. Informácie o úložisku sú nasledujúce:

Nainštalujte najnovšiu dostupnú verziu (PostrgeSQL 11) pomocou yum.

yum nainštalujte postgresql11-server -y

Počas inštalácie sa nainštaluje samotný server PostgreSQL a potrebné knižnice:

Inštalácia: libicu-50.2-3.el7.x86_64 1/4 Inštalácia: postgresql11-libs-11.5-1PGDG.rhel7.x86_64 2/4 Inštalácia: postgresql11-11.5-1PGDG.rhel7.x86_64 3/4 Inštalácia: postgresql11-server- 11,5-1PGDG.rhel7.x86_64 4/4

Po inštalácii balíkov je potrebné inicializovať databázu:

/ usr / pgsql-11 / bin / postgresql-11-setup initdb

Ihneď pridajte databázový server na spustenie a spustite ho:

systemctl povoliť postgresql-11

systemctl start postgresql-11

Skontrolujte, či je server spustený a či nie sú problémy, skontrolujte jeho stav:

[root @ server ~] # systemctl status postgresql-11

● postgresql-11.service - databázový server PostgreSQL 11 Načítaný: načítaný (/usr/lib/systemd/system/postgresql-11.service; povolený; predvoľba dodávateľa: deaktivovaný) aktívny: aktívny (beží) od st. 2019-09-18 13:01:56 +06; Pred 26 rokmi Dokumenty: https://www.postgresql.org/docs/11/static/ Proces: 6614 ExecStartPre = / usr / pgsql-11 / bin / postgresql-11-check-db-dir $ PGDATA (code = ukončený, stav = 0 / ÚSPECH) Hlavná PID: 6619 (postmaster) CGroup: /system.slice/postgresql-11.service ├─6619 / usr / pgsql-11 / bin / postmaster -D / var / lib / pgsql / 11 / data / ├─6621 postgres: logger ├─6623 postgres: checkpointer ├─6624 postgres: pozadí spisovateľ ├─6625 postgres: walwriter ├─6626 postgres: autovacuum launcher ├─6627 postgres: zberač štatistík └─6628 postgres: logický replikátor 18. september 13:01:56 server.1.com systemd [1]: Spustenie databázového servera PostgreSQL 11 ... 18. september 13:01:56 server.1.com postmaster [6619]: 2019-09-18 13: 01: 56.399 +06 [6619] LOG: počúvanie na adrese IPv6 ":: 1", port 5432, september 18 13:01:56 server.1.com postmaster [6619]: 2019-09-18 13: 01: 56,399 +06 [6619 ] LOG: počúvanie na adrese IPv4 "127.0.0.1", port 5432 18 september 13:01:56 server.1.com postmaster [6619]: 2019-09-18 13: 01: 56.401 +06 [6619] LOG: počúvanie na sokete Unix "/var/run/postgresql/.s.PGSQL.5432" 18 september 13:01:56 server.1.com postmaster [6619]: 2019-09-18 13: 01: 56.409 +06 [6619] LOG: Počúvanie na Unix sokete "/tmp/.s.PGSQL.5432" 18 september 13:01:56 server.1.com postmaster [6619]: 2019-09-18 13: 01: 56.427 +06 [ 6619] LOG: presmerovanie výstupu protokolu na proces zberania protokolov 18. september 13:01:56 server.1.com postmaster [6619]: 2019-09-18 13: 01: 56.427 +06 [6619] TIP: Zobrazí sa budúci výstup protokolu. v adresári „log“. 18. september 13:01:56 server.1.com systemd [1]: Spustený databázový server PostgreSQL 11.

Ak potrebujete prístup k PostgreSQL zvonku, musíte otvoriť port TCP / 5432 v štandardnom firewalle Centos 7:

# firewall-cmd --get-active-zones

verejné rozhrania: eth0

# firewall-cmd --zone = public --add-port = 5432 / tcp - permanentný
# firewall-cmd - obnoviť

Alebo prostredníctvom iptables:

# iptables-A INPUT -m state - status NEW -m tcp -p tcp --port 5432 -j ACCEPT

#služba iptables sa reštartuje

Ak je SELinux povolený, postupujte takto:

setsebool -P httpd_can_network_connect_db 1

Pripojte sa k PostgreSQL, vytvorte databázu, užívateľ

V predvolenom nastavení je pri inštalácii PostgreSQL v systéme jeden užívateľský postgres.

Neodporúčam používať ho na prácu s databázami, je lepšie vytvoriť používateľov pre každú databázu osobitne.

Ak sa chcete pripojiť k serveru postgres, musíte zadať príkaz:

[root @ server /] # sudo -u postgres psql

psql (11.5) Zadajte "help" pre pomoc.

postgres = #

Otvorila sa konzola PostgreSQL. Ukážeme niekoľko jednoduchých príkladov riadenia PostgreSQL z konzoly psql.

pretože každý užívateľ systému sa môže prihlásiť do postrgesql, najskôr musíte zmeniť heslo užívateľa postgres.

ALTER ROLE postgres With HESLO 'super_str0ng_pa $$ word';

Ihneď vytvorte novú databázu, užívateľ a poskytnite mu plné práva na túto databázu:

postgres = # CREATE DATABASE mydbtest;

postgres = # VYTVORIŤ UŽÍVATEĽA mydbuser S heslom '123456789';

postgres = # GRANT VŠETKY VÝSLEDKY NA DATABÁZE mydbtest TO mydbuser;

Pripojte sa k databáze:

postgres = # \ c názov databázy

Tabuľky so zoznamom:

postgres = # \ dt

Zoznam databázových dopytov:

postgres = # select * from pg_stat_activity where datname = "dbname"

Obnoviť všetky pripojenia k databáze:

postgres = # select pg_terminate_backend (pid) z pg_stat_activity kde datname = 'dbname'

Informácie o aktuálnej relácii je možné získať nasledovne:

postgres = # \ conninfo

Na dokončenie konzoly psql spustite:

postgres = # \ q

Ako ste si už všimli, syntax sa nelíši od tej istej MariaDB alebo MySQL, a preto sa nebudeme zdržiavať príkazov rovnakého typu..

Všimnite si, že pre pohodlnejšiu správu PostgreSQL databáz z webového rozhrania sa odporúča použiť pgAdmin4 (napísaný v Pythone a Javascript / jQuery). Jedná sa o analógiu k obvyklým webovým vývojárom PhpMyAdmin.

Základné možnosti konfiguračného súboru PostgreSQL

Konfiguračné súbory Postgresql sú umiestnené v adresári / var / lib / pgsql / 11 / data:

  • postgresql.conf - Samotný konfiguračný súbor postgresql
  • pg_hba.conf - súbor s nastaveniami prístupu. V tomto súbore môžete nastaviť rôzne obmedzenia pre používateľov, nastaviť politiku pre pripojenie k databáze;
  • pg_ident.conf - tento súbor sa používa pri identifikácii klientov pomocou protokolu ident.

Ak chcete zabrániť miestnym používateľom v prihlásení do postgresu bez autorizácie, v súbore pg_hba.conf zadajte:

local all all md5 host all all 127.0.0.1/32 md5

Zvážte najdôležitejšie parametre v konfiguračnom súbore postgresql.conf:

  • listen_addresses - označuje, ktoré adresy IP bude server akceptovať pripojenia klientov. Štandardne je zadaný localhost, čo znamená, že je možné iba lokálne pripojenie. Ak to chcete urobiť na všetkých rozhraniach IPv4, zadajte 0.0.0.0
  • max_connections - rovnako ako v iných DBMS je to maximálny počet súčasných pripojení k databázovému serveru;
  • temp_buffers - maximálna veľkosť dočasných vyrovnávacích pamätí;
  • shared_buffers - Množstvo zdieľanej pamäte, ktorú používa databázový server. Spravidla je nastavená na 25% pamäte nainštalovanej na serveri;
  • effective_cache_size - Parameter, ktorý pomáha plánovači postgresu určiť množstvo dostupnej pamäte na ukladanie do pamäte cache na disk. Parameter sa zvyčajne nastaví na veľkosť 50 - 75% celkovej pamäte RAM na serveri;
  • work_mem - množstvo pamäte, ktoré sa použije pri interných operáciách triedenia DBMS - OBJEDNÁVKA, DISTINCT a zlúčenie;
  • maintenance_work_mem - množstvo pamäte, ktoré sa použije na vnútorné operácie - VACUUM, CREATE INDEX a ALTER TABLE ADD FOREIGN KEY;
  • fsync - Ak je táto voľba povolená, DBMS bude čakať, kým sa fyzické údaje zapíšu na pevný disk. Ak je fsync povolený, bude pre vás ľahšie obnoviť databázu po zlyhaní systému alebo hardvéru. Zahrnutie tohto parametra samozrejme výrazne znižuje výkon DBMS, ale zvyšuje spoľahlivosť úložiska. Pri deaktivácii tohto parametra sa vyplatí zakázať full_page_writes;
  • max_stack_depth - maximálna veľkosť zásobníka (predvolene 2 MB);
  • max_fsm_pages - pomocou tohto parametra môžete na serveri spravovať voľné miesto na disku. Napríklad po odstránení údajov z tabuľky sa predtým obsadené miesto na disku neuvoľní, ale na mape voľného priestoru sa označí menovkou „free“ a potom sa použije na nové záznamy v tabuľke. Ak server aktívne zaznamenáva / odstraňuje údaje v tabuľkách, zvýšenie tohto parametra bude mať pozitívny vplyv na výkon;
  • wal_buffers - množstvo zdieľanej pamäte (shared_buffers), ktorá sa používa na ukladanie údajov WAL;
  • wal_writer_delay - čas medzi periódami zápisu WAL na disk;
  • commit_delay - oneskorenie medzi zápisom transakcie do vyrovnávacej pamäte WAL a jej vyprázdnením na disk;
  • synchronous_commit - parameter určuje, že výsledok úspešného dokončenia transakcie sa odošle, keď sa dáta WAL fyzicky zapíšu na disk.

Zálohovanie a obnova databázy v PostgreSQL

Existuje niekoľko spôsobov, ako zálohovať databázu PostgreSQL. Uvažujme o najjednoduchšej možnosti..

Najprv skontrolujte, ktoré databázy sú spustené na serveri:

postgres = # \ list

Máme 4 databázy, z ktorých 3 sú systém (postgres a šablóna).

Predtým sme vytvorili databázu s názvom „mydbtest“ pomocou jej príkladu a zálohovania.

Jedným zo spôsobov zálohovania je vykonanie pomocou nástroja pg_dump:

sudo -u postgres pg_dump mydbtest> /root/dupm.sql - vykonáme požiadavku od používateľa postgres, zadáme požadovanú databázu a cestu k súboru, do ktorého chcete uložiť výpis z databázy. Váš záložný systém môže vyzdvihnúť výpis databázy alebo ak používate webový server, môžete ho odoslať do cloudového úložiska.

Na obnovenie určeného výpisu do požadovanej databázy môžete použiť pomocný program psql:

sudo -u postgres psql mydbtest < /root/dupm.sql

Zálohu môžete vytvoriť aj v špeciálnom formáte výpisu a komprimovať pomocou gzip:

sudo -u postgres pg_dump -Fc mydbtest> /root/dumptest.sql

Takýto výpis sa obnoví pomocou obslužného programu pg_restore:

sudo -u postgres pg_restore -d mydbtest /root/dumptest.sql

Podrobnejšie nastavenia nájdete v pomocníkovi týchto nástrojov:

man psql
muž pg_dump
muž pg_restore

Optimalizácia a ladenie PostgreSQL

V predchádzajúcom článku o MariaDB sme ukázali, ako sa dajú parametre konfiguračného súboru my.cnf pomocou tunerov znížiť na ideálne hodnoty. Pre PostgreSQL existuje, hoci bolo presnejšie povedané, že taký nástroj, aký existoval, PgTun, ale nanešťastie nebol už dlho aktualizovaný. Zároveň existuje mnoho online služieb, pomocou ktorých môžete nakonfigurovať optimálnu konfiguráciu pre PostgreSQL. Páči sa mi služba pgtune.leopard.in.ua.

Rozhranie je veľmi jednoduché. Musíte zadať parametre servera (profil, procesory, pamäť, typ diskov) a kliknúť na tlačidlo „Generovať“. Výsledkom bude variant konfiguračného súboru postgresql.conf s odporúčanými hodnotami hlavných parametrov DBMS..

Napríklad pre server VPS SSD s 2 GB RAM, 2 CPU sa na spustenie niekoľkých serverov odporúčajú nasledujúce nastavenia v postgresql.conf:

# Verzia DB: 11 # Typ OS: linux # Typ DB: web # Celková pamäť (RAM): 2 GB # Počet CPU: 2 # Počet pripojení: 20 # Ukladanie údajov: ssd max_connections = 20 shared_buffers = 512MB efektivní_cache_size = 1536 MB údržba_work_mem = 128 MB checkpoint_completion_target = 0,7 wal_buffers = 16 MB default_statistics_target = 100 random_page_cost = 1,1 efektívne_io_concurrency = 200 work_mem = 26214kB min_wal_size = 1GB max_wal_size = 2GB max_worker_processes = 2 max_parallel_ers =

A v skutočnosti to nie je jediný zdroj, v čase písania, boli k dispozícii podobné služby:

  • Konfigurátor Cybertec PostgreSQL
  • Konfiguračný nástroj PostgreSQL

Pomocou týchto služieb môžete rýchlo nakonfigurovať počiatočné parametre DBMS pre vaše zariadenie a úlohy. V budúcnosti sa musíte spoľahnúť nielen na prostriedky servera, ale tiež analyzovať databázu ako celok, jej veľkosť, počet pripojení a na základe toho vykonať ďalšie doladenie parametrov PostgreSQL..

Kategórie