เชี่ยวชาญ PostgreSQL: คู่มือการติดตั้งและการกำหนดค่า – ส่วน 1

โพสต์นี้แนะนำ PostgreSQL และแนะนำคุณผ่านการติดตั้งและการกำหนดค่า Version 9.3 บน Linux®

บทนำ

PostgreSQL เป็น Open-source Relational Database Management System (RDBMS) ที่ Advanced ที่สุดในโลก องค์กรชั้นนำหลายแห่ง เช่น Apple, IMDB, Skype, Uber, Lockheed Martin, Verizon และอื่นๆ ใช้ PostgreSQL RDBMS นี้เริ่มต้นในปี 1986 เป็นส่วนหนึ่งของโปรเจกต์ POSTGRES ที่ University of California at Berkeley และมี Active Development มากกว่า 30 ปีบน Core Platform

PostgreSQL รันบน Operating Systems หลักทั้งหมด และเป็น ACID-Compliant มาตั้งแต่ 2001 Acronym ACID ประกอบด้วย Components ต่อไปนี้:

• Atomicity: คุณรับประกันว่า Either Entire Transaction สำเร็จ หรือ None ของมันทำได้
• Consistency: คุณรับประกันว่า All Data เป็น Consistent ทุก Data ถูกต้องตาม Defined Rules Rules รวมถึง Constraints, Cascades และ Triggers ที่ Apply ไปยัง Database
• Isolation: All Transactions เกิดขึ้นใน Isolation Transaction ไม่สามารถ Read Data จาก Transaction อื่นที่ยังไม่ Complete ได้
• Durability: หลังจากคุณ Commit Transaction มันจะคงอยู่ใน System แม้จะมี System Crash ทันทีหลัง Operation

ไม่น่าแปลกใจที่ PostgreSQL ทำหน้าที่เป็น Open-source Relational Database ที่เลือกใช้โดยคนและองค์กรมากมาย เพราะ Add-ons ที่ทรงพลังอย่าง PostGIS Geospatial Database Extender

Image Source: https://postgresql-database.blogspot.com/2013/08/postgresql-architecture.html

Support

Production Support SLA's พร้อมใช้งานจากบริษัทต่อไปนี้:

• https://www.enterprisedb.com
• https://www.2ndquadrant.com/
• https://www.revsys.com/
• https://imperoit.com/PostgreSQL_Support.htm

Supported Versions: Current (12) / 11 / 10 / 9.6 / 9.5 / 9.4 Development Versions: devel Unsupported Versions: 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2

Installation และ Configuration

ทำตามขั้นตอนต่อไปนี้เพื่อ Install และ Configure Postgres 9.3:

Install Postgres 9.3 บน Linux 7.1

รันคำสั่งต่อไปนี้เพื่อ Install Postgres 9.3 บน Red Hat® Linux 7.1:

[root@snwdbsolpeprod01 ~]# cat /etc/redhat-release Red Hat Enterprise Linux Server release 7.1 (Maipo) 

สร้างโฟลเดอร์ว่าง

สร้างโฟลเดอร์ว่างสำหรับ Database Installation

[root@snwdbsolpeprod01 mnt]# mkdir postt [root@snwdbsolpeprod01 postt]# pwd /mnt/postt 

Download RPM

รันคำสั่งต่อไปนี้เพื่อ Download Red Hat Package Manager (RPM) สำหรับ OS Version ของคุณเพื่อเริ่ม Postgres Installation:

[root@snwdbsolpeprod01 postt]# wget https://yum.postgresql.org/9.3/redhat/rhel-7-x86_64/pgdg-redhat93-9.3-2.noarch.rpm 

Install RPM

Install RPM Package โดยใช้คำสั่งต่อไปนี้:

root@snwdbsolpeprod01 postt]# rpm -ivh pgdg-redhat93-9.3-2.noarch.rpm 

ติดตั้ง Additional Packages

หลังจาก Install RPM แล้ว คุณต้อง Install Postgre Packages บางอย่างที่ Install DB Software

[root@snwdbsolpeprod01 postt]# yum install postgresql-contrib.x86_64 [root@snwdbsolpeprod01 postt]# yum install postgresql93-server.x86_64 

กำหนดค่า PGDATA Location

กำหนดว่าจะจัดเก็บข้อมูลที่ไหน หากต้องการใช้ Non-default Location สำหรับ Data ให้ Edit PostgreSQL Service sysconfig File และเปลี่ยน PGDATA Argument

vi /etc/rc.d/init.d/postgresql vi /etc/sysconfig/pgsql/postgresql 

หมายเหตุ: หาก PostgreSQL ใน sysconfig/pgsql ไม่มีอยู่ ให้สร้างมันและเพิ่มบรรทัดที่ระบุว่าคุณต้องการจัดเก็บข้อมูลที่ไหน ตามตัวอย่างต่อไปนี้:

[root@snwdbsolpeprod01 pgsql]# cd /etc/sysconfig/pgsql/ [root@snwdbsolpeprod01 pgsql]# vi postgresql [root@snwdbsolpeprod01 pgsql]# cat postgresql PDGATA=/mnt/postt 

Initialize Database

คำสั่งแรก (ต้องการเพียงครั้งเดียว) คือ Initialize Database ใน PGDATA

service <name> initdb 

ตัวอย่าง: สำหรับ Version 9.3:

service postgresql-9.3 initdb 

หรือ

/usr/pgsql-9.3/bin/postgresql93-setup initdb [root@snwdbsolpeprod01 data]# /usr/pgsql-9.3/bin/postgresql93-setup initdb Initializing database ... OK 

ตั้งค่า Postgres ให้เริ่มอัตโนมัติ

หากคุณต้องการ PostgreSQL เริ่มอัตโนมัติเมื่อ OS เริ่ม ใช้คำสั่งต่อไปนี้:

[root@snwdbsolpeprod01 data]# chkconfig postgresql-9.3 on 

หมายเหตุ: Forwarding Request ไป 'systemctl enable postgresql-9.3.service'

Start PostgreSQL Service

เพื่อ Start PostgreSQL Service ให้รันคำสั่งต่อไปนี้:

[root@snwdbsolpeprod01 data]# systemctl start postgresql-9.3.service 

กำหนดค่าฐานข้อมูล

คุณสามารถกำหนดค่าฐานข้อมูลได้ง่ายโดย Update postgresql.conf ตามตัวอย่างต่อไปนี้:

vi /var/lib/pgsql/9.3/data/postgresql.conf 

เปลี่ยนสิ่งต่อไปนี้:

listen_address = '*' port = 15000 max_connections=300 shared_buffers = 8192MB # min 128kB # (change requires restart) temp_buffers = 128MB # min 800kB max_prepared_transactions = 20 # zero disables the feature log_destination = 'csvlog' logging_collector = on log_directory = '/mnt/pgsql/logs' log_filename = 'postgresql-%a.log' #------------------------------------------------ # AUTOVACUUM PARAMETERS #------------------------------------------------ autovacuum = on # Enable autovacuum subprocess? 'on' # requires track_counts to also be on. #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and # their durations, > 0 logs only # actions running at least this number # of milliseconds. autovacuum_max_workers = 3 # max number of autovacuum subprocesses # (change requires restart) autovacuum_naptime = 10080min # time between autovacuum runs autovacuum_vacuum_threshold = 1000 # min number of row updates before # vacuum #autovacuum_analyze_threshold = 50 # min number of row updates before # analyze #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum # (change requires restart) #autovacuum_multixact_freeze_max_age = 400000000 # maximum Multixact age # before forced vacuum # (change requires restart) #autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for # autovacuum, in milliseconds; # -1 means use vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit 

กำหนดค่าการเชื่อมต่อฐานข้อมูล

เพื่อ Restrict หรือ Manage Database Connection Setting ให้รันคำสั่งต่อไปนี้:

vi /var/lib/pgsql/9.3/data/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer local all postgres md5 local all postgres ident # IPv4 local connections: # IPv6 local connections: host all all ::1/128 ident host all all 0.0.0.0/0 md5 # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres peer #host replication postgres 127.0.0.1/32 ident #host replication postgres ::1/128 ident 

กำหนดค่า Firewall

เพื่อกำหนดค่า Port บน Firewall ให้รันคำสั่งต่อไปนี้:

iptables -I INPUT -p tcp --dport 15000 --syn -j ACCEPT service iptables save service iptables restart root@snwdbsolpeprod01 postt]# service postgresql-9.3 restart 

สร้าง User หรือ Role

เพื่อสร้าง User หรือ Role ใหม่ใน Database ให้รันคำสั่งต่อไปนี้:

su – postgres psql -p 15000 postgres=# CREATE ROLE OCT1 LOGIN UNENCRYPTED PASSWORD 'test@123' INHERIT REPLICATION; 

สร้าง Tablespace

เพื่อสร้าง Tablespace ใหม่บน Database ให้รันคำสั่งต่อไปนี้:

postgres=# CREATE TABLESPACE OCT1_tablespace OWNER ilusr LOCATION '/usrdata/pgsql/data/oct'; 

สร้าง Database

เพื่อสร้าง Database ใหม่ ให้รันคำสั่งต่อไปนี้:

postgres=# CREATE DATABASE OCT1 WITH ENCODING='UTF8' OWNER=test LC_CTYPE='en_US.UTF-8' CONNECTION LIMIT=-1 TABLESPACE=OCT1_tablespace; 

Basic Commands

Basic Administration Commands บางอย่างรวมถึง:

Stop และ Start PostgreSQL

/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data stop /opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data start /opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data restart /opt/PostgreSQL/9.3/bin/pg_ctl -D opt/PostgreSQL/9.4/data –m smart stop #wait for complete the transactions /opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m fast stop #Immediate stop /opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m immediate stop #Abort the DB /opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m smart restart /opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m fast restart /opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m immediate restart 

ตรวจสอบ PostgreSQL Version

postgres=# select version(); 

ระบุ Activity บน Database Level เฉพาะ

select pid,backend_xid,backend_xmin,query from pg_stat_activity ; 

Analyze Table Status

select relname,last_autoanalyze,last_analyze,n_mod_since_analyze from pg_stat_all_tables; 

หา Physical Path ของ Table

postgres=# SELECT pg_relation_filepath('testpitr1'); pg_relation_filepath ---------------------- base/13003/16399 [postgres@postgres221 data]$ ls -l /mnt/postt/data/base/13003/16399 -rw------- 1 postgres postgres 256024576 Feb 21 06:36 /mnt/postt/data/base/13003/16399 

ได้ Schema Names ภายใน Instance หรือ Cluster

select schema_name from information_schema.schemata; select nspname from pg_catalog.pg_namespace; post_gre=# \dn List of schemas Name | Owner --------------+---------- kailash_test | postgres public | postgres (2 rows) 

ได้ DB Names ภายใน Instance หรือ Cluster

template1=# select datname from pg_database; template1 template0 post_gre template1=# \l post_gre | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres + | | | | | postgres=CTc/postgres + | | | | | kailash_s=CTc/postgres template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | template1=# select usename from pg_catalog.pg_user; kailash kailash_s postgres template1=# \du kailash | | {} kailash_s | Superuser, Create role, Create DB | {} postgres | Superuser, Create role, Create DB, Replication | {} 

สรุป

Tag Line ของ PostgreSQL อ้างว่าเป็น "The world's most advanced open source database." อย่างไรก็ตาม PostgreSQL ไม่ได้เป็นแค่ Relational มันเป็น Object Relational ความแตกต่างนี้ให้มัน Advantage บางอย่างเหนือ Open Source SQL Databases อื่นๆ เช่น MySQL, MariaDB และ Firebird PostgreSQL เป็นตัวเลือกที่ชัดเจนบน AWS Cloud สำหรับการ Migrate In-house RDBMS ไป Open Source RDBMS บน CLOUD

ใน Part 2 ของบล็อกนี้ ผมจะครอบคลุม Postgre Backup Restore และ Recovery

ใช้แท็บ Feedback เพื่อแสดงความคิดเห็นหรือถามคำถาม คุณยังสามารถ Chat Now เพื่อเริ่มการสนทนาได้

เรียนรู้เพิ่มเติมเกี่ยวกับ Database Services ของเรา