PostgreSQL орнату және баптау

PostgreSQL — объектілі-реляциялық модельге негізделген деректер қорын басқару жүйесі. Ол SQL тілімен жұмыс істейді және икемділігімен, сенімділігімен және көптеген мүмкіндіктерімен танымал. PostgreSQL әсіресе әдеттегі ДҚБЖ-лер әрдайым қолдай бермейтін стандартты емес деректер құрылымдарымен жұмыс істеу қажет күрделі жобаларда жиі қолданылады.

Бұл мақалада Ubuntu 18.04 операциялық жүйесі орнатылған виртуалды арнаулы серверге (VPS) PostgreSQL орнату процесін қарастырамыз. Сондай-ақ осы ДҚБЖ-мен жұмыс істеудің негізгі аспектілеріне тоқталамыз.

Осы нұсқаулықта сипатталған әрекеттерді сәтті орындау үшін серверіңізде sudo құқықтары бар пайдаланушы болуы қажет.

PostgreSQL орнату

PostgreSQL Ubuntu-дың ресми репозиторийлеріне кіреді, сондықтан оны орнату бірнеше қарапайым қадамнан тұрады:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Бұл командалар қолжетімді пакеттер туралы ақпаратты жаңартып, PostgreSQL-ді, соның ішінде ДҚБЖ-мен жұмыс істеуге арналған қосымша функциялар мен утилиталарды беретін contrib пакетін орнатады.

PostgreSQL орнатылған кезде postgres атты жүйелік рөл мен Linux пайдаланушысы құрылады, ол арқылы PostgreSQL-мен өзара әрекеттесуге болады.

Пайдаланушылар және рөлдер

PostgreSQL-де "пайдаланушылар" терминінің орнына "рөлдер" ұғымы қолданылады. Көп жағдайда рөлдерді деректер қорының пайдаланушылары ретінде қарастыруға болады.

Рөлдер түрлі функцияларды атқара алады: олар деректер қорының объектілеріне (мысалы, кестелерге) иелік етіп, басқа рөлдерге осы объектілерге қол жеткізуге рұқсат бере алады. Сондай-ақ рөлдерді басқа рөлдерге қосуға болады — пайдаланушы топтың артықшылықтарын мұраға алу үшін топқа қосылатыны сияқты.

PostgreSQL-ді орнатқанда жүйе postgres рөлін автоматты түрде құрады. Бұл рөлді ДҚБЖ-мен өзара әрекеттесу үшін пайдалана аласыз. Осы рөл атынан кіру үшін келесі команданы орындаңыз:

sudo su - postgres

Содан кейін PostgreSQL интерактивті интерфейсін іске қосыңыз:

psql

PostgreSQL интерфейсінен шығу үшін \q командасын енгізіңіз:

postgres=# \q

Қол жеткізу құқықтарын басқаруды жеңілдету үшін PostgreSQL әр деректер қоры үшін жеке рөл құруды көздейді. Сондай-ақ рөлдер мен Linux пайдаланушыларының аттары сәйкес келсе, деректер қорына қосылу ыңғайлырақ болады.

Жаңа рөл құру

Рөлді жүйелік консольден (psql арқылы емес) құру үшін келесі команданы пайдаланыңыз:

createuser -P --interactive

-P жалауы пайдаланушыға құпиясөзді бірден тағайындауға мүмкіндік береді, ал --interactive қосымша параметрлерді енгізу үшін интерактивті режимді іске қосады.

Жүйе кезекпен жаңа рөлдің атын, оның құпиясөзі мен құпиясөзді растауды сұрайды, сондай-ақ артықшылықтарды анықтауға мүмкіндік береді: рөлді суперпайдаланушы ету керек пе, оның деректер қорларын және басқа рөлдерді құруға құқығы бар ма. Таңдауыңыз бойынша жай ғана y / n енгізіп, Enter басыңыз.

Рөлді Postgres командалық жолынан құру үшін CREATE ROLE командасын пайдаланыңыз.

Егер бұл команданы қосымша опцияларсыз пайдалансаңыз, пайдаланушы ешқандай артықшылықсыз құрылады. Сондықтан қажетті артықшылықтарды рөлді құру кезінде бірден көрсету ұсынылады. Барлық қолжетімді опциялармен мына команданы орындап танысуға болады:

\h CREATE ROLE

Жаңа рөл құру үшін мынаны орындаңыз:

CREATE ROLE рөл_аты WITH LOGIN CREATEDB CREATEROLE;

Содан кейін жаңа пайдаланушы үшін құпиясөз орнатыңыз:

\password рөл_аты

Бар рөлдерді қарау

Құрылған пайдаланушылар мен олардың артықшылықтарын қарау үшін Postgres командалық жолына өтіңіз:

psql

Содан кейін келесі команданы орындаңыз:

\du

Шығыс мысалы:

                                   List of roles
 Role name |                        Attributes                          | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 appuser   | Create role, Create DB                                     | {}
 tweb      | Create role, Create DB                                     | {}

Шығысты жабу үшін q пернесін басыңыз, ал Postgres командалық жолынан шығу қажет болса, \q енгізіңіз.

Рөлді жою

Рөлді жою үшін жүйелік консольдегі мына команданы пайдалануға болады:

dropuser рөл_аты

Сондай-ақ Postgres консоліндегі команданы пайдалануға болады:

DROP ROLE рөл_аты;

Пайдаланушы құпиясөзін ауыстыру

Рөлдердің бірінің құпиясөзін өзгерту үшін келесі қадамдарды орындаңыз:

1. Postgres-ке суперпайдаланушы (мысалы, postgres) немесе тиісті артықшылықтары бар басқа рөл атынан қосылыңыз.

2. Мына команданы орындаңыз:

ALTER USER рөл_аты WITH PASSWORD 'жаңа_құпиясөз';

Назар аударыңыз: бұл операция .psql_history файлында құпиясөзбен бірге ашық түрде сақталады. Қауіпсіздік мақсатында тиісті жазбаны файлдан жою ұсынылады.

Әдетте бұл файл /var/lib/postgresql каталогында орналасады. Оның орнын табу үшін жүйе терминалында келесі команданы орындаңыз:

grep postgres /etc/passwd | cut -d ':' -f 6

Содан кейін файлға апаратын дұрыс жолды көрсетіп, оны ашыңыз:

sudo nano /var/lib/postgresql/.psql_history

Құпиясөзі бар жазбаны жойып, өзгерістерді сақтаңыз.

Деректер қорын құру

Деректер қорын жүйелік консольден құру үшін келесі команданы орындаңыз:

createdb база_аты

Деректер қорын Postgres консолінен құру үшін келесі команданы пайдаланыңыз:

CREATE DATABASE база_аты;

Postgres консолінде деректер қорларының тізімін қарау үшін \l командасын орындаңыз:

postgres=# \l

Деректер қорына қосылу

1. Әдепкі бойынша (қосымша опцияларсыз) Postgres ағымдағы пайдаланушы атынан аттас базаға қосылуға тырысады. Егер Linux пайдаланушысының, Postgres рөлінің және деректер қорының аттары сәйкес келсе, мынаны орындаңыз:

psql

Мысалы, серверде "linuxuser" пайдаланушысы және Postgres-те "linuxuser" рөлі, сондай-ақ дәл сондай атты деректер қоры болса, осы командамен қосыла аласыз:

Қосылғаннан кейін ағымдағы қосылым туралы ақпаратты қарау үшін \conninfo командасын пайдаланыңыз.

1.2. Егер деректер қорының аты басқаша болса, оны командада былай көрсетіңіз:

psql -d база_аты

2. Егер рөл аты Linux пайдаланушысының атымен сәйкес келмесе, қосылу командасында қосымша параметрлерді көрсету қажет.

2.1. Егер рөл аты мен база аты сәйкес келсе, келесі команданы орындаңыз:

psql -U рөл_аты -h localhost -W

\conninfo шығысынан көріп отырғанымыздай, біз "appuser" базасына аттас рөл атынан қосылдық.

2.2. Егер рөл аты мен база аты әртүрлі болса, қосылу командасында деректер қорын да көрсету қажет:

psql -U рөл_аты -d база_аты -h localhost -W

3. Егер сіз Postgres консолінде болсаңыз және басқа деректер қорына ауысқыңыз келсе, келесі команданы пайдаланыңыз:

\c база_аты

Сақтық көшірмелер (дампты экспорттау және импорттау)

PostgreSQL-ді серверге орнатқанда pg_dump және pg_restore утилиталары да орнатылады, олар деректер қорларының сақтық көшірмелерін командалық жолдан жасауға (pg_dump) және деректерді осы көшірмелерден қалпына келтіруге (pg_restore) мүмкіндік береді.

Сақтық көшірме жасау

Деректер қорының сақтық көшірмесін жасап, оны серверде сақтау үшін келесі команданы орындаңыз:

pg_dump -h хост -U рөл_аты -F дамп_форматы -f дамп_жолы база_аты

Мұндағы параметрлердің мағынасы:

• хост — деректер қоры орналасқан сервер, localhost, IP мекенжай немесе домендік атау ретінде көрсетілуі мүмкін;
• рөл_аты — сақтық көшірме жасалатын PostgreSQL пайдаланушысының аты;
• дамп_форматы — дамп сақталатын формат, 'c' (custom - .tar.gz архиві), 't' (tar - .tar архиві) немесе 'p' (plain - сығылмаған мәтіндік файл, көбінесе .sql) ретінде көрсетіледі;
• дамп_жолы — файл атауын қоса алғанда, сақтық көшірме сақталатын файлға апаратын жол;
• база_аты — сақтық көшірмесі жасалатын деректер қорының аты.

Мысалы:

pg_dump -h localhost -U appuser -F c -f /home/user/backups/dump.tar.gz appuser

Бұл команданы орындағаннан кейін жүйе -U параметрінде көрсетілген Postgres пайдаланушысының (бұл жағдайда appuser) құпиясөзін сұрайды.

Дамптан қалпына келтіру

.tar.gz және .tar форматтарында сақталған дамптарды импорттау үшін pg_restore утилитасын былай пайдаланыңыз:

pg_restore -h хост -U рөл_аты -F дамп_форматы -d база_аты дамп_жолы

Мұндағы параметрлердің мағынасы:

• хост — деректер қоры орналасқан сервер, localhost, IP мекенжай немесе домендік атау ретінде көрсетілуі мүмкін;
• рөл_аты — импорт орындалатын PostgreSQL пайдаланушысының аты;
• дамп_форматы — дамп сақталған формат, .tar.gz архиві үшін 'c', ал .tar архиві үшін 't' деп көрсету қажет;
• база_аты — дамп импортталатын деректер қорының аты;
• дамп_жолы — атауын қоса алғанда, дамп файлына апаратын жол.

Мысалы:

pg_restore -h localhost -U appuser -F c -d new_db /home/user/backups/dump.tar.gz

.sql форматындағы дамптарды импорттау үшін cat командасын былай пайдалануға болады:

cat дамп_жолы | psql -h хост -U рөл_аты база_аты

Мұндағы параметрлердің мағынасы:

• дамп_жолы — атауын қоса алғанда, дамп файлына апаратын жол;
• хост — деректер қоры орналасқан сервер, localhost, IP мекенжай немесе домендік атау ретінде көрсетілуі мүмкін;
• рөл_аты — импорт орындалатын PostgreSQL пайдаланушысының аты;
• база_аты — дамп импортталатын деректер қорының аты.

Мысалы:

cat /home/user/backups/dump.sql | psql -h localhost -U appuser new_db

Деректер қоры шаблондарымен жұмыс

PostgreSQL орнатылғанда үш деректер қоры автоматты түрде құрылады: postgres, template0 және template1.

Template0 және template1 — жаңа пайдаланушылық деректер қорларын құру үшін қолданылатын деректер қоры шаблондары.

Шын мәнінде, CREATE DATABASE командасын орындағанда PostgreSQL template1 шаблонына негізделген жаңа деректер қорын құрады. Егер template1 шаблонына өзгерістер енгізсеңіз, бұл өзгерістер оның негізінде құрылған барлық жаңа деректер қорларында көрініс табады. Бұл, мысалы, template1-ге кестелер мен деректер қосу үшін ыңғайлы, өйткені олар барлық жаңа базаларда автоматты түрде қолжетімді болады.

Назар аударыңыз: шаблонға өзгерістер енгізу үшін (мысалы, кеңейтімдерді орнату) template1 базасына суперпайдаланушы (мысалы, postgres) құқықтарымен қосылу қажет.

template1 шаблонына қосылу үшін мынаны орындаңыз:

\c template1

Содан кейін кеңейтімді орнатуға болады, мысалы:

CREATE EXTENSION кеңейтім_атауы;

Мысалы, template1-ге pgcrypto кеңейтімін орнатсаңыз, барлық жаңа деректер қорларында құрылған кезде бұл кеңейтім автоматты түрде болады.

Template0 — template1-ге енгізілген өзгерістері жоқ таза деректер қорларын құру үшін қолданылатын бастапқы шаблон. Оны сондай-ақ деректер қорының кодтауын немесе локалін өзгерту үшін де пайдалануға болады, бұл әдепкі бойынша template1 пайдаланғанда мүмкін емес.

template0 негізінде жаңа деректер қорын құру үшін келесі команданы орындаңыз:

CREATE DATABASE база_аты TEMPLATE template0;

Егер деректер қорын құру кезінде нақты кодтауды немесе локальді көрсету қажет болса, template0-ді былай пайдаланыңыз:

CREATE DATABASE база_аты TEMPLATE template0 ENCODING 'SQL_ASCII';

template1-ді қайта құру

template0-ді бастапқы шаблон ретінде пайдаланып, template1 базасын қайта құруға да болады. Ол үшін алдымен template1 енді шаблон емес екеніне көз жеткізіңіз:

UPDATE pg_database SET datistemplate = false WHERE datname = 'template1';

Содан кейін template1 базасын жойыңыз:

DROP DATABASE template1;

Соңында template0 негізінде жаңа template1 базасын құрыңыз:

CREATE DATABASE template1 OWNER postgres TEMPLATE template0 is_template true;

Параметрлер және конфигурация файлдары

Конфигурация файлының орнын анықтау үшін (әдетте ол мына жолда орналасады: /etc/postgresql/нұсқа/main/postgresql.conf) мына команданы пайдалануға болады:

SHOW config_file;

PostgreSQL-дің белгілі бір параметрінің мәнін білу үшін келесі команданы пайдаланыңыз:

SHOW параметр;

Параметр туралы қосымша ақпаратты, соның ішінде ол көрсетілген файл туралы ақпаратты, сондай-ақ ең үлкен және ең кіші мәндерді алу үшін pg_settings көрінісін пайдалануға болады:

SELECT * FROM pg_settings WHERE name = 'параметр';

Мысалы:

SELECT * FROM pg_settings WHERE name = 'max_connections';

Егер ақпаратты ыңғайлырақ форматта ұсыну қажет болса, \x командасымен шығысты кесте режиміне ауыстыруға болады:

Postgres-ті басқаруға арналған басқа пайдалы командалар

Postgres командалық жолының барлық қолжетімді опцияларының тізімін қарау үшін мынаны орындаңыз:

psql --help

Linux командалық жолынан деректер қорларының тізімін қарау үшін мынаны пайдаланыңыз:

psql -l

Барлық қолжетімді psql командаларының тізімін көру үшін \? орындаңыз:

postgres=# \?

Қолжетімді SQL командаларының (сұраныстардың) тізімін қарау үшін \h пайдаланыңыз:

postgres=# \h

Нақты SQL командасы бойынша анықтама алу үшін мынаны орындаңыз:

postgres=# \h сұраныс

# Мысалы:
postgres=# \h CREATE TABLE

Ағымдағы қосылым туралы ақпарат алу үшін мынаны орындаңыз:

postgres=# \conninfo

Егер Postgres командалық жолынан қоршам (shell) командаларын орындау қажет болса, \! пайдаланыңыз:

postgres=# \! shell_командасы

# Мысалы, ағымдағы жұмыс каталогын шығару үшін:
postgres=# \! pwd

Басқа деректер қорына қосылу үшін мынаны пайдаланыңыз:

postgres=# \c база_аты