CentOS/RHEL पर PostgreSQL स्थापित और कॉन्फ़िगर करें

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

PostgreSQL ताकत:

• एसक्यूएल मानकों का पूर्ण अनुपालन;
• मल्टीवर्सन समवर्ती नियंत्रण (एमवीसीसी) के कारण उच्च प्रदर्शन;
• मापनीयता (उच्च लोड वातावरण में व्यापक रूप से उपयोग किया जाता है);
• एकाधिक प्रोग्रामिंग भाषाओं का समर्थन;
• लचीला लेनदेन और प्रतिकृति तंत्र;
• 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

इस रिपॉजिटरी में PosgreSQL के नवीनतम और पुराने दोनों संस्करण हैं। भंडार के बारे में जानकारी इस तरह दिखती है:

आइए yum का उपयोग करके PostrgeSQL 11 को स्थापित करें।

# yum install postgresql11-server -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

इसके अलावा, PostgreSQL डेमॉन को सक्षम करें और इसे systemctl का उपयोग करके ऑटो-स्टार्ट में जोड़ें:

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

सेवा की स्थिति जांचें:

# systemctl status postgresql-11

● postgresql-11.service - PostgreSQL 11 डेटाबेस सर्वरलोडेड:लोडेड (/usr/lib/systemd/system/postgresql-11.service; सक्षम; विक्रेता प्रीसेट:अक्षम) सक्रिय:सक्रिय (चल रहा) बुध 2020-10 से -18 16:02:15 +06; 26s पहले डॉक्स:https://www.postgresql.org/docs/11/static/Process:8714 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited) , स्थिति =0 / सफलता) मुख्य पीआईडी:8719 (पोस्टमास्टर) सीसमूह:/system.slice/postgresql-11.service├─8719 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/ डेटा/├─8721 पोस्टग्रेज़:लॉगर├─8723 पोस्टग्रेज़:चेकपॉइंटर├─8724 पोस्टग्रेज़:बैकग्राउंड राइटर├─8725 पोस्टग्रेज़:वॉलराइटर├─8726 पोस्टग्रेज़:ऑटोवैक्यूम लॉन्चर├─8727 पोस्टग्रेज़:स्टैटिस्टिक्स कलेक्टर└─8728 पोस्टग्रेज़:लॉजिकल प्रतिकृति लॉन्चरअक्टूबर 18 16:02:16 host1.woshub.com systemd[1]:PostgreSQL 11 डेटाबेस सर्वर शुरू कर रहा है...

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

पब्लिकइंटरफेस: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

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

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

पोस्टग्रेज़ सर्वर से कनेक्ट करने के लिए, यह कमांड चलाएँ:

# sudo -u postgres psql

psql (11.5)सहायता के लिए "सहायता" टाइप करें।

postgres=#

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

डिफ़ॉल्ट पोस्टग्रेज़ उपयोगकर्ता पासवर्ड बदलें:

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;

डीबी से जुड़ने के लिए:

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

जैसा कि आपने देखा है, सिंटैक्स मारियाडीबी या माईएसक्यूएल के समान है।

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

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

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

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

स्थानीय उपयोगकर्ताओं को बिना प्राधिकरण के पोस्टग्रेज में लॉग इन करने से रोकने के लिए, अपने pg_hba.conf में निम्नलिखित निर्दिष्ट करें:

स्थानीय सभी md5होस्ट सभी 127.0.0.1/32 md5

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

• listen_addresses — आईपी पते सेट करता है जिससे सर्वर क्लाइंट कनेक्शन स्वीकार करेगा। डिफ़ॉल्ट मान लोकलहोस्ट है, इसका मतलब है कि केवल एक स्थानीय कनेक्शन संभव है। सभी IPv4 इंटरफेस को सुनने के लिए, यहां 0.0.0.0 निर्दिष्ट करें;
• max_connections -एक डीबी सर्वर से एक साथ कनेक्शन की अधिकतम संख्या;
• 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 - यदि यह पैरामीटर सक्षम है, तो डीबीएमएस हार्ड डिस्क पर डेटा के भौतिक लेखन की प्रतीक्षा करेगा। यदि fsync सक्षम है, तो सिस्टम या हार्डवेयर विफलता के बाद आपके डेटाबेस को पुनर्प्राप्त करना आपके लिए आसान होगा। जाहिर है, यदि यह पैरामीटर सक्षम है, तो डेटाबेस प्रबंधन प्रणाली का प्रदर्शन कम होगा, लेकिन भंडारण की विश्वसनीयता अधिक होगी। यदि आप इसे अक्षम करते हैं, तो यह full_page_writes को भी अक्षम करने योग्य है;
• max_stack_depth — अधिकतम स्टैक आकार (डिफ़ॉल्ट रूप से 2MB);
• max_fsm_pages — इस पैरामीटर का उपयोग करके, आप सर्वर पर मुक्त डिस्क स्थान का प्रबंधन कर सकते हैं। उदाहरण के लिए, किसी तालिका से कुछ डेटा को हटाने के बाद, पहले कब्जा कर लिया गया स्थान खाली नहीं होता है, लेकिन मुक्त स्थान मानचित्र पर मुक्त के रूप में चिह्नित किया जाता है और आगे नई प्रविष्टियों के लिए उपयोग किया जाता है। यदि आप अक्सर अपने सर्वर पर तालिकाओं में डेटा लिखते/हटाते हैं, तो यदि आप इस पैरामीटर का बड़ा मान सेट करते हैं तो प्रदर्शन में वृद्धि होगी;
• wal_buffers - साझा स्मृति आकार (shared_buffers) WAL डेटा रखने के लिए उपयोग किया जाता है;
• wal_writer_delay - डिस्क पर WAL लिखने की लगातार अवधियों के बीच का समय;
• commit_delay — वाल बफर को लेन-देन लिखने और डिस्क पर इसके राइट-थ्रू के बीच की देरी;
• synchronous_commit — पैरामीटर सेट करता है कि सफल लेनदेन का परिणाम WAL डेटा को डिस्क पर भौतिक रूप से लिखे जाने के बाद भेजा जाएगा।

PostgreSQL डेटाबेस का बैकअप लें और पुनर्स्थापित करें

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

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

postgres=# \list

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

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

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

# sudo -u postgres pg_dump mydbtest > /root/dupm.sql —

इस कमांड को पोस्टग्रेज यूजर के तहत चलाएं, एक डेटाबेस और फाइल के लिए एक पथ निर्दिष्ट करें जिसे आप डेटाबेस डंप को सेव करेंगे। आपका बैकअप सिस्टम डेटाबेस को डंप कर सकता है, या वेब सर्वर का उपयोग करने के मामले में आप इसे अपने कनेक्टेड क्लाउड स्टोरेज खाते में भेज सकते हैं।

डेटाबेस में डंप को पुनर्स्थापित करने के लिए, 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 प्रदर्शन ट्यूनिंग और अनुकूलन

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

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

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

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

• साइबरटेक पोस्टग्रेएसक्यूएल विन्यासकर्ता
• PostgreSQL कॉन्फ़िगरेशन टूल

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