home

หน้าแรก
 Computer >> คอมพิวเตอร์ >  >> ระบบ >> Linux

ติดตั้งและกำหนดค่า PostgreSQL บน CentOS/RHEL

ในบทความนี้ เราจะทำการติดตั้ง PostgreSQL 11 บน Linux CentOS 7 ดำเนินการกำหนดค่าพื้นฐาน พิจารณาพารามิเตอร์ไฟล์การกำหนดค่าหลักและวิธีการปรับแต่งประสิทธิภาพ PostgreSQL เป็นระบบจัดการฐานข้อมูลเชิงวัตถุที่ได้รับความนิยมฟรี แม้ว่าจะได้รับความนิยมน้อยกว่า MySQL/MariaDB แต่ก็มีความเป็นมืออาชีพมากที่สุด

จุดแข็งของ PostgreSQL:

  • ปฏิบัติตามมาตรฐาน SQL อย่างสมบูรณ์
  • ประสิทธิภาพสูงเนื่องจากการควบคุมการทำงานพร้อมกันหลายเวอร์ชัน (MVCC)
  • ความสามารถในการปรับขนาด (ใช้กันอย่างแพร่หลายในสภาพแวดล้อมที่มีภาระงานสูง);
  • รองรับภาษาการเขียนโปรแกรมหลายภาษา
  • กลไกการทำธุรกรรมและการจำลองแบบยืดหยุ่น
  • รองรับข้อมูล JSON

จะติดตั้ง PostgreSQL บน CentOS/RHEL ได้อย่างไร

แม้ว่า PostgreSQL จะสามารถติดตั้งได้จากที่เก็บ CentOS ฐาน แต่เราจะติดตั้งที่เก็บสำหรับนักพัฒนาเนื่องจากคุณสามารถหาเวอร์ชันของแพ็คเกจปัจจุบันได้ที่นั่น

ก่อนอื่น เพิ่ม PosgreSQL repo:

# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

ที่เก็บนี้มีทั้ง PosgreSQL เวอร์ชันล่าสุดและเวอร์ชันก่อนหน้า ข้อมูลเกี่ยวกับที่เก็บมีลักษณะดังนี้:

ติดตั้งและกำหนดค่า PostgreSQL บน CentOS/RHEL

มาติดตั้ง PostrgeSQL 11 โดยใช้ yum กันเถอะ

# 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 11.5-1PGDG.rhel7.x86_64 4/4

หลังจากติดตั้งแพ็คเกจแล้ว คุณจะต้องเริ่มต้นฐานข้อมูล:

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

นอกจากนี้ ให้เปิดใช้งาน PostgreSQL daemon และเพิ่มเพื่อเริ่มต้นอัตโนมัติโดยใช้ 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 agoDocs: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:ตัวเปิดใช้การจำลองแบบลอจิคัล 18 ต.ค. 16:02:16 host1.woshub.com systemd[1]:กำลังเริ่มต้นเซิร์ฟเวอร์ฐานข้อมูล PostgreSQL 11...

หากคุณต้องการเข้าถึง PostgreSQL จากภายนอก ให้เปิดพอร์ต TCP 5432 ในไฟร์วอลล์ CentOS เริ่มต้น:
# 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 จะมีผู้ใช้เพียงรายเดียวในระบบ — postgres ฉันไม่แนะนำให้ใช้บัญชีนี้เพื่อทำงานประจำวันกับฐานข้อมูล เป็นการดีกว่าที่จะสร้างผู้ใช้แยกกันสำหรับแต่ละฐานข้อมูล

ในการเชื่อมต่อกับเซิร์ฟเวอร์ postgres ให้รันคำสั่งนี้:

# sudo -u postgres psql 

psql (11.5)พิมพ์ "help" เพื่อขอความช่วยเหลือ

postgres=#

คอนโซล PostgreSQL จะปรากฏขึ้น จากนั้นเราจะแสดงตัวอย่างง่ายๆ ของการจัดการ PostgreSQL จากคอนโซล psql

เปลี่ยนรหัสผ่านผู้ใช้ 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 บน CentOS/RHEL

การตั้งค่าพารามิเตอร์การกำหนดค่า PostgreSQL หลัก

ไฟล์การกำหนดค่า Postgresql อยู่ใน /var/lib/pgsql/11/data :

  1. postgresql.conf — ไฟล์กำหนดค่า postgresql;
  2. pg_hba.conf — ไฟล์ที่มีการตั้งค่าการเข้าถึง ในไฟล์นี้ คุณสามารถตั้งค่าข้อจำกัดต่างๆ สำหรับผู้ใช้ของคุณหรือตั้งค่านโยบายการเชื่อมต่อฐานข้อมูล
  3. pg_ident.conf — ไฟล์นี้ใช้เพื่อระบุไคลเอ็นต์ผ่านโปรโตคอลการระบุตัวตน

เพื่อป้องกันไม่ให้ผู้ใช้ในพื้นที่ลงชื่อเข้าใช้ postgres โดยไม่ได้รับอนุญาต ให้ระบุสิ่งต่อไปนี้ใน pg_hba.conf ของคุณ:

โลคัล md5host ทั้งหมดทั้งหมด 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 – พารามิเตอร์ที่เปิดใช้งานตัวจัดกำหนดการ postgres เพื่อกำหนดจำนวนหน่วยความจำที่พร้อมใช้งานสำหรับการแคชบนไดรฟ์ในเครื่อง โดยปกติจะถูกตั้งค่าเป็น 50-75% ของ 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 — ขนาดสแต็กสูงสุด (2MB โดยค่าเริ่มต้น);
  • max_fsm_pages — ด้วยการใช้พารามิเตอร์นี้ คุณสามารถจัดการพื้นที่ว่างบนดิสก์บนเซิร์ฟเวอร์ได้ ตัวอย่างเช่น หลังจากลบข้อมูลบางส่วนออกจากตาราง พื้นที่ที่ถูกครอบครองก่อนหน้านี้จะไม่ว่าง แต่จะถูกทำเครื่องหมายว่าว่างบนแผนที่พื้นที่ว่างและใช้สำหรับรายการใหม่ต่อไป หากคุณมักจะเขียน/ลบข้อมูลในตารางบนเซิร์ฟเวอร์ของคุณ ประสิทธิภาพจะเพิ่มขึ้นหากคุณตั้งค่าพารามิเตอร์นี้ให้มากขึ้น
  • wal_buffers – ขนาดหน่วยความจำที่ใช้ร่วมกัน (shared_buffers) ที่ใช้ในการเก็บข้อมูล WAL;
  • wal_writer_delay – เวลาระหว่างช่วงเวลาต่อเนื่องกันของการเขียน WAL บนดิสก์
  • commit_delay — ความล่าช้าระหว่างการเขียนธุรกรรมไปยังบัฟเฟอร์ WAL และการเขียนผ่านไปยังดิสก์
  • synchronous_commit — ชุดพารามิเตอร์ที่ผลลัพธ์ของการทำธุรกรรมที่ประสบความสำเร็จจะถูกส่งหลังจากข้อมูล WAL ถูกเขียนลงบนดิสก์จริง

สำรองและกู้คืนฐานข้อมูล PostgreSQL

คุณสามารถสำรองฐานข้อมูล PostgreSQL ได้หลายวิธี ลองพิจารณาสิ่งที่ง่ายที่สุด

ก่อนอื่น ให้ตรวจสอบว่าฐานข้อมูลใดกำลังทำงานอยู่บนเซิร์ฟเวอร์ของคุณ:

postgres=# \list

ติดตั้งและกำหนดค่า PostgreSQL บน CentOS/RHEL

เรามีฐานข้อมูล 4 ฐานข้อมูล โดย 3 ฐานข้อมูลเป็นฐานข้อมูล (postgres และเทมเพลต)

ก่อนหน้านี้เราได้สร้าง DB ด้วยชื่อ mydbtest และตอนนี้เราจะสำรองข้อมูล

คุณสามารถสำรองฐานข้อมูล PostgreSQL ของคุณโดยใช้ pg_dump เครื่องมือ:

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

รันคำสั่งนี้ภายใต้ผู้ใช้ postgres ระบุฐานข้อมูลและพาธไปยังไฟล์ที่คุณจะบันทึกดัมพ์ของฐานข้อมูล ระบบสำรองข้อมูลของคุณอาจทำการดัมพ์ฐานข้อมูล หรือคุณสามารถส่งไปยังบัญชีที่เก็บข้อมูลบนคลาวด์ที่เชื่อมต่อของคุณในกรณีที่ใช้เว็บเซิร์ฟเวอร์

ในการกู้คืนการถ่ายโอนข้อมูลไปยังฐานข้อมูล ให้ใช้ psql :

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

ติดตั้งและกำหนดค่า PostgreSQL บน CentOS/RHEL

คุณยังสามารถสร้างข้อมูลสำรองในรูปแบบดัมพ์พิเศษและบีบอัดโดยใช้ 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) .

อินเทอร์เฟซนั้นง่ายมาก คุณเพียงแค่ต้องระบุพารามิเตอร์เซิร์ฟเวอร์ของคุณ (โปรไฟล์ โปรเซสเซอร์ หน่วยความจำ ประเภทดิสก์) แล้วคลิก "สร้าง" คุณจะได้รับตัวเลือกของ postgresql.conf ที่มีค่าที่แนะนำของพารามิเตอร์ PostgreSQL หลัก

ตัวอย่างเช่น แนะนำให้ใช้การตั้งค่า postgresql.conf ต่อไปนี้สำหรับเซิร์ฟเวอร์ VPS SSD ที่มี RAM 4xGB และ 4xvCPU:

# เวอร์ชัน DB:11# ประเภท OS:linux# ประเภท DB:web# หน่วยความจำทั้งหมด (RAM):4 GB# จำนวน CPU:4# จำนวนการเชื่อมต่อ:100# การจัดเก็บข้อมูล:ssdmax_connections =100shared_buffers =1GBประสิทธิภาพ_cache_size =3GBmaintenance_work_mem =256MBcheckpoint_completion_target =0.7wal_buffers =16MBdefault_statistics_target =100random_page_cost =1.1ผล_io_concurrency =200work_mem =5242kBmin_wal_size =1GBmax_wal_size =4GBmax_worker_processes =4max_parallel_workers_per_gather =2max_parallel_parallances 

 ติดตั้งและกำหนดค่า PostgreSQL บน CentOS/RHEL 
 

 อันที่จริง มันไม่ใช่แหล่งข้อมูลเดียวในขณะที่เขียนบทความ นอกจากนี้ยังมีบริการที่คล้ายกัน:
 
     
  • ตัวกำหนดค่า Cybertec PostgreSQL
    • 
 
  • เครื่องมือกำหนดค่า PostgreSQL
    • 
 

 

 การใช้บริการเหล่านี้ทำให้คุณสามารถกำหนดค่าพารามิเตอร์ PostgreSQL พื้นฐานสำหรับฮาร์ดแวร์และงานของคุณได้อย่างรวดเร็ว หลังจากนั้น คุณจะไม่เพียงแต่พิจารณาทรัพยากรเซิร์ฟเวอร์ของคุณเท่านั้น แต่ยังวิเคราะห์การทำงานของฐานข้อมูล ขนาด จำนวนการเชื่อมต่อ และปรับแต่งพารามิเตอร์ PostgreSQL ของคุณตามข้อมูลเหล่านี้ด้วย