หัดใช้ PostgreSQL

ช่วงนี้เหตุจำเป็นต้องใช้ฐานข้อมูล PostgreSQL  เลยลองติดตั้ง และหัดใช้กันซะหน่อย เห็นว่าน่าจะมีประโยชน์จึงมาเผยแพร่ให้อ่านกัน

เนื่องจากคุ้นเคยกับ MySQL มานาน ในแต่ละหัวข้อจะเปรียบเทียบกับคำสั่ง mysql

กรุณาสังเกต prompt ที่รันคำสั่ง จะมีอยู่ 2 โหมด คือ UNIX shell และ postgres ที่เข้าได้ด้วยคำสั่ง psql

ติดตั้ง postgresql จากไฟล์ rpm

บทความนี้ทดสอบบน Fedora 14

ติดตั้งจากไฟล์ rpm ที่อยู่ในแผ่นติดตั้ง Fedora 14

[root@fc14-64a ~]# mount /dev/cdrom /media
mount: block device /dev/sr0 is write-protected, mounting read-only

ไฟล์ที่ต้องติดตั้งมี 3 ไฟล์ ดังนี้

[root@fc14-64a ~]# cd /media/Packages/
[root@fc14-64a Packages]# ls -l postgresql-*
-rw-r--r-- 6 root 101737 2804052 Oct  6 22:31 postgresql-8.4.5-1.fc14.x86_64.rpm
-rw-r--r-- 6 root 101737  197124 Oct  6 22:26 postgresql-libs-8.4.5-1.fc14.x86_64.rpm
-rw-r--r-- 6 root 101737 3409060 Oct  6 22:31 postgresql-server-8.4.5-1.fc14.x86_64.rpm

ใช้คำสั่ง rpm เพื่อติดตั้ง

[root@fc14-64a Packages]# rpm -i postgresql-libs-8.4.5-1.fc14.x86_64.rpm
[root@fc14-64a Packages]# rpm -i postgresql-8.4.5-1.fc14.x86_64.rpm
[root@fc14-64a Packages]# rpm -i postgresql-server-8.4.5-1.fc14.x86_64.rpm
[root@fc14-64a ~]# umount /media

รันเซอร์วิส postgresql (server)

ใช้คำสั่ง service เพื่อรัน postgresql

[root@fc14-64a ~]# service postgresql start
/var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first.
                                                           [FAILED]

ไม่สามารถรันได้ เพราะยังไม่มีไฟล์ที่จำเป็นของฐานข้อมูล

[root@fc14-64a ~]# ls -l /var/lib/pgsql/data/
total 0

สร้างไฟล์ฐานข้อมูลเริ่มต้นด้วยคำสั่ง service initdb

[root@fc14-64a ~]# service postgresql initdb
Initializing database:                                     [  OK  ]

หลังจากรัน initdb จะมีไฟล์ถูกสร้างขึ้นในไดเรคทอรี /var/lib/pgsql/data/

[root@fc14-64a ~]# ls -l /var/lib/pgsql/data/
total 36
drwx------ 5 postgres postgres    38 Jan 30 17:29 base
drwx------ 2 postgres postgres  4096 Jan 30 17:29 global
drwx------ 2 postgres postgres    17 Jan 30 17:29 pg_clog
-rw------- 1 postgres postgres  3411 Jan 30 17:29 pg_hba.conf
-rw------- 1 postgres postgres  1631 Jan 30 17:29 pg_ident.conf
drwx------ 2 postgres postgres     6 Jan 30 17:29 pg_log
drwx------ 4 postgres postgres    34 Jan 30 17:29 pg_multixact
drwx------ 2 postgres postgres     6 Jan 30 17:29 pg_stat_tmp
drwx------ 2 postgres postgres    17 Jan 30 17:29 pg_subtrans
drwx------ 2 postgres postgres     6 Jan 30 17:29 pg_tblspc
drwx------ 2 postgres postgres     6 Jan 30 17:29 pg_twophase
-rw------- 1 postgres postgres     4 Jan 30 17:29 PG_VERSION
drwx------ 3 postgres postgres    58 Jan 30 17:29 pg_xlog
-rw------- 1 postgres postgres 16867 Jan 30 17:29 postgresql.conf

ใช้คำสั่ง service เพื่อรันเซอร์วิส postgresql อีกครั้ง

[root@fc14-64a ~]# service postgresql start
Starting postgresql service:                               [  OK  ]

ถึงตอนนี้เราก็ได้ postgresql server ไว้ลองใช้งานแล้ว ใช้คำสั่ง ps เพื่อดูโพรเซสของ postgresql

[root@fc14-64a ~]# ps -ef | grep postgres
postgres  1101     1  2 17:29 ?        00:00:00 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
postgres  1103  1101  0 17:29 ?        00:00:00 postgres: logger process
postgres  1105  1101  0 17:29 ?        00:00:00 postgres: writer process
postgres  1106  1101  0 17:29 ?        00:00:00 postgres: wal writer process
postgres  1107  1101  0 17:29 ?        00:00:00 postgres: autovacuum launcher process
postgres  1108  1101  0 17:29 ?        00:00:00 postgres: stats collector process

คำสั่ง psql (client)

ส่วนโปรแกรมฝั่ง client ต้องใช้คำสั่ง psql เพื่อติดต่อกับ postgresql server

ลองรันคำสั่ง psql ด้วย root จะขึ้น error ไม่สามารถเข้า database ได้

[root@fc14-64a ~]# psql
psql: FATAL:  Ident authentication failed for user "root"

ใช้คำสั่ง su เปลี่ยนเป็น postgres แล้วรันคำสั่ง psql อีกที จะเข้าโหมด client ได้

[root@fc14-64a ~]# su - postgres
-bash-4.1$ id
uid=26(postgres) gid=26(postgres) groups=26(postgres)
-bash-4.1$ psql
psql (8.4.5)
Type "help" for help.

พิมพ์ “\h” หรือ help เพื่อดูวิธีการใช้งานเบื้องต้น  พิมพ์ “\q” เพื่อออกจาก postgres กลับสู่ shell

เครื่องหมาย prompt # แสดงถึงการเข้าด้วย database superuser

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
postgres=# \q

-bash-4.1$

SHOW DATABASES

ในโหมด postgres พิมพ์ “\l” หรือ “SELECT * FROM pg_database;” เพื่อแสดง database ที่มีอยู่ หรือ

-bash-4.1$ psql
psql (8.4.5)
Type "help" for help.
postgres-# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 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
                                                             : postgres=CTc/postgres
(3 rows)
postgres=#
postgres=# SELECT * FROM pg_database;
  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datconfig |
datacl
-----------+--------+----------+-------------+-------------+---------------+--------------+--------------+---------------+--------------+---------------+-----------+---------------
----------------------
 template1 |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | t            |           -1 |         11563 |          648 |          1663 |           | {=c/postgres,p
ostgres=CTc/postgres}
 template0 |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | t             | f            |           -1 |         11563 |          648 |          1663 |           | {=c/postgres,p
ostgres=CTc/postgres}
 postgres  |     10 |        6 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |           -1 |         11563 |          648 |          1663 |           |
(3 rows)
postgres=#

 

CREATE DATABASE

การสร้าง database ใน postgresql ทำได้ด้วยคำสั่งบน shell คือ “createdb” ตามด้วยชื่อที่ต้องการสร้าง

ตัวอย่างเช่น ต้องการสร้างฐานข้อมูลชื่อ “testdb”

-bash-4.1$ createdb testdb

แสดงชื่อฐานข้อมูลที่สร้าง

-bash-4.1$ psql
psql (8.4.5)
Type "help" for help.
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 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
                                                             : postgres=CTc/postgres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

CREATE TABLE

บน shell พิมพ์คำสั่ง psql ตามด้วยชื่อ database เพื่อเข้าสู่ชื่อฐานข้อมูล สังเกตว่า prompt จะขึ้นชื่อ database ที่ใช้อยู่

-bash-4.1$ psql testdb
psql (8.4.5)
Type "help" for help.
testdb=#

ทดลองสร้าง table ชื่อ weather  รูปแบบก็เป็นมาตรฐาน SQL ทั่วไป

testdb=# CREATE TABLE weather (
testdb(#     city            varchar(80),
testdb(#     temp_lo         int,           -- low temperature
testdb(#     temp_hi         int,           -- high temperature
testdb(#     prcp            real,          -- precipitation
testdb(#     date            date
testdb(# );
CREATE TABLE
testdb=#

พิมพ์ “\d” หรือ “SELECT * FROM pg_tables WHERE schemaname=’public’;” เพื่อแสดงชื่อ table ที่มีอยู่

testdb=# \d
          List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | weather | table | postgres
(1 row)
testdb=# SELECT * FROM pg_tables WHERE schemaname='public';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+-----------+------------+------------+------------+----------+-------------
 public     | weather   | postgres   |            | f          | f        | f
(1 row)

ลองใส่ข้อมูลเข้า table ด้วย คำสั่ง INSERT และดึงข้อมูลด้วยคำสั่ง SELECT

testdb=# INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT 0 1
testdb=# SELECT * FROM weather;
     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
(1 row)

DROP TABLE

ลบ table ด้วยคำสั่ง “DROP TABLE”

testdb=# DROP TABLE weather;
DROP TABLE
testdb=# SELECT * FROM weather;
ERROR:  relation "weather" does not exist
LINE 1: SELECT * FROM weather;
                      ^
testdb=# \d
No relations found.

DROP DATABASE

ลบ database ด้วยคำสั่ง shell “dropdb”

-bash-4.1$ dropdb testdb

ข้อมูลอ้างอิง

Leave a Reply

Your email address will not be published.