Copyright © 2009 by Peekanung 1
PostgreSQL 8.3 Manual
Copyright © 2009 by Peekanung 2
1. เก่ียวกับ PostgreSQL 2. Installation PostgreSQL บน Ubuntu
3.
2.1 เก่ียวกับ pg_hba.conf 2.2 เก่ียวกับ postgresql.conf 2.3 Install pgAdmin GUI Application การใช้งาน PostgreSQL 3.1 postgreSQL-Client Application (psql) 3.2 การจัดการ Database 3.3 การจัดการ Table และ View 3.3.1 Table 3.3.2 View 3.4 การจัดการ Roles 3.5 Data type 3.6 การ Import/Export data 3.7 postgreSQL Functions 3.8 Partitioning Table 3.9 Materialized View 3.9.1 Snapshot 3.9.2 Eager 3.9.3 Lazy 3.9.4 Very Lazy 3.10 การสำารองข้อมูล 3.10.1 Backup 3.10.2 Restore 3.11 Database Maintenance
Page 3 4 6 7 8 10 10 10 12 12 14 15 19 20 21 22 24 24 27 32 32 35 35 36 37
Copyright © 2009 by Peekanung 3
1. เก่ย ี วกับ PostgreSQL PostgreSQL เปน object-relational database management system (ORDBMS) พัฒนามาจาก POSTGRES Version 4.2 โดย University of California at Berkeley ภาควิชา Computer Science PostgreSQL เปนโปรแกรม Open source สามารถนำ าไปใช้งานได้โดยไมมีคาใช้จาย และสนั บสนุนมาตรฐาน SQL:2003 ซ่ึงมีความสามารถตางๆ เชน complex query foreign keys triggers views transactional integrity multiversion concurrency control
นอกจากนี้ PostgreSQL ยังให้ผู้ใช้งาน(User)กำาหนดการใช้งานเองได้ เชน Data types Functions Operators Aggregate functions Index methods Procedural languages
รายละเอียดทั่วไปเกี่ยวกับ PostgreSQL ขนาดฐานข้อมูล : ไมจำากัด ขนาดตารางข้อมูล(table) : ไมเกิน 32 TB ขนาดของข้อมูลแตละรายการ(row) : ไมเกิน 400 GB ขนาดของข้อมูลแตละ field : 1GB จำานวนรายการ(rows) ในตารางข้อมูล(table) : ไมจำากัด จำานวน columns ในตารางข้อมูล(table): 250-1600 columns ขึ้นอยูกับชนิ ด column จำานวน indexes ในตารางข้อมูล(table) : ไมจำากัด การตัง้ช่ ือ ขึ้นต้นด้วยตัวอักษรหรือ underscore(_) ความยาวไมเกิน 31 ตัวอักษร ไมซ้ำากับ reserve word หรือถ้าต้องการให้ใช้ Quote(“”) การใช้ Quote(“”) จะทำาให้ช่ือท่ีอยูใน Quote(“”)เปนแบบ Case Sensitive ช่ ือท่ีอยูในประเภทเดียวกันต้องไมซ้ำากัน เชน ■ ช่ ือ Database ท่ีอยูใน Cluster เดียวกันต้องไมซ้ำากัน ■ ช่ ือ Table ท่ีอยูใน Database เดียวกันต้องไมซ้ำากัน ■ ช่ ือ Column ท่ีอยูใน Table เดียวกันต้องไมซ้ำากัน ■ ช่ ือ Index ท่ีอยูใน Database เดียวกันต้องไมซ้ำากัน รูปด้านลาง เป็ นโครงสร้างอยางงายของ PostgreSQL
Copyright © 2009 by Peekanung 4
2. Install PostgreSQL 8.3 บน Ubuntu ในการติดตัง้ระบบฐานข้อมูล PostgreSQL นั ้นผู้ใช้งานทำาได้โดย Install Package Postgresql ด้วยคำาสัง่ตอไปนี้ $ sudo apt-get install postgresql หลังจากท่ี Os ทำาการ install Package เสร็จเรียบร้อยแล้ว Home ของ PostgreSQL จะถูกติดตัง้อยูท่ี /var/lib/postgres/ และจะมีกลุมของผู้ใช้งาน และ user ท่ีช่ือวา postgres ซ่ ึงใช้ในการเรียกใช้งานระบบฐานข้อมูล PostgreSQL แตเพ่ ือความปลอดภัยในการใช้งานระบบฐานข้อมูล เราไมควรจะอนุญาติให้ใช้ username : postgres เข้าใช้งาน ระบบจึงควรสร้าง user ใหมด้วยคำสัง่ (ตัวอยาง : user01) $ createuser -P user01 //ใช้ -P เพ่ ือ assign password ให้ user Enter password for new role: Enter it again: Shall the new role be a superuser? (y/n) Shall the new role be allowed to create databases? (y/n) Shall the new role be allowed to create more new roles? (y/n) หลังจากท่ีผู้ใช้งานสร้าง user ได้แล้ว ผู้ใช้งานยังสามารถสร้าง database ด้วยคำาสัง่ (ตัวอยาง : mydb) $ createdb mydb สามารถเข้าใช้งาน Database ท่ีถูกสร้างไว้ด้วยคำาสัง่ตอไปนี้ $ psql -d mydb //ใช้ -d เพ่ ือระบุ ช่ ือ database หรือ $ psql -d mydb -U user01 //ใช้ -U เพ่ ือระบุ user ท่ีใช้ในการ connect (ถ้าไม่ระบุจะหมายถึง user : postgres เสมอ )
หมายเหตุ 1. สามารถเข้าใช้งาน database โดยใช้คำาสัง่ psql ตามด้วยช่ ือ Database และช่ ือ User ตามลำาดับ โดยไมต้องระบุ option เน่ ืองจาก default format เป็ น psql
เชน $ psql mydb user01
Copyright © 2009 by Peekanung 5 2. การเข้าใช้งานทุกครัง้ ต้องระบุข่ือ Database เสมอ ( ช่ ือ User จะกำาหนดหรือไมก็ได้) เชน $ psql mydb // สามารถ connect ได้ โดย Database = mydb , User = postgres (default user) $ psql -d mydb // สามารถ connect ได้ โดย Database = mydb , User = postgres (default user) $ psql user01 // ไม่สามารถ connect ได้ เน่ อ ื งจากไม่มี database ช่ ือ user01 3. สามารถเข้าใช้งานโดยการระบุเฉพาะช่ ือ User โดยใช้ option -U ได้กรณี เดียวคือ ช่ ือ Database และช่ ือ ๊User เป็ นช่ ือ เดียวกัน เชน 3.1 ช่ ือ Database และช่ ือ ๊User เป็ นช่ ือเดียวกัน คือ xyz $ createuser -P xyz // สร้าง user ช่ ือ xyz โดยกำาหนดให้มี password $ createdb xyz // สร้าง database ช่ ือ xyz $ psql xyz xyz // สามารถ connect ได้ โดย Database = xyz , User = xyz $ psql xyz // สามารถ connect ได้ โดย Database = xyz , User = postgres (default user) $ psql -d xyz // สามารถ connect ได้ โดย Database = xyz , User = postgres (default user) $ psql -U xyz // สามารถ connect ได้ โดย Database = xyz , User = xyz 3.2 ช่ ือ User ไมตรงกับช่ ือ Database $ createuser -P abc // สร้าง user ช่ ือ abc โดยกำาหนดให้มี password $ psql abc error “psql: FATAL: database "abc" does not exist” เน่ ืองจากไม่มี Database ช่ ือ abc $ psql -d abc error “psql: FATAL: database "abc" does not exist” เน่ ืองจากไม่มี Database ช่ ือ abc $ psql -U abc Password for user abc: // ใส่ password ของ user abc error “psql: FATAL: database "abc" does not exist” เน่ อ ื งจากไม่มี Database ช่ ือ abc สามารถ connect โดยไมระบุ option ได้ แตต้องเรียงตามลำาดับดังนี้ : host (ถ้ามี), ช่ ือ database, ช่ ือ user เชน $ psql mydb abc // สามารถ connect ได้ โดย Database = mydb , User = abc โดยเราสามารถตรวจสอบรายช่ ือ Database ตางๆ ได้โดยใช้คำาสัง่ $ psql -l
แก้ไข Configuration file 1. แก้ไขไฟล์ postgresql.conf เพ่ ือให้สามารถ remote เข้ามาใช้งาน database ได้ $ sudo vi /etc/postgresql/8.3/main/postgresql.conf แก้ไขในสวนของ ‘Connections and Authentication’ section… ดังนี listen_addresses = '*' // อนุญาตให้เคร่ ือง Client ท่ีใช้ IP อ่ ืน นอกจาก localhost สามารถเช่ ือมต่อกับ database ได้ และ password_encryption = on // กำาหนดให้มีการเข้ารหัส password ของทุก User ใน Database
Copyright © 2009 by Peekanung 6 Note: ไฟล์ postgresql.conf เป็ นไฟล์ท่ีใช้เก็บ Configuation ตางๆ ของ PostgreSQL ดังนี้ (เฉพาะ parameter ท่ีเปิ ดใช้อยูเทานั ้น) # FILE LOCATIONS data_directory = '/var/lib/postgresql/8.3/main' hba_file = '/etc/postgresql/8.3/main/pg_hba.conf' ident_file = '/etc/postgresql/8.3/main/pg_ident.conf' external_pid_file = '/var/run/postgresql/8.3-main.pid'
// Directory ท่ใี ช้เก็บ data และ configuration file ของ database // Configuration file เก่ียวกับสิทธิก ์ ารเข้าถึง database // Configuration file ท่ีใช้ map user กับ identifier user เพือใช้งานระบบ // path file ท่ีใช้บันทึก process id
# CONNECTIONS AND AUTHENTICATION listen_addresses = '*' // อนุญาตให้เคร่ ือง Client ท่ีใช้ IP อ่ ืน นอกจาก localhost สามารถเช่ ือมตอกับ database ได้ port = 5432 // หมายเลข port ของ database server max_connections = 100 // กำาหนดจำานวนสูงสุดของการเช่ ือมตอกับ database ในเวลาเดียวกัน (default = 32) unix_socket_directory = '/var/run/postgresql' // Directory สำาหรับการเช่ ือมตอจาก Client Application ssl = true // เปิ ดการใช้งาน SSL (Secure Sockets Layer) password_encryption = on // กำาหนดให้มีการเข้ารหัส password ของทุก User ใน Database # RESOURCE USAGE (except WAL) shared_buffers = 24MB // กำาหนดขนาดของ share buffer สำาหรับการทำางานของ database (คาน้ อยสุดคือ 128 kB หรือ max_connection*16 kB) max_fsm_pages = 153600 // กำาหนดขนาดท่ีใช้เก็บ free space เพ่ ือนำ ากลับมาใช้ใหม (default = 10000 , คาน้ อยสุด คือ จำานวนrealtion*16) # ERROR REPORTING AND LOGGING log_line_prefix = '%t ' // รูปแบบของช่ ือ log file ท่ข ี ึ้นต้นด้วยเวลาท่ไี มรวม millisecond # CLIENT CONNECTION DEFAULTS datestyle = 'iso, mdy' //รูปแบบของวันเวลาท่ีใช้ในระบบ lc_messages = 'en_US.UTF-8' // รูปแบบของตัวอักษรบน error message lc_monetary = 'en_US.UTF-8' // รูปแบบของตัวอักษรท่ีใช้กับข้อมูลจำานวนเงิน lc_numeric = 'en_US.UTF-8' // รูปแบบของตัวอักษรท่ใี ช้กับข้อมูลประเภทตัวเลข lc_time = 'en_US.UTF-8' //รูปแบบของตัวอักษรของเวลา default_text_search_config = 'pg_catalog.english' //มาตราฐานรูปแบบของการเรียงข้อมูลตามตัวอักษร
2. แก้ไขไฟล์ /etc/postgresql/8.3/main/pg_hba.conf เป็ นการกำาหนดผ้้ที่มีสิทธิเ์ข้าถึง database
โดยแก้ไขให้มีคาดังนี้
# Database administrative login by UNIX sockets # local database user auth-method local all postgres ident sameuser local all all md5 # IPv4 local connections: # host database user CIDR-address auth-method host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5
อธิบาย local all postgres ident sameuser // กำานดให้ user : postgress บนเคร่ ือง local เข้าใช้งานได้ทุก database (ไม่ต้องใส่ password ของ database) local all all md5 // กำาหนดให้ทุก user บนเคร่ ือง local เข้าใช้งานได้ทุก database โดยต้องใส่ password ของ database host all all 127.0.0.1/32 md5 host all all ::1/128 md5 // กำาหนดให้ทุก user ในกลุ่ม IP ท่ีกำาหนด สามารถconnect ผ่าน TCP/IP โดยต้องใส่ password ของ database หลังจากนั ้นให้ restart services ใหม $ service postgresql-8.3 restart [ start | stop | restart ]
2.1 เก่ียวกับ pg_hba.conf
การตรวจสอบสิทธิของเคร่ ืองลูกขายในการเข้าใช้งาน postgreSQL server จะถูกควบคุมโดยข้อความท่ีระบุในไฟล์ pg_hba.conf ท่ีอยูใน /etc/postgresql/8.3/main/pg_hba.conf. (HBA ยอมาจาก host-based authentication) ซ่ึงไฟล์ pg_hba.conf นี้จะถูกติดตัง้เม่ ือทำาการสร้าง database รูปแบบทัว่ไปของไฟล์ pg_hba.conf คือกลุมของข้อความแยกตามบรรทัด หน่ ึงชุดของข้อความจะต้องจบลงในหน่งึ บรรทัด บรรทัดวางจะถูกข้ามไปโดยไมมีการประมวลผล และข้อความท่ีอยูหลังเคร่ ืองหมาย # จะเปนคำาอธิบายและไมถูกประมวล ผล เชนกัน แตละบรรทัดประกอบหลายข้อความท่ีถูกแยกจากกันด้วยชองวาง ในแตละข้อความสามารถมีชองวางได้ แตจะต้องค รอม ด้วยเคร่ ืองหมาย " " แตละบรรทัดจะระบุ ชนิ ดของการเช่ ือมตอชวงหมายเลข ip ของเคร่ ืองลูกขาย ช่ ือของฐานข้อมูลท่ีจะเข้าใช้งาน ช่ ือ user และวิธีการตรวจสอบสิทธิ กรณี มีหลายบรรทัดท่ีเข้าเง่ ือนไข บรรทัดแรกท่ีสอดคล้องตามเง่ ือนไขท่ีเช่ ือมตอเข้ามาจะถูกเลือกมาใช้ เพ่ ือการตรวจสอบสิทธิ ถ้าสิทธิไมถูกต้องการเช่ ือมตอนั ้นจะถูกยกเลิก โดยไมสนใจบรรทัดอ่ ืนท่ีกำาหนดสิทธิแตกตางกัน รูปแบบของแตละบรรทัดสามารเขียนได้หลายรูปแบบตามตัวอยาง
Copyright © 2009 by Peekanung 7 local DATABASE host DATABASE hostssl DATABASE hostnossl DATABASE
USER USER USER USER
METHOD CIDR-ADDRESS CIDR-ADDRESS CIDR-ADDRESS
[OPTION] METHOD METHOD METHOD
[OPTION] [OPTION] [OPTION]
แตละข้อความ สามารถอธิบายได้ดังนี้ local เปนการเช่ ือมตอในรูปแบบท่ีเคร่ ืองลูกขาย (client) และฐานข้อมูลอยูในเคร่ ืองเดียวกัน host เปนเช่ ือมตอของเคร่ ืองลูกขายโดยใช้ TCP/IP ซ่ึงข้อมูลเปนแบบเข้ารหัสหรือไมก็ได้ hostssl เปนเช่ ือมตอของเคร่ ืองลูกขายโดยใช้ TCP/IP ซ่ ึงข้อมูลเปนแบบเข้ารหัสเทานั ้น hostnossl เปนเช่ ือมตอของเคร่ ืองลูกขายโดยใช้ TCP/IP ซ่ ึงข้อมูลเปนแบบไมเข้ารหัส DATABASE กำาหนดวาจะอนุญาตให้เข้าใช้งานฐานข้อมูลไหนโดยระบุช่ือฐานข้อมูล หรือสามารถใช้คาตางๆเหลานี้ • all เพ่ ือระบุทุกฐานข้อมูล • sameuser เพ่ ือกำาหนดวาฐานข้อมูลท่ีเข้าใช้งานมีช่ือตรงกับ user ท่ีเรียกเข้าใช้งาน • samegroup กำาหนดวา user ท่ีขอเข้าใช้งานต้องเปนสมาชิกของกลุมท่ีมช ี ่ ือตรงกับฐานข้อมูลท่ีขอเข้าใช้งาน • ถ้ากำาหนดหลายฐานข้อมูลให้คัน ่ ด้วยเคร่ ืองหมาย , (comma) • ในกรณี ระบุเปนช่ ือไฟล์ให้นำาหน้ าช่ ือไฟล์ด้วยเคร่ ืองหมาย @ และไฟล์นี้จะต้องอยูไดเรคทอรีเดียวกับไฟล์ pg_hba.conf USER ระบุช่ือของ user ท่ีจะอนุญาตให้เเข้าใช้งาน database สามารถใช้คาเหลานี้ แทนได้ • all เพ่ ือระบุทุก user สามารถเข้าใช้งาน database ได้ • ถ้ากำาหนดหลาย user ให้คัน ่ แตละ user ด้วยเคร่ ืองหมาย comma ( , ) • ถ้าระบุเปนช่ ือของกลุม user ให้นำาหน้ าช่ ือกลุมด้วยเครืองหมาย + CIDR-ADDRESS (Classless Inter-Domain Routing ) ระบุหมายเลข ip ของเคร่ ือง client ท่ีจะอนุญาตให้เข้าใช้งาน (ไม สามารถระบุเปน domain หรือ ช่ ือเคร่ ือง) METHOD ระบุวิธีการตรวจสอบตัวตน ของ user ท่ีเช่ ือมตอเข้ามา สามารถใช้ตัวเลือกเหลานี้ Trust ไมมีเง่ ือนไขในการในการตรวจสอบ ไมต้องใส password Reject ปฏิเสธทุกการเช่ ือมตอโดยไมมีเง่ ือนไข md5 เคร่ ือง client ท่ีเข้าขอเข้าใช้งานต้องใส password ของ database ในรูปแบบท่ีเข้ารหัสแบบ MD5 crypt คล้ายๆแบบ MD5 ( สำาหรับ version 7.2 หรือ ตำ่ากวา ) password คล้าย MD5 แต password จะถูกสงในรูปแบบท่ีไมมีการเข้ารหัส krb5 (Kerberos V5) เป็ นการตรวจสอบตัวตนของ user สำาหรับการเช่ ือมตอแบบ TCP/IP เทานั ้น ident รับคา user name ของเคร่ ือง client จากระบบปฏิบัติการ โดยเคร่ ือง client จะต้องมี ident server ทำางานอยู กรณี Client อยูในเคร่ ืองเดียวกับ server ระบบปฏิบัติการจะต้อง Unix-domain socket credentials pam ตรวจสอบตัวตนของ user โดยใช้บริการ Pluggable Authentication Modules (PAM) ของบางระบบปฏิบัติ การ เชน Unix
OPTION ใส option ขึ้นกับวาเลือกการพิสูจน์ตัวตน (authentication) แบบไหน เชนถ้าใช้ method แบบ ident หรือ pam ท่ี option นี้จะต้องระบุช่ือผู้ให้บริการ
2.2 เก่ียวกับ postgresql.conf ไฟล์ postgresq.conf เป็ นไฟล์ท่ีใช้เก็บ configuration ทัง้หมดของ postgreSQL ซ่ึีง parameter และคาตางๆ ท่ต ี ้อง เปิ ดใช้งาน มีดังนี้ listen_addresses = ‘*’ : เพ่ ืออนุญาตให้เคร่ ือง Client ท่ีใช้หมายเลข IP (IP address) อ่ ืนๆ ท่ีไมใช localhost สามารถติดตอเข้าใช้งานฐานข้อมูล data_directory : path directory ท่ีใช้เก็บโปรเเกรมระบบฐานข้อมูลและ configure file ของระบบ hba_file : path file ท่ีใช้ในการควบคุมการเข้าใช้งานระบบฐานข้อมูลของ host ident_file : path file ท่ีใช้ในการ map user กับ identifier user เพือใช้งานระบบ external_pid_file : '/var/run/postgresql/8.3-main.pid' > path file ท่ีใช้บันทึก process id port = 5432 : port ท่ีถูกเปิ ดใช้งานระบบฐานข้อมูล max_connections = 100 : requires สูงสุดท่ีสามารถให้บริการ unix_socket_directory = '/var/run/postgresql' : set path ในการเรียกใช้งานระบบ ssl = true : ระบุการสงข้อมูลในรูปแบบของการเข้ารหัส password_encryption = on : ระบุรูปแบบการสงข้อมูลวาจะมีการเข้ารหัสตัวข้อมูลด้วยหรือไม shared_buffers = 24MB : shared buffers มีขนาดให้ใช้งานเทากับ 24MB
Copyright © 2009 by Peekanung 8
max_fsm_pages = 153600 : พ้ืนท่ีวางสูงสุดของ disk ใน shared free-space map log_line_prefix = '%t ' : รูปแบบของช่ ือ log file ท่ีขึ้นต้นด้วยเวลาท่ีไมรวม millisecond datestyle = 'iso, mdy' : รูปแบบของวันเวลาท่ีใช้ในระบบ lc_messages = 'en_US.UTF-8' : รูปแบบของตัวอักษรบน error message lc_monetary = 'en_US.UTF-8' : รูปแบบของตัวอักษรท่ีใช้กับข้อมูลจำานวนเงิน lc_numeric = 'en_US.UTF-8' : รูปแบบของตัวอักษรท่ีใช้กับข้อมูลประเภทตัวเลข lc_time = 'en_US.UTF-8' : รูปแบบของตัวอักษรของเวลา default_text_search_config = 'pg_catalog.english' : มาตราฐานรูปแบบของการเรียงข้อมูลตามตัวอักษร
2.3 Install pgAdmin GUI Application ในการ Install pgAdmin ทำาได้โดยใช้คำาสัง่ตอไปนี้ $ sudo apt-get install pgadmin3
แล้ว Run scripts ตอไปนี้ เพ่ ือให้สามารถใช้งาน pgAdmin ได้ $ sudo su postgres -c psql < /usr/share/postgresql/8.3/contrib/adminpack.sql $ cd /usr/share/postgresql/8.3/contrib $ su postgres -c psql postgres=# adminpack.sql
หรือ
การเข้าใช้งาน ไปท่ี Applications > Programming > pgAdmin III
คลิกป ุม 'Add a connection to a server' เพ่ ือสร้างการเช่ ือมตอ
Copyright © 2009 by Peekanung 9 ระบุคาตาง ๆ ของ Database เพ่ ือใช้ในการเช่ ือมตอ แล้วกด OK
จะปรากฎหน้ าตาของ pgAdmin ซ่ ึงแบงหน้ าตางออกเป็ น 2 สวน ดังนี้ 1. Object browser ของแตละ Connection ( ด้านซ้าย ) 2. Properties, Statistics, Dependencies และ Dependcents ของ Object ( ด้านขวา )
Copyright © 2009 by Peekanung 10
3. การใช้งาน PostgreSQL 3.1 PostgreSQL-Client Application (psql) ได้จาก
โปรแกรม PostgreSQL-Client คือ psql จะใช้โปรแกรมนี้ ได้ อยูใน Text Mode หรือ Command line psql เปนโปรแกรมท่ีใช้ติดตอกับ Database Server แบบ command line โดยสามารถเรียกดู option ตางๆของ psql $ psql --help Syntax : psql [options] [dbname [username]]
Options: -d <ช่ ือฐานข้อมูล> -f <ช่ ือไฟล์> -h -l -p -q -U -V -W
กำาหนด database name ท่ีต้องการติดตอ (default: postgres) Execute queries จากไฟล์ กำาหนด database server host (default: localhost) แสดงรายการฐานข้อมูลท่ีมีอยูทัง้หมด กำาหนด database server port (default: 5432) ทำางานโดยไมแสดงข้อความจากระบบ แสดงเฉพาะผลลัพธ์ท่ีได้จาก query กำาหนด database username (default: postgres) แสดง version information Prompt เพ่ ือใส password (ปกติจะต้อง ใส password อยูแล้ว)
การจัดการเกี่ยวกับ Service ของ PostgreSQL (ต้อง Login ด้วย root) ตรวจสอบสถานะของ service $ service postgresql-8.3 status หรือ /etc/init.d/ postgresql-8.3 status Start service $ service postgresql-8.3 start หรือ /etc/init.d/ postgresql-8.3 start Stop service $ service postgresql-8.3 stop หรือ /etc/init.d/ postgresql-8.3 stop
3.2 การจัดการ Database
• เม่ ือใช้โปรแกรม psql คำาสัง่ท่ใี ช้คือภาษา SQL ตามมาตรฐาน SQL92/SQL99 • คำาสัง่ท่ีเปนภาษา SQL ให้ปดคำาสัง่ด้วย ; สัง่ทำางานด้วย • คำาสัง่ของ psql ท่ีขึ้นต้นด้วย \ ไมต้องปดคำาสัง่ด้วย ; สัง่ทำางานด้วย
(1) สร้าง database ใหม $ createdb [OPTION]... [DBNAME] [DESCRIPTION] Options: -D, --tablespace=TABLESPACE -E, --encoding=ENCODING -O, --owner=OWNER -T, --template=TEMPLATE -e, --echo -q, --quiet --help --version Connection options: -h, --host=HOSTNAME -p, --port=PORT -U, --username=USERNAME -W, --password
กำาหนด tablespace ให้กับ database กำาหนด encoding ให้กับ database กำาหนด user ท่ีจะเป็ นเจ้าของ database สร้าง database ตาม template ท่ีกำาหนด กำาหนดให้แสดงคำาสัง่ท่จี ะถูกสงไป server กำาหนดให้ไมต้องแสดงผลจากการสัง่ create แสดงตัวชวยเหลือเก่ียวกับการสร้าง database แสดงเวอร์ชัน ่ database server host หรือ socket directory database server port กำาหนด user name ท่ีใช้ connect กำาหนดให้ระบุ password ทันที
Copyright © 2009 by Peekanung 11 ตัวอยาง $ createdb test with encoding='win874' // สร้าง database ช่ ือ 'test' โดยกำาหนด encoding เป็ น 'win874' $ createdb test –U postgres –E win874 –O user1 // สร้าง database ช่ ือ 'test' โดยกำาหนด username ท่ีใช้ connect เป็ น 'postgres' กำาหนด encoding เป็ น 'win874' และให้ user1 เป็ นเจ้าของ database หรือใช้ SQL Command SQL > CREATE DATABASE database-name [ WITH [ OWNER [=] {username|DEFAULT}] [ TEMPLATE [=] {template-name|DEFAULT}] [ ENCODING [=] {encoding|DEFAULT}] [ TABLESPACE [=] tablespace ]] ตัวอยาง SQL > CREATE DATABASE test ENCODING 'win874'; // สร้าง database ช่ ือ 'test' โดยกำาหนด encoding เป็ น 'win874' SQL > CREATE DATABASE test ENCODING 'win874' OWNER user1; // สร้าง database ช่ ือ 'test' โกำาหนด encoding เป็ น 'win874' และให้ user1 เป็ นเจ้าของ database หมายเหตุ ในการกำาหนด OWNER โดยปกติ(default) จะกำาหนดให้ผู้ท่ีสร้างเปน OWNER แตถ้าเปน superuser เชน postgres จะสามารถกำาหนด OWNER ให้ user อ่ ืนได้ (2) ลบ database $ dropdb [OPTION]... [DBNAME] [DESCRIPTION] Options: -e, --echo -i ,--interactive -h, --host=HOSTNAME -p, --port=PORT -U, --username=USERNAME -W, --password ตัวอยาง $ dropdb demo $ dropdb -i demo –U postgres
กำาหนดให้แสดงคำาสัง่ท่จี ะถูกสงไป server กำาหนดให้ตรวจสอบความถูกต้องของคำาสัง่กอนทำางาน database server host หรือ socket directory database server port กำาหนด user name ท่ีใช้ drop database กำาหนดให้ระบุ password ทันที // ลบ database ช่ ือ 'demo' // ลบ database ข่ ือ 'domo' ด้วย user 'postgres'
หรือใช้ SQL Command SQL > DROP DATABASE ตัวอยาง
SQL > DROP DATABASE test; / ลบ database ช่ ือ 'test' *หมายเหตุ ฐานข้อมูลท่ีเปดใช้อยู ไมสามารถลบได้ ต้องปดกอน โดยใช้คำาสัง่ \q หรือ เปิ ดฐานข้อมูลอ่ ืน เชน \c test
(3) ด้ database $ psql -l ตัวอยาง
$ psql -l List of databases Name | Owner | Encoding ---------------+------------+---------db01 | user01 | UTF8 mydb | postgres | UTF8 postgres | postgres | UTF8
// คำาสัง่ psql -l จะแสดง Name,Owner,Encoding ของ database
Copyright © 2009 by Peekanung 12 template0 | postgres | UTF8 template1 | postgres | UTF8
หรือใช้ SQL Command SQL > select * from pg_database; SQL > \l ตัวอยาง
SQL > SELECT * FROM pg_database ;
หรือ
// pg_database จะแสดงรายละเอียดทัง้หมดของ database
datname|datdba|encoding|datistemplate|datallowconn|datconnlimit|datlastsysoid|datfrozenxid| dattablespace | datconfig | datacl ----------+---------+-----------+-----------------+-----------------+---------------+-----------------+---------------+-------------------+-------------+-----------db01 |16535 | 6 |f |t | -1 | 11510 | 379 | 16538| |
// คำาสัง่ \l จะแสดงผลเหมือนคำาสัง่ psql -l
SQL > \l (4) แก้ไข database
SQL Command SQL > ALTER DATABASE name [ [ WITH ] option [ ... ] ] Options:
ตัวอยาง
CONNECTION LIMIT connlimit จำากัดการเข้าถึง database โดยถ้ากำาหนดเป็ น -1 จะหมายถึง unlimit RENAME TO newname เปล่ียนช่ ือ database OWNER TO new_owner เปล่ียนเจ้าของ database SET configuration_parameter {TO|=}{value|DEFAULT} แก้ไข parameter ของ database (กำาหนดเอง) SET configuration_parameter FROM CURRENT แก้ไข paramter ให้เป็ นไปตาม session นี้ RESET configuration_parameter reset configuration ของ database RESET ALL reset ทุก configuration ของ database
SQL > ALTER DATABASE test RENAME TO test_01; // แก้ไข database โดยเปล่ียนช่ ือจาก 'test' เป็ นช่ ือ 'test_01' SQL > ALTER DATABASE test OWNER TO user01; // แก้ไข database โดยเปล่ียนเจ้าของเป็ น 'user01' SQL > ALTER DATABASE test SET enable_indexscan TO off; // แก้ไข database 'test' โดยกำาหนดเป็ นแบบไม่ใช้ index scaning
(5) การ connect เข้าใช้งาน database ใช้ SQL Command SQL > \c เชน
3.3
SQL > \c mydb user01 // connect เข้า database 'mydb' ด้วย user 'user01' ถ้าไม่ระบุ user จะเป็ นการ connect ด้วย user ปั จจุบันท่ใี ข้อย่่
การจัดการ Table และ View
3.3.1 Table (1) การสร้าง Table
SQL > CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [ { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ] | table_constraint | LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES } ] ... } [, ... ]
]) [ INHERITS ( parent_table [, ... ] ) ] WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace ]
Copyright © 2009 by Peekanung 13 หมายเหตุ OIDs คือ Object Identifier เป็ น Internal ID ของ postgreSQL ถ้าระบุเป็ น WITH OIDS จะสามารถใช้คำาสัง่ SELECT เพ่ ือดู OIDs ได้ โดย SQL > SELECT oid, * FROM a; oid | a_id | v ----------+------+---16880 | 1 | 11 16881 | 2 | 22 (2 rows) ตัวอยาง SQL > CREATE TABLE testtable (id int, name varchar(30)); SQL > CREATE TABLE person ( did integer, name varchar(40), PRIMARY KEY(did) ); SQL > CREATE TABLE distributors ( did integer PRIMARY KEY DEFAULT nextval(’serial’), name varchar(40) NOT NULL CHECK (name <> ”) ); (2) การด้ Table ด้ รายละเอียด Table ทัง้หมด SQL > \d ; // คำาสัง่ \d จะแสดงผลทัง้ Table , View และ sequence List of relations Schema | Name | Type | Owner ------------+--------------------------+----------+---------public |a | table | user01 public | arr | table | postgres public | b_v | view | user01
หรือ
SQL > SELECT * FROM pg_tables ; // pg_tables จะแสดงผลเฉพาะ Table หรือระบุช่ือ table SQL > SELECT * FROM pg_tables where tablename = 'shipment'; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers -------------------+--------------+----------------+---------------+----------------+------------+------------public | shipment | user01 | |t |f |t (1 row)
ด้โครงสร้างของ Table เชน
SQL > \d table_name; SQL > \d shipment;
Table "public.shipment" Column | Type | Modifiers -------------------+-----------------------------+------------------------------------------------------id | integer | not null default nextval('shipment_id_seq'::regclass) address | text | not null shipping_date | timestamp without time zone | not null Indexes: "shipment_pkey" PRIMARY KEY, btree (id) Triggers: shipment_insert_trigger BEFORE INSERT ON shipment FOR EACH ROW EXECUTE PROCEDURE shipment_insert()
ด้ข้อม้ลใน Table (3) การลบ Table เชน
SQL > SELECT * FROM table_name ;
SQL > DROP TABLE table_name; SQL > DROP TABLE testtable1;
Copyright © 2009 by Peekanung 14 (4) การแก้ไขโครงสร้าง Table (Alter Table) แก้ไขช่ ือ table SQL > ALTER TABLE tbtest RENAME TO tbdemo; แก้ไขช่ ือ column SQL > ALTER TABLE tbdemo RENAME COLUMN col1 TO democol; เพ่ิม column SQL > ALTER TABLE tbdemo ADD COLUMN col1; ลบ column SQL > ALTER TABLE tbdemo DROP COLUMN col1; เปล่ียน Owner SQL > ALTER TABLE tbdemo CHANGE OWNER TO ‘new user name’; กำาหนด default SQL > ALTER TABLE tbdemo ADD COLUMN col2 SET DEFAULT ‘1’ ; ยกเลิก default SQL > ALTER TABLE tbdemo ALTER COLUMN col2 DROP DEFAULT ;
(5) Temporary Tables Temporary Tables เปนตารางชัว่คราว จะมีอยูใน database session ท่ีมีการกำาหนดขึ้นเทานั ้น เม่ ือมีการปด หรือ ยกเลิกการทำางานของ database session นั ้น temporary tables ก็จะถูกลบโดยอัตโนมัติ การใช้งาน Temporary Tables นั ้น จะใช้งานและมองเห็นได้เฉพาะผู้ท่ีสร้างใน database session นั ้นๆเทานั ้น ผู้ใช้ คนอ่ ืนๆไมสามารถมองเห็นได้ ดังนั ้นผู้ใช้งานแตละคนจึงสามารถสร้าง Temporary Tables ช่ ือเดียวกันได้ Temporary tables สวนมากจะถูกใช้ในกรณี ท่ีต้องการข้อมูล จาก Query ท่ีซับซ้อน ท่ีต้องการ execute คำาสัง่ในที เดียว แล้วนำ าข้อมูลท่ีได้ไปเก็บไว้ท่ี Temporary Tables ตัวอยาง SQL > SELECT * INTO TEMPORARY position_doctor FROM position WHERE postcode=60403; 3.3.2 View Views เปนตารางข้อมูลเทียม ซ่ ึง view จะเกิดจากตารางข้อมูล(Table) ท่ีมีอยูในฐานข้อมูล โดยใช้คำาสัง่ SELECT ข้อมูลมาแสดงตามท่ีกาำ หนด View จะมีการกำาหนดสิทธิตางๆแยกจาก ตาราง และสวนตางๆ ของฐานข้อมูล ดังนั ้นเราจึง สามารถกำาหนดสิทธิให้ผู้ใช้ท่ีเราต้องการกำาหนดเห็นเฉพาะข้อมูลในสวนท่ีต้องการได้ (1) การสร้าง View SQL > CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ] AS query ตัวอยาง
SQL > CREATE VIEW myview AS SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;
(2) การด้View ด้โครงสร้าง view เชน SQL > \d myview;
SQL > \d view_name;
View "public.myview" Column | Type | Modifiers ------------+-----------------------+----------city | character varying(80) | temp_lo | integer | temp_hi | integer | prcp | real | date | date | name | text | population | real | altitude | integer | View definition: SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.name, cities.population, cities.altitude FROM weather, cities WHERE weather.city::text = cities.name;
Copyright © 2009 by Peekanung 15 การด้ข้อม้ลใน View SQL > SELECT * FROM myview; (3) การลบ View SQL > DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] Parameters: IF EXISTS name CASCADE RESTRICT
ถ้าไมมีช่ือ view ท่ีต้องการลบ ให้แสดง notice แทน error ช่ ือ view กรณี ท่ีมี objects ใดขึ้นอยูกับ view นี้ ให้ถูกลบโดยอัตโนมัติ กรณี ท่ีมี objects ใดขึ้นอยูกับ view นี้ จะไมอนุญาตให้ drop view (เป็ นคา default)
ตัวอยาง กรณี veiw 'v_myveiw' ไมมีอย้จริง SQL > DROP view v_myview; ERROR: view "v_myview" does not exist //ไม่สามารถลบ view ได้เน่ ืองจากไม่มีช่ือ view นี้อย่่จริง
SQL > DROP VIEW IF EXISTS v_myview; NOTICE: view "v_myview" does not exist, skipping DROP VIEW (4) การแก้ไข View ทำาได้แคการเปล่ียนช่ ือ view เทานั ้น SQL > ALTER VIEW name RENAME TO newname ; เชน
3.4
SQL > ALTER VIEW myview RENAME TO myview02; // แก้ไข view โดยเปล่ียนช่ ือจาก 'myview' เป็ น 'myview02'
การจัดการ Roles
postgreSQL ใช้ 'roles' ในการจัดการ การเข้าถึง database สำาหรับ postgreSQL version 8.1 หรือน้ อยกวา user และ group จะถือเป็ นคนละอยางกัน แตหลังจาก version 8.1 เป็ นต้นมา role สามารถเป็ นได้ทัง้ user , group หรือทัง้ สองอยาง 3.4.1 การสร้าง Roles คำาสัง่ CREATE USER เป็ น alias ของคำาสัง่ CREATE ROLE สามารถใช้แทนกันได้ มีข้อแตกตางเพียงข้อเดียวคือ CREATE USER จะมี option 'LOGIN' เป็ น default จึงสามารถใช้ในการ Log in ได้เลย CREATE ROLE จะมี option 'NOLOGIN' เป็ น default ถ้าต้องการใช้ Log in ได้ด้วยต้องระบุ option 'LOGIN' ผู้ท่ีมีสิทธิในการสร้าง Users จะต้องเปน Superuser เทานั ้น (default คือ user : postgres) SQL > CREATE [USER|ROLE] name [ [ WITH ] option [...] ] Options :
กำาหนดให้เป็ น superuser หรือไม่ กำาหนดให้สามารถ create database ได้หรือไม่ กำาหนดให้สามารถ create role ได้หรือไม่ กำาหนดให้สามารถ create user ได้หรือไม่ กำาหนดให้ได้รับ privileges จาก role ท่ีเป็ นสมาชิกอย่่หรือไม่ กำาหนดให้ใช้ในการ Log in ได้ด้วยหรือไม่ จำากัดจำานวนท่ีใช้ connect หรือไม่ (ค่า -1 หมายถึง unlimit) (default คือ unlimit) | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' encryp password หรือไม่ SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN | CONNECTION LIMIT connlimit
Copyright © 2009 by Peekanung 16 | VALID UNTIL 'timestamp' | IN ROLE rolename [, ...] | IN GROUP rolename [, ...] | ROLE rolename [, ...] | ADMIN rolename [, ...] | USER rolename [, ...] | SYSID uid
กำาหนดวัน และเวลา หมดอายุให้ password assign role ให้กับ user ท่ีสร้าง assign group ให้กับ user ท่ีสร้าง ( ปั จจุบันเลิกใช้แล้ว ถ้ากำาหนด option นี้ จะหมายถึง option IN ROLE) กำาหนด user ให้กับ role ท่ีสร้าง กำาหนด user ให้กับ role ท่ีสร้าง โดย user จะมี Admin option ด้วย ปั จจุบันเลิกใช้แล้ว ถ้ากำาหนด option นี้จะหมายถึง option ROLE ปั จจุบันเลิกใช้แล้ว
หมายเหตุ option ท่ีขีดเส้นใต้ไว้ หมายถึง คา default
ตัวอยาง กำาหนด
SQL > CREATE USER user01; // สร้าง user ช่ ือ 'user01' แบบไม่ระบุ password ( ซ่ ึง user นี้จะยังเข้าใช้งาน database ไม่ได้ ต้อง password ให้กับ user ก่อน) SQL > ALTER USER user01 WITH PASSWORD 'user0123' ; // กำาหนด password ให้ user ท่ีสร้างไว้แล้ว SQL > CREATE USER user01 WITH PASSWORD 'user0123'; // สร้าง user ช่ ือ 'user01' แบบระบุ password SQL > CREATE USER user01 WITH PASSWORD 'user0123' CREATEDB; // สร้าง user ท่ีสามารถสร้าง Database ได้ SQL > CREATE USER test1 role test2; // assign role 'test1' ให้ user 'test2' เทียบเท่ากับ SQL > GRANT test1 TO test2 ; ในกรณี นี้ test1 เป็ น role , test2 เป็ น user (หลัง option role จะเป็ น user) SQL > CREATE USER test3 in role test2 ; // assign role 'test2' ให้ user 'test3' เทียบเท่ากับ SQL > GRANT test2 TO test3; ในกรณี นี้ test2 เป็ น role , test3 เป็ น user (หลัง option in role จะเป็ น role ) SQL > CREATE USER test4 admin test2 ; // assign role 'test4' ให้ user 'test2' โดย 'test2' สามารถ grant privileges ให้ user อ่ ืนได้ เทียบเท่ากับ SQL > GRANT test2 TO test3 WITH ADMIN OPTION ; 3.4.2 การแก้ไข Roles SQL > ALTER [USER|ROLE] user-name [[WITH] option ]...
Option :
SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp'
3.4.3 การลบ Roles SQL > DROP USER user-name
3.4.4 การด้ Roles
Copyright © 2009 by Peekanung 17 SQL > \du List of roles Role name | Superuser | Create role | Create DB | Connections | Member of -----------+-----------+-------------+-----------+-------------+----------abc | yes | yes | yes | no limit | {} postgres | yes | yes | yes | no limit | {} test | no | yes | yes | no limit | {} testuser | no | yes | yes | no limit | {} user01 | no | yes | yes | no limit | {} xyz | yes | yes | yes | no limit | {} (6 rows)
SQL > SELECT * FROM pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig ----------+----------+-------------+----------+-----------+----------+----------+----------postgres | 10 |t |t |t | ******** | | test | 16514 | t |f |f | ******** | | testuser | 16532 | t |f |f | ******** | | user01 | 16535 | t |f |f | ******** | | xyz | 16582 | t |t |t | ******** | | abc | 16587 | t |t |t | ******** | | (6 rows)
3.4.5 การกำาหนดสิทธิการใช้งานข้อม้ล เม่ ือมีการสร้างตารางข้อมูลขึ้นมา ผู้ท่ีสร้างตาราง(owner)เทานั ้นท่ีมีสิทธิในการใช้งาน ถ้าต้องการให้ผู้อ่ืนใช้งานได้ด้วย จะต้องมีการแก้ไขสิทธิของตาราง(Table’s permission) โดยการใช้คำาสัง่ GRANT โดยคำาสัง่นี้ สามารถกำาหนดขอบเข ตการใช้งาน SELECT, UPDATE, DELETE, RULE,ALL โดยท่ีกำาหนดการใช้ เปนรายบุคคล กลุม หรือทัง้หมด(Public) และ REVOKE เปนคำาสัง่ ยกเลิกการกำาหนดสิทธิตางๆ ท่เี กิดจากคำาสัง่ GRANT (1) GRANT SQL > GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] ; SQL > GRANT { { USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] } ON SEQUENCE sequencename [, ...] TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] SQL > GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE dbname [, ...] TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] SQL > GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] SQL > GRANT { USAGE | ALL [ PRIVILEGES ] } ON LANGUAGE langname [, ...] TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] SQL > GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } ON SCHEMA schemaname [, ...] TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] SQL > GRANT { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespacename [, ...] TO { [ GROUP ] rolename | PUBLIC } [, ...] [ WITH GRANT OPTION ] SQL > GRANT role [, ...] TO rolename [, ...] [ WITH ADMIN OPTION ]
Copyright © 2009 by Peekanung 18 ตัวอยาง SQL > GRANT all ON DATABASE pis TO user1; // กำาหนดให้ user1 มีสิทธิท ์ ุกอย่างบน database pis SQL > GRANT all ON person TO user1; // กำาหนดให้ user1 มีสิทธิท ์ ุกอย่างบน table person SQL > GRANT select ON person TO user2; // กำาหนดให้ user2 มีสิทธิ ์ select บน table person (2) REVOKE SQL > REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRIGGER } [,...] | ALL [ PRIVILEGES ] } ON [ TABLE ] tablename [, ...] FROM { [ GROUP ] rolename | PUBLIC } [, ...] [ CASCADE | RESTRICT ] SQL > REVOKE [ GRANT OPTION FOR ] {{ USAGE | SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] } ON SEQUENCE sequencename [, ...] FROM { [ GROUP ] rolename | PUBLIC } [, ...] [ CASCADE | RESTRICT ] SQL > REVOKE [ GRANT OPTION FOR ] {{ CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PREVILEGES]} ON DATABASE dbname [, ...] FROM { [ GROUP ] rolename | PUBLIC } [, ...] [ CASCADE | RESTRICT ] SQL > REVOKE [ GRANT OPTION FOR ] { EXECUTE | ALL [PRIVILEGES]} ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] FROM { [ GROUP ] rolename | PUBLIC } [, ...] [ CASCADE | RESTRICT ] SQL > REVOKE [ GRANT OPTION FOR ] { USAGE | ALL | PRIVILEGES ] } ON LANGUAGE langname [, ...] FROM { [ GROUP ] rolename | PUBLIC } [, ...] [ CASCADE | RESTRICT ] SQL >REVOKE [ GRANT OPTION FOR ] { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] } ON SCHEMA schemaname [, ...] FROM { [ GROUP ] rolename | PUBLIC } [, ...] [ CASCADE | RESTRICT ] SQL >REVOKE [ GRANT OPTION FOR ] { CREATE | ALL [ PRIVILEGES ] } ON TABLESPACE tablespacename [, ...] FROM { [ GROUP ] rolename | PUBLIC } [, ...] [ CASCADE | RESTRICT ] SQL >REVOKE [ ADMIN OPTION FOR ] role [, ...] FROM rolename [, ...] [ CASCADE | RESTRICT ] ตัวอยาง SQL > REVOKE all ON DATABASE pis FROM user1; // ยกเลิกการให้สิทธิท ์ ุกอย่างบน database pis แก่ user1 SQL > REVOKE all ON person FROM user1; // ยกเลิกการให้สิทธิท ์ ุกอย่างบน table pis แก่ user1 SQL > REVOKE select ON person FROM user2; // ยกเลิกการให้สิทธิ ์ select บน table pis แก่ user2
Copyright © 2009 by Peekanung 19 3.5 Data type
เม่ ือสร้าง Table จะต้องมีการกำาหนดชนิ ดของข้อมูลท่ีจะเก็บไว้ในแตละคอลัมน์(Column) เชน ช่ ือเปนตัวอักษร วันเกิด เก็บคาเปนวันท่ี เงินเดือนเปนตัวเลข เป็ นต้น ข้อมูลท่ีอยูใน PostgreSQL ถูกกำาหนดด้วยชนิ ดข้อมูล(data type) ซ่ ึงมีประเภทของชนิ ดข้อมูล(data type) หลาย ประเภท ดังนี้ 3.5.1 Character types Data type
Description
Character varying(n), varchar (n)
variable-length with limit
Character (n) , char (n)
fixed-length, blank padded
text
variable unlimited length 3.5.2 Numberic types
Data type
Size
Description
Range
smallint
2 bytes
small range fixed- precision
-32768 to +32767
integer
4 bytes
usual choice for integer
-2147483648 to +2147483647
bigint
8 bytes
large-range integer
-9223372036854775808 to 9223372036854775807
decimal
variable
user-specified precision, exact
no limit
numeric
variable
user-specified precision, exact
no limit
real
4 bytes
variable-precision, inexact
6 decimal digits precision
double precision 8 bytes
variable-precision, inexact
15 decimal digits precision
serial
4 bytes
autoincrementing integer
1 to 2147483647
bigserial
8 bytes
large autoincrementing integer
1 to 9223372036854775807
3.5.3 Date/Time types Data type
Size
Description
timestamp [ (p) ] [ without time zone ]
8 bytes
both date and time
timestamp [ (p) ] with time zone
8 bytes
both date and time, with time zone
interval [ (p) ]
12 bytes
time intervals
date
4 bytes
dates only
time [ (p) ] [ without time zone ]
8 bytes
times of day only
time [ (p) ] with time zone 3.5.4 Logical types
12 bytes
times of day only, with time zone
Data type
Description
Renge
boolean(bool)
TRUE, FALSE, or NULL
true, 't', 'y', 'yes', 1 false, 'f', 'n', 'no', 0
Copyright © 2009 by Peekanung 20 3.6 การ Import / Export data
คำาสั่ง COPY ในการนำ าข้อมูลเข้าและออกจากฐานข้อมูลโดยการใช้คำาสัง่ COPY ซ่ ึงคำาสัง่นี้ จะเขียนข้อมูลจากตารางเปนไฟล์ข้อมูลชนิ ด ASCII และนำ าข้อมูลท่ีเปนไฟล์ข้อมูลชนิ ด ASCII เข้า table ได้ คำาสัง่นี้สามารถนำ าไปใช้ในการสำารองข้อมูล หรือสงผานข้อมูลระหวาง postgreSQL กับโปรแกรมอ่ ืนๆได้ COPY tablename [ ( column [, ...] ) ] FROM { 'filename' | STDIN } [ [ WITH ] [ BINARY ] [ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ] [ NULL [ AS ] 'null string' ] [ CSV [ HEADER ]
[ QUOTE [ AS ] 'quote' ] [ ESCAPE [ AS ] 'escape' ] [ FORCE NOT NULL column [, ...] ] COPY { tablename [ ( column [, ...] ) ] | ( query ) } TO { 'filename' | STDOUT } [ [ WITH ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ] [ CSV [ HEADER ]
[ QUOTE [ AS ] 'quote' ] [ ESCAPE [ AS ] 'escape' ] FORCE QUOTE column เป [, น ...]คำา] สัง่สำาเนาข้อมูลในตารางไปเปนไฟล์ข้อมูล SQL >[COPY...TO
quote)
SQL > COPY...FROM เปนคำาสัง่นำ าข้อมูลไฟล์เข้าตาราง อธิบาย BINARY ข้อม่ลจะเป็ น binary format ทำาให้ไม่สามาถใช้ option DELIMITER, NULL หรือ CSV ได้ OIDS ระบุให้เอาค่า OIDS ด้วย DELIMITERS กำาหนดตัวคัน ่ ระหว่าง column (default จะเป็ น tab) NULL กำาหนดค่าให้ฟิลด์ท่ีเป็ น null (default คือ \N สำาหรับ text file , เป็ นค่าว่างสำาหรับ csv file ) CSV กำาหนดให้เป็ น csv file format HEADER สำาหรับ output ค่าจะเป็ นช่ ือ column , input เป็ นการบอกว่าบรรทัดแรกเป็ น header QUOTE กำาหนด ASCII quotation character สำาหรับ csv file format (default คือ doubleตัวอยาง SQL > COPY country TO '/tmp/country.txt' WITH DELIMITER '|'; // copy ข้อม่ลใน table 'country' เก็บไว้ท่ี /tmp ช่ ือไฟล์ country.txt ข้อม่ลถ่กคัน ่ ด้วย | SQL > COPY country TO '/tmp/country.txt' WITH NULL 'null data'; // copy ข้อม่ลใน table 'country' เก็บไว้ท่ี /tmp ช่ ือไฟล์ country.txt โดยข้อม่ลท่ีเป็ น null จะถ่กแทนท่ีด้วย คำาว่า 'null data' SQL > COPY country FROM '/tmp/country.txt' ; // นำ าข้อม่ลในไฟล์ /tmp/country.txt เข้า table country
Copyright © 2009 by Peekanung 21 3.7 PostgreSQL function
PostgreSQL มีคุณสมบัติให้สร้าง Server-side Function ซ่ ึงบางครัง้เรียกวา Stored procedures ได้ การท่ีเราสร้าง Server-side Function และ function จะติดไปกับ Database จะทำาให้นำาไปใช้งานได้กับโปร แกรมอ่ ืนๆได้ โดยท่ีโปรแกรมเหลานั ้นไมต้องสร้างหรือสำาเนา function เหลานั ้นไปอีก เราสามารถเรียกใช้ function จากคำาสัง่ SQL Queries ได้เลย และเม่ ือมีการแก้ไข function ทุกโปรแกรมท่ีเรียก ใช้ function นี้กจ็ ะเปล่ียนไปตามท่ีแก้ไขด้วย Server-side functions สามารถเขียนได้จากภาษาตางๆดังนี้ SQL PL/PGSQL PL/TCL PL/Perl C ตัวอยางการสร้าง SQL functions ในการสร้าง function สามารถกำาหนดช่ ือและบันทึกเก็บไว้เพ่ ือเรียกใช้ในภายหลังได้อีก การสร้างใช้คำาสัง่ CREATE FUNCTION และการลบใช้คำาสัง่ DROP FUNCTION คำาสัง่ CREATE FUNCTION ต้องการข้อมูลตางๆท่ีใช้กำาหนดการสร้างดังนี้ − ช่ ือ Function − จำานวน Argument − ชนิ ดข้อมูลของแตละ Argument − Function return type − การทำางาน − ภาษาท่ีใช้ในการทำางาน เชน SQL > CREATE FUNCTION tax(numeric) RETURNS numeric AS 'SELECT ($1*0.07::numeric(8,2))::numeric(8,2);' LANGUAGE 'sql'; SQL > SELECT tax(20); tax -----1.40 (1 row) อธิบาย − ช่ ือ Function คือ tax − จำานวน Argument 1 การเรียกใช้ argument กำาหนดตามลำาดับ $1 $2 $3 − ชนิ ดข้อมูลของแตละ Argument คือ numeric − Function return type เป็ น numeric − การทำางาน คือ SELECT ($1*0.07::nueric(8,2)):: nueric(8,2); ในคำาสัง่การทำางานสามารถใช้ INSERT UPDATE DELETE ได้รวมทัง้ Multiple Queries แยกคำาสัง่โดยใช้ semicolons คัน ่ แตละคำาสัง่ − ภาษาท่ีใช้ในการทำางาน คือ SQL การใช้ function ในการ SELECT Query จะชวยลดปญหาความผิดพลาดในการ join table แตก็ทำาให้ทำางานช้ากวา เน่ ืองจากการใช้ function จะมีการ execution function ทุกๆรายการข้อมูล ** หมายเหตุ ในกรณี ท่ีสร้าง Function แล้วเกิด error ERROR: language "plpgsql" does not exist HINT: Use CREATE LANGUAGE to load the language into the database. วิธีแก้ไขคือ ต้อง Login ด้วย user ท่ีเป็ นเจ้าของ Database แล้วใช้คำาสัง่
Copyright © 2009 by Peekanung 22 SQL > CREATE LANGUAGE <ภาษาท่ีใช้>; เชน SQL > CREATE LANGUAGE plpgsql; // เป็ นการ install PostgreSQL procedural languages (PL/pgSQL)
3.8 Partitioning table การทำา Partitioning Table คือ การแบงข้อมูลใน Table ออกเป็ น Table ยอยหลายๆ Table เหมาะสำาหรับ Table ท่ีมีข้อมูล จำานวนมาก ซ่ึงมีข้อดีตา งๆ ดังนี้ Manageability สามารถจัดการกับข้อมูลสวนยอยได้โดยไมกระทบกับข้อมูลทัง้หมด การ Backup / Recovery สามารถแยกทำาแตละ partition ได้ ทำาให้การทำางานเร็วขึ้น และไมมีผลกระทบกับ partition อ่ ืน สามารถแยก Tablespace ของแตละ Partition ได้ ทำาให้การจัดการ tablespace ทำาได้งายขึ้น Availability เม่ ือต้องมีการจัดการบางอยางเดียวกับ Disk เชน เปล่ียน หรือซอม disk ระบบยังสามารถทำางานตอไปได้ เน่ ืองจาก ไมมีผลกระทบใดๆ ตอข้อมูลท่ีอยูตาง disk กัน (กรณี ท่ีแตละ partition มีการแยก disk กัน) Perfermance การ Query ข้อมูลจาก partition จะเร็วกวาการ query จาก master table เน่ ืองจากข้อมูลมีจำานวนน้ อยกวา ชนิ ดของ Partitioning table ใน Postgresql 1. Range Partitioning : แบง partition ตามชวงของข้อมูล โดยไมมีการทับ ซ้อน (overlap) ของข้อมูลในแตละ partition เชน แบงเป็ นชวงปี หรือ ชวงเดือน เป็ นต้น 2. List Partitioning : แบง partition ตามรายการ หรือหมวดหมู ตามคา ของข้อมูลท่ีกำาหนด เชน แบงข้อมูลตามทวีปตางๆ ของโลก เป็ นต้น ตัวอยาง การสร้าง Partition table 1. แก้ไขไฟล์ postgresql.conf โดย set constraint_exclusion = on ซ่ึงจะทำาให้ query's planner สามารถใช้ constraints ในการเพ่ิมประสิทธิภาพของ query ได้ 2. หลังจากนั ้นให้ restart service ของ database โดยต้อง log in ด้วย user : root แล้วใข้คำาสัง่ $ service postgresql-8.3 restart 3. สร้าง 'Master table' SQL > CREATE TABLE shipment ( id SERIAL PRIMARY KEY, address TEXT NOT NULL, shipping_date TIMESTAMP NOT NULL); 4.สร้าง 'Child tables' เพ่ ือใช้เป็ น partition ของ Master table โดยมี Contraints เป็ นตัวข้อกำาหนด column key ในการ แบง partition SQL > CREATE TABLE shipment_part_2008 ( CHECK (shipping_date >= DATE '2008-01-01' AND shipping_date < DATE '2009-01-01') ) INHERITS (shipment); SQL > CREATE TABLE shipment_part_pre2008 ( CHECK (shipping_date < DATE '2008-01-01') ) INHERITS (shipment); 5. สร้าง Index ให้แตละ child table (โดยใช้ key เดียวกับ column key ของ constrains ดังตัวอยาง คือ shipping_date ) SQL > CREATE INDEX shipping_date_2008 ON shipment_part_2008 (shipping_date); SQL > CREATE INDEX shipping_date_pre2008 ON shipment_part_pre2008 (shipping_date); SQL > CREATE INDEX shipping_date_post2008 ON shipment_part_post2008 (shipping_date); 6. สร้าง Function เพ่ ือกำาหนดให้มีการจัดเก็บข้อมูลใน partition table ท่ีถูกต้อง ในท่ีนี้ต้องการแบงเป็ น 3 partition คือ ปี 2008 (shipment_part_2008),กอนปี 2008 (shipment_part_pre2008) และหลังปี 2008 (shipment_part_post 2008)
Copyright © 2009 by Peekanung 23 โดยใช้ฟิลด์ shipping_date เป็ น key SQL > CREATE OR REPLACE FUNCTION shipment_insert() RETURNS TRIGGER AS $$ BEGIN IF (NEW.shipping_date >= DATE '2008-01-01' AND NEW.shipping_date < DATE '2009-01-01') THEN INSERT INTO shipment_part_2008 VALUES (NEW.*); ELSIF (NEW.shipping_date < DATE '2008-01-01') THEN INSERT INTO shipment_part_pre2008 VALUES (NEW.*); ELSEIF (NEW.shipping_date > DATE '2008-12-31') THEN INSERT INTO shipment_part_post2008 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date out of range. Fix the shipment_insert() function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; 7. สร้าง Trigger เพ่ ือเป็ นการสัง่ execute function กอนมีการ Insert data เข้า master table ทุกครัง้ SQL > CREATE TRIGGER shipment_insert_trigger BEFORE INSERT ON shipment FOR EACH ROW EXECUTE PROCEDURE shipment_insert(); 8. ทดสอบโดยการ Insert ข้อมูลเข้า Master table SQL > INSERT INTO shipment (address, shipping_date) VALUES ('Alaska', '2008-08-08'); SQL > INSERT INTO shipment (address, shipping_date) VALUES ('Texas', '2007-07-07'); SQL > INSERT INTO shipment (address, shipping_date) VALUES ('Alaska', '2009-08-08'); SQL > SELECT * FROM shipment ; id | address | shipping_date ----+---------+--------------------1 | Dakota | 2008-08-08 00:00:00 2 | Texas | 2007-07-07 00:00:00 3 | Alaska | 2009-08-08 00:00:00 (3 rows) SQL > SELECT * FROM shipment_part_2008; id | address | shipping_date ----+---------+--------------------1 | Dakota | 2008-08-08 00:00:00 (1 row)
SQL > SELECT * FROM shipment_part_pre2008; id | address | shipping_date ----+---------+--------------------2 | Texas | 2007-07-07 00:00:00 (1 row) SQL > SELECT * FROM shipment_part_post2008 ; id | address | shipping_date ----+---------+--------------------6 | Alaska | 2009-08-08 00:00:00 (1 rows) จะเห็นวา ข้อมูลถูก Insert เข้า partition table ท่ีถูกต้อง ตามข้อกำาหนดแล้ว
Copyright © 2009 by Peekanung 24 3.9 Materialized view Materialized view (mview) เหมือน View ตรงท่ีมี link definition อยูกับ base table และเหมือน table ตรงท่ีมันจะเก็บ ข้อมูลไว้ท่ต ี ัวเอง DBA จะเป็ นผู้กำาหนดวาจะมีการ refresh data บอยแคไหน โดยท่ี user ไมจำาเป็ นต้องรู้วามี mview อยู เพราะ สามารถ select statement ผาน base table ได้เหมือนเดิม ข้อแตกตางระหวาง materialized view กับ view View
Materialized view
ไมมีข้อมูลอยูจริง มีแต statement ท่ีเอาไว้เรียกข้อมูลจาก เป็ นการ duplicate data จาก base table ทำาให้มีข้อมูล base table (logical representation of table) จริงเก็บไว้ท่ี mview เลย ทำาให้การ access data เร็วกวา view Select ได้อยางเดียว ไมสามารถใช้คำาสัง่ Insert, Update, Delete ข้อมูลใน view ได้
นอกจาก Select แล้ว สามารถใช้คำาสัง่ Insert, Update, Delete ข้อมูลใน view ได้
หมายเหตุ Postgresql ไมสามารถใช้ Insert, Update, Delete กับ view ได้ Oracle สามารถใช้คำาสัง่ Insert, Update, Delete ได้ ในกรณี ท่ี view ไมได้เกิดจากการ join table การคำานวณ หรือ virtual column ตัวอยาง virtual column เชน SQL > create view v1 as select id, 'abc' as name from table1; //name เป็ น virtual column ไม่สามารถใช้คำาสัง่ Insert, Update, Delete กับ column 'name' ได้ (แต่ใช้กับ 'id' โดยไม่ระบุ 'name'ได้) SQL > insert into v1(id,name) values (2,'name02') ; // insert ไม่ได้ เน่ ืองจาก name เป็ น virtual column SQL > insert into v1(id) values (2) ; // insert ได้เน่ ืองจากระบุเฉพาะ column 'id' ซ่ ึงเป็ น column ท่ีมีอย่่จริง ขนิ ดของ materialized view 1. Snapshot ■ ข้อมูลจะเปล่ียนแปลงตาม base table เม่ ือมีการสัง่ให้ refresh data (Manual) ■ เหมาะสำาหรับข้อมูลท่ีมีการเปล่ียนแปลงครัง้ละจำานวนมาก แตไมเหมาะกับข้อมูลท่ีมีการเปล่ียนแปลงบอย 2. Eager ■ ข้อมูลจะเปล่ียนแปลงทุก transaction เม่ ือ base table มีการเปล่ียนแปลง ■ ถ้า base table มีการเปล่ียนแปลงบอยครัง้ จะเกิดการเปล่ียนแปลงท่ี mview อยูเสมอ ทำาให้สิน ้ เปลือง ทรัพยากร ■ เหมาะสำาหรับข้อมูลท่ี base table มีการเปล่ียนแปลงน้ อย แตไมเหมาะกับการ import หรือ เปล่ียนแปลง ข้อมูลจำานวนมาก 3. Lazy ■ ข้อมูลจะเปล่ียนแปลงตาม base table เม่ ือมีการ commit เทานั ้น ดังนั ้นข้อมูลจะไมมีการเปล่ียนแปลงบอย เทาแบบ Eager แตข้อมูลจะถูกต้องตรงกับ base table มากกวาแบบ snapshot เน่ ืองจากไมต้องรอให้มีการ สัง่ refresh ■ ข้อมูลใน mview กับ base table อาจจะไมตรงกันชัว ่ ขณะ (ขณะท่ียังอยูใน transaction แตยังไมได้ commit) 4. Very lazy ■ คล้ายแบบ snapshot คือข้อมูลจะเปล่ียนแปลงเม่ ือมีการสัง่ refresh data แตการ update ข้อมูลจะทำาได้เร็ว กวา และใช้ทรัพยากรน้ อยกวา วิธีการทำา Materialized view 3.9.1 Snapshot ข้อมูลใน materialized view จะมีการเปล่ียนแปลง เม่ ือถูกสัง่ refresh data เทานั ้น 1.1 สร้าง table matviews เพ่ ือเก็บข้อมูลเก่ียวกับ materialized view SQL > CREATE TABLE matviews ( mv_name NAME NOT NULL PRIMARY KEY, --ช่ ือ mveiws v_name NAME NOT NULL, -- ช่ ือ view ท่ีเป็ น base last_refresh TIMESTAMP WITH TIME ZONE) -- วันเวลาท่ีมีการสัง่ refresh data
Copyright © 2009 by Peekanung 25 WITH OIDS; 1.2 สร้าง Function create_matview เพ่ ือให้เก็บข้อมูลลง table matviews ทุกครัง้ท่ีมีการสัง่ refresh data พร้อมเช็ควาช่ ือ mviews ต้องไมซำา้ SQL > CREATE OR REPLACE FUNCTION create_matview(NAME, NAME) RETURNS VOID SECURITY DEFINER LANGUAGE plpgsql AS 'DECLARE matview ALIAS FOR $1; view_name ALIAS FOR $2; entry matviews%ROWTYPE; BEGIN SELECT * INTO entry FROM matviews WHERE mv_name = matview; IF FOUND THEN RAISE EXCEPTION ''Materialized view ''''%'''' already exists.'', matview; END IF; EXECUTE ''REVOKE ALL ON '' || view_name || '' FROM PUBLIC''; EXECUTE ''GRANT SELECT ON '' || view_name || '' TO PUBLIC''; EXECUTE ''CREATE TABLE '' || matview || '' AS SELECT * FROM '' || view_name; EXECUTE ''REVOKE ALL ON '' || matview || '' FROM PUBLIC''; EXECUTE ''GRANT SELECT ON '' || matview || '' TO PUBLIC''; INSERT INTO matviews (mv_name, v_name, last_refresh) VALUES (matview, view_name, CURRENT_TIMESTAMP); RETURN; END '; 1.3 สร้าง Function drop_matview เพ่ ือให้ลบ materialized view ออกจาก table matviews SQL > CREATE OR REPLACE FUNCTION drop_matview(NAME) RETURNS VOID SECURITY DEFINER LANGUAGE plpgsql AS 'DECLARE matview ALIAS FOR $1; entry matviews%ROWTYPE; BEGIN SELECT * INTO entry FROM matviews WHERE mv_name = matview; IF NOT FOUND THEN RAISE EXCEPTION ''Materialized view % does not exist.'', matview; END IF; EXECUTE ''DROP TABLE '' || matview; DELETE FROM matviews WHERE mv_name=matview; RETURN; END'; 1.4 สร้าง Function refresh_matview เพ่ ือสัง่ refresh data โดยในการสัง่ refresh data ต้องมีการระบุช่ือ materialized view ด้วยเสมอ SQL > CREATE OR REPLACE FUNCTION refresh_matview(name) RETURNS VOID SECURITY DEFINER LANGUAGE plpgsql AS ' DECLARE matview ALIAS FOR $1; entry matviews%ROWTYPE; BEGIN SELECT * INTO entry FROM matviews WHERE mv_name = matview; IF NOT FOUND THEN RAISE EXCEPTION ''Materialized view % does not exist.'', matview; END IF; EXECUTE ''DELETE FROM '' || matview;
Copyright © 2009 by Peekanung 26 EXECUTE ''INSERT INTO '' || matview || '' SELECT * FROM '' || entry.v_name; UPDATE matviews SET last_refresh=CURRENT_TIMESTAMP WHERE mv_name=matview; RETURN; END';
เล่น
1.5 ตอไปเป็ นการสร้างตัวอยาง table และ view สำาหรับทำา materialized view SQL > CREATE TABLE player ( pname VARCHAR(255) PRIMARY KEY ); //table เก็บรายละเอียดผ้่ //table เก็บรายละเอียดคะแนน
SQL > CREATE TABLE game_score ( pname VARCHAR(255) NOT NULL, score INTEGER NOT NULL); SQL > CREATE VIEW player_total_score_v AS SELECT pname, sum(score) AS total_score FROM game_score GROUP BY pname; SQL > SQL > SQL > SQL > SQL > SQL > SQL >
//view เก็บรายละเอียดคะแนนสะสมของผ้่เล่น
INSERT INTO player VALUES ('player01'); INSERT INTO player VALUES ('player02'); INSERT INTO game_score VALUES ('player01','20'); INSERT INTO game_score VALUES ('player01','10'); INSERT INTO game_score VALUES ('player02','5'); INSERT INTO game_score VALUES ('player02','10'); INSERT INTO game_score VALUES ('player02','5');
SQL > SELECT * from player; pname ---------player01 player02 (2 rows) SQL > SELECT * from game_score ; pname | score ----------+------player01 | 20 player01 | 10 player02 | 5 player02 | 10 player02 | 5 (5 rows) SQL > SELECT * from player_total_score_v ; pname | total_score ----------+------------player01 | 30 player02 | 20 (2 rows) 1.6 สร้าง materialized views ช่ ือ player_total_score_mv โดยใช้ function create_matview SQL > SELECT create_matview('player_total_score_mv', 'player_total_score_v'); // player_total_score_mv เป็ นช่ ื่อ materialized view ซ่ ึงต้องกำาหนดเองในแต่ละครัง้ท่ีสร้าง
Copyright © 2009 by Peekanung 27 // player_total_score_v เป็ นช่ ือ view ท่ีได้สร้างไว้ตัง้แต่แรก SQL > CREATE INDEX pname_idx ON player_total_score_mv(pname); // สร้าง index ให้กับ materialized view โดยใช้ pname เป็ น ่key 1.7 เม่ ือต้องการ Refresh data ใช้ function refresh_matviews ซ่ ึงการ refresh ทุกครัง้ ต้อง drop index เดิมกอน เพ่ ือให้การ refresh ทำาได้เร็วขึ้น แล้วคอยสร้าง index ใหม SQL > DROP INDEX pname_idx ON player_total_score_mv; SQL > SELECT refresh_matview('player_total_score_mv'); SQL > CREATE INDEX pname_idx ON player_total_score_mv(pname); *หมายเหตุ : ข้อมูลบน player_total_score_mv จะยังไมมีการเปล่ียนแปลงแม้วา base table มีการเปล่ียนแปลง แล้ว จนกระทัง่มีการสัง่ refresh data เกิดขึ้น 3.9.2 Eager ข้อมูลของ materialized view จะเปล่ียนแปลงทุกครัง้ท่ี table เปล่ียนแปลง โดยใช้ความสามารถของ trigger ท่ีอยูบน table ในการจัดการ ซ่ ึง table นี้ เรียกวา 'Underlying table' ประเภทของความสัมพันธ์ระหวาง underlying table กับ materialized view (1) One-to-one ข้อมูลใน view อาจ select มาจากบางคอลัมน์ หรือทัง้หมดของ underlying table rows ใน view อาจไมขึ้นอยูกับ underlying table ตัวอยางเชน เม่ ือ user 1 คนเปล่ียน password จะสงผลตอข้อมูลใน view แค 1 row เทานั ้น (2) Many-to-one ข้อมูลหลายๆ rows ใน view ขึ้นอยูกับ 1 rows บน underlying table ซ่ ึงเป็ นแบบท่ีเจอคอนข้างบอยในการ join table เชน ถ้ามีการเปล่ียน group name จะสงผลตอข้อมูลใน view หลาย row (3) One-to-many ข้อมูลใน view 1 rows มาจากหลาย row ใน underlying table เชน ผลรวมของคะแนน ท่ีได้มาจากคะแนนยอยในแตละครัง้ (4) Many-to-many เป็ นกรณี ท่ีเจอไมบอยนั ก คือ หลายๆ rows ใน view มาจากหลายๆ rows ใน underlying table ซ่ ึงอาจมา จากการรวมกันของแตละ relation หลายๆแบบ การทำา Eager materialized view 2.1 สร้าง function mv_refresh_row โดย ซ่ ึงมีขัน ้ ตอนกา่รทำางานคราวๆ ดังนี้ ระบุ Primary key ให้ materialized veiw delete row ท่ีมีอยูเดิมใน function โดยระบุจาก primary key ท่ีได้กำาหนดไว้ หลังจากนั ้น select ข้อมูลมาจาก base view แล้ว insert เข้า materialized view 2.2 สร้าง function mv_refresh mv_refresh จะ refresh เฉพาะ row ท่ีมีการเปล่ียนแปลง ซ่ ึงจะทำางานตามเวลาท่ีได้กำาหนดไว้ 2.3 สร้าง trigger ซ่ ึงมีขัน ้ ตอนการทำางาน คราวๆ ดังนี้
ระบุ Primary key ให้ materialized view
สำาหรับการ delete และ insert ให้เรียกใช้ function mv_refresh_row
สำาหรับการ update ต้องมีการระบุ row ท่ีจะมีการเปล่ียนแปลงข้อมูล ตัวอยาง SQL > CREATE TABLE a ( a_id INT PRIMARY KEY, v INT) WITH OIDS;
-- สร้าง table a
SQL > CREATE TABLE b ( b_id INT PRIMARY KEY, a_id INT REFERENCES a, v INT,
-- สร้าง table b
Copyright © 2009 by Peekanung 28 expires TIMESTAMP ) WITH OIDS; SQL > CREATE TABLE c ( c_id INT PRIMARY KEY, b_id INT REFERENCES b, v INT) WITH OIDS;
--สร้าง table c
SQL > CREATE VIEW b_v AS -- สร้าง view b_v ซ่ ึงมาจากการ join table a, b และ c SELECT b.b_id AS b_id, a.v AS a_v, b.v AS b_v, sum(c.v) AS sum_c_v FROM a JOIN b USING (a_id) JOIN c USING (b_id) WHERE (b.expires IS NULL OR b.expires >= now()) GROUP BY b.b_id, a.v, b.v; SQL > SELECT create_matview('b_mv', 'b_v');// สร้าง materialized view โดยใช้ function create_matview จากตัวอยาง ข้อมูลใน table a 1 แถว อาจมีผลกับหลายแถวใน view b_v ( 1 : m) , ใน table b 1 แถว มีผลกับ 1 แถวใน view b_v (1:1) และหลาย rows ใน table c มีผลกับ view b_v เพียง 1 แถว (m : 1)
สร้าง function b_mv_refresh_row SQL > CREATE FUNCTION b_mv_refresh_row(b_mv.b_id%TYPE) RETURNS VOID SECURITY DEFINER LANGUAGE 'plpgsql' AS 'BEGIN DELETE FROM b_mv WHERE b_id = $1; INSERT INTO b_mv SELECT * FROM b_v WHERE b_id = $1; RETURN; END'; สร้าง function b_mv_refresh SQL > CREATE FUNCTION b_mv_refresh() RETURNS VOID SECURITY DEFINER LANGUAGE 'plpgsql' AS 'BEGIN PERFORM b_mv_refresh_row(b_id) //calling a function that has side-effects but no useful result value FROM b, matviews WHERE matviews.mv_name = ''b_mv'' AND b.expires >= matviews.last_refresh AND b.expires < now(); UPDATE matviews
Copyright © 2009 by Peekanung 29 SET last_refresh = now() WHERE mv_name = ''b_mv''; RETURN; END '; สร้าง trigger ให้ table a, b และ c
1. trigger ของ table a มีดงั นี้ 1.1 trigger b_mv_ut จะทำางานหลังจากมีการ update data ใน table a โดยเรียกใช้ function b_mv_a_ut (ซ่ึงจะเรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE FUNCTION b_mv_a_ut() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS 'BEGIN IF OLD.a_id = NEW.a_id THEN PERFORM b_mv_refresh_row(b.b_id) FROM b WHERE b.a_id = NEW.a_id; ELSE PERFORM b_mv_refresh_row(b.b_id) FROM b WHERE b.a_id = OLD.a_id; PERFORM b_mv_refresh_row(b.b_id) FROM b WHERE b.a_id = NEW.a_id; END IF; RETURN NULL; END '; SQL > CREATE TRIGGER b_mv_ut AFTER UPDATE ON a FOR EACH ROW EXECUTE PROCEDURE b_mv_a_ut(); 1.2 trigger b_mv_dt จะทำางานหลังจากมีการ delete data ใน table a โดยเรียกใช้ function b_mv_a_dt (ซ่ึงจะ เรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE FUNCTION b_mv_a_dt() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN PERFORM b_mv_refresh_row(b.b_id) FROM b WHERE b.a_id = OLD.a_id; RETURN NULL; END '; SQL > CREATE TRIGGER b_mv_dt AFTER DELETE ON a FOR EACH ROW EXECUTE PROCEDURE b_mv_a_dt(); 1.3 trigger b_mv_it จะทำางานหลังจากมีการ insert data ใน table a โดยเรียกใช้ function b_mv_a_it (ซ่ึงจะ เรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE FUNCTION b_mv_a_it() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN PERFORM b_mv_refresh_row(b.b_id) FROM b WHERE b.a_id = NEW.a_id; RETURN NULL; END'; SQL > CREATE TRIGGER b_mv_it AFTER INSERT ON a FOR EACH ROW EXECUTE PROCEDURE b_mv_a_it();
2. trigger ของ table b มีดงั นี้ 2.1 trigger b_mv_ut จะทำางานหลังจากมีการ update data ใน table b โดยเรียกใช้ function b_mv_b_ut (ซ่ึงจะ เรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE FUNCTION b_mv_b_ut() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN IF OLD.b_id = NEW.b_id THEN PERFORM b_mv_refresh_row(NEW.b_id);
Copyright © 2009 by Peekanung 30 ELSE PERFORM b_mv_refresh_row(OLD.b_id); PERFORM b_mv_refresh_row(NEW.b_id); END IF; RETURN NULL; END'; SQL > CREATE TRIGGER b_mv_ut AFTER UPDATE ON b FOR EACH ROW EXECUTE PROCEDURE b_mv_b_ut(); 2.2 trigger b_mv_dt จะทำางานหลังจากมีการ delete data ใน table b โดยเรียกใช้ function b_mv_b_dt (ซ่ึงจะ เรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE FUNCTION b_mv_b_dt() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN PERFORM b_mv_refresh_row(OLD.b_id); RETURN NULL; END'; SQL > CREATE TRIGGER b_mv_dt AFTER DELETE ON b FOR EACH ROW EXECUTE PROCEDURE b_mv_b_dt(); 2.3 trigger b_mv_it จะทำางานหลังจากมีการ insert data ใน table b โดยเรียกใช้ function b_mv_b_it (ซ่ึงจะ เรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE FUNCTION b_mv_b_it() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN PERFORM b_mv_refresh_row(NEW.b_id); RETURN NULL; END'; SQL > CREATE TRIGGER b_mv_it AFTER INSERT ON b FOR EACH ROW EXECUTE PROCEDURE b_mv_b_it();
3. trigger ของ table c 3.1 trigger b_mv_ut จะทำางานหลังจากมีการ update data ใน table c โดยเรียกใช้ function b_mv_c_ut (ซ่ึงจะ เรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE FUNCTION b_mv_c_ut() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN IF OLD.b_id = NEW.b_id THEN PERFORM b_mv_refresh_row(NEW.b_id); ELSE PERFORM b_mv_refresh_row(OLD.b_id); PERFORM b_mv_refresh_row(NEW.b_id); END IF; RETURN NULL; END'; SQL > CREATE TRIGGER b_mv_ut AFTER UPDATE ON c FOR EACH ROW EXECUTE PROCEDURE b_mv_c_ut(); 3.2 trigger b_mv_dt จะทำางานหลังจากมีการ delete data ใน table c โดยเรียกใช้ function b_mv_c_dt (ซ่ึงจะ เรียกใช้ function b_mv_refresh_row อีกที) SQL >CREATE FUNCTION b_mv_c_dt() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN PERFORM b_mv_refresh_row(OLD.b_id);
Copyright © 2009 by Peekanung 31 RETURN NULL; END'; SQL > CREATE TRIGGER b_mv_dt AFTER DELETE ON c FOR EACH ROW EXECUTE PROCEDURE b_mv_c_dt(); 3.3 trigger b_mv_it จะทำางานหลังจากมีการ insert data ใน table c โดยเรียกใช้ function b_mv_c_it (ซ่ ึงจะ เรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE FUNCTION b_mv_c_it() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN PERFORM b_mv_refresh_row(NEW.b_id); RETURN NULL; END'; SQL > CREATE TRIGGER b_mv_it AFTER INSERT ON c FOR EACH ROW EXECUTE PROCEDURE b_mv_c_it(); ทดลอง Insert ข้อมูลเข้า table a, b และ c ตามลำาดับ SQL > INSERT INTO a VALUES (1,11); SQL > INSERT INTO a VALUES (2,22); SQL > INSERT INTO b VALUES (1,1,111,'2009-06-01'); SQL > INSERT INTO b VALUES (2,2,222,'2009-06-01'); SQL > INSERT INTO c VALUES (1,1,1111); SQL > INSERT INTO c VALUES (2,2,2222); SQL > INSERT INTO c VALUES(2,2,3333); ทดลอง Select ข้อมูลจาก table และ view SQL > SELECT * FROM a; a_id | v ------+---1 | 11 2 | 22 (2 rows) SQL > SELECT * FROM b; b_id | a_id | v | expires ------+------+-----+--------------------1 | 1 | 111 | 2009-04-25 00:00:00 2 | 2 | 222 | 2009-04-25 00:00:00 (2 rows) SQL > SELECT * FROM c; c_id | b_id | v ------+------+-----1 | 1 | 1111 2 | 2 | 2222 3 | 2 | 3333 (3 rows) SQL > SELECT * FROM b_mv; b_id | a_v | b_v | sum_c_v ------+-----+-----+--------1 | 11 | 111 | 1111 2 | 22 | 222 | 5555 (2 rows)
Copyright © 2009 by Peekanung 32 3.9.3 Lazy Lazy materialized view จะ update เม่ ือ transaction commit แล้ว ซ่ ึงจะเหมาะกับกรณี ท่ีข้อมูลในบาง row มีการ เปล่ียนแปลงบอย 3.9.4 Very lazy Very lazy materialized view จะ update เม่ ือมีการสัง่ refresh data แตการ update ข้อมูลจะทำาได้เร็วกวา และใช้ ทรัพยากรน้ อยกวา (1) สร้าง table matview_changes เพ่ ือเก็บข้อมูลการเปล่ียนแปลง materialized view ซ่ ึงประกอบด้วยข้อมูล OID และ primary key ของ materialized view SQL > CREATE TABLE matview_changes ( mv_oid OID PRIMARY KEY, pkey INTEGER NOT NULL ); (2) สร้าง function matview_queue_refresh_row เพ่ ือ insert oid และ primary key เข้า table matview_changes SQL > CREATE FUNCTION matview_queue_refresh_row(NAME, INTEGER) RETURNS VOID SECURITY DEFINER LANGUAGE 'plpgsql' AS ' DECLARE mv OID; test INTEGER; BEGIN SELECT INTO mv oid FROM matviews WHERE mv_name = $1; SELECT INTO test pkey FROM matview_changes WHERE matview_changes.mv_oid = mv AND matview_changes.pkey = $2; IF NOT FOUND THEN INSERT INTO matview_changes (mv_oid, pkey) VALUES (mv, $2); END IF; RETURN ; END '; (3) สร้าง function b_mv_refresh SQL > CREATE OR REPLACE FUNCTION b_mv_refresh() RETURNS VOID SECURITY DEFINER LANGUAGE 'plpgsql' AS ' DECLARE mv OID; BEGIN SELECT INTO mv oid FROM matviews WHERE mv_name = ''b_mv''; PERFORM b_mv_refresh_row(b_id) FROM b, matviews WHERE matviews.oid = mv AND b.expires >= matviews.last_refresh AND b.expires < now(); PERFORM b_mv_refresh_row(pkey) FROM matview_changes WHERE mv_oid = mv; UPDATE matviews SET last_refresh = now() WHERE mv_name = ''b_mv''; END ';
Copyright © 2009 by Peekanung 33 สร้าง trigger ให้ table a, b และ c 1. trigger ของ table a มีดงั นี้ 1.1 trigger b_mv_ut จะทำางานหลังจากมีการ update data ใน table a โดยเรียกใช้ function b_mv_a_ut (ซ่ึงจะเรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE OR REPLACE FUNCTION b_mv_a_ut() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN IF OLD.a_id = NEW.a_id THEN PERFORM matview_queue_refresh_row(''b_mv'', b.b_id) FROM b WHERE b.a_id = NEW.a_id; ELSE PERFORM matview_queue_refresh_row(''b_mv'', b.b_id) FROM b WHERE b.a_id = OLD.a_id; PERFORM matview_queue_refresh_row(''b_mv'', b.b_id) FROM b WHERE b.a_id = NEW.a_id; END IF; RETURN NULL; END '; SQL > CREATE TRIGGER b_mv_ut AFTER UPDATE ON a FOR EACH ROW EXECUTE PROCEDURE b_mv_a_ut(); 1.2 trigger b_mv_dt จะทำางานหลังจากมีการ delete data ใน table a โดยเรียกใช้ function b_mv_a_dt (ซ่ึงจะ เรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE OR REPLACE FUNCTION b_mv_a_dt() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN PERFORM matview_queue_refresh_row(''b_mv'', b.b_id) FROM b WHERE b.a_id = OLD.a_id; RETURN NULL; END '; SQL > CREATE TRIGGER b_mv_dt AFTER DELETE ON a FOR EACH ROW EXECUTE PROCEDURE b_mv_a_dt(); 1.3 trigger b_mv_it จะทำางานหลังจากมีการ insert data ใน table a โดยเรียกใช้ function b_mv_a_it (ซ่ึงจะ เรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE OR REPLACE FUNCTION b_mv_a_it() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN PERFORM matview_queue_refresh_row(''b_mv'', b.b_id) FROM b WHERE b.a_id = NEW.a_id; RETURN NULL; END '; SQL > CREATE TRIGGER b_mv_it AFTER INSERT ON a FOR EACH ROW EXECUTE PROCEDURE b_mv_a_it(); 2. trigger ของ table b มีดังนี้ 2.1 trigger b_mv_ut จะทำางานหลังจากมีการ update data ใน table b โดยเรียกใช้ function b_mv_b_ut (ซ่ึงจะ เรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE OR REPLACE FUNCTION b_mv_b_ut() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN IF OLD.b_id = NEW.b_id THEN PERFORM matview_queue_refresh_row(''b_mv'', NEW.b_id); ELSE PERFORM matview_queue_refresh_row(''b_mv'', OLD.b_id); PERFORM matview_queue_refresh_row(''b_mv'', NEW.b_id); END IF; RETURN NULL; END ';
Copyright © 2009 by Peekanung 34 SQL > CREATE TRIGGER b_mv_ut AFTER UPDATE ON b FOR EACH ROW EXECUTE PROCEDURE b_mv_b_ut(); 2.2 trigger b_mv_dt จะทำางานหลังจากมีการ delete data ใน table b โดยเรียกใช้ function b_mv_b_dt (ซ่ึงจะ เรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE OR REPLACE FUNCTION b_mv_b_dt() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN PERFORM matview_queue_refresh_row(''b_mv'', OLD.b_id); RETURN NULL; END '; SQL > CREATE TRIGGER b_mv_dt AFTER DELETE ON b FOR EACH ROW EXECUTE PROCEDURE b_mv_b_dt(); 2.3 trigger b_mv_it จะทำางานหลังจากมีการ insert data ใน table b โดยเรียกใช้ function b_mv_b_it (ซ่ึงจะ เรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE OR REPLACE FUNCTION b_mv_b_it() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN PERFORM matview_queue_refresh_row(''b_mv'', NEW.b_id); RETURN NULL; END '; SQL > CREATE TRIGGER b_mv_it AFTER INSERT ON b FOR EACH ROW EXECUTE PROCEDURE b_mv_b_it(); 3. trigger ของ table c 3.1 trigger b_mv_ut จะทำางานหลังจากมีการ update data ใน table c โดยเรียกใช้ function b_mv_c_ut (ซ่ึงจะ เรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE OR REPLACE FUNCTION b_mv_c_ut() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN IF OLD.b_id = NEW.b_id THEN PERFORM matview_queue_refresh_row(''b_mv'', NEW.b_id); ELSE PERFORM matview_queue_refresh_row(''b_mv'', OLD.b_id); PERFORM matview_queue_refresh_row(''b_mv'', NEW.b_id); END IF; RETURN NULL; END '; SQL > CREATE TRIGGER b_mv_ut AFTER UPDATE ON c FOR EACH ROW EXECUTE PROCEDURE b_mv_c_ut(); 3.2 trigger b_mv_dt จะทำางานหลังจากมีการ delete data ใน table c โดยเรียกใช้ function b_mv_c_dt (ซ่ึงจะ เรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE OR REPLACE FUNCTION b_mv_c_dt() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN PERFORM matview_queue_refresh_row(''b_mv'', OLD.b_id); RETURN NULL; END '; SQL > CREATE TRIGGER b_mv_dt AFTER DELETE ON c FOR EACH ROW EXECUTE PROCEDURE b_mv_c_dt();
Copyright © 2009 by Peekanung 35 3.3 trigger b_mv_it จะทำางานหลังจากมีการ insert data ใน table c โดยเรียกใช้ function b_mv_c_it (ซ่ ึงจะ เรียกใช้ function b_mv_refresh_row อีกที) SQL > CREATE OR REPLACE FUNCTION b_mv_c_it() RETURNS TRIGGER SECURITY DEFINER LANGUAGE 'plpgsql' AS ' BEGIN PERFORM matview_queue_refresh_row(''b_mv'', NEW.b_id); RETURN NULL; END '; SQL > CREATE TRIGGER b_mv_it AFTER INSERT ON c FOR EACH ROW EXECUTE PROCEDURE b_mv_c_it();
3.10 การสำารองข้อม้ล (ฺBackup / Restore) การสำารองข้อมูล เปนส่ิงท่ค ี วรทำาอยางสมำาเสมอในการใช้งานฐานข้อมูล ย่งิ ทำาการสำารองข้อมูลบอยเทาไร ก็จะสามา รถปองกันความเสียหายท่ีอาจจะเกิดขึ้นกับฐานข้อมูล และสามารถนำ าข้อมูลท่ีมีความทันสมัยกลับคืนมาได้มากเทานั ้น โดยมีวิธีการ ในการสำารองข้อมูลได้ 3 วิธี คือ • SQL dump • File system level backup • On-line backup ซ่ึงในท่ีนี้จะกลาวถึงเพียงวิธีเดียวคือ SQL dump 3.10.1 Backup คำาสัง่ pg_dump เปนคำาสัง่ท่ีนำาเอาฐานข้อมูลท่ีระบุออกมาเปน Text File หรือรูปแบบอ่ ืนๆท่ีระบุ คำาสัง่ pg_dumpall เป็ นคำาสัง่ท่ีสำารองทุกฐานข้อมูล ออกมาเป็ น Text File หรือรูปแบบอ่ ืนๆ ท่ีระบุ $ PG_DUMP [options] dbname > outfile
$ PG_DUMPALL [options] > outfile Options: -a --data-only -b --blob -c --clean
สำาเนาเฉพาะข้อมูล ไมเอาโครงสร้าง สำาเนา large objects ด้วย (จะเป็ นคา default เม่ ือระบุ--schema, --table, or –schema-only) กำาหนดให้ output สร้างคำาสัง่ drop database กอน create (ใช้ได้เฉพาะกรณี ท่ี output เป็ น plain-text format เทานั ้น) -C --create กำาหนดให้ output สร้างคำาสัง่สำาหรับสร้าง database ไว้ด้วย (ใช้ได้เฉพาะกรณี ท่ี output เป็ น plain-text format เทานั ้น) -d --insert ให้นำาข้อมูลเข้าโดยใช้คำาสัง่ INSERT แทนคำาสัง่ COPY ของ PostgreSQL ซ่ ึงจะทำาให้นำาไปใช้กับ ระบบฐานข้อมูลอ่ ืน เชน oracle ได้ -D --column-inserts เหมือน -d แตมีการระบุ Column ด้วย -E --encoding ระบุ character set encoding (default คาจะเป็ นคาเดียวกับ database ท่ี dump) -f --file=file เก็บข้อมูลลงแฟมท่ีกำาหนด -F, format {c|t|p} เก็บข้อมูลลงแฟมตามรูปแบบท่ีกำาหนด โดย c (custom) : กำาหนด output เอง (เป็ น default)เชน output.dmp , output.dump ,... t ( tar) : กำาหนดเป็ น tar archive p (plain) : เป็ นคา default, output เป็ น plain-text SQL scripts -h --host database server host name -n --schema สำาเนาเฉพาะ schema ท่ีกำาหนด -N --exclude schema ไมสำาเนา schema ท่ีกำาหนด -O --no owner ไมตัง้คาเจ้าของ ให้ใช้เหมือนเจ้าของเดิม -o --oids สำาเนา oids ออกมาด้วย -p --port ระบุหมายเลข port ของ database server -s --schema-only สำาเนาเฉพาะโครงสร้าง schema ไมสำาเนาข้อมูล -S --superuser ระบุ superuser เพ่ ือใช้ disable trigger (กรณี ท่ีต้องมีการ disable trigger) -t --table สำาเนาเฉพาะ table ท่ีกำาหนด -T --exclude table ไมสำาเนา table ท่ีกำาหนด
Copyright © 2009 by Peekanung 36 -U --username -v --verbose -W --password -x --no privileges -z --compress 0..9 ตัวอยาง
กำาหนดช่ ือ user ท่ีใช้ในการติดตอ กำาหนดให้ dump file แสดงรายละเอียดตางๆของการ dump ด้วยเชน message , start/stop time กำาหนดให้มีการใส password กอน connect เข้า database ไมสำาเนาคำาสัง่เก่ียวกับสิทธิการใช้งาน (grant/revoke) ระบุการบีบอัด โดย 0 คือไมมีการบีบอัด (default คือ ไมบีบอัด) และ 9 คือระดับสูงสุด
$ pg_dump pis -f /tmp/pis.dump // dump database 'pis' ออกมาโดยเก็บไว้ท่ี /tmp ช่ ือไฟล์ pis.dump $ pg_dump –c –Fc –Z9 pis > pis.dump // dump database 'pis' ออกมาโดยใช้ช่ือไฟล์ pis.dump โดยเพ่ิมคำาสัง่ drop database ก่อน สัง่ create , กำาหนด output file แบบกำาหนดเอง และให้มีการบีบอัดแบบส่งสุด $ pg_dump –U postgres –c –Fc –Z9 pis > pis.dump // dump database 'pis' ออกมา โดยใช้ช่ือไฟล์ pis.dump กำาหนด username ท่ีใช้ติดตอคือ postgres กำาหนด output file แบบกำาหนดเอง และให้มีการบีบอัดแบบส่งสุด
3.10.2 Restore การนำ าข้อมูลสำารองท่ีได้จาก pg_dump ติดตัง้กลับเข้าฐานข้อมูลนั ้น ต้องพิจารณาใช้คำาสัง่ ท่ีใช้ในการนำ าเข้าจากชนิ ดของ แฟมข้อมูลสำารอง คือ • ถ้าแฟมข้อมูลสำารองอยูในรูปบีบอัด(สร้างจาก option –Fc หรือ –Ft) ให้ใช้คำาสัง่ $ PG_RESTORE [options..] [filename] • นอกจากนั ้นใช้คำาสัง่ psql $ psql dbname < infile Options: -a --data-only -c --clean - C --create -d --database-name -e --exit-on-error
restore เฉพาะข้อมูล ไมเอาโครงสร้าง กำาหนดให้ drop database กอนทำาการ restore create database กอน install connect เข้า database ท่ีกำาหนดไว้แล้ว restore ลงใน database นั ้น ออกจากการทำางานทันทีเม่ ือเกิด error (default คือ ทำางานตอไปแล้่วแสดง error ออกมา) -f --file ระบุ output จากการ restore ออกมาเป็ นไฟล์ -F, format {c|t|} restore ข้อมูล ตามรูปแบบท่ีกำาหนด ปกติถ้าไมกำาหนดคำาสัง่ pg_restore จะตรวจสอบให้อัตโนมัติ -i --ignore-version ไมตรวจสอบ version ของ database -I --index restore เฉพาะ index เทานั ้น -l --list แสดงรายละเอียดของไฟล์ -n --namespace restore เฉพาะ object ท่ีอยูใน schema ท่ีระบุไว้ -O --no-owner ไมต้องตัง้คาเจ้าของให้เหมือนเจ้าของเดิม -h --host ระบุ database server host name -p --port ระบุหมายเลข port ของ database server -s --schema-only restore เฉพาะโครงสร้าง schema ไมสำาเนาข้อมูล -S --superuser ระบุ superuser เพ่ ือใช้ disable trigger -t --table restore เฉพาะ table ท่ีกำาหนด -U --username กำาหนดช่ ือ user ท่ีใช้ในการติดตอ -W --password กำาหนดให้มีการใส password กอน connect เข้า database -x --no privileges ไม restore คำาสัง่เก่ียวกับสิทธิการใช้งาน (grant/revoke)
ตัวอยาง
$ pg_restore -C -d postgres db.dump // restore database โดยสัง่ให้มีการ create database ก่อน restore และระบุช่ือ database เป็ น postgres
ตัวอยางการ backup และ restore $ pg_dump test > /tmp/test.dump // backup database ข่ ือ 'test' ไว้ท่ี /tmp/test.dump
Copyright © 2009 by Peekanung 37 $ createdb newtest // สร้าง database รอไว้ ช่ ือ newtest $ psql newtest < /tmp/test.dump // restore เข้า database 'newtest' จากท่ี backup ไว้
3.11 Database Maintenance การบำารุงรักษา Database ด้วย Routine Vacuuming มีวัตถุประสงค์ดังนี้
1. เพ่ ือให้ database ทำางานได้ตามปกติ 2. เพ่มิ ความเร็วในการทำางานของ database 3. กู้คน ื พ้ืนท่ี disk ท่ีถูกใช้ในการ Update/Delete
เม่ ือมีการ Update/Delete ข้อมูล postgreSQL จะยังคงเก็บข้อมูล version เกาๆเอาไว้ (เป็ นการทำางานของ Multiversion Concurrency Control : MVCC) ข้อมูลเกาท่ีถูกเก็บไว้นี้จะใช้พ้ืนท่ี disk เพ่ิ่มขึ้นเร่ ือยๆ ซ่ ึงจำาเป็ นต้องมีการกู้ พ้ืนท่ี disk ออกมาเพ่ ือใช้สำาหรับข้อมูลใหม และเพ่ ือป้ องกันการโตขึ้นของพ้ืนท่ี disk สามารถทำาได้โดยการรัน VACUUM การรัน VACUUM เหมาะสำาหรับ table ท่ีมีการ update/delete ข้อมูลจำานวนมาก หรือบอยครัง้ ซ่ ึงไมมีความจำาเป็ น นั กสำาหรับ table เล็กๆ หรือ table ท่ีไมคอยมีการเปล่ียนแปลงข้อมูล VACUUM แบงออกเป็ น 2 ประเภท คือ LAZY VACUUM และ VACUUM FULL 1. LAZY VACUUM lazy vacuum หรือ vacuum จะทำางานโดยการกำาหนด expire data เกาใน table และ index เพ่ ือใช้งานใหมใน อนาคต พ้ืนท่ี disk จะไมถูกคืนให้แก OS ข้อดีของ vacuum คือสามารถรันไปพร้อมๆกับการทำางานอ่ ืนๆของ database ได้ vacuum จึงเหมาะสำาหรับการสัง่ run แบบ routine เพราะไมมีผลกระทบตอการทำางานของ databa se และใข้ทรัพยากรในการ run น้ อย 2. VACUUM FULL เป็ นการเรียกคืนพ้ืนท่ีจากการ expire row version ซ่ ึงพ้ืนท่ีจากการสัง่ vacuum full นี้จะถูกคืนให้กับ OS ด้วย แตข้อ เสียคือ table ท่ีกำาลังทำา VACUUM FULL จะถูก lock จนกวาจะทำางานเสร็จ vacuum full เหมาะสำาหรับกรณี ท่ีต้องการคืน พ้ืนท่ีให้แก OS หรือกรณี ท่ีมีการ update/delete ข้อมูลจำานวนมาก ซ่ ึงการทำา VACUUM FULL จะใช้เวลาทำานานกวา VACUUM คำาสั่ง
SQL > VACUUM [FULL] [FREEZE] [VERBOSE] [ tablename] SQL > VACUUM [FULL] [FREEZE] [VERBOSE] ANALYZE [ tablename] [(column [,...] )]]
กรณี ท่ีไมระบุช่ือ table จะหมายถึงสัง่ VACUUM ทุก ๆ table ใน database แตสำาหรับคำาสัง่ VACUUM ANALYZE จะต้องระบุช่ือ table ด้วยเสมอ parameters FREEZE VERBOSE ANALYZE
เทียบเทากับการ set parameters 'vacuum_freeze_min_age' = 0 เป็ นการสัง่ cutoff age ของ transaction แสดงรายละเอียดการทำางานของ VACUUM ในแตละ table update คาสถิติเพ่ ือใช้ในการตัดสินใจเก่ียวกับประสิทธิภาพของ query
