|
|
|
目前最新的CentOS 6.3中，仍然使用的是PostgreSQL 8.4版本。為了滿足另一應用系統(tǒng)的需求，必須安裝PostgreSQL 9及以上版本。本文將敘述如何使用PostgreSQL官方軟件倉庫和YUM工具，實現(xiàn)自動安裝PostgeSQL 9。我使用的CentOS版本為6.3，該操作方法同樣適用于使用CentOS 5, RedHat或者Fedora版本的朋友，只是需要注意下載文件時有對應的版本選擇。
1、下載并安裝PostgreSQL官方軟件倉庫
訪問http://yum.pgrpms.org/repopackages.php，選擇對應的操作系統(tǒng)版本，下載repo rpm文件。對應我的操作系統(tǒng)CentOS 6.3 32位版本，使用wget命令下載，操作如下：
[root@wardking ~]# wget http://yum.pgrpms.org/9.2/redhat/rhel-6-i386/pgdg-centos92-9.2-6.noarch.rpm –2012-10-11 02:52:40– http://yum.pgrpms.org/9.2/redhat/rhel-6-i386/pgdg-centos92-9.2-6.noarch.rpm Resolving yum.pgrpms.org… 98.129.198.114 Connecting to yum.pgrpms.org|98.129.198.114|:80… connected. HTTP request sent, awaiting response… 200 OK Length: 5328 (5.2K) [application/x-redhat-package-manager] Saving to: pgdg-centos92-9.2-6.noarch.rpm 100%[==============================================================================================================================>] 5,328 24.7K/s in 0.2s 2012-10-11 02:52:42 (24.7 KB/s) – “pgdg-centos92-9.2-6.noarch.rpmâ€安裝軟件倉庫
[root@wardking ~]# rpm -ivh pgdg-centos92-9.2-6.noarch.rpm warning: pgdg-centos92-9.2-6.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY Preparing… ########################################### [100%] 1:pgdg-centos92 ########################################### [100%]編輯CentOS-Base.repo，將目前的CentOS倉庫中的版本排除
[root@wardking ~]# vi /etc/yum.repos.d/CentOS-Base.repo # remarked out baseurl= line instead. [base] name=CentOS-$releasever – Base mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os #baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/ gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6 exclude=postgresql* #released updates [updates] name=CentOS-$releasever – Updates mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates #baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/ gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6 exclude=postgresql*試一下，應該可以看到最新的PostgreSQL出現(xiàn)在可用軟件列表中：
[root@wardking ~]# yum list postgres* Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirrors.btte.net * epel: mirrors.ustc.edu.cn * extras: mirrors.btte.net * rpmforge: mirror.fairway.ne.jp * updates: mirrors.btte.net base extras pgdg92 pgdg92/primary_db updates Installed Packages postgresql.i686 8.4.13-1.el6_3 postgresql-contrib.i686 8.4.13-1.el6_3 postgresql-devel.i686 8.4.13-1.el6_3 postgresql-libs.i686 8.4.13-1.el6_3 postgresql-plperl.i686 8.4.13-1.el6_3 postgresql-server.i686 8.4.13-1.el6_3 Available Packages postgresql-ip4r.i686 1.05-1.el6 postgresql-pgpool-II.i686 3.2.0-1.el6 postgresql-pgpool-II-devel.i686 3.2.0-1.el6 postgresql-pgpool-II-recovery.i686 3.2.0-1.el6 postgresql-plparrot.i686 0.04-5.el6 postgresql-plruby.i686 0.5.3-4.el6 postgresql-plruby-doc.i686 0.5.3-4.el6 postgresql-relay.i686 1.3-2.2.el6.rf postgresql92.i686 9.2.1-1PGDG.rhel6 postgresql92-contrib.i686 9.2.1-1PGDG.rhel6 postgresql92-debuginfo.i686 9.2.1-1PGDG.rhel6 postgresql92-devel.i686 9.2.1-1PGDG.rhel6 postgresql92-docs.i686 9.2.1-1PGDG.rhel6 postgresql92-jdbc.i686 9.2.1000-1PGDG.rhel6 postgresql92-jdbc-debuginfo.i686 9.2.1000-1PGDG.rhel6 postgresql92-libs.i686 9.2.1-1PGDG.rhel6 postgresql92-odbc.i686 09.01.0200-1PGDG.rhel6 postgresql92-odbc-debuginfo.i686 09.01.0200-1PGDG.rhel6 postgresql92-plperl.i686 9.2.1-1PGDG.rhel6 postgresql92-plpython.i686 9.2.1-1PGDG.rhel6 postgresql92-pltcl.i686 9.2.1-1PGDG.rhel6 postgresql92-server.i686 9.2.1-1PGDG.rhel6 postgresql92-tcl.i686 2.0.0-1.rhel6 postgresql92-tcl-debuginfo.i686 2.0.0-1.rhel6 postgresql92-test.i686 9.2.1-1PGDG.rhel6
將舊版本中的postgresql軟件包全部刪除
[root@wardking ~]# yum remove postgresql postgresql-contrib postgresql-libs postgresql-devel postgresql-plperl postgr Loaded plugins: fastestmirror Setting up Remove Process Resolving Dependencies –> Running transaction check —> Package postgresql.i686 0:8.4.13-1.el6_3 will be erased —> Package postgresql-contrib.i686 0:8.4.13-1.el6_3 will be erased —> Package postgresql-devel.i686 0:8.4.13-1.el6_3 will be erased —> Package postgresql-libs.i686 0:8.4.13-1.el6_3 will be erased –> Processing Dependency: libpq.so.5 for package: libdbi-dbd-pgsql-0.8.3-5.1.el6.i686 –> Processing Dependency: postgresql-libs for package: libdbi-dbd-pgsql-0.8.3-5.1.el6.i686 —> Package postgresql-plperl.i686 0:8.4.13-1.el6_3 will be erased —> Package postgresql-server.i686 0:8.4.13-1.el6_3 will be erased –> Running transaction check —> Package libdbi-dbd-pgsql.i686 0:0.8.3-5.1.el6 will be erased –> Finished Dependency Resolution Dependencies Resolved ====================================================================================================================== Package Arch Version ====================================================================================================================== Removing: postgresql i686 8.4.13-1.el6_3 postgresql-contrib i686 8.4.13-1.el6_3 postgresql-devel i686 8.4.13-1.el6_3 postgresql-libs i686 8.4.13-1.el6_3 postgresql-plperl i686 8.4.13-1.el6_3 postgresql-server i686 8.4.13-1.el6_3 Removing for dependencies: libdbi-dbd-pgsql i686 0.8.3-5.1.el6 Transaction Summary ====================================================================================================================== Remove 7 Package(s) Installed size: 34 M Is this ok [y/N]: y Downloading Packages: Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Warning: RPMDB altered outside of yum. Erasing : postgresql-devel-8.4.13-1.el6_3.i686 Erasing : postgresql-contrib-8.4.13-1.el6_3.i686 Erasing : postgresql-plperl-8.4.13-1.el6_3.i686 Erasing : postgresql-server-8.4.13-1.el6_3.i686 Erasing : postgresql-8.4.13-1.el6_3.i686 Erasing : libdbi-dbd-pgsql-0.8.3-5.1.el6.i686 Erasing : postgresql-libs-8.4.13-1.el6_3.i686 Verifying : postgresql-8.4.13-1.el6_3.i686 Verifying : postgresql-server-8.4.13-1.el6_3.i686 Verifying : postgresql-contrib-8.4.13-1.el6_3.i686 Verifying : libdbi-dbd-pgsql-0.8.3-5.1.el6.i686 Verifying : postgresql-devel-8.4.13-1.el6_3.i686 Verifying : postgresql-plperl-8.4.13-1.el6_3.i686 Verifying : postgresql-libs-8.4.13-1.el6_3.i686 Removed: postgresql.i686 0:8.4.13-1.el6_3 postgresql-contrib.i686 0:8.4.13-1.el6_3 postgresql-devel.i686 0:8.4.13- postgresql-plperl.i686 0:8.4.13-1.el6_3 postgresql-server.i686 0:8.4.13-1.el6_3 Dependency Removed: libdbi-dbd-pgsql.i686 0:0.8.3-5.1.el6 Complete!2、 開始安裝最新版PostgreSQL
現(xiàn)在已經可以使用yum直接安裝
yum -y install postgresql postgresql-contrib postgresql-libs postgresql-plperl postgresql-server Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: centos.ustc.edu.cn * epel: ftp.jaist.ac.jp * extras: centos.ustc.edu.cn * rpmforge: mirror.hmc.edu * updates: ftp.iij.ad.jp Setting up Install Process Resolving Dependencies –> Running transaction check —> Package postgresql92.i686 0:9.2.1-1PGDG.rhel6 will be installed —> Package postgresql92-contrib.i686 0:9.2.1-1PGDG.rhel6 will be installed —> Package postgresql92-libs.i686 0:9.2.1-1PGDG.rhel6 will be installed —> Package postgresql92-plperl.i686 0:9.2.1-1PGDG.rhel6 will be installed —> Package postgresql92-server.i686 0:9.2.1-1PGDG.rhel6 will be installed –> Finished Dependency Resolution Dependencies Resolved ====================================================================================================================== Package Arch Version ====================================================================================================================== Installing: postgresql92 i686 9.2.1-1PGDG.rhel6 postgresql92-contrib i686 9.2.1-1PGDG.rhel6 postgresql92-libs i686 9.2.1-1PGDG.rhel6 postgresql92-plperl i686 9.2.1-1PGDG.rhel6 postgresql92-server i686 9.2.1-1PGDG.rhel6 Transaction Summary ====================================================================================================================== Install 5 Package(s) Total download size: 5.3 M Installed size: 21 M Downloading Packages: (1/5): postgresql92-9.2.1-1PGDG.rhel6.i686.rpm (2/5): postgresql92-contrib-9.2.1-1PGDG.rhel6.i686.rpm (3/5): postgresql92-libs-9.2.1-1PGDG.rhel6.i686.rpm (4/5): postgresql92-plperl-9.2.1-1PGDG.rhel6.i686.rpm (5/5): postgresql92-server-9.2.1-1PGDG.rhel6.i686.rpm ———————————————————————————————————————- Total Running rpm_check_debug Running Transaction Test Transaction Test Succeeded Running Transaction Installing : postgresql92-libs-9.2.1-1PGDG.rhel6.i686 Installing : postgresql92-9.2.1-1PGDG.rhel6.i686 Installing : postgresql92-server-9.2.1-1PGDG.rhel6.i686 Installing : postgresql92-plperl-9.2.1-1PGDG.rhel6.i686 Installing : postgresql92-contrib-9.2.1-1PGDG.rhel6.i686 Verifying : postgresql92-plperl-9.2.1-1PGDG.rhel6.i686 Verifying : postgresql92-server-9.2.1-1PGDG.rhel6.i686 Verifying : postgresql92-libs-9.2.1-1PGDG.rhel6.i686 Verifying : postgresql92-contrib-9.2.1-1PGDG.rhel6.i686 Verifying : postgresql92-9.2.1-1PGDG.rhel6.i686 Installed: postgresql92.i686 0:9.2.1-1PGDG.rhel6 postgresql92-contrib.i686 0:9.2.1-1PGDG.rhel6 pos postgresql92-plperl.i686 0:9.2.1-1PGDG.rhel6 postgresql92-server.i686 0:9.2.1-1PGDG.rhel6 Complete!
3、初始化數(shù)據庫并啟動
安裝操作順利完成后，對數(shù)據庫進行初始化，然后啟動服務
[root@wardking ~]# service postgresql-9.2 initdb Initializing database: [ OK ] [root@wardking ~]# service postgresql-9.2 start Starting postgresql-9.2 service: [ OK ]如果有錯誤信息出現(xiàn)，檢查日志文件 /var/lib/pgsql/9.2/data/pg_log，查找線索
4、 配置工作環(huán)境
PostgreSQL數(shù)據庫操作的默認賬戶為postgres，其操作目錄為/var/lib/pgsql，配置文件.bash_profile中的默認內容為
[ -f /etc/profile ] && source /etc/profile PGDATA=/var/lib/pgsql/9.1/data export PGDATA以上配置中包括了數(shù)據庫所在的路徑，但是沒有可執(zhí)行命令文件所在的目錄，為操作便利，更正如下：
[ -f /etc/profile ] && source /etc/profile PGDATA=/var/lib/pgsql/9.1/data export PGDATA PATH=$PATH:$HOME/bin:/usr/pgsql-9.1/bin export PATH5、設置默認賬戶postgres的操作密碼
[root@wardking ~]# su – postgres -bash-4.1$ psql postgres postgres psql (9.2.1) Type “help” for help. postgres=# alter user postgres with password ‘postgres’; ALTER ROLE postgres=#6、 提升postgreSQL安全
默認配置中，postgreSQL使用明文存儲密碼，非常不安全，需要修改配置文件，更改密碼驗證方式為md5
[root@wardking ~]# vi /var/lib/pgsql/9.1/data/pg_hba.conf # Put your actual configuration here # ———————————- # # If you want to allow non-local connections, you need to add more # “host” records. In that case you will also need to make PostgreSQL # listen on a non-local interface via the listen_addresses # configuration parameter, or via the -i or -h command line switches. # TYPE DATABASE USER ADDRESS METHOD # “local” is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 # Allow replication connections from localhost, by a user with the # replication privilege. #local replication postgres peer #host replication postgres 127.0.0.1/32 ident #host replication postgres ::1/128 ident為了使配置文件生效，必須讓postgreSQL重新載入配置，有多種方式可以實現(xiàn)：
第一種方式： 直接使用命令操作
[root@wardking ~]# su – postgres -bash-4.1$ pg_ctl reload server signaled -bash-4.1$第二種方式：切換到postgres用戶，通過psql命令，調用pg_reload_conf();
-bash-4.1$ psql postgres postgres psql (9.1.1) Type “help” for help. postgres=# select pg_reload_conf(); pg_reload_conf —————- t (1 row) postgres=#第三種方式：切換到postgres用戶，同樣使用psql命令，但無須登錄PostgreSQL控制臺
-bash-4.1$ psql postgres postgres -c “select pg_reload_conf();” Password for user postgres: pg_reload_conf —————- t (1 row) -bash-4.1$允許遠程連接PostgreSQL數(shù)據庫
PostgreSQL的主配置文件postgresql.conf位于目錄/var/lib/pgsql/9.2/data，其中CONNECTIONS AND AUTHENTICATION段中的配置如下
#—————————————————————————— # CONNECTIONS AND AUTHENTICATION #—————————————————————————— # – Connection Settings - #listen_addresses = ‘localhost’ # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to ‘localhost’, ‘*’ = all # (change requires restart) #port = 5432 # (change requires restart)以上默認設置僅接受來自本地(Localhost)的連接，如果需要允許遠程主機連接，需要將listen_addresses注釋取消，并將localhost更改為*或者指定的網卡IP地址。此處還可以更改默認的監(jiān)聽端口5432
修改完成后，需要重新啟動postgresql-9.2系統(tǒng)服務，重新載入配置文件后方可生效。
如果修改該文件導致服務無法啟動，可以查看/var/lib/pgsql/9.2/pg_log文件，查找線索進行排錯。
重新啟動完成后，進入postgres用戶控制臺，登錄數(shù)據庫操作界面，可以驗證更改結果
-bash-4.1$ psql Password: psql (9.1.1) Type “help” for help. postgres=# show listen_addresses; listen_addresses —————— * (1 row) postgres=# show port; port —— 5432 (1 row) postgres=#原文地址：http://www.wardking.com/2012/10/%e4%b8%bacentos%e5%ae%89%e8%a3%85%e6%9c%80%e6%96%b0%e7%89%88postgresql/
|
|
發(fā)表留言請先登錄！
|