Návod popisuje jak nainstalovat databázový server PostgreSQL 11 na operačním systému CentOS s kódováním UTF8, locale cs_CZ.UTF-8 a řazením C(utf8_bin).
1.) Instalace locale na CentOS
Nejprve zkontrolujeme na systému locale příkazem:
a pokud neobsahuje cs_CZ.UTF-8, tak jej doinstalujeme buď samotné nebo použijem příkaz pro instalaci všech locale:
dnf install langpacks-en glibc-all-langpacks
2.) Provedeme module disable
Pokud chcete instalovat Postgres 11, tak je potřeba spuštění tohoto příkazu jinak se vám bude defaultně cpát verze nejvyšší (PSQL12,PSQL13 atd.).
dnf -qy module disable postgresql
3.) Instalace balíčku (client a server)dnf -y install postgresql11 postgresql11-server
Last metadata expiration check: 0:43:40 ago on Wed 15 Apr 2020 03:24:34 PM CEST.
Dependencies resolved.
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
postgresql11 x86_64 11.7-1PGDG.rhel8 COSUS.amarin.cz8PostgreSQL11 1.7 M
postgresql11-server
x86_64 11.7-1PGDG.rhel8 COSUS.amarin.cz8PostgreSQL11 4.9 M
Installing dependencies:
postgresql11-libs
x86_64 11.7-1PGDG.rhel8 COSUS.amarin.cz8PostgreSQL11 390 k
Transaction Summary
================================================================================
Install 3 Packages
Total download size: 7.0 M
Installed size: 33 M
Downloading Packages:
(1/3): postgresql11-libs-11.7-1PGDG.rhel8.x86_6 19 MB/s | 390 kB 00:00
(2/3): postgresql11-11.7-1PGDG.rhel8.x86_64.rpm 48 MB/s | 1.7 MB 00:00
(3/3): postgresql11-server-11.7-1PGDG.rhel8.x86 77 MB/s | 4.9 MB 00:00
Total 107 MB/s | 7.0 MB 00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : postgresql11-libs-11.7-1PGDG.rhel8.x86_64 1/3
Running scriptlet: postgresql11-libs-11.7-1PGDG.rhel8.x86_64 1/3
Installing : postgresql11-11.7-1PGDG.rhel8.x86_64 2/3
Running scriptlet: postgresql11-11.7-1PGDG.rhel8.x86_64 2/3
Running scriptlet: postgresql11-server-11.7-1PGDG.rhel8.x86_64 3/3
Installing : postgresql11-server-11.7-1PGDG.rhel8.x86_64 3/3
Running scriptlet: postgresql11-server-11.7-1PGDG.rhel8.x86_64 3/3
Verifying : postgresql11-11.7-1PGDG.rhel8.x86_64 1/3
Verifying : postgresql11-libs-11.7-1PGDG.rhel8.x86_64 2/3
Verifying : postgresql11-server-11.7-1PGDG.rhel8.x86_64 3/3
Installed:
postgresql11-11.7-1PGDG.rhel8.x86_64
postgresql11-server-11.7-1PGDG.rhel8.x86_64
postgresql11-libs-11.7-1PGDG.rhel8.x86_64
Complete!
4.) Změna umístění výchozí cesty pro data
Ve výchozím stavu jsou se soubory ukládají /var/lib/pgsql/11/data/ Pokud je potřeba ukládat data do jiné cesty např. /data/postgres je potřeba v souboru
postgresql-11.service udělat úpravu:
vi /usr/lib/systemd/system/postgresql-11.service
a změnit:
Environment=PGDATA=/var/lib/pgsql/11/data/
na:
Environment=PGDATA=/data/postgres/
Toto provedete, ale pouze v případě, že je to nutné (např. v případě, že je adresářová koncepce přesně stanovena).
Restart daemona
5.) Inicializace databáze
Inicializace DB s kódováním
UTF8, locale
cs_CZ.UTF8 a collate=
C (odpovídá řazení
utf8_bin)
sudo su postgres
/usr/pgsql-11/bin/initdb -D /var/lib/pgsql/11/data/ --encoding UTF8 --locale=cs_CZ.UTF-8 --lc-collate=C
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locales
COLLATE: C
CTYPE: cs_CZ.UTF-8
MESSAGES: cs_CZ.UTF-8
MONETARY: cs_CZ.UTF-8
NUMERIC: cs_CZ.UTF-8
TIME: cs_CZ.UTF-8
initdb: could not find suitable text search configuration for locale "cs_CZ.UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/pgsql/11/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Europe/Prague
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/pgsql-11/bin/pg_ctl -D /var/lib/pgsql/11/data/ -l logfile start
Zjednodušení inicializace DB s defaultním en_US.UTF-8 (toto je jen na ukázkui)
/usr/pgsql-11/bin/postgresql-11-setup initdb
Initializing database ... OK
6.) Start a enable postgres
Start postgres
systemctl start postgresql-11.service
Enable postgres, aby najela po restartu serveru
systemctl enable postgresql-11.service
Přepnutí na uživatele pod kterým běží DB
Spuštění postgres promptu
List databází:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
postgres | postgres | UTF8 | C | cs_CZ.UTF-8 |
template0 | postgres | UTF8 | C | cs_CZ.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | cs_CZ.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
Ověření verze Postgres:
version
PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
(1 row)
Ověření cesty pro ukládání dat:
data_directory
/var/lib/pgsql/11/data
(1 row)
7.) Vytvoření databáze, založení uživatelů, konfigurace firewall, základní tuning
Ve firewalld (CentOS firewall) byla povolena služba postgresql - otevřen port 5432:
sudo firewall-cmd --add-service=postgresql --permanent
sudo firewall-cmd --reload
Změna použité metody šifrování hesel z MD5 (již není považováno za bezpečné na scram-sha-256):
Do konfiguračního souboru /var/lib/pgsql/11/data/postgresql.conf přidáme následující řádek:
password_encryption = scram-sha-256
Zaheslování základního uživatele postgres heslo
ALTER USER postgres WITH PASSWORD '************';
Vytvoření databázového uživatele "dbuser" heslo
create user sbuser with encrypted password '************';
Vytvoření databáze testovací databáez testdb3 (nelze vytvořet datábáze velkými písmeny TESTDB3 - Postgres to zkonvertuje na malé) s českým kódováním:
create database testdb3 encoding UTF8 LC_COLLATE 'C' template template0;
Nastavení práv na databázi pro "userdb" uživatele:
grant all privileges on database testdb3 to dbuser;
Povolení vzdáleného přístupu obecně - postgresql začne naslouchat na všech rozhraních na portu 5432:
Přidáme do konfiguračního souboru
/var/lib/pgsql/11/data/postgresql.conf následující řádek:
Restartujeme službu postgresql
systemctl restart postgresql-11.service
Povolení vzdáleného přístup z aplikačního serveru srv1.mojedomena.cz pouze pro uživatele "dbuser" na jeho dvě databáze:
Přidání následujících řádků na konec souboru
/var/lib/pgsql/11/data/pg_hba.conf:
host testdb3 dbuser 10.XXX.XXX.XXX/XX scram-sha-256
Restartujeme službu postgresql
systemctl restart postgresql-11.service
Performance tuning
Přidáme/odkomentujeme/upravíme následující řádky v konfiguračním souboru
/var/lib/pgsql/11/data/postgresql.conf:
max_connections = 20
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
random_page_cost = 1.1
effective_io_concurrency = 20
work_mem = 26MB
wal_buffers = -1
min_wal_size = 1GB
checkpoint_timeout = 30min
max_wal_size = 3GB
max_worker_processes = 4
max_parallel_workers = 4
max_parallel_workers_per_gather = 2
max_parallel_maintenance_workers = 2
max_pred_locks_per_transaction = 2048
Hodnoty jsou pro každou instalaci individuální podle toho k čemu bude řešení použito a jaké jsou k dispozici systémové prostředky.
Restartujeme službu postgresql
systemctl restart postgresql-11.service