Print-friendly Version

將資料庫由 MySQL 切換為 PostgreSQL 8.2

Scott Fehrman (2007 年 10 月)

目錄：

• 入門
• 設定環境
• 初始化資料庫
• 啟動資料庫服務
• 使用資料庫服務
• 停止資料庫
• 文件
• 使用管理工具
• 將 SQL 程序檔從 MySQL 轉換成 PostgreSQL
• 摘要
• Solaris 10 8/07 OS 中的 PostgreSQL 特性和增強功能
• 更多資訊

我偶爾會使用 MySQL。既然現在已經正式發行 Solaris 10 8/07 作業系統，其中含有 PostgreSQL 的最新支援，我決定給它一次機會，看看 MySQL 使用者如何著手使用 PostgreSQL。

以下是這一版 Solaris 10 中部份的 PostgreSQL 特性和增強功能：

• 提高效能
• 服務管理功能 (SMF) 整合
• 動態追蹤 (DTrace) 探查
• 具備 RBAC 支援之預先定義的 postgres 使用者
• 隨附的 JDBC 驅動程式

這一版 Solaris 10 中新的 PostgreSQL 特性和增強功能清單如下所示。Sun PostgreSQL 官方網頁上有更多資訊。

入門

PostgreSQL 服務預設為停用狀態：

# svcs postgresql
STATE          STIME    FMRI
disabled       Sep_06   svc:/application/database/postgresql:version_81
disabled       Sep_06   svc:/application/database/postgresql:version_82
#

注意：在進一步瞭解之前，請注意 postgresql 服務有兩個不同的實例。Solaris 10 (8/07) 作業系統可支援較新的 PostgreSQL 8.2 版以及之前的 PostgreSQL 8.1 版。本文件的內容只涵蓋 PostgreSQL 8.2 版。 如果您要使用新的資料庫專案，建議您使用 PostgreSQL 8.2 版。 請查看 Postgres 網站上的詳細資訊。

Solaris 10 8/07 作業系統也有預先配置的 postgres 使用者，並採用 Solaris RBAC 功能，以便此使用者/角色可以管理 SMF postgresql:version_82 服務。

# grep postgres /etc/user_attr
postgres::::type=role;profiles=Postgres Administration,All
# su - postgres
$ id
uid=90(postgres) gid=90(postgres)
$ profiles
Postgres Administration
All
Basic Solaris User
$ profiles -l

    Postgres Administration:
        /usr/postgres/8.2/bin/initdb            uid=postgres
        /usr/postgres/8.2/bin/ipcclean          uid=postgres
        /usr/postgres/8.2/bin/pg_controldata    uid=postgres
        /usr/postgres/8.2/bin/pg_ctl            uid=postgres
        /usr/postgres/8.2/bin/pg_resetxlog      uid=postgres
        /usr/postgres/8.2/bin/postgres          uid=postgres
        /usr/postgres/8.2/bin/postmaster        uid=postgres
....
$

必須先設定 PostgreSQL，才可加以使用。我們需要執行幾項簡單的作業：

• 設定環境 (主要是為了讓指令更簡單)，PATH、MANPATH 和 PGDATA。
• 初始化資料庫。
• 啟動資料庫服務。

設定環境 (8.2 版)

• 變更 PATH
  
  如上所述，Solaris 同時支援 PostgreSQL 8.1 和 8.2 版。如果預設 PATH 變數維持不變，則使用 Postgres 指令 (在 /usr/bin 中)。修改您的 PATH，將 /usr/postgres/8.2/bin 放在任何其他路徑名稱之前。您可以在使用者的登入程序檔中、在全域登入程序檔 (如/etc/profile) 中，或在指令行中設定 PATH：

  # echo $PATH
  /usr/sbin:/usr/bin
  # PATH=/usr/postgres/8.2/bin:
  # export PATH
  # echo $PATH
  /usr/postgres/8.2/bin:/usr/sbin:/usr/bin
  

• 變更 MANPATH
  
  如上所述，Solaris 同時支援 PostgreSQL 8.1 和 8.2 版。如果預設 MANPATH 變數維持不變，則使用 Postgres 線上手冊 (在 /usr/man 中)。修改您的 MANPATH，將 /usr/postgres/8.2/man 放在任何其他路徑名稱之前。您可以在使用者的登入程序檔中、在全域登入程序檔 (如/etc/profile) 中設定 MANPATH，或在指令行中設定：

  # echo $MANPATH
  /usr/man:/usr/sfw/man
  # MANPATH=/usr/postgres/8.2/man:
  # export MANPATH
  # echo $MANPATH
  /usr/postgres/8.2/man:/usr/man:/usr/sfw/man
  #
  

• 設定 PGDATA
  
  PostgreSQL 資料庫可使用一個稱為 PGDATA 的環境變數。許多 PostgreSQL 指令都使用這個變數。如果使用了 PGDATA，就必須將其設定為 PostgreSQL 資料目錄所在處的完全合格路徑名稱。PostgreSQL 資料的預設位置是 /var/postgres/8.2/data。您可將 PGDATA 設定為這個目錄或其他位置，然後對於需要知道資料目錄之路徑名稱的 PostgreSQL 指令，您就不必使用 -D 選項。

  $ PGDATA=/var/postgres/8.2/data
  $ export PGDATA
  $ echo $PGDATA
  /var/postgres/8.2/data
  $
  

初始化資料庫 (8.2 版)

PostgreSQL 資料庫需要「首次」初始化，才能予以啟動。使用 initdb 指令初始化資料庫。如需更多詳細資訊，請參閱 initdb 線上手冊。以 postgres 使用者身份執行 initdb 指令。在這個範例中，我是使用 initdb 指令和 PostgreSQL 資料目錄的完全合格路徑。如果您已設定 PATH 和 PGDATA 環境變數，應該只需要執行 $ initdb：

# su - postgres
$  /usr/postgres/8.2/bin/initdb -D /var/postgres/8.2/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale C.

fixing permissions on existing directory /var/postgres/8.2/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers/max_fsm_pages ... 32MB/204800
creating configuration files ... ok
creating template1 database in /var/postgres/8.2/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

    /usr/postgres/8.2/bin/postgres -D /var/postgres/8.2/data
or
    /usr/postgres/8.2/bin/pg_ctl -D /var/postgres/8.2/data -l logfile start

$ 

注意：initdb 指令的輸出會顯示如何啟動和停止資料庫。您可以忽略這些指令。我們將使用 Solaris 服務管理功能來管理 PostgreSQL 資料庫。

啟動資料庫服務 (8.2 版)

PostgreSQL 資料庫已準備進行啟動。使用 svcs 和 svcadm 指令 (以 postgres 使用者身份) 啟動、停止、監視服務：

# su - postgres

$ svcs postgresql
STATE          STIME    FMRI
disabled       Sep_06   svc:/application/database/postgresql:version_81
disabled       14:06:11 svc:/application/database/postgresql:version_82

$ svcadm -v enable -s postgresql:version_82
svc:/application/database/postgresql:version_82 enabled.

$ svcs -l postgresql:version_82
fmri         svc:/application/database/postgresql:version_82
name         PostgreSQL RDBMS
enabled      true
state        online
next_state   none
state_time   Fri Sep 14 14:23:49 2007
logfile      /var/svc/log/application-database-postgresql:version_82.log
restarter    svc:/system/svc/restarter:default
contract_id  379 
dependency   require_all/none svc:/milestone/network:default (online)
dependency   require_all/none svc:/system/filesystem/local:default (online)

$

啟動系統時，Solaris 服務管理功能將自動啟動 PostgreSQL 資料庫。

使用資料庫 (8.2 版)

用於互動式指令行操作的主要 PostgreSQL 指令是 psql。如需 psql 詳細資訊，請參閱線上手冊。以下是 --help 選項的輸出 (我是使用 psql 指令的完全合格路徑，如果您的 PATH 設定正確，可以只使用指令名稱)：

$ /usr/postgres/8.2/bin/psql --help
This is psql 8.2.4, the PostgreSQL interactive terminal.

Usage:
  psql [OPTIONS]... [DBNAME [USERNAME]]

General options:
  -d DBNAME       specify database name to connect to (default: "postgres")
  -c COMMAND      run only single command (SQL or internal) and exit
  -f FILENAME     execute commands from file, then exit
  -1 ("one")      execute command file as a single transaction
  -l              list available databases, then exit
  -v NAME=VALUE   set psql variable NAME to VALUE
  -X              do not read startup file (~/.psqlrc)
  --help          show this help, then exit
  --version       output version information, then exit

Input and output options:
  -a              echo all input from script
  -e              echo commands sent to server
  -E              display queries that internal commands generate
  -q              run quietly (no messages, only query output)
  -o FILENAME     send query results to file (or |pipe)
  -n              disable enhanced command line editing (readline)
  -s              single-step mode (confirm each query)
  -S              single-line mode (end of line terminates SQL command)
  -L FILENAME     send session log to file

Output format options:
  -A              unaligned table output mode (-P format=unaligned)
  -H              HTML table output mode (-P format=html)
  -t              print rows only (-P tuples_only)
  -T TEXT         set HTML table tag attributes (width, border) (-P tableattr=)
  -x              turn on expanded table output (-P expanded)
  -P VAR[=ARG]    set printing option VAR to ARG (see \pset command)
  -F STRING       set field separator (default: "|") (-P fieldsep=)
  -R STRING       set record separator (default: newline) (-P recordsep=)

Connection options:
  -h HOSTNAME     database server host or socket directory (default: "local socket")
  -p PORT         database server port (default: "5432")
  -U NAME         database user name (default: "postgres")
  -W              prompt for password (should happen automatically)

For more information, type "\?" (for internal commands) or "\help"
(for SQL commands) from within psql, or consult the psql section in
the PostgreSQL documentation.

Report bugs to .
$ 

若要進入互動式終端機模式，請使用 psql 指令，而不需任何選項：

$ /usr/postgres/8.2/bin/psql
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

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=# 

postgres=# 提示表示 PostgreSQL 已準備就緒，正等待相關指示。網際網路上有許多 PostgreSQL 指導，所以我不打算詳細說明如何使用 PostgreSQL。PostgreSQL 文件網站上有一組完整的教學和參考文件。以下是幾個用來測試一些基本操作的指令。

建立表格：

postgres=# CREATE TABLE location 
( 
CODE        VARCHAR(8) NOT NULL PRIMARY KEY,
DESCRIPTION VARCHAR(32) NOT NULL
);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "location_pkey" for table "location"
CREATE TABLE

描述表格：

postgres=# \dt
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | location | table | postgres
(1 row)

填入表格：

postgres=# INSERT INTO location ( CODE, DESCRIPTION ) VALUES
('loc30','Austin, Texas'),
('loc31','New York, New York'),
('loc32','Chicago, Illinois'),
('loc33','Dallas, Texas'),
('loc34','San Jose, California'),
('loc35','Atlanta, Georgia'),
('loc99','Denver, Colorado');
INSERT 0 7

選取表格：

postgres=# select * from location;
 code  |     description
-------+----------------------
 loc30 | Austin, Texas
 loc31 | New York, New York
 loc32 | Chicago, Illinois
 loc33 | Dallas, Texas
 loc34 | San Jose, California
 loc35 | Atlanta, Georgia
 loc99 | Denver, Colorado
(7 rows)

刪除表格：

postgres=# drop table location;
DROP TABLE

停止資料庫 (8.2 版)

如果想停止 PostgreSQL 資料庫，可使用 Solaris 服務管理功能的 svcadm 指令：

# su - postgres
$ svcadm disable -s postgresql:version_82
$ svcs postgresql:version_82
STATE          STIME    FMRI
disabled       15:11:38 svc:/application/database/postgresql:version_82

文件

許多手冊都提供大量資訊。我建議由 postgres_82 線上手冊開始著手，其內容涵蓋 Solaris 實作特有的 PostgreSQL。以下是我所使用的部份線上手冊：

PostgreSQL 網站上有一組實用的線上文件：

• 手冊
• 版本說明
• 書籍

使用管理工具

對於想要使用圖形化使用者介面 (GUI) 的使用者而言，pgadmin3 相當理想。請至 pgAdmin 網站下載適用於 Solaris 的 pgadmin。SPARC 和 x86/x64 平台都有預先建立的套裝軟體。安裝很簡單。下載 tar.gz 檔，並存到暫時的位置。二進位碼會安裝於 /opt/bin

# gunzip pgadmin3-1.6.3_x86.tar.gz
# tar xf pgadmin3-1.6.3_x86.tar
# pkgadd -d . pgadmin3
# /opt/bin/pgadmin3

將 SQL 程序檔從 MySQL 轉換成 PostgreSQL

我有幾個使用 MySQL 的專案。我需要在 PostgreSQL 中有相同的功能。不幸地，這些程序檔不做些小修改就無法使用。以下是我所做的變更，這與 CREATE TABLE 指令有關。用於建立 TABLESPACE、DATABASE、SCHEMA 的指令則不成問題。

TIMESTAMP

DATETIME

TIMESTAMP(0) DEFAULT LOCALTIMESTAMP

TIMESTAMP(3)

AUTO_INCREMENT

SERIAL

BIGINT

NUMERIC(19,0)

MEDIUMTEXT

TEXT

摘要

從 MySQL 轉換為 PostgreSQL 很容易。我不需要：

• 安裝 MySQL 5.x。
• 建立 mysql 使用者/群組。
• 設定 RBAC 或建立 SMF 服務或 init 程序檔。
• 取得 JDBC 驅動程式。

Solaris 10 8/07 作業系統中的 PostgreSQL 特性和增強功能

以下是這一版 Solaris 10 中新的 PostgreSQL 特性和增強功能的簡介：

• 開放原始碼已免費整合並納入 Solaris 10 作業系統中。PostgreSQL 可輕鬆加以擴充及自訂，而不需任何授權費用，即可提供很高的價值。
• 最佳化並增強的效能充分利用領先的 Solaris 技術，並支援原生 DTrace 探查、預見式自我修復功能和 Solaris 容器。
• 由 Sun 提供支援。對於 PostgreSQL for Solaris，Sun 提供了遍及全球的全面性服務，包括全年無休的企業級支援。
• 可信賴且高度可用的功能，如多版本貨幣控制 (MVCC)、時間點回復和 ACID 完全相容。透過支援 Solaris 預見式自我修復功能和 Solaris 容器使這些功能更強大，目的是排除當機時間和資料損失的相關風險和成本。
• 建置時即考量到預設值就能提供安全，可支援多重驗證方法、ANSI 角色和權限及加密功能。
• 在高負載下仍有高效能和延伸性 -- 不僅是可管理的資料量，還有可同時容納的使用者數目。PostgreSQL for Solaris 核心程式碼可在本機支援 DTrace 探查，以增強效能瓶頸的可見度和加速解決。
• 具有 RBAC 支援之預先定義的 postgres 使用者
• 隨附的 JDBC 驅動程式

更多資訊

以下是其他相關資源：

• Sun 教育訓練 (http://www.sun.com/training/)
• 支援：
  • Sun 服務與解決方案
  • SunSolve Online
• 相關資源：
  • PostgreSQL 文件網站
  • BigAdmin 文章：OpenSolaris OS 中的 PostgreSQL
  • sun.com 上 適用於 Solaris 10 作業系統的 PostgreSQL 相關資訊
  • BigAdmin 系統管理入口網站的資料庫資源集合

Unless otherwise licensed, code in all technical manuals herein (including articles, FAQs, samples) is provided under this License.