Namestite in konfigurirajte PostgreSQL v CentOS-u

V tem članku bomo namestili DBMS PostgreSQL 11 v Linuxu CentOS 7, izvedli bomo osnovno konfiguracijo strežnika in DBMS, upoštevali glavne parametre konfiguracijske datoteke kot tudi načine nastavitve uspešnosti. PostgreSQL je priljubljen brezplačni sistem za upravljanje objektnih in relacijskih baz podatkov. Čeprav ni tako razširjena kot MySQL / MariDB, je najbolj profesionalna.

Prednosti PostgreSQL:

  • Popolno skladnost s standardi SQL;
  • Visoka uspešnost z večvarnostnim upravljanjem hkratnega financiranja (MVCC);
  • Razširljivost (široko uporabljana v zelo obremenjenih okoljih);
  • Podpora za številne programske jezike;
  • Zanesljivi mehanizmi za prenos in kopiranje;
  • Podpora podatkov JSON.

Vsebina:

  • Namestite PostgreSQL na CentOS / RHEL
  • Povežite se s PostgreSQL, ustvarite bazo podatkov, uporabnika
  • Osnovne možnosti konfiguracijske datoteke PostgreSQL
  • Varnostno kopiranje in obnova baze podatkov v PostgreSQL
  • PostgreSQL optimizacija in uglaševanje

Namestite PostgreSQL na CentOS / RHEL

Čeprav je PostgreSQL mogoče namestiti iz osnovnega skladišča CentOS, bomo skladišče namestili od razvijalcev, saj ima vedno najnovejšo različico paketa.

Prvi korak je namestitev skladišča PosgreSQL (trenutno je nameščen na naslednji način):

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

To repozitorij vsebuje tako najnovejše različice PostgreSQL kot starejše različice. Informacije o skladišču so naslednje:

Namestite zadnjo različico (PostrgeSQL 11) z uporabo yum.

umestite postgresql11-server -y

Med namestitvijo se namesti sam strežnik PostgreSQL in potrebne knjižnice:

Namestitev: libicu-50.2-3.el7.x86_64 1/4 Namestitev: postgresql11-libs-11.5-1PGDG.rhel7.x86_64 2/4 Namestitev: postgresql11-11.5-1PGDG.rhel7.x86_64 3/4 Namestitev: postgresql11-server- 11.5-1PGDG.rhel7.x86_64 4/4

Po namestitvi paketov morate inicializirati bazo podatkov:

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

Prav tako takoj dodajte strežnik baz podatkov in ga zaženite:

systemctl omogočiti postgresql-11

systemctl začetek postgresql-11

Če želite preveriti, ali se je strežnik zagnal in da ni težav, preverite njegovo stanje:

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

● postgresql-11.service - strežnik baz podatkov PostgreSQL 11 Naloženo: naloženo (/usr/lib/systemd/system/postgresql-11.service; omogočeno; prednastavitev ponudnika: onemogočeno) Aktivno: aktivno (deluje) od srede 2019-09-18 13:01:56 +06; Pred 26 meseci Dokumenti: https://www.postgresql.org/docs/11/static/ Proces: 6614 ExecStartPre = / usr / pgsql-11 / bin / postgresql-11-check-db-dir $ PGDATA (code = izstopil, status = 0 / USPEH) Glavni 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: pisava v ozadju ├─6625 postgres: walwriter ├─6626 postgres: autovacuum launcher ├─6627 postgres: statistika zbiralec └─6628 postgres: zaganjalnik logične replikacije 18. september 13:01:56 server.1.com systemd [1]: Zagon strežnika baz podatkov PostgreSQL 11 ... 18. september 13:01:56 server.1.com postmaster [6619]: 2019-09-18 13: 01: 56.399 +06 [6619] LOG: poslušanje na naslovu IPv6 ":: 1", vrata 5432, 18. september 13:01:56 strežnik.1.com poštar [6619]: 2019-09-18 13: 01: 56.399 +06 [6619 ] LOG: poslušanje naslova IPv4 "127.0.0.1", vrata 5432, september 13 13:01:56 server.1.com poštar [6619]: 2019-09-18 13: 01: 56.401 +06 [6619] LOG: poslušanje v Unix socket "/var/run/postgresql/.s.PGSQL.5432" 18. september 13:01:56 server.1.com poštar [6619]: 2019-09-18 13: 01: 56.409 +06 [6619] LOG: poslušanje v Unix socket "/tmp/.s.PGSQL.5432" 18. september 13:01:56 server.1.com poštar [6619]: 2019-09-18 13: 01: 56.427 +06 [ 6619] LOG: preusmeritev izhoda dnevnika v postopek zbiranja dnevnikov 18. september 13:01:56 server.1.com postmaster [6619]: 2019-09-18 13: 01: 56.427 +06 [6619] NAMIG: Prikaže se prihodnji izhod dnevnika v imeniku "log". 18. september 13:01:56 server.1.com systemd [1]: Začel strežnik baz podatkov PostgreSQL 11.

Če potrebujete dostop do PostgreSQL od zunaj, morate odpreti vrata TCP / 5432 v standardnem požarnem zidu v Centos 7:

# požarni zid-cmd - območja, ki so aktivna

javni vmesniki: eth0

# požarni zid-cmd --zone = javni --add-port = 5432 / tcp - stalni
# požarni zid-cmd - naloži

Ali prek iptables:

# iptables-A INPUT -m stanje --state NOVO -m tcp -p tcp --portport 5432 -j SPREJEM

znova zaženite #service iptables

Če je omogočen SELinux, storite:

setsebool -P httpd_can_network_connect_db 1

Povežite se s PostgreSQL, ustvarite bazo podatkov, uporabnika

Pri namestitvi PostgreSQL je privzeto v sistemu en uporabnik-podgres.

Ne priporočam, da ga uporabljate za delo z bazami podatkov, bolje je ustvariti uporabnike za vsako bazo podatkov posebej.

Če se želite povezati s strežnikom postgres, morate vnesti ukaz:

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

psql (11.5) Za pomoč vnesite "help".

postgres = #

Odprla se je konzola PostgreSQL. Pokažimo nekaj preprostih primerov upravljanja PostgreSQL s konzole psql.

Ker vsak uporabnik sistema se lahko prijavi v postrgesql, najprej morate spremeniti geslo uporabnika postgres.

ALTER ROLE postgres with PASSWORD 'super_str0ng_pa $$ word';

Takoj ustvarite novo bazo podatkov, uporabnika in mu dajte polne pravice do te baze podatkov:

postgres = # CREATE DATABASE mydbtest;

postgres = # CREATE USER mydbuser Z geslom '123456789';

postgres = # PODELI VSE PRIVILEGE NA PODATKU mydbtest TO mydbuser;

Povezava z bazo podatkov:

postgres = # \ c ime baze podatkov

Seznam tabel:

postgres = # \ dt

Seznam poizvedb v bazi:

postgres = # select * od pg_stat_activity kjer je datname = "dbname"

Ponastavitev vseh povezav z bazo podatkov:

postgres = # izberite pg_terminate_backend (pid) iz pg_stat_activity kjer je ime name = 'dbname'

Informacije o trenutni seji so na voljo na naslednji način:

postgres = # \ conninfo

Za dokončanje konzole psql zaženite:

postgres = # \ q

Kot ste že opazili, se skladnja ne razlikuje od istega MariaDB ali MySQL, zato se ne bomo zadržali na ukazih iste vrste.

Upoštevajte, da je za bolj priročno upravljanje baz podatkov PostgreSQL iz spletnega vmesnika priporočljivo uporabiti pgAdmin4 (napisano v Python in Javascript / jQuery). To je analogni običajnim spletnim razvijalcem PhpMyAdmin.

Osnovne možnosti konfiguracijske datoteke PostgreSQL

Konfiguracijske datoteke postgresql se nahajajo v imeniku / var / lib / pgsql / 11 / data:

  • postgresql.conf - Konfiguracijska datoteka postgresql sama
  • pg_hba.conf - datoteka z nastavitvami dostopa. V tej datoteki lahko nastavite različne omejitve za uporabnike, nastavite pravilnik za povezavo z bazo;
  • pg_ident.conf - ta datoteka se uporablja pri identifikaciji strank s protokolom ident.

Če želite preprečiti, da se lokalni uporabniki brez dovoljenja prijavijo v postreže, v datoteki pg_hba.conf podajte:

lokalno vse vse md5 gosti vse 127.0.0.1/32 md5

Upoštevajte najpomembnejše parametre v konfiguracijski datoteki postgresql.conf:

  • poslušajte naslove - označuje, kateri IP naslovi bo strežnik sprejel povezave odjemalca. Privzeto je določen localhost, kar pomeni, da je mogoča le lokalna povezava. Če želite to narediti na vseh vmesnikih IPv4, podajte 0,0.0.0
  • max_connections - tako kot v drugih sistemih DBMS je tudi to največje število hkratnih povezav s strežnikom baz podatkov;
  • temp_buffers - največja velikost začasnih blažilnikov;
  • shared_buffers - Količina skupnega pomnilnika, ki ga uporablja strežnik baz podatkov. Običajno je nastavljen na 25% pomnilnika, nameščenega na strežniku;
  • učinkovita_cache_size - Parameter, ki pomaga razporejevalniku postgresov določiti količino razpoložljivega pomnilnika za predpomnjenje na disk. Običajno je parameter nastavljen na velikosti 50-75% celotnega RAM-a na strežniku;
  • work_mem - količino pomnilnika, ki ga bodo uporabljale notranje operacije razvrščanja DBMS - NAROČI PO, DISTINCT in združi;
  • vzdrževanje_delo_mem - količina pomnilnika, ki ga bodo uporabljale notranje operacije - VACUUM, CREATE INDEX in ALTER TABLE ADD FOREIGN KEY;
  • fsync - če je ta možnost omogočena, bo DBMS počakal, da se fizični podatki zapišejo na trdi disk. Če je omogočena funkcija fsync, boste lažje obnovili bazo podatkov po izpadu sistema ali strojne opreme. Seveda vključitev tega parametra znatno zmanjša zmogljivost DBMS, vendar poveča zanesljivost shranjevanja. Ko onemogočite ta parameter, je vredno onemogočiti full_page_writes;
  • max_stack_depth - največja velikost skladanja (privzeto 2 MB);
  • max_fsm_pages - s tem parametrom lahko upravljate prosti prostor na disku na strežniku. Na primer, ko izbrišete podatke iz tabele, se prej zasedeni prostor ne sprosti na disku, temveč se na zemljevidu prostega prostora označi z oznako »prosto« in nato uporabi za nove vnose v tabeli. Če strežnik aktivno snema / briše podatke v tabelah, bo povečanje tega parametra pozitivno vplivalo na uspešnost;
  • wal_buffers - količino skupnega pomnilnika (shared_buffers), ki se uporablja za shranjevanje WAL podatkov;
  • wal_writer_delay - čas med obdobji pisanja WAL na disk;
  • zavezi_delay - zamuda med pisanjem transakcije v medpomnilnik WAL in njegovo prikazovanje na disk;
  • sinhrono_pošiljanje - parameter določa, da bo rezultat uspešnega zaključka transakcije poslan, ko bodo podatki WAL fizično zapisani na disk.

Varnostno kopiranje in obnova baze podatkov v PostgreSQL

Obstaja več načinov za varnostno kopiranje baze podatkov PostgreSQL. Razmislimo o najpreprostejši možnosti..

Najprej preverite, katere baze podatkov se izvajajo na strežniku:

postgres = # \ seznam

Imamo 4 podatkovne baze, od tega 3 sistemske (postgres in predloga).

Pred tem smo ustvarili bazo podatkov z imenom "mydbtest" in uporabili njen primer ter ustvarili varnostno kopijo.

Eden od načinov za varnostno kopiranje je, da ga izvršite s pomočjo pripomočka pg_dump:

sudo -u postgres pg_dump mydbtest> /root/dupm.sql - izvedemo zahtevo uporabnika postgres, določimo želeno bazo podatkov in pot do datoteke, kamor želite shraniti smetišče baze podatkov. Vaš rezervni sistem lahko prevzame smetišče baze podatkov, če pa uporabljate spletni strežnik, ga lahko pošljete v shrambo v oblaku.

Če želite obnoviti navedeni zapis v želeno bazo podatkov, lahko uporabite pripomoček psql:

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

Prav tako lahko ustvarite varnostno kopijo v posebnem zapisu zapisa in jo stisnete s pomočjo gzip:

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

Takšen smetišče se obnovi s pomočjo pripomočka pg_restore:

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

Boljše napredne nastavitve najdete v pomoči za te pripomočke:

človek psql
moški pg_dump
moški pg_restore

PostgreSQL optimizacija in uglaševanje

V prejšnjem članku o MariaDB smo pokazali, kako se lahko parametri konfiguracijske datoteke my.cnf zmanjšajo na idealno s pomočjo uglaševalcev. Za PostgreSQL obstaja, čeprav bi bilo pravilneje reči, da obstaja takšen pripomoček, kot je PgTun, vendar žal ni bil dolgo posodobljen. Hkrati obstaja veliko spletnih storitev, s katerimi lahko konfigurirate optimalno konfiguracijo za vaš PostgreSQL. Všeč mi je storitev pgtune.leopard.in.ua.

Vmesnik je zelo preprost. Določiti morate parametre vašega strežnika (profil, procesorje, pomnilnik, vrsto diskov) in kliknite gumb »Ustvari«. Posledično vam bo na voljo različica konfiguracijske datoteke postgresql.conf s priporočenimi vrednostmi glavnih parametrov DBMS.

Na primer, za strežnik VPS SSD z 2 GB RAM-a in 2 CPU-ja so za zagon več mest priporočljive naslednje nastavitve v postgresql.conf:

# Različica DB: 11 # vrsta OS: linux # vrsta DB: splet # skupni pomnilnik (RAM): 2 GB # število CPU-jev: 2 # število povezav: 20 # shranjevanje podatkov: ssd max_connections = 20 shared_buffers = 512MB efektivna_cache_size = 1536MB vzdrževanje_work_mem = 128MB checkpoint_completion_target = 0,7 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1,1 efektivna_io_konzornost = 200 work_mem = 26214kB min_wal_size = 1GB max_wal_size = 2 GB max_worker_process = 2 max_parallel_workersers_2_2

In to dejansko ni edini vir, v času pisanja so bile na voljo podobne storitve:

  • Cybertec PostgreSQL konfigurator
  • Orodje za konfiguriranje PostgreSQL

S pomočjo teh storitev lahko hitro konfigurirate začetne parametre DBMS za svojo opremo in opravila. V prihodnosti se morate zanesti ne le na strežniške vire, temveč tudi analizirati bazo podatkov kot celoto, njeno velikost, število povezav in na podlagi tega opraviti nadaljnjo natančno nastavitev parametrov PostgreSQL.

Kategorija