В тази статия ще инсталираме СУБД PostgreSQL 11 на Linux CentOS 7, ще извършим основна конфигурация на сървъра и СУБД, ще разгледаме основните параметри на конфигурационния файл, както и начините за настройка на производителността. PostgreSQL е популярна безплатна обектно-релационна система за управление на база данни. Въпреки че не е толкова разпространен като MySQL / MariDB, той е най-професионален.
Силни страни на PostgreSQL:
- Пълно съответствие със SQL стандартите;
- Висока ефективност чрез многовариантно управление на паралелността (MVCC);
- Мащабируемост (широко използван в силно натоварена среда);
- Поддръжка на много езици за програмиране;
- Надеждни механизми за транзакции и репликации;
- Поддръжка на данни JSON.
Съдържание:
- Инсталирайте PostgreSQL на CentOS / RHEL
- Свържете се с PostgreSQL, създайте база данни, потребител
- Основни опции за конфигурационен файл на PostgreSQL
- Архивиране и възстановяване на база данни в PostgreSQL
- PostgreSQL оптимизация и настройка
Инсталирайте PostgreSQL на CentOS / RHEL
Въпреки че PostgreSQL може да бъде инсталиран от базовото хранилище CentOS, ние ще инсталираме хранилището от разработчиците, тъй като винаги има най-новата версия на пакета.
Първата стъпка е да инсталирате хранилището PosgreSQL (в момента той е инсталиран, както следва):
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Това хранилище съдържа както най-новите версии на PostgreSQL, така и по-старите версии. Информацията в хранилището е следната:
Инсталирайте последната налична версия (PostrgeSQL 11) с помощта на yum.
yum инсталирате postgresql11-сървър -y
По време на инсталацията се инсталира самият PostgreSQL сървър и необходимите библиотеки:
Инсталиране: libicu-50.2-3.el7.x86_64 1/4 Инсталиране: postgresql11-libs-11.5-1PGDG.rhel7.x86_64 2/4 Инсталиране: postgresql11-11.5-1PGDG.rhel7.x86_64 3/4 Инсталиране: postgresql11-server- 11.5-1PGDG.rhel7.x86_64 4/4
След като инсталирате пакетите, трябва да инициализирате базата данни:
/ usr / pgsql-11 / bin / postgresql-11-setup initdb
Освен това незабавно добавете сървъра на базата данни за стартиране и го стартирайте:
systemctl активира postgresql-11
systemctl start postgresql-11
За да сте сигурни, че сървърът е стартиран и че няма проблеми, проверете състоянието му:
[root @ server ~] # systemctl status postgresql-11
● postgresql-11.service - PostgreSQL 11 сървър на база данни Зареден: зареден (/usr/lib/systemd/system/postgresql-11.service; активиран; настройка на доставчика: деактивиран) Активен: активен (работи) от сряда 2019-09-18 13:01:56 +06; Преди 26 години Документи: https://www.postgresql.org/docs/11/static/ Процес: 6614 ExecStartPre = / usr / pgsql-11 / bin / postgresql-11-check-db-dir $ PGDATA (code = излязъл, статус = 0 / УСПЕХ) Основен PID: 6619 (пощенски началник) 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: фонов писател ├─6625 postgres: walwriter ├─6626 postgres: autovacuum launcher ├─6627 postgres: статистика колектор └─6628 postgres: логическа репликация на репликация 18 септември 13:01:56 server.1.com systemd [1]: Стартиране на PostgreSQL 11 сървър на база данни ... 18 септември 13:01:56 server.1.com postmaster [6619]: 2019-09-18 13: 01: 56.399 +06 [6619] ЛОГ: слушане на IPv6 адрес „:: 1“, порт 5432 септември 18 13:01:56 сървър.1.com пощенски майстор [6619]: 2019-09-18 13: 01: 56.399 +06 [6619 ] LOG: слушане на IPv4 адрес „127.0.0.1“, порт 5432 септември 18 13:01:56 сървър.1.com поща [6619]: 2019-09-18 13: 01: 56.401 +06 [6619] LOG: слушане на Unix сокет "/var/run/postgresql/.s.PGSQL.5432" 18 септември 13:01:56 сървър.1.com поща: [6619]: 2019-09-18 13: 01: 56.409 +06 [6619] LOG: слушане на Unix сокет "/tmp/.s.PGSQL.5432" 18 септември 13:01:56 сървър.1.com пощенски майстор [6619]: 2019-09-18 13: 01: 56.427 +06 [ 6619] LOG: пренасочване на изходния журнал към процеса на събиране на журнали, сеп 18 13:01:56 server.1.com postmaster [6619]: 2019-09-18 13: 01: 56.427 +06 [6619] СЪВЕТ: Бъдещият изход на журнала ще се появи в директория "лог". 18 септември 13:01:56 server.1.com systemd [1]: Стартиран PostgreSQL 11 сървър на база данни.
Ако имате нужда от достъп до PostgreSQL отвън, трябва да отворите TCP / 5432 порта в стандартната защитна стена в Centos 7:
# firewall-cmd --get-active-зони
обществени интерфейси: eth0
# firewall-cmd --zone = public --add-port = 5432 / tcp - постоянен
# firewall-cmd - зареждане
Или чрез iptables:
# iptables-A INPUT -m състояние - state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
#service iptables се рестартира
Ако SELinux е активиран, направете:
setsebool -P httpd_can_network_connect_db 1
Свържете се с PostgreSQL, създайте база данни, потребител
По подразбиране, когато инсталирате PostgreSQL, има един потребител -postgres в системата.
Не препоръчвам да го използвате за работа с бази данни, по-добре е да създадете потребители за всяка база данни поотделно.
За да се свържете със postgres сървъра, трябва да въведете командата:
[root @ server /] # sudo -u postgres psql
psql (11.5) Въведете "help" за помощ.
postgres = #
Конзолата PostgreSQL се отвори. Нека покажем няколко прости контролни примера на PostgreSQL от конзолата psql.
защото всеки потребител на системата може да влезе в postrgesql, първо трябва да промените паролата на потребителя на postgres.
ALTER ROLE postgres С PASSWORD 'super_str0ng_pa $$ word';
Незабавно създайте нова база данни, потребителя и му дайте пълни права върху тази база данни:
postgres = # СЪЗДАВАНЕ НА DATABASE mydbtest;
postgres = # СЪЗДАВАНЕ НА ПОТРЕБИТЕЛЯ mydbuser С парола '123456789';
postgres = # ПРЕДОСТАВЯТ ВСИЧКИ ПРИВИЛЕГИ НА ДАТАБАЗА mydbtest TO mydbuser;
Свържете се с базата данни:
postgres = # \ c име на база данни
Списък с таблици:
postgres = # \ dt
Списък на заявките към базата данни:
postgres = # select * от pg_stat_activity където datname = "dbname"
Нулиране на всички връзки към базата данни:
postgres = # select pg_terminate_backend (pid) от pg_stat_activity където datname = 'dbname'
Информация за текущата сесия може да бъде получена, както следва:
postgres = # \ conninfo
За да завършите psql конзолата, изпълнете:
postgres = # \ q
Както вече забелязахте, синтаксисът не се различава от същия MariaDB или MySQL и следователно няма да се спираме на командите от същия тип.
Обърнете внимание, че за по-удобно управление на PostgreSQL бази данни от уеб интерфейса се препоръчва използването на pgAdmin4 (написано в Python и Javascript / jQuery). Това е аналог на обичайните уеб разработчици на PhpMyAdmin.
Основни опции за конфигурационен файл на PostgreSQL
Postgresql конфигурационните файлове се намират в директорията / var / lib / pgsql / 11 / data:
- postgresql.conf - Самият конфигурационен файл postgresql
- pg_hba.conf - файл с настройки за достъп. В този файл можете да зададете различни ограничения за потребителите, да зададете политика за свързване към базата данни;
- pg_ident.conf - този файл се използва при идентифициране на клиенти, използващи идентичен протокол.
За да предотвратите локалните потребители да влизат в postgres без разрешение, посочете във файла pg_hba.conf:
локално всички всички md5 хост всички всички 127.0.0.1/32 md5
Помислете за най-важните параметри в конфигурационния файл postgresql.conf:
- listen_addresses - указва кои IP адреси сървърът ще приема клиентски връзки. По подразбиране е зададен localhost, което означава, че е възможна само локална връзка. За да направите това за всички IPv4 интерфейси, посочете 0.0.0.0
- max_connections - както в други СУБД, това е максималният брой едновременни връзки към сървъра на базата данни;
- temp_buffers - максимален размер на временни буфери;
- shared_buffers - Количеството споделена памет, използвана от сървъра на базата данни. Обикновено се задава на 25% от паметта, инсталирана на сървъра;
- effective_cache_size - Параметър, който помага на планировчика на postgres да определи количеството на наличната памет за кеширане на диска. Обикновено параметърът се задава в размер 50-75% от общата RAM памет на сървъра;
- work_mem - количеството памет, което ще бъде използвано от вътрешните операции за сортиране на СУБД - ORDER BY, DISTINCT и сливане;
- maintenance_work_mem - количеството памет, което ще се използва от вътрешни операции - VACUUM, CREATE INDEX и ALTER TABLE ADD FOREIGN KEY;
- fsync - ако тази опция е активирана, СУБД ще изчака физическите данни да бъдат записани на твърдия диск. С активирана fsync ще ви бъде по-лесно да възстановите базата данни след грешка в системата или хардуера. Естествено, включването на този параметър значително намалява производителността на СУБД, но увеличава надеждността на съхранението. Когато деактивирате този параметър, си струва да деактивирате full_page_writes;
- max_stack_depth - максимален размер на стека (2 MB по подразбиране);
- max_fsm_pages - използвайки този параметър, можете да управлявате свободното пространство на диска на сървъра. Например, след изтриване на данни от таблицата, заеманото преди това пространство не се освобождава на диска, а се маркира на картата с свободно пространство с етикет „свободно“ и след това се използва за нови записи в таблицата. Ако сървърът активно записва / изтрива данни в таблици, увеличаването на този параметър ще повлияе положително на производителността;
- wal_buffers - количеството споделена памет (shared_buffers), която се използва за съхраняване на WAL данни;
- wal_writer_delay - времето между периодите на записване на WAL на диск;
- commit_delay - забавянето между записването на транзакция в буфера WAL и предаването й на диск;
- synchronous_commit - параметърът определя, че резултатът от успешното приключване на транзакцията ще бъде изпратен, когато данните на WAL бъдат записани физически на диск.
Архивиране и възстановяване на база данни в PostgreSQL
Има няколко начина за архивиране на PostgreSQL база данни. Нека помислим за най-простия вариант..
Първо проверете кои бази данни работят на сървъра:
postgres = # \ списък
Имаме 4 бази данни, 3 от които са системни (постгреси и шаблон).
По-рано създадохме база данни с името „mydbtest“, използвайки нейния пример и архивирайте.
Един от начините за архивиране е да го изпълните с помощта на помощната програма pg_dump:
sudo -u postgres pg_dump mydbtest> /root/dupm.sql - изпълняваме заявката от потребителя postgres, уточняваме желаната база данни и пътя към файла, в който искате да запазите сметището на базата данни. Вашата система за архивиране може да вземе сметището на базата данни или ако използвате уеб сървър, можете да го изпратите до вашето облачно хранилище.
За да възстановите посочения буфер в желаната база данни, можете да използвате помощната програма psql:
sudo -u postgres psql mydbtest < /root/dupm.sql
Можете също да създадете резервно копие в специален формат за зареждане и да се компресира с помощта на gzip:
sudo -u postgres pg_dump -Fc mydbtest> /root/dumptest.sql
Такъв демпф се възстановява с помощта на полезната програма pg_restore:
sudo -u postgres pg_restore -d mydbtest /root/dumptest.sql
По-разширените настройки могат да бъдат намерени в помощта за тези помощни програми:
мъж psql
мъж pg_dump
мъж pg_restore
PostgreSQL оптимизация и настройка
В предишната статия за MariaDB показахме как параметрите на конфигурационния файл my.cnf могат да бъдат намалени до идеал с помощта на тунери. За PostgreSQL има, въпреки че е по-правилно да се каже такава полезност като PgTun, но за съжаление тя не е актуализирана отдавна. В същото време има много онлайн услуги, с които можете да конфигурирате оптималната конфигурация за вашия PostgreSQL. Харесва ми услугата pgtune.leopard.in.ua.
Интерфейсът е много прост. Трябва да посочите параметрите на вашия сървър (профил, процесори, памет, тип дискове) и да кликнете върху бутона „Генериране“. В резултат на това ще ви бъде предложен вариант на конфигурационния файл postgresql.conf с препоръчителни стойности на основните DBMS параметри.
Например, за VPS SSD сървър с 2 GB RAM, 2 процесора, следните настройки в postgresql.conf се препоръчват за стартиране на няколко сайта:
# DB Версия: 11 # Тип на ОС: Linux # DB Тип: web # Обща памет (RAM): 2 GB # Брой процесори: 2 # Брой връзки: 20 # Съхранение на данни: ssd max_connections = 20 shared_buffers = 512MB efektiv_cache_size = 1536MB поддръжка_работка_mem = 128MB checkpoint_completion_target = 0.7 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1.1 ефективни_io_concurrency = 200 work_mem = 26214kB min_wal_size = 1GB max_wal_size = 2GB max_worker_process = 2 max_parallel_workers_ 2
И това всъщност не е единственият ресурс, по времето на писането се предлагаха подобни услуги:
- Cybertec PostgreSQL конфигуратор
- PostgreSQL инструмент за конфигуриране
Използвайки тези услуги, можете бързо да конфигурирате първоначалните DBMS параметри за вашето оборудване и задачи. В бъдеще трябва да разчитате не само на сървърните ресурси, но и да анализирате базата данни като цяло, нейния размер, брой връзки и въз основа на това да извършите допълнителна фина настройка на PostgreSQL параметрите.
