CentOS/RHEL-এ PostgreSQL ইনস্টল ও কনফিগারেশন

এই আর্টিকেলে আমরা Linux CentOS 7-এ PostgreSQL 11 ইনস্টল করব, বেসিক কনফিগারেশন করব, মূল কনফিগারেশন ফাইল প্যারামিটার এবং পারফরম্যান্স টিউনিং মেথড বিবেচনা করব। PostgreSQL হলো একটি জনপ্রিয় ফ্রি অবজেক্ট-রিলেশনাল ডাটাবেস ম্যানেজমেন্ট সিস্টেম। যদিও এটি MySQL/MariaDB-এর চেয়ে কম জনপ্রিয়, তবে এটি সবচেয়ে প্রফেশনাল।

PostgreSQL-এর স্ট্রেংথ:

• SQL স্ট্যান্ডার্ডের সাথে পূর্ণ συμ্মতি;
• মাল্টিভার্সন কনকারেন্সি কন্ট্রোল (MVCC) nedeniyle হাই পারফরম্যান্স;
• স্কেলেবিলিটি (হাই-লোড এনভায়রনমেন্টে ব্যাপকভাবে ব্যবহৃত);
• বহু প্রোগ্রামিং ল্যাঙ্গুয়েজ সাপোর্ট;
• রেজিলিয়েন্ট ট্রানজ্যাকশন এবং রেপ্লিকেশন মেকানিজম;
• 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

PSQL ব্যবহার করে PostgreSQL-এ ডাটাবেস, ইউজার তৈরি এবং পারমিশন দিন

ডিফল্টভাবে, 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;

ডিবিতে সংযোগ করতে:

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 — একটি ডিবি সার্ভারের সাথে همزمان কানেকশনের সর্বোচ্চ সংখ্যা;
• temp_buffers — অস্থায়ী বাফারদের সর্বোচ্চ সাইজ;
• shared_buffers — একটি ডাটাবেস সার্ভার দ্বারা ব্যবহৃত শেয়ার্ড মেমরির সাইজ। সাধারণভাবে, টোটাল সার্ভার RAM-এর ২৫% মান সেট করা হয়;
• effective_cache_size — একটি প্যারামিটার যা postgres শেডিউলারকে লোকাল ড্রাইভে ক্যাশিং-এর জন্য উপলব্ধ মেমরির পরিমাণ নির্ধারণ করতে সক্ষম করে। সাধারণত, এটি সার্ভারের টোটাল RAM-এর ৫০-৭৫% সেট করা হয়;
• 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 — সর্বোচ্চ স্ট্যাক সাইজ (ডিফল্ট ২MB);
• max_fsm_pages — এই প্যারামিটার ব্যবহার করে আপনি সার্ভারে ফ্রি ডিস্ক স্পেস ম্যানেজ করতে পারেন। উদাহরণস্বরূপ, একটি টেবিল থেকে কিছু ডেটা ডিলিট করার পর, আগে الذيকৃত স্পেস ফ্রি হয় না, বরং ফ্রি স্পেস ম্যাপে ফ্রি হিসেবে চিহ্নিত হয় এবং পরবর্তীতে নতুন এন্ট্রির জন্য ব্যবহার করা হয়। আপনি যদি আপনার সার্ভারে টেবিলগুলিতে ঘন ঘন লিখতে/মুছতে থাকেন, তবে এই প্যারামিটারের বড় মান সেট করলে পারফরম্যান্স বাড়বে;
• wal_buffers — WAL ডেটা রাখতে ব্যবহৃত শেয়ার্ড মেমোরি সাইজ (shared_buffers);
• wal_writer_delay — ডিস্কে WAL লেখার দুটি সর্বাগামী পিরিয়ডের মাঝপথের সময়;
• commit_delay — একটি ট্রানজ্যাকশন WAL বাফারে লেখার পর এবং এর ডিস্কে রাইট-থ্রু-এর মাঝপথের ডिले;
• synchronous_commit — প্যারামিটারটি নির্ধারণ করে যে সফল ট্রানজ্যাকশনের ফলাফল WAL ডেটা ফিজিক্যালি ডিস্কে লেখার পর পাঠানো হবে।

postgres=# \list

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

# sudo -u postgres psql mydbtest < /root/dupm.sql

# sudo -u postgres pg_dump -Fc mydbtest > /root/dumptest.sql

# sudo -u postgres pg_restore -d mydbtest /root/dumptest.sql

# 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