CentOS/RHEL पर PostgreSQL इंस्टॉल और कॉन्फिगर करें

इस लेख में हम Linux CentOS 7 पर PostgreSQL 11 इंस्टॉल करेंगे, बेसिक कॉन्फिगरेशन करेंगे, मुख्य कॉन्फिगरेशन फ़ाइल पैरामीटर और परफॉरमेंस ट्यूनिंग मेथड पर विचार करेंगे। PostgreSQL एक लोकप्रिय फ्री ऑब्जेक्ट-रिलेशनल डेटाबेस मैनेजमेंट सिस्टम है। हालांकि यह MySQL/MariaDB से कम लोकप्रिय है, लेकिन यह सबसे प्रोफेशनल है।

PostgreSQL की ताकत:

• SQL मानकों का पूर्ण अनुपालन;
• मल्टीवर्जन कॉन्करेंसी कंट्रोल (MVCC) के कारण उच्च प्रदर्शन;
• स्केलेबिलिटी (हाई-लोड वातावरण में व्यापक रूप से उपयोग किया जाता है);
• कई प्रोग्रामिंग भाषाओं का सपोर्ट;
• लचीला ट्रांजेक्शन और रेप्लिकेशन मैकेनिज्म;
• JSON डेटा का सपोर्ट।

CentOS/RHEL पर PostgreSQL कैसे इंस्टॉल करें?

हालांकि 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 के लेटेस्ट और पहले के संस्करण दोनों हैं। रिपॉजिटरी के बारे में जानकारी इस तरह दिखती है:

आइए yum का उपयोग करके PostrgeSQL 11 इंस्टॉल करें।

# yum install postgresql11-server -y

PostgreSQL सर्वर और आवश्यक लाइब्रेरी इंस्टॉल हो जाएंगी:

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

पैकेज इंस्टॉल होने के बाद, आपको डेटाबेस इनिशियलाइज़ करना होगा:

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

साथ ही, PostgreSQL डेमन इनेबल करें और systemctl का उपयोग करके इसे ऑटो-स्टार्ट में जोड़ें:

# systemctl enable postgresql-11
# systemctl start postgresql-11

सर्विस स्टेटस चेक करें:

# systemctl status postgresql-11

● postgresql-11.service - PostgreSQL 11 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2020-10-18 16:02:15 +06; 26s ago
Docs: https://www.postgresql.org/docs/11/static/
Process: 8714 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 8719 (postmaster)
CGroup: /system.slice/postgresql-11.service
├─8719 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
├─8721 postgres: logger
├─8723 postgres: checkpointer
├─8724 postgres: background writer
├─8725 postgres: walwriter
├─8726 postgres: autovacuum launcher
├─8727 postgres: stats collector
└─8728 postgres: logical replication launcher
Oct 18 16:02:16 host1.woshub.com systemd[1]: Starting PostgreSQL 11 database server...

यदि आप PostgreSQL को बाहर से एक्सेस करना चाहते हैं, तो डिफ़ॉल्ट CentOS firewalld में TCP पोर्ट 5432 खोलें:
# firewall-cmd --get-active-zones

public
interfaces: eth0

# firewall-cmd --zone=public --add-port=5432/tcp --permanent
# firewall-cmd --reload

या iptables के साथ:

# iptables -A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
# service iptables restart

यदि SELinux इनेबल है, तो निम्न कमांड चलाएं:

# setsebool -P httpd_can_network_connect_db 1

PostgreSQL में डेटाबेस, यूज़र बनाने और अनुमति देने के लिए PSQL का उपयोग

डिफ़ॉल्ट रूप से, जब आप PostgreSQL इंस्टॉल करते हैं तो सिस्टम में केवल एक यूज़र होता है — postgres। मैं दैनिक डेटाबेस कार्य के लिए इस अकाउंट का उपयोग करने की अनुशंसा नहीं करता। प्रत्येक डेटाबेस के लिए अलग यूज़र बनाना बेहतर है।

Postgres सर्वर से कनेक्ट करने के लिए, यह कमांड चलाएं:

# sudo -u postgres psql

psql (11.5)
Type "help" for help.

postgres=#

PostgreSQL कंसोल प्रकट होता है। फिर हम psql कंसोल से PostgreSQL मैनेजमेंट के कुछ सरल उदाहरण दिखाएंगे।

डिफ़ॉल्ट postgres यूज़र पासवर्ड बदलें:

ALTER ROLE postgres WITH PASSWORD 's3tPa$$w0rd!';

एक नया डेटाबेस और यूज़र बनाएं, और यूज़र को नए डेटाबेस पर पूर्ण पहुंच दें:

postgres=# CREATE DATABASE newdbtest;
postgres=# CREATE USER mydbuser WITH password '!123456789';
postgres=# GRANT ALL PRIVILEGES ON DATABASE newdbtest TO mydbuser;

DB से कनेक्ट करने के लिए:

postgres=# \c databasename

टेबल की सूची प्रदर्शित करने के लिए:

postgres=# \dt

डेटाबेस कनेक्शन की सूची प्रदर्शित करने के लिए:

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

डेटाबेस के सभी कनेक्शन रीसेट करने के लिए:

postgres=# select pg_terminate_backend(pid) from pg_stat_activity where datname = 'dbname'

वर्तमान सत्र के बारे में जानकारी प्राप्त करने के लिए:

postgres=# \conninfo

psql कंसोल से बाहर निकलने के लिए, यह कमांड चलाएं:

postgres=# \q

जैसा कि आपने देखा, सिंटैक्स MariaDB या MySQL के समान है।

हम ध्यान देंगे कि वेब इंटरफ़ेस से PostgreSQL डेटाबेस को अधिक सुविधाजनक तरीके से मैनेज करने के लिए, pgAdmin4 का उपयोग करने की अनुशंसा की जाती है (Python और Javascript/jQuery पर लिखा गया)। यह PhpMyAdmin के समान है जिससे कई वेब डेवलपर परिचित हैं।

मुख्य PostgreSQL कॉन्फिगरेशन पैरामीटर सेट करना

Postgresql कॉन्फिगरेशन फ़ाइलें /var/lib/pgsql/11/data में स्थित हैं:

1. postgresql.conf — postgresql कॉन्फिगरेशन फ़ाइल;
2. pg_hba.conf — एक्सेस सेटिंग्स वाली फ़ाइल। इस फ़ाइल में, आप अपने यूज़र के लिए विभिन्न प्रतिबंध सेट कर सकते हैं या डेटाबेस कनेक्शन पॉलिसी सेट कर सकते हैं;
3. pg_ident.conf — यह फ़ाइल आइडेंट प्रोटोकॉल पर क्लाइंट की पहचान करने के लिए उपयोग की जाती है।

लोकल यूज़र को ऑथोराइजेशन के बिना postgres में लॉग इन करने से रोकने के लिए, अपने pg_hba.conf में निम्न निर्दिष्ट करें:

local all all md5
host all all 127.0.0.1/32 md5

आइए postgresql.conf में सबसे महत्वपूर्ण पैरामीटर पर विचार करें:

• listen_addresses — उन IP एड्रेस को सेट करता है जिन्हें सर्वर क्लाइंट कनेक्शन के लिए स्वीकार करेगा। डिफ़ॉल्ट मान localhost है, इसका मतलब है कि केवल लोकल कनेक्शन संभव है। सभी IPv4 इंटरफ़ेस सुनने के लिए, यहां 0.0.0.0 निर्दिष्ट करें;
• max_connections — DB सर्वर पर एक साथ कनेक्शन की अधिकतम संख्या;
• temp_buffers — अस्थायी बफर का अधिकतम आकार;
• shared_buffers — डेटाबेस सर्वर द्वारा उपयोग की जाने वाली शेयर्ड मेमोरी का आकार। आमतौर पर, कुल सर्वर RAM का 25% मान सेट किया जाता है;
• effective_cache_size — एक पैरामीटर जो पोस्टग्रेस शेड्यूलर को लोकल ड्राइव पर कैशिंग के लिए उपलब्ध मेमोरी की मात्रा निर्धारित करने में सक्षम बनाता है। आमतौर पर, इसे सर्वर पर कुल RAM के 50-75% पर सेट किया जाता है;
• work_mem — डेटाबेस मैनेजमेंट सिस्टम के आंतरिक सॉर्टिंग ऑपरेशन — ORDER BY, DISTINCT और मर्जिंग द्वारा उपयोग की जाने वाली मेमोरी का आकार;
• maintenance_work_mem — आंतरिक ऑपरेशन — VACUUM, CREATE INDEX और ALTER TABLE ADD FOREIGN KEY द्वारा उपयोग की जाने वाली मेमोरी का आकार;
• fsync — यदि यह पैरामीटर इनेबल है, तो DBMS हार्ड डिस्क पर डेटा की फिजिकल राइटिंग की प्रतीक्षा करेगा। यदि fsync इनेबल है, तो आपके लिए सिस्टम या हार्डवेयर विफलता के बाद अपने डेटाबेस को रिकवर करना आसान होगा। जाहिर है, यदि यह पैरामीटर इनेबल है, तो डेटाबेस मैनेजमेंट सिस्टम का प्रदर्शन कम होगा, लेकिन स्टोरेज विश्वसनीयता अधिक होगी। यदि आप इसे डिसेबल करते हैं, तो full_page_writes को डिसेबल करना भी उचित है;
• max_stack_depth — अधिकतम स्टैक साइज़ (डिफ़ॉल्ट रूप से 2MB);
• max_fsm_pages — इस पैरामीटर का उपयोग करके, आप सर्वर पर फ्री डिस्क स्पेस मैनेज कर सकते हैं। उदाहरण के लिए, टेबल से कुछ डेटा डिलीट करने के बाद, पहले घेरा गया स्पेस फ्री नहीं होता, बल्कि फ्री स्पेस मैप पर फ्री के रूप में मार्क किया जाता है और आगे नई एंट्री के लिए उपयोग किया जाता है। यदि आप अपने सर्वर पर टेबल में अक्सर डेटा लिखते/हटाते हैं, तो इस पैरामीटर का बड़ा मान सेट करने पर प्रदर्शन बढ़ेगा;
• wal_buffers — WAL डेटा रखने के लिए उपयोग की जाने वाली शेयर्ड मेमोरी साइज़ (shared_buffers);
• wal_writer_delay — डिस्क पर WAL लिखने के लगातार पीरियड के बीच का समय;
• commit_delay — ट्रांजेक्शन को WAL बफर में लिखने और इसके डिस्क पर राइट-थ्रू के बीच की देरी;
• synchronous_commit — पैरामीटर सेट करता है कि सफल ट्रांजेक्शन का परिणाम WAL डेटा के डिस्क पर फिजिकली लिखे जाने के बाद भेजा जाएगा।

PostgreSQL डेटाबेस बैकअप और रिस्टोर

आप PostgreSQL डेटाबेस का बैकअप कई तरीकों से ले सकते हैं। आइए सबसे आसान पर विचार करें।

सबसे पहले, चेक करें कि आपके सर्वर पर कौन से डेटाबेस चल रहे हैं:

postgres=# \list

हमारे पास 4 डेटाबेस हैं, उनमें से 3 सिस्टम वाले हैं (postgres और template)।

पहले हमने mydbtest नाम से एक DB बनाया था और अब हम इसका बैकअप लेंगे।

आप pg_dump टूल का उपयोग करके अपने PostgreSQL डेटाबेस का बैकअप ले सकते हैं:

# 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

PostgreSQL परफॉरमेंस ट्यूनिंग और ऑप्टिमाइज़ेशन

MariaDB से संबंधित पिछले लेख में, हमने ट्यूनर का उपयोग करके my.cnf कॉन्फिगरेशन फ़ाइल पैरामीटर ऑप्टिमाइज़ करने का तरीका दिखाया था। PostgreSQL के लिए PgTun था, लेकिन दुर्भाग्य से यह लंबे समय से अपडेट नहीं हुआ था। साथ ही, बहुत सी ऑनलाइन सर्विस हैं जिनका उपयोग आप अपने PostgreSQL कॉन्फिगरेशन को ऑप्टिमाइज़ करने के लिए कर सकते हैं। मुझे PGTune (pgtune.leopard.in.ua) पसंद है।

इंटरफ़ेस बहुत सरल है। आपको बस अपने सर्वर पैरामीटर (प्रोफाइल, प्रोसेसर, मेमोरी, डिस्क टाइप) निर्दिष्ट करने और "Generate" पर क्लिक करने की आवश्यकता है। आपको मुख्य PostgreSQL पैरामीटर के अनुशंसित मान वाली postgresql.conf का एक संस्करण पेश किया जाएगा।

उदाहरण के लिए, 4xGB RAM और 4xvCPU वाले VPS SSD सर्वर के लिए निम्न postgresql.conf सेटिंग्स अनुशंसित हैं:

# DB Version: 11
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 4 GB
# CPUs num: 4
# Connections num: 100
# Data Storage: ssd
max_connections = 100
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

वास्तव में, लेख लिखे जाने तक यह एकमात्र संसाधन नहीं है। इसी तरह की सेवाएं भी उपलब्ध हैं:

• Cybertec PostgreSQL Configurator
• PostgreSQL Configuration Tool

इन सेवाओं का उपयोग करके, आप अपने हार्डवेयर और कार्यों के लिए बेसिक PostgreSQL पैरामीटर जल्दी से कॉन्फिगर कर सकते हैं। बाद में आप न केवल अपने सर्वर संसाधनों पर विचार कर पाएंगे, बल्कि अपने डेटाबेस के ऑपरेशन, इसके आकार, कनेक्शन की संख्या का विश्लेषण भी कर पाएंगे और इस जानकारी के आधार पर अपने PostgreSQL पैरामीटर को फाइन-ट्यून कर पाएंगे।