PostgreSQL Database Server
สารบัญ
การใช้
PostgreSQL กับ Web Page
การใช้คำสั่ง
สร้าง database และ สร้าง ผู้ใช้
การสร้างตารางและกำหนดสิทธิ์ของผู้ใช้
การใช้งาน
PostgreSQL ผ่าน ODBC โดยใช้ MS
Access
การใช้ควบคุม
Postgress ผ่าน Web site
PostgreSQL คืออะไร
เป็น Object-Relational DBMS โดยสามารถใช้รูปแบบของภาษา SQL ได้เกือบทั้งหมด และสามารถใช้ subselects , transactions , user-defined types และ functions ได้ อีกทั้งเป็น Database ซึ่งให้ Source code ฟรี ด้วย
ประวัติการพัฒนา
· เป็น Project ของ Prof. Michael Stonebraker ที่ มหาวิทยาลัย Berkeley
· ซึ่งเดิมพัฒนามาจาก Ingres ซึ่งเป็นที่รู้จักในชื่อ CA-Ingres II ซึ่ง Ingres ใช้ ภาษา query , QUEL เป็นภาษาของตัวเอง ปัจจุบันได้หยุดพัฒนาไปแล้ว แต่ยังสามารถนำมาใช้ได้ฟรี
· ต่อมา Prof. Stonebraker ได้นำมาพัฒนาเป็น Postgres (มาจาก after Ingres) ซึ่งได้ใช้ภาษา query เป็น POSTQUEL เป็น Postgres version 4.2
· ต่อมาในช่วง ปี 1987 Postgres ได้เสนอ rules , procedures , time travel , extensible types และ object-relational concepts
· Postgres ถูกนำมาใช้ เพื่อการค้า ในชื่อว่า Illustra (ปัจจุบัน ถูก Informix ซื้อไป และรวมเข้าไว้ใน Universal Server)
· ต่อมา นักศึกษาปริญญาเอก 2 คน คือ Andrew Yu และ Jolly Chen ได้พัฒนา Postgres ให้ใช้ภาษา query ตามรูปแบบที่เป็นมาตราฐาน แทนที่ ภาษา POSTQUEL เดิม ซึ่งได้เผยแพร่ในปี 1995 จึงเรียกเป็น Postgres95 หรือ version 5 หลังจากนั้น การพัฒนาต่อ โดย กลุ่มพัฒนาทาง Internet
· ปัจจุบัน เปลี่ยนชื่อเป็น PostgreSQL พัฒนาต่อเนื่องเป็น version 7.1
ลักษณะโครงสร้าง
ระบบที่ใช้ PostgreSQL จะติดตั้ง PostgreSQL ไว้ที่เครื่อง Server ซึ่งเป็นที่เก็บ database ด้วย และยังสามารถ ติดตั้ง PostgreSQL ได้มากกว่า 1 ชุดใน Server เครื่องเดียว
ผู้ดูแลระบบ PostgreSQL จะใช้ชื่อว่า postgres ซึ่งเป็นผู้ดูแลทั้ง ตัวโปรแกรม และ database ซึ่งสามารถทำงานกับบางคำสั่งเฉพาะ เพื่อจัดการ database และ ผู้ใช้บริการ (user) ซึ่ง ผู้ดูแลระบบ database (postgres) จะคล้ายการทำงานของ superuser ในระบบ Unix หน้าที่ของ postgres สามารถ สร้างชื่อ user และกำหนดสิทธิและระดับการใช้งานต่างๆได้
PostgreSQL ใช้รูปแบบการทำงาน แบบ Client/Server ซึ่งในการทำงานจะประกอบด้วย 3 process ทำงานร่วมกัน คือ
1. Postmaster เป็น supervisory daemon process ซึ่งจัดการติดต่อระหว่าง Frontend กับ Backend process ในการ allocate share buffer , จัดการค่าเริ่มต้นต่างๆในระหว่างเริ่มทำงาน และเก็บบันทึกการเข้าใช้ระบบและความผิดพลาดต่างๆที่เกิดขึ้น
2. Postgres เป็น backend process เพื่อจัดการ database ถือว่าส่วนนี้เป็น process ที่ทำงานจริงๆ เช่น ทำงานตาม query โดย Postmaster จะสั่งให้สร้าง Backend process สำหรับทุกๆ การเชื่อมต่อกับ Frontend ดังนั้น Postgres นี้จะทำงานที่ server
3. Frontend เป็น application ซึ่งจะทำงานที่เครื่อง client และจะส่งคำสั่งการเชื่อมต่อ หรือคำสั่งต่างๆ มาที่ Postmaster แล้ว Postmaster จึงส่งต่อการทำงานไปที่ Postgres
หลักการทำงานของ PostgreSQL
การทำงานจะแบ่ง process ที่ทำงานดังที่กล่าวมาแล้ว คือ
1. ในส่วนของ Supervisory daemon process คือ Postmaster
2. ในส่วนของ User's Frontend application เช่น โปรแกรม psql หรือ CGI-Perl
3. และในส่วน Backend database servers คือ Postgres
· เมื่อโปรแกรม ทาง Frontend ต้องการข้อมูล หรือทำงานกับ database โดยเรียกผ่านทาง library libpq ซึ่ง library libpq นี้ จะส่ง requests ผ่านทาง Network ไปยัง Postmaster เมื่อ Postmaster ได้รับ request ดังกล่าว ทาง Postmaster จะสร้าง Backend process ขึ้นที่ server เพื่อติดต่อกับ Frontend แทน การทำงานนั้นจะเกิดขึ้นระหว่าง Frontend กับ Backend โดยไม่ผ่าน Postmaster อีก และ Postmaster ก็ทำงานต่อไป คือรอรับ request อื่นๆต่อไป
· Library libpq จะให้ หนึ่ง Frontend สามารถติดต่อได้หลาย Backend processes แต่การทำงานยังเป็นแบบ single threaded เนื่องจาก library libpq ยังไม่สามารถทำ multithreaded ได้
· ตามหลักการที่กล่าวมา ดังนั้น Postmaster กับ Backend จะต้องทำงานอยู่ที่ เครื่องเดียวกัน คือ database server แต่ Frontend จะทำงานที่เครื่องใดก็ได้
รูปแสดงการเชื่อมต่อระหว่าง
client กับ server
ภาษา query ที่ใช้
ภาษา query ที่ PostgreSQL ใช้อิงตามมาตราฐาน SQL3 ซึ่งกำลังจะเป็น มาตราฐานของภาษา Query โดยมีการเพิ่มคุณสมบัติต่างๆ เช่น extensible type system, inheritance, functions และ production rules จากภาษา POSTQUEL เดิม และยังเป็นไปตามมาตราฐาน ANSI SQL ซึ่งประกอบด้วยคำสั่ง คือ
· abort transaction, alter table, begin transaction, change acl, close, copy, create aggregate, create database, create function, create index, create operator, create rule, create table, create type, create version, create view, declare, delete, drop aggregate, drop database, drop function, drop index, drop operator, drop rule, drop table, drop type, drop view, end transaction, extend index, fetch, grant, insert, load, purge, revoke, select, update, vacuum
· expressions สามารถเป็นได้ทั้ง arbitrary arithmetic, logical , functional expressions , aggrgates.
SQL References
SQL Reference Books
1. The Practical SQL Handbook, Judity Bowman, Sandra Emerson, and Marcy Damovsky, ISBN: 0-201-44787-8, 1996, Addison-Wesley.
2. A Guide to the SQL Standard, C. J. Date and Hugh Darwen, ISBN: 0-201-96426-0, 1997, Addison-Wesley.
3. Understanding the New SQL, Jim Melton and Alan R. Simon, ISBN: 1-55860-245-3, 1993, Morgan Kaufmann.
PostgreSQL-Specific Documentation
1. The PostgreSQL Administrator's Guide, 1998-10-01, The PostgreSQL Global Development Group.
2. The PostgreSQL Developer's Guide, 1998-10-01, The PostgreSQL Global Development Group.
3. The PostgreSQL Programmer's Guide, 1998-10-01, The PostgreSQL Global Development Group.
4. The PostgreSQL Tutorial Introduction, 1998-10-01, The PostgreSQL Global Development Group.
5. The PostgreSQL User's Guide, 1998-10-01, The PostgreSQL Global Development Group.
6. The Postgres95 User Manual, A. Yu and J. Chen, The POSTGRES Group, Sept. 5, 1995, University of California, Berkeley CA.
Proceedings and Articles
1. PostgreSQL DBMS กับการนำมาใช้กับ WWW, นายณรงศักดิ์ พิมพ์พรรณชาติ
2. Partial indexing in POSTGRES : research project, Nels Olson, 1993, ISSN: UCB Engin T7.49.1993 O676, University of California, Berkeley CA.
3. A Unified Framework for Version Modeling Using Production Rules in a Database System, L. Ong and J. Goh, April, 1990, ISSN: ERL Technical Memorandum M90/33, University of California, Berkeley CA.
4. The Postgres Data Model, L. Rowe and M. Stonebraker.
5. Generalized partial indexes, P. Seshadri and A. Swami, 1995, ISSN: Cat. No.95CH35724, IEEE Computer Society Press.
6. The Design of Postgres, M. Stonebraker and L. Rowe.
7. The Design of the Postgres Rules System, M. Stonebraker, E. Hanson, and C. H. Hong.
8. The Postgres Storage System, M. Stonebraker.
9. A Commentary on the Postgres Rules System, M. Stonebraker, M. Hearst, and S. Potamianos.
10. The case for partial indexes (DBMS), M. Stonebraker.
11. The Implementation of Postgres, M. Stonebraker, L. A. Rowe, and M. Hirohama.
12. On Rules, Procedures, Caching and Views in Database Systems, M. Stonebraker and et al.
HOMEPAGE of
PostgreSQL http://www.postgresql.org
การใช้ PostgreSQL กับ Web Page
ลักษณะการทำงานเป็นดังรูป
การติดตั้ง PostgreSQL
PostgreSQL 7.1 (http://www.postgresql.org)
Compiling and installing new postgres.
1. create account postgres (and supposed with group postgres)
2.
gunzip c postgresql-v7.1 | tar xvf
3.
cd postgresql-v7.1/src
4.
./configure prefix=/usr/local/pgsql with-perl
with-php with-odbc with-tcl with-libs=/usr/lib
5.
if with-tcl then add -L/usr/lib lcryptto the line
SHLIB_LINK in the file Makefile under src/interfaces/libpgtcl since this is a
bug in configure command)
6.
make all
7.
mkdir /usr/local/pgsql
8.
make install
9. if with-perl then cd src/interfaces/perl5 and then perl Makefile.PL and make install
10. also install man pages by cd postgresql-v6.4.2/doc and make install
11.
chown R postgres:postgres /usr/local/pgsql
12.
setenv LD_LIBRARY_PATH /usr/local/pgsql/lib
13. add /usr/local/pgsql/lib line to /etc/ld.so.conf and then run /sbin/ldconfig
14.
initdb
(this will create data directory under /usr/local/pgsql as
/usr/local/pgsql/data and create template database named template1)
15.
cp postgres.init /etc/rc.d/init.d/postgresql
16.
cd /etc/rc.d/rc3.d; ln s ../init.d/postgresql
S85postgresql
17.
cd /etc/rc.d/rc4.d; ln s ../init.d/postgresql
S85postgresql
18. cd /etc/rc.d/rc5.d; ln s ../init.d/postgresql S85postgresql
19.
add these lines to init script under user home directory
PGLIB=/usr/local/pgsql/lib
PGDATA=/usr/local/pgsql/data
การใช้คำสั่ง สร้าง database และ สร้าง ผู้ใช้
การสร้าง ผู้ใช้ใหม่ ทำโดยใช้ login postgres และใช้คำสั่ง createuser จากนั้นป้อนรายละเอียดของผู้ใช้ โดยสามารถใช้ชื่อเดียวกับ ชื่อของ unix ก็ได้แต่ไม่ควร เหตุผลในเรื่องความปลอดภัย และไม่ควรกำหนดให้ผู้ใช้นั้น สามารถสร้าง ผู้ใช้อื่น ต่อได้อีก
การสร้าง database ใหม่ ทำโดยใช้คำสั่ง createdb โดยควรจะ login เป็น ชื่อผู้ใช้ที่จะเป็นเจ้าของ database นั้น
การสร้างตารางและกำหนดสิทธิ์ของผู้ใช้
การกำหนด ตารางที่ใช้ จะกำหนดขึ้นภายหลัง และจะต้อง grant สิทธิการใช้จากตาราง โดยมีข้อกำหนดดังนี้
1. admin ของแต่ละหน่วยงาน สามารถ เพิ่ม/ลบ/แก้ไข ตารางได้ , สามารถ เรียกดู/เพิ่ม/ลบ/แก้ไข ข้อมูลในตารางได้ ทำโดย
admin สร้าง table ใน database จะมีสิทธิในการ ในเพิ่ม/ลบ table อยู่แล้ว จากนั้น admin กำหนดสิทธิ์ในการเข้าใช้ตาราง คือ
REVOKE {select,insert,update,delete,rule,all} ON {table,view,sequence,index} FROM {public | GROUP group | username} เช่น
REVOKE all ON rainfall_table FROM rid_user ;
GRANT {select,insert,update,delete,rule,all} ON {table,view,sequence,index} TO {public | GROUP group | username} เช่น
GRANT insert,update,delete,select ON rainfall_table TO rid_user ;
GRANT select ON rainfall_table TO rid_nobody ;
การใช้งาน PostgreSQL ผ่าน ODBC โดยใช้ MS Access
ความต้องการของระบบ
1. PostgreSQL ODBC Driver จาก http://www.insightdist.com/psqlodbc/ โดย Postdrv.exe
2. MS Windows , MS Access , ODBC
การติดตั้ง
1. ติดตั้ง Postdrv.exe ใน Windows
2. ไปที่ Control Panel -> 32 bit ODBC -> File DSN -> Add -> เลือก PostgreSQL -> Next -> ใส่ชื่อที่ต้องการ -> Finish -> ใส่ข้อมูลของ Database ที่ต้องการ -> Connection -> ให้ใส่รายละเอียดให้ถูกต้อง
การใช้งาน
1. เปิด MS Access แล้วสร้างฐานข้อมูลใหม่
2. Import ตาราง โดย ไปที่ เมนู -> แฟ้ม -> รับข้อมูลภายนอก -> เชื่อมโยงตาราง -> เปลี่ยนชนิดของแฟ้มเป็น ODBC -> เลือก database ที่จะใช้กับ Postgres
การกำหนดความปลอดภัย
การกำหนดสิทธิ ของ postgres user โดยแก้ไข /usr/local/pgsql/pg_hba.conf หรือ /var/lib/pgsql/data/pg_hba.conf โดย เพิ่มบรรทัดดังนี้
host database IP ที่อนุญาต subnet mask password password file
host all 203.151.120.0 255.255.255.0 password postgres_password
นั่นคือ user จาก IP 203.151.120.X สามารถใช้งานได้ทุก database โดยใช้ user ตามใน postgres_password (รายละเอียดศึกษาได้จาก pg_hba.conf)
การใช้ควบคุม Postgress ผ่าน Web site
· # tar xvf phpPgAdmin.x.x.tar ที่ home ของ web server
· ที่ Browes พิมพ์ http://web_server/phpPgAdmin/
· Username : postgres database template1