2013/08/14

PostgreSQL 9.2 及多主機 Replication 複製機制設定

PostgreSQL 9.2 及多主機同步複製機制設定

PostgreSQL install 9.2(CentOS 6.x)

  • CentOS 6.X 版本的 postgresql 是 8.4 版,很多機制還沒有很完備。如果只是一般的使用是足夠,但如需要使用 replication 的功能,建議安裝 9.0 以上版本,比較不會有使用上面的限制。且 9.0 以上版本有很多機制可以選擇(例如:Slony-I Replication),但我選擇內建簡單設定的版本來完成我簡單備份的目的。
  • 如果需要有複製的機制,建議更新到最新版本的 postgresql ,最新穩定版為 postgresql 9.2 版本
  • 如果之前有預設的 postgresql 請先備份、關閉及移除,避免錯亂。在 centos 的 yum remove or rpm remove 機制並不會將 data 目錄移除,但仍建議如果有資料記得用 pg_dump_all 備份出來再做後續的變更。
[root@power pgsql]# /etc/init.d/postgresql stopStopping postgresql service: [ OK ][root@power pgsql]# chkconfig postgresql off
# 安裝 yum repo , 及軟體啟動 DB
[root@powerful ~]# rpm -i http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarch.rpm
[root@powerful ~]# yum install -y postgresql92-server postgresql92-contrib
[root@powerful ~]# service postgresql-9.2 initdb
[root@powerful ~]# chkconfig postgresql-9.2 on

# 9.2 版預設路徑
[root@power 9.2]# pwd
/var/lib/pgsql/9.2

# 切換為 postgres 使用者測試,可以不用密碼登入系統且取得最高權限
# 但是 9.2 因為環境變數的關係會有錯誤訊息,需要自行修正
[root@powerful data]# su - postgres                        [FAILED]
-bash-4.1$ psql -f postgres
psql: symbol lookup error: psql: undefined symbol: PQconnectdbParams

# 請在 /etc/profile 加入下面內容
[root@powerful data]# tail /etc/profile
# for postgresql LIB by mtchang
LD_LIBRARY_PATH=/usr/pgsql-9.2/lib
export LD_LIBRARY_PATH
PATH=/usr/pgsql-9.2/bin:$PATH
export PATH

# 用 source 將變數重新 load 進 shell
[root@powerful data]# source /etc/profile

[root@powerful data]# /etc/init.d/postgresql-9.2 restart
Stopping postgresql-9.2 service:                           [  OK  ]
Starting postgresql-9.2 service:                           [  OK  ]

[root@powerful data]# su - postgres
-bash-4.1$ psql
psql (9.2.4)
Type "help" for help.

# 改變 postgres 的預設密碼
postgres=# alter user postgres with password '12345678';
postgres=# \q
  • 以上就是一個單純可以本機登入的 postgresql ,可以用來將 pg_dump_all restore 的環境。
  • 如果要做 pg_dump_all restore 正常需要這些動作
  1. 停止 postgresql 服務 ex: /etc/init.d/postgresql-9.2 stop
  2. 刪除原本的 data 目錄 ex:rm -rf /var/lib/pgsql/data
  3. 重新初始話 database ex: /etc/init.d/postgresql-9.2 initdb
  4. 使用 psql 匯入備份檔案
  5. psql -U postgres -f pg_dump_all_備份檔 postgres
# 在 postgres 的身份下
-bash-4.1$ psql -U postgres -f pg_dump_all的備份檔案.sql  postgres
  • 但是實務上要使用,通常是透過網路加上密碼的存取,所以要修改 pg_hba.conf 這檔案讓它可以透過網路工作。
[root@powerful data]# cat /var/lib/pgsql/9.2/data/postgresql.conf  | grep listen
listen_addresses = '*'  # what IP address(es) to listen on;

[root@powerful data]# tail /var/lib/pgsql/9.2/data/pg_hba.conf 
# ph_hba.conf 檔案的最後請加上這些,讓這些網路可以透過 md5 用密碼登入系統
# 其他行列請先註解 # 或刪除不使用,後續要用再加上去。
host all     all  110.117.69.0/24  md5
host all     all  112.254.0.0/16  md5
host    all         all         127.0.0.1/32          md5
host    all         all         ::1/128               md5
local   all         all                               md5

[root@power data]# /etc/init.d/postgresql-9.2 restart
Stopping postgresql-9.2 service:                           [  OK  ]
Starting postgresql-9.2 service:                           [  OK  ]

# 測試透過網路IP及密碼可否登入
[root@powerful data]# psql -U postgres -h 110.117.69.11 -W
Password for user postgres: 
psql (9.2.4)
Type "help" for help.

postgres=# \q
  • 以上就是可以透過網路使用的 postgresql 了,但如果要好用這其實很不方便,所以通常會透過 create user 建立 superuser 管理者帳號來負責所有 postgresql 的活動。這部份請參考上面講義的說明。

Replication PostgreSQL

  • 在這個例子中,我使用兩台機器一台為 master DB 負責主要的資料庫讀寫,和平常的 postgresql DB 一樣的功能。另外一台為 slave DB 負責依據紀錄檔案的交易紀錄,抄寫來自援 master DB 的資料,即使斷線也可以從斷線的那時候繼續抄入複製。
  • 資料庫同步複寫功能啟用,他是透過交易的功能達成同步抄寫資料庫的目的。
[root@power data]# cat postgresql.conf 
# 加上這三行在最後
wal_level = hot_standby
max_wal_senders = 1
wal_keep_segments = 50

# 加入底下這條,讓 slave db 可以連入 master db 讀取交易紀錄的資料。
[root@power data]# cat /var/lib/pgsql/9.2/data/pg_hba.conf 
host replication postgres 110.117.69.14/32     trust

# 重新啟動
[root@power data]# /etc/init.d/postgresql-9.2 restart
Stopping postgresql-9.2 service:                           [  OK  ]
Starting postgresql-9.2 service:                           [  OK  ]

# 切換到 postgres 帳號,準備將 master DB 的紀錄複製到 slave DB,方法很簡單
# 使用線上即時備份成為一個 sql.tar 的檔案,透過 ssh copy 複製到 slave DB
# 然後將回復到 slave 機器的 data 資料夾內,這時包含資料交易紀錄也一併複製過去了。
[root@power data]# su - postgres
-bash-4.1$ psql -c "SELECT pg_start_backup('replbackup');"
Password: 
 pg_start_backup 
-----------------
 0/DF000020
(1 row)

-bash-4.1$ tar cfP /tmp/db_file_backup.tar /var/lib/pgsql/9.2/data

-bash-4.1$ psql -c "SELECT pg_stop_backup();"
Password: 
NOTICE:  WAL archiving is not enabled; you must ensure that all required 
WAL segments are copied through other means to complete the backup
 pg_stop_backup 
----------------
 0/DF0000E0
(1 row)

-bash-4.1$ ls -lh /tmp/db_file_backup.tar 
-rw-r--r--. 1 postgres postgres 6.7G Aug 14 21:36 /tmp/db_file_backup.tar

-bash-4.1$ exit
logout

[root@power data]# scp /tmp/db_file_backup.tar root@110.117.69.14:/tmp/

  • 切換到 Slave postgresql DB 設定,先確定是同一個 postgresql 版本,先關閉 postgresql 服務,並將檔案回復還原。修改 postgresql.conf 設定為 host_standby 的機器,隨時同步 master db 的資料。
[root@powerful 9.2]# /etc/init.d/postgresql-9.2 stop
[root@powerful 9.2]# mv /var/lib/pgsql/9.2/data /var/lib/pgsql/9.2/data.bak
[root@powerful 9.2]# pwd
/var/lib/pgsql/9.2
[root@powerful 9.2]# tar xvfP /tmp/db_file_backup.tar
[root@powerful data]# rm /var/lib/pgsql/9.2/data/postmaster.pid 
rm: remove regular file `/var/lib/pgsql/9.2/data/postmaster.pid'? y

[root@powerful data]# vim /var/lib/pgsql/9.2/data/postgresql.conf 
# These settings are ignored on a master server.
hot_standby = on
#hot_standby = off                      # "on" allows queries during recovery

# 設定要抄寫的 master DB 位置
[root@powerful data]# cp /usr/pgsql-9.2/share/recovery.conf.sample recovery.conf
[root@powerful data]# vim recovery.conf 
standby_mode = on
primary_conninfo = 'host=110.117.69.11 port=5432'

[root@powerful data]# chown postgres.postgres /var/lib/pgsql/9.2/data/recovery.conf 

[root@powerful data]# /etc/init.d/postgresql-9.2 restart
Stopping postgresql-9.2 service:                           [  OK  ]
Starting postgresql-9.2 service:                           [  OK  ]
  • 觀看 log 紀錄應該會看到連到 primary DB 的紀錄,及最後中斷前的交易紀錄。
[root@powerful data]# tail /var/lib/pgsql/9.2/data/pg_log/postgresql-Wed.log 
LOG:  database system was shut down at 2013-08-14 21:34:44 CST
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
LOG:  database system was interrupted; last known up at 2013-08-14 21:35:36 CST
LOG:  entering standby mode
LOG:  redo starts at 0/DF000020
LOG:  record with zer寫錯或有o length at 0/DF0000B8
LOG:  streaming replication successfully connected to primary
LOG:  consistent recovery state reached at 0/DF0000E0
LOG:  database system is ready to accept read only connections
  • 接下來請自行對 Master DB 寫入 , Slave DB 讀取測試驗證。
  • 參考:
  1. http://opensourcedbms.com/dbms/setup-replication-with-postgres-9-2-on-centos-6redhat-el6fedora/
  2. http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#What_Can_You_Do_With_Binary_Replication.3F


最新版本:http://jangmt.com/wiki/index.php/PostgreSQL_install#PostgreSQL_install_9.2.28CentOS_6.x.29
這測試只有幾次,如有問題或寫錯請不吝告知,謝謝。

2013/08/10

在 RHEL/CENTOS 6 Debian Linux 上使用 PostgreSQL資料庫

Contents

ABOUT

  • 安裝 postgresql 在 debian linux or centos linux



在RHEL/CENTOS 上使用 PostgreSQL資料庫

PostgreSQL

  • PostgreSQL是自由的關聯式資料庫伺服器(DBMS),授權模式採取 BSD 授權許可最大好處是可以用於商業營運或修改成為自己的產品一部分而無須支付任何費用也不必擔心需要將修改過後的原始碼公開等眾多GPL授權軟體需要面對的 問題。它具有商業資料庫絕大部分的功能,對於中小企業的應用十分的足夠。相較於 MySQL 這開放的資料庫系統,他有更完整的關聯式資料庫功能及優秀的穩定性。

為何要用postgresql?

  • 為何要用轉用 postgresql?
  • 我需要一個可以商業化的資料庫:PostgreSQL 採用授權較為寬鬆的 BSD 授權,對於商業公司而言不用擔心日後 PostgreSQL 的開發人員突然反悔改成其他授權模式的影響。
  • 一個強大的資料庫引擎:交易、觸發、內存程序等完整的關聯式資料庫機制,提供開發人員在開發過程中更方便的操作。
  • 一個取得容易,價格低廉的資料庫引擎:自由的BSD授權提供最便宜的成本。世界各地都有相關的 Mirror site。
  • 商用資料庫知名產品經常的更新版本及更新版本就收錢的風格令人不敢使用。
  • MySQL同樣是開放的資料庫管裡系統,為何選擇PostgreSQL?
  • MySQL 本來是各很棒的選項,但是自從被SUN收購,而SUN又和Oracle合併後眾人從沒看好 Oracle 會好好善待它!!從 Oracle 對待 OpenSolaris 這各自由軟體專案的態度就可以想像以後 MySQL 可能的下場。
  • MySQL 在 5 版之前和 PostgreSQL 的定位並不衝突,MySQL 自從 4 版及 5 版加入觸發、內存程序、交易等功能後,雖然是各更完整的資料庫系統,但變得相對不穩定,且速度也因為更複雜的處理機制讓它疑似變慢了。

PostgreSQL 的安裝

postgresql 的套件安裝

  • postgresql 的套件安裝
  • 安裝 postgresql server 及文字 client 端工具
[root@lab ~]# yum install postgresql postgresql-server
  • 驗證你所安裝的套件
[root@lab ~]# rpm -qa | grep postgresql
postgresql-8.1.23-1.el5_6.1
postgresql-server-8.1.23-1.el5_6.1
postgresql-libs-8.1.23-1.el5_6.1 
  • 重新啟動 postgresql 服務
[root@lab ~]# /etc/init.d/postgresql restart
正在停止 postgresql 服務:                            [失敗]
正在初始化資料庫:                                     [確定]
正在啟動 postgresql 服務:                            [確定]
  • postgresql 程式在linux 上會以一個名稱為 postgres 的使用者帳戶執行,
[root@lab ~]# finger postgres
Login: postgres          Name: PostgreSQL Server
Directory: /var/lib/pgsql            Shell: /bin/bash
Never logged in.
No mail.
No Plan.
  • 如果你仔細觀察會發現 postgres 使用者的家在 /var/lib/pgsql 目錄,目錄內 data 包涵了所有 postgresql 所有需要用到的檔案內容。包含所有的設定檔、紀錄檔及資料庫檔案都在裡面,備份時只要把整各打包備份即可。
[root@lab sql]# ls /var/lib/pgsql/ -la
總計 72
drwx------  4 postgres postgres 4096  4月 29 19:56 .
drwxr-xr-x 34 root     root     4096  4月 29 14:55 ..
drwx------  2 postgres postgres 4096  3月 31 01:23 backups
-rw-------  1 postgres postgres 1082  4月 29 19:56 .bash_history
-rw-r--r--  1 postgres postgres   85  3月 31 01:23 .bash_profile
drwx------ 11 postgres postgres 4096  4月 29 19:32 data
-rw-------  1 postgres postgres 2277  4月 29 18:29 pgstartup.log
-rw-------  1 postgres postgres  386  4月 29 18:04 .psql_history
-rw-------  1 postgres postgres 1417  4月 29 19:54 .viminfo
[root@lab sql]# ls /var/lib/pgsql/data/ -la
總計 148
drwx------ 11 postgres postgres  4096  4月 29 19:32 .
drwx------  4 postgres postgres  4096  4月 29 19:56 ..
drwx------  6 postgres postgres  4096  4月 29 23:36 base
drwx------  2 postgres postgres  4096  4月 29 23:43 global
drwx------  2 postgres postgres  4096  4月 29 14:56 pg_clog
-rw-------  1 postgres postgres  3330  4月 29 18:30 pg_hba.conf
-rw-------  1 postgres postgres  1460  4月 29 14:56 pg_ident.conf
drwx------  2 postgres postgres  4096  4月 29 14:56 pg_log
drwx------  4 postgres postgres  4096  4月 29 14:56 pg_multixact
drwx------  2 postgres postgres  4096  4月 29 14:56 pg_subtrans
drwx------  2 postgres postgres  4096  4月 29 14:56 pg_tblspc
drwx------  2 postgres postgres  4096  4月 29 14:56 pg_twophase
-rw-------  1 postgres postgres     4  4月 29 14:56 PG_VERSION
drwx------  3 postgres postgres  4096  4月 29 14:56 pg_xlog
-rw-------  1 postgres postgres 13804  4月 29 18:09 postgresql.conf
-rw-------  1 postgres postgres    57  4月 29 18:29 postmaster.opts
-rw-------  1 postgres postgres    45  4月 29 18:29 postmaster.pid

在資料庫系統上,建立使用者與資料庫

  • 資料庫使用者
  • 資料庫使用者從概念上與系統上面的使用者是完全無關的。但是在 postgresql 資料庫使用者名稱(roles)在整個資料庫中預設會透過和Linux系統帳戶同樣的帳戶名稱認證,這是資料庫為了安全性所設計的驗證方式之一。
  • 一般在工作上,通常會為一個專案工作建立一個專用的資料庫帳戶及及資料庫,並將權限獨立給該資料庫使用者(roles)可以存取該資料庫並指定允許的資料庫存取範圍。避免因為單一資料庫使用者被入侵後影響整個資料庫管理系統。
  • 在SQL指令中可以透過底下的指令建立資料庫使用者(roles)及資料庫。
  • 建立一個資料庫使用者(roles)名稱為 name 及密碼為 string 的使用者
CREATE USER name WITH PASSWORD 'string';
  • 建立一個資料庫 dbname
CREATE DATABASE dbname;
  • 在剛裝好 postgresql 的時候,系統只有純文字的 SQL 管理工具(psql)可操作,並且設定好了一個有個資料庫使用者(roles)叫做 postgres ,他可以透過作業系統的系統的 postgres 使用者身份登入 postgresql 資料庫系統,並且取得最高管理者的權限。
  • 切換成為 postgres 使用者,因為在 RHEL 中預設系統登入系統使用的為 ident sameuser 的認證模式,所以使用Lnux OS postgres 使用者身份登入資料庫系統就可以得到最高資料庫系統的管理權限且過程中不需要密碼。這裡登入資料庫的方式是使用命令列的 psql 這這指令。
# root 切換成 postgres
[root@lab data]# su - postgres
# 登入  postgresql 資料庫,因為系統預設為 ident sameuser 的認證模式,所以不用密碼就可以登入
[root@lab pg_log]# su - postgres
-bash-3.2$ psql  template1
Welcome to psql 8.1.23, 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 的密碼為 12345678
template1=# alter user postgres with password "12345678";

# 建立一個使用者為 mtchang 且將密碼修改為 1234 並且賦予 superuser 的權限。
template1=# CREATE USER mtchang WITH PASSWORD '1234';
ALTER ROLE

template1=# ALTER ROLE mtchang WITH superuser;
ALTER ROLE

# 離開資料庫
template1=# \q

  • 這樣你就完成了資料庫的最高管理者 postgresql 的密碼修改及建立另一個超級使用者帳號了。

更改 PostgreSQL 的登入認證模式

  • 但是這樣的登入方式使用者只能在本機上工作,對於網頁程式的開發很不方便。我們可以透過修改登入的認證模式讓系統可以透過 TCP/IP 連線的方式登入系統,透過網路的分享方式可以讓本機或遠端的程式透過網路直接存取這台電腦的資料庫;要小心的是開放了網路連線也相對的把資料庫開放給全部 的網路使用,所以在開放權限上需要進行適當的管控。
  • pg_hba.conf 設定值說明 http://developer.postgresql.org/pgdocs/postgres/auth-pg-hba-conf.html
  • 更改 postgresql 的認證模式增加 host 認證方式
[root@lab ~]# vim /var/lib/pgsql/data/pg_hba.conf 
# 省略很多...
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
# 開放本地端同資料庫使用者帳號的 linux 使用者可以不用密碼從本機登入系統
local   all         all                               ident sameuser
# IPv4 local connections: IPV4本地端
host    all         all         127.0.0.1/32          ident sameuser
# IPv6 local connections: IPV6本地端
host    all         all         ::1/128               ident sameuser

# IPv4 local connections:
# 可以透過 tcp/ip 從 127.0.0.1/32 及 110.111.69.0/24 登入
host    all         all         127.0.0.1/32          md5
host    all         all         110.111.69.0/24       md5

# 本地端的使用者使用 Unix domain socket及 md5 密碼登入 
local   all         all                               md5
  • 原來 Postgresql 的連線只開放本機 IP 可以存取,如果需要讓外面 IP 可以存取,需要打開預設的連線設定檔案 /var/lib/pgsql/data/postgresql.conf 將 listen_addresses = ‘localhost’ 修改為 listen_addresses = ‘*’ 結果如下:
[root@lab ~]# vim /var/lib/pgsql/data/postgresql.conf 
# - Connection Settings -

listen_addresses = '*'                  # what IP address(es) to listen on; 
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
  • 重新啟動 postgresql 服務
[root@lab data]# /etc/init.d/postgresql restart
正在停止 postgresql 服務:                                  [  確定  ]
正在啟動 postgresql 服務:                                  [  確定  ]
  • 驗證 postgresql 服務是否已開啟(TCP port 5432)
[root@lab ~]# netstat -an | grep 5432
tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN      
tcp        0      0 :::5432                     :::*                        LISTEN      
unix  2      [ ACC ]     STREAM     LISTENING     6984191 /tmp/.s.PGSQL.5432
  • psql使用 host 的登入方式登入
[root@lab pg_log]# psql -U postgres -h 127.0.0.1
Password for user postgres: 
Welcome to psql 8.1.23, 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=# \q
  • 設定不正確,可能會發生下面的錯誤。
[root@lab ~]# psql -U jangmt
psql: 嚴重錯誤:  Ident驗證使用者"jangmt"失敗
  • 登入系統預設會以使用者帳號登入系統,如果沒有此資料庫會產生錯誤警告。
[jangmt@lab ~]$ psql -U jangmt
psql: 嚴重錯誤:  資料庫"jangmt"不存在
  • 在認證過程中如果發生問題,請觀看記錄檔 /var/lib/pgsql/data/pg_log/postgresql-Fri.log 可以獲得類似的下面的訊息紀錄。
[root@lab pg_log]# tail /var/lib/pgsql/data/pg_log/postgresql-Fri.log  -n 20
LOG:  下一個交易ID:662,下一個OID:16394
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  資料庫系統待命
LOG:  transaction ID wrap limit is 2147484146, limited by database "postgres"
LOG:  could not connect to Ident server at address "127.0.0.1", port 113: 連線被拒絕
嚴重錯誤:  Ident驗證使用者"postgres"失敗
錯誤:  資料庫"sqladmin"不存在
LOG:  transaction ID wrap limit is 2147484146, limited by database "postgres"
LOG:  transaction ID wrap limit is 2147484146, limited by database "postgres"
LOG:  收到快速關閉的要求
LOG:  正在關閉
LOG:  資料庫系統已關閉
LOG:  正在關閉logger
LOG:  資料庫系統於 2011-04-29 18:29:29 CST 被關閉
LOG:  檢查點記錄於 0/398728
LOG:  redo記錄於 0/398728,undo記錄於 0/0,關閉 TRUE
LOG:  下一個交易ID:674,下一個OID:16394
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  資料庫系統待命
LOG:  transaction ID wrap limit is 2147484146, limited by database "postgres"

安裝 PostgreSQL 管理工具

  • PhpPgAdmin是一套使用 php 程式編寫,透過 web 介面來管理 postgresql 資料庫的資料庫管理工具。它和 mysql 上面 phpmyadmin 很類似,事實上很多概念是從那裡參考而來。但是加入了很多 postgresql 特有的功能。在安裝過程中如果有問題可以參考官方網站上面的 FAQ 有很多解決的方式及已知的問題。
  • FAQ http://phppgadmin.sourceforge.net/doku.php?id=faq_docs
  • 另外一套比較知名的管理工具是 pgAdmin III 這是一套圖形化介面的管理工具。提供更多友善的設計,如:圖形化的查詢設計師,對 TSearch 全文搜索引擎 的支持 .....等眾多商業化軟體才有的功能..
Psql-3.jpg
  • 安裝 phpmyadmin 前請先確認你的 linux 已經有 apache + php 的功能支援,如果沒有可以透過 yum 指令快速的安裝這些套件。
yum -y install httpd httpd-devel httpd-manual
yum -y install php-pgsql php-mbstring \
php-soap php-xml php-mcrypt php-pear php-cli php-devel php-gd
  • 並可以透過php的 phpinfo(); 函示確認php是否已經有支援 postgresql 的存取。
[root@lab html]# vim /var/www/html/phpinfo.php 
  • 當網頁出現 psql 的函示庫,表示該 php 有支援 postgresql 的存取。
Pssql phppgadmin 3.jpg



  • 你可以使用 yum 安裝 phppgadmin 來管理 postgresql
[root@lab html]# yum search phppgadmin
phpPgAdmin.noarch : Web-based PostgreSQL administration 

[root@lab html]# yum install phpPgAdmin
# 安裝好後需要設定專屬設定檔,讓非本機的使用者可以登入管理。
[root@powerful data]# cat /etc/httpd/conf.d/phpPgAdmin.conf 
#
# This configuration file maps the phpPgAdmin directory into the URL space. 
# By default this application is only accessible from the local host.
#

Alias /phpPgAdmin /usr/share/phpPgAdmin


    Order deny,allow
    Deny from all
    Allow from 127.0.0.1
    Allow from ::1
    # 請依據你的存取位置設定 ip 範圍
    Allow from 110.111.69.0/24
    Allow from 122.254.0.0/16
    # Allow from .example.com

[root@lab html]# /etc/init.d/httpd restart
  • 另一個方式可以從官方網站下載最新的 phpPgAdmin 程式自行安裝,從官方網站下載最新版本的phppgadmin http://phppgadmin.sourceforge.net
  • 底下的範例不使用官方預設的 phpmyadmin 套件安裝,如果你想使用官方預設把包好的套件,請自行用 yum 安裝程式。
Psql phppgadmin 1.jpg
  • 解開放到目錄 /var/www/html 內
[root@lab html]# ls /var/www/html/ -l
總計 1072
-rw-r--r--  1 root root      17  4月 29 18:50 phpinfo.php
drwxr-xr-x 12 root root    4096  1月  4 03:23 phpPgAdmin-5.0.2
  • 請修改 phpPgAdmin 設定檔
[root@lab html]# vim /var/www/html/phpPgAdmin-5.0.2/conf/config.inc.php

        // Hostname or IP address for server.  Use '' for UNIX domain socket.
        // use 'localhost' for TCP/IP connection on this computer
        // 底下欄位加入 localhost 內容,指定此程式連接到放在本機 host 上
        $conf['servers'][0]['host'] = 'localhost';

        //把底下這行最後的 true 更換為 false 
        //$conf['extra_login_security'] = true;
        $conf['extra_login_security'] = false;
  • 修改完成後,直接輸入剛剛更改的 postgresql 最高管理者的帳號及密碼就可以登入資料庫管理系統開始工作。
Pssql phppgadmin 2.jpg

建立使用者及匯入資料

  • 在上面部分已經完成最高管理者登入網頁管理介面的設定,對於一個專案或工作來說絕對不要用最高管理員來寫程式,因為 SQL injection 在程式上面的防堵常會因為程式設計師的不小心而發生漏洞,造成不可預期的狀況。所以底下將說明如何建立一個普通受限制的使用者及資料庫,並且匯入些資料。
  • 在 shell 中雖然有提供 createdb 及 createuser 這些指令,但在使用上並不會比單純的 psql 配合 sql 語法來的簡單好用。所以底下以 psql + sql 語法建立資料庫及作相關的操作。

建立使用者

  • -c 參數可以直接在命令列下 SQL 敘述句
  • -e 參數是將 SQL 的執行指令過程全部列出來
[root@lab sql]# psql -h localhost -U postgres -W -e -c "CREATE USER jangmt WITH PASSWORD '密碼' ;"
Password for user postgres: 
CREATE USER jangmt WITH PASSWORD '密碼' ;
CREATE ROLE

建立資料庫

  • 建立資料庫前一定要確定已經建立使用者,否則 OWNER 後面的這個使用者身份會發生語法錯誤。
[root@lab sql]# psql -h localhost -U postgres -W -e -c "CREATE DATABASE jangmtDB OWNER jangmt ENCODING 'UTF8';"
Password for user postgres: 
CREATE DATABASE jangmtDB OWNER jangmt ENCODING 'UTF8';
CREATE DATABASE

匯入資料

  • -f 參數可以讀取檔案 sql.txt 內的 SQL 敘述輸入到資料庫系統內
[root@lab sql]# psql -h localhost -U jangmt -d jangmtdb -W -e -f sql.txt 
Password for user jangmt: 

# 底下開使用 SQL 敘述,如需測試可以複製成為檔案 sql.txt 在測試時使用
CREATE TABLE STUDENTS (
  學號 numeric(15,5) default NULL,
  班級座號 varchar(9) default NULL,
  姓名 varchar(7) default NULL,
  出生年月日 date default NULL,
  身分証號碼 varchar(11) default NULL,
  住址 varchar(33) default NULL,
  家長 varchar(9) default NULL,
  電話 varchar(15) default NULL,
  科別 varchar(10) default NULL,
  畢業國中 varchar(22) default NULL
);
CREATE TABLE
INSERT INTO STUDENTS VALUES (911001.00000, '10101', '王于穎', '1984-03-05 00:00:00', 'C100000012', 
'基隆市安樂區安和一街4巷4-3號4F', '王世傑', '02-24310667', '商業經營科', '基隆市市立安樂國中畢業');
INSERT 0 1
INSERT INTO STUDENTS VALUES (911002.00000, '10102', '王彗如', '1980-09-08 00:00:00', 'F200000026', 
'台北縣瑞芳鎮一坑路426號', '王文淵', '02-24971835', '商業經營科', '台北縣縣立瑞芳國中畢業');
INSERT 0 1
INSERT INTO STUDENTS VALUES (911003.00000, '10103', '王琇榆', '1984-08-28 00:00:00', 'F200000035', 
'基隆市暖暖區源遠路292巷1-5號1F', '王進豐', '02-24570828', '商業經營科', '基隆市市立暖暖國中畢業');
INSERT 0 1
INSERT INTO STUDENTS VALUES (911004.00000, '10104', '朱勝真', '1984-08-28 00:00:00', 'F100000042', 
'台北縣瑞芳鎮逢甲路337號', '朱水順', '02-24970773', '商業經營科', '台北縣縣立瑞芳國中修業');
INSERT 0 1
  • 執行 sql select 指令,確認資料都有匯入系統內。
[root@lab sql]# psql -h localhost -U jangmt -d jangmtdb -W -e -c 'select 學號,班級座號,姓名 from students;'
Password for user jangmt: 
select 學號,班級座號,姓名 from students;
     學號     | 班級座號 |  姓名  
--------------+----------+--------
 911001.00000 | 10101    | 王于穎
 911002.00000 | 10102    | 王彗如
 911003.00000 | 10103    | 王琇榆
 911004.00000 | 10104    | 朱勝真
(4 行)
Psql-1.jpg

匯出資料

  • pg_sump 可以將資料庫依需求匯出,以供備份或轉移系統使用。
[root@lab sql]# pg_dump -f sql_dump.txt -h localhost -U jangmt -W jangmtdb
密碼:

# 把備份檔內容 sql_dump.txt 呈現
[root@lab sql]# cat sql_dump.txt 
--
-- PostgreSQL database dump
--
SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;
--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--
COMMENT ON SCHEMA public IS 'Standard public schema';
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: students; Type: TABLE; Schema: public; Owner: jangmt; Tablespace: 
--
CREATE TABLE students (
    "學號" numeric(15,5),
    "班級座號" character varying(9),
    "姓名" character varying(7),
    "出生年月日" date,
    "身分証號碼" character varying(11),
    "住址" character varying(33),
    "家長" character varying(9),
    "電話" character varying(15),
    "科別" character varying(10),
    "畢業國中" character varying(22)
);
-- ...資料太多予以省略....

shell script 建立使用者資料

  • 如果要透過 shell script 方便建立資料庫的使用者帳號及資料庫,可以使用 psql 指令並用配合 postgres 這個超級使用者登入系統來執行必要得 SQL 敘述。因為在命令列的工具 psql 並沒有提供密碼預設輸入的功能,只能透過鍵盤互動輸入。增加了安全姓,相對也造成自動化處理上的麻煩。
  • 所以在這裡我使用了 pg_hba.conf 的 ident sameuser 認證方式,讓只要是 postgres 使用者執行的 psql 都不用輸入密碼即可自動執行 SQL 敘述,來執行自動化的作業。
  • 切換到 postgres 帳號及測試 psql 登入免密碼。
[root@lab tmp]# su - postgres
-bash-3.2$ psql 
Welcome to psql 8.1.23, 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=# \q
  • 上面的範例沒有問題的話請用編輯器建立底下的 shell script
-bash-3.2$ cat ./pssqladd.sh 
#!/bin/bash
# get argc
sqlaccount=$1
sqlpassword=$2

# check $1 and $2
if !(test -z $sqlaccount;) then
  if !(test -z $sqlpassword;) then
 echo "account is $sqlaccount , DB is $sqlaccount , password is $sqlpassword"
 # sql
 echo "CREATE USER $sqlaccount WITH PASSWORD '$sqlpassword';
CREATE DATABASE $sqlaccount OWNER $sqlaccount ENCODING 'UTF8'; " > /tmp/pgsqladd.txt
 echo "psql -f /tmp/pgsqladd.txt" | bash
  else
 echo 'error!!! need DB user password'
 echo 'usage:./pssqladd.sh DBusername DBuserpassword'
  fi
else 
 echo 'error!!! need DB user name'
 echo 'usage:./pssqladd.sh DBusername DBuserpassword'
fi
  • 記得賦予此 shell 可執行權或使用 bash pssqladd.sh 方式執行 shell script
-bash-3.2$ chmod +x pssqladd.sh
  • 執行script ,建立 a01 帳號及 a01 資料庫,密碼為 a01test
-bash-3.2$ ./pssqladd.sh a01 a01test
account is a01 , DB is a01 , password is a01test
CREATE ROLE
CREATE DATABASE
  • 驗證
Psql-2.jpg

參考資料


debian 安裝 postgresql 資料庫

  • 使用 apt 直接安裝 postgresql 資料庫
root@sql:~# apt-get install postgresql
  • 請先產生一個linux 系統帳戶,給這個使用者
# 增加帳號
root@sql:~# useradd -m -g users -s /bin/bash mtchang
# 修改密碼
root@sql:~# passwd mtchang
  • 裝完後系統會產生一個最高權限的使用者使用者名為 postgres ,以 root 身份 su 切換到這個使用者即可開設帳號
root@sql:~# su - postgres
postgres@sql:~$ 
  • 連接到資料庫伺服器,使用 postgres 帳號身份,template1為系統預設表格
postgres@sql:~$ psql template1
psql (8.4.7)
Type "help" for help.

template1=# 



  • 新增加一個資料庫使用者 mtchang ,並且賦予密碼 Password1234
template1=# CREATE USER mtchang WITH PASSWORD 'Password1234';
CREATE ROLE

# 建立一個資料庫名稱為 mtchang
template1=# CREATE DATABASE mtchang;
CREATE DATABASE

# 給予使用者 mtchang 資料庫 mtchang 的所有存取權限
template1=# GRANT ALL PRIVILEGES ON DATABASE mtchang to mtchang;
GRANT

# 離開 \q
template1=# \q
  • 切換到 mtchang linux 使用者身份,測試PostgreSQL可否登入
root@sql:~# su - mtchang
$ bash
mtchang@sql:~$ psql -d mtchang -U mtchang
psql (8.4.7)
Type "help" for help.

mtchang=> \q
  • 但如果要作系統管理,只有 postgres 才有權限,此帳號預設沒有密碼所以得先設定密碼才可以讓這各使用者從遠端登入。
postgres@sql:~$ psql template1
psql (8.4.7)
Type "help" for help.
# 修改密碼將使用者 postgres 密碼修改為 Password1234
template1-# alter user postgres with password 'Password1234';
template1-# \q

安裝 phpPgAdmin 管理介面

  • php 寫成的 web 介面 phpPgAdmin
root@sql:~# apt-get install phpPgAdmin
  • 修改 phppgadmin 管理工具的設定檔,讓 postgres 允許登入
// If extra login security is true, then logins via phpPgAdmin with no
// password or certain usernames (pgsql, postgres, root, administrator)
// will be denied. Only set this false once you have read the FAQ and
// understand how to change PostgreSQL's pg_hba.conf to enable
// passworded local connections.
// $conf['extra_login_security'] = true;
// 修改底下這一行即可
   $conf['extra_login_security'] =false;
  • 用 root 修改 apache 設定,加入允許連線的 ip 範圍
root@sql:~# nano /etc/phppgadmin/apache.conf 
Alias /phppgadmin /usr/share/phppgadmin/



DirectoryIndex index.php

Options +FollowSymLinks
AllowOverride None

order deny,allow
deny from all
# 底下這一段 allow from 指定為看哪些 ip 可通行此管理介面
allow from 127.0.0.0/255.0.0.0 ::1/128
allow from 192.168.0.0/255.255.0.0
allow from 112.104.187.187
# allow from all


  php_flag magic_quotes_gpc Off
  php_flag track_vars On
  php_value include_path .




# 記得重新啟動
root@sql:~# /etc/init.d/apache2 restart
Restarting web server: apache2 ... waiting .
Postgresql setup 1a.jpg
  • 通常只要作到這裡,就可以透過圖形介面的方式建立新帳號了。
Postgresql setup 1b.jpg
  • 參考資料:
  1. http://www.cyberciti.biz/faq/linux-installing-postgresql-database-server/
  2. http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/

大量建立帳號及匯入資料

  • 自動化建立帳號,為了方便管理維護,我把他寫成 shell script 以方便快速增加使用者。
  1. 建立帳號前請先建立 linux 使用者帳號
  2. 建立完成後會將帳號密碼寄到使用者的信箱及 root 信箱。
postgres@sql:~$ nano pgsqladd.sh 

#!/bin/bash
# get argc
sqlaccount=$1

# check $1
if !(test -z $sqlaccount;) then
  echo "account is $sqlaccount"
  #sqlpassword='qwer1234'
  sqlpassword=$(openssl rand -hex 3)
  email="root@localhost,$sqlaccount@localhost";
  echo "
CREATE USER $sqlaccount WITH PASSWORD '$sqlpassword' ;
CREATE DATABASE $sqlaccount;
GRANT ALL PRIVILEGES ON DATABASE $sqlaccount to $sqlaccount;
" > pgsqladd.txt

  # display sql code
  tail pgsqladd.txt

  # run sql
  echo "psql -U postgres -d postgres  < pgsqladd.txt" | bash

  # rm pgsqladd.txt -f


  # email infomation to user
  echo "echo '
postgresql web admin 
http://localhost/phppgadmin/
pssql account: $sqlaccount
pgsql password: $sqlpassword
pgsql host: localhost
' | mail -s 'User $sqlaccount pgsql account create' $email " | bash

 echo "User $sqlaccount pgsql account create, password in mail box"

else
 echo 'usage: pgsqladd account [email]'
fi







最新版本在  "http://jangmt.com/wiki/index.php?title=PostgreSQL_install"
* 2013.08.10 修正錯誤