2013年1月21日 星期一

[Ubuntu] 安裝PostgreSQL9.1步驟

雖然一開始Ubuntu就安裝了postgresql,但是在搞不清楚狀況的一開始我還是把它給砍了= =
然後再重裝一遍:
sudo apt-get install -y postgresql-9.1 postgresql-client-9.1 postgresql-contrib-9.1

相關的執行檔會被放在這個資料夾:
/usr/lib/postgresql/9.1/bin

在安裝時一直遇到找不到放置PGDATA的錯誤訊息,所以我們要在9.1資料夾下創建一個"data"資料夾,並更改擁有者為postgres:
mkdir /usr/lib/postgresql/9.1/data
chown postgres /usr/lib/postgresql/9.1/data
此時再執行su postgres,切換為postgres身分,在/usr/lib/postgresql/9.1/bin的路徑下執行:
./initdb -D mkdir ../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 en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

fixing permissions on existing directory ../data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 24MB
creating configuration files ... ok
creating template1 database in ../data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... 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:

    ./postgres -D ../data
or
    ./pg_ctl -D ../data -l logfile start


接著我們就可以執行
./postgres -D ../data

./pg_ctl -D ../data -l logfile start
來啟動服務了。

Postgresql這時候已經可以接受連線,我們來執行createdb指令來新增一個資料庫:
postgres@li440-29:/usr/lib/postgresql/9.1/bin$ createdb test

然後執行psql -d test來進入console,如果直接執行psql是會直接進入預設的postgres資料庫的,快樂的打指令之前請先確認一下所在的DB。

而pg_ctl是用來對postgres做操作的控制台,可以執行"pg_ctl --help"來查看它的功能,這邊先列出一部分:
Usage:
  pg_ctl init[db]               [-D DATADIR] [-s] [-o "OPTIONS"]
  pg_ctl start   [-w] [-t SECS] [-D DATADIR] [-s] [-l FILENAME] [-o "OPTIONS"]
  pg_ctl stop    [-W] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
  pg_ctl restart [-w] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN-MODE]
                 [-o "OPTIONS"]
  pg_ctl reload  [-D DATADIR] [-s]
  pg_ctl status  [-D DATADIR]
  pg_ctl promote [-D DATADIR] [-s]
  pg_ctl kill    SIGNALNAME PID

此時執行netstat -ap應該能看到postgres正在listen

但因為每次啟動PG都要輸入-D參數實在太麻煩,我們是先將PGDATA加入環境變數中,打開~/.bashrc,在最後一行加上:
export PGDATA=/usr/lib/postgresql/9.1/data

後記

在重啟服務時,常常會有失敗的情況,這時只要切換終止模式即可:
./pg_ctl -D ../data restart -m fast