目前最新的CentOS 6.3中,仍然使用的是PostgreSQL
8.4版本。为了满足另一应用系统的需求,必须安装PostgreSQL
9及以上版本。本文将叙述如何使用PostgreSQL官方软件仓库和YUM工具,实现自动安装PostgeSQL
9。我使用的CentOS版本为6.3,该操作方法同样适用于使用CentOS 5,
RedHat或者Fedora版本的朋友,只是需要注意下载文件时有对应的版本选择。
安装软件仓库
修改完成后,需要重新启动postgresql-9.2系统服务,重新载入配置文件后方可生效。
如果修改该文件导致服务无法启动,可以查看/var/lib/pgsql/9.2/pg_log文件,查找线索进行排错。
重新启动完成后,进入postgres用户控制台,登录数据库操作界面,可以验证更改结果
- 下载并安装PostgreSQL官方软件仓库
[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â€
安装软件仓库
编辑CentOS-Base.repo,将目前的CentOS仓库中的版本排除
[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%]
[root@wardking ~]# vi /etc/yum.repos.d/CentOS-Base.repo试一下,应该可以看到最新的PostgreSQL出现在可用软件列表中:
# 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*
[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 postgrLoaded plugins: fastestmirrorSetting up Remove ProcessResolving 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 ResolutionDependencies Resolved======================================================================================================================Package Arch Version======================================================================================================================Removing:postgresql i686 8.4.13-1.el6_3postgresql-contrib i686 8.4.13-1.el6_3postgresql-devel i686 8.4.13-1.el6_3postgresql-libs i686 8.4.13-1.el6_3postgresql-plperl i686 8.4.13-1.el6_3postgresql-server i686 8.4.13-1.el6_3Removing for dependencies:libdbi-dbd-pgsql i686 0.8.3-5.1.el6Transaction Summary======================================================================================================================Remove 7 Package(s)Installed size: 34 MIs this ok [y/N]: yDownloading Packages:Running rpm_check_debugRunning Transaction TestTransaction Test SucceededRunning TransactionWarning: RPMDB altered outside of yum.Erasing : postgresql-devel-8.4.13-1.el6_3.i686Erasing : postgresql-contrib-8.4.13-1.el6_3.i686Erasing : postgresql-plperl-8.4.13-1.el6_3.i686Erasing : postgresql-server-8.4.13-1.el6_3.i686Erasing : postgresql-8.4.13-1.el6_3.i686Erasing : libdbi-dbd-pgsql-0.8.3-5.1.el6.i686Erasing : postgresql-libs-8.4.13-1.el6_3.i686Verifying : postgresql-8.4.13-1.el6_3.i686Verifying : postgresql-server-8.4.13-1.el6_3.i686Verifying : postgresql-contrib-8.4.13-1.el6_3.i686Verifying : libdbi-dbd-pgsql-0.8.3-5.1.el6.i686Verifying : postgresql-devel-8.4.13-1.el6_3.i686Verifying : postgresql-plperl-8.4.13-1.el6_3.i686Verifying : postgresql-libs-8.4.13-1.el6_3.i686Removed: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_3Dependency Removed:libdbi-dbd-pgsql.i686 0:0.8.3-5.1.el6Complete!
2. 开始安装最新版PostgreSQL
现在已经可以使用yum直接安装
yum -y install postgresql postgresql-contrib postgresql-libs postgresql-plperl postgresql-serverLoaded plugins: fastestmirrorLoading 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.jpSetting up Install ProcessResolving 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 ResolutionDependencies Resolved======================================================================================================================Package Arch Version======================================================================================================================Installing:postgresql92 i686 9.2.1-1PGDG.rhel6postgresql92-contrib i686 9.2.1-1PGDG.rhel6postgresql92-libs i686 9.2.1-1PGDG.rhel6postgresql92-plperl i686 9.2.1-1PGDG.rhel6postgresql92-server i686 9.2.1-1PGDG.rhel6Transaction Summary======================================================================================================================Install 5 Package(s)Total download size: 5.3 MInstalled size: 21 MDownloading 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———————————————————————————————————————-TotalRunning rpm_check_debugRunning Transaction TestTransaction Test SucceededRunning TransactionInstalling : postgresql92-libs-9.2.1-1PGDG.rhel6.i686Installing : postgresql92-9.2.1-1PGDG.rhel6.i686Installing : postgresql92-server-9.2.1-1PGDG.rhel6.i686Installing : postgresql92-plperl-9.2.1-1PGDG.rhel6.i686Installing : postgresql92-contrib-9.2.1-1PGDG.rhel6.i686Verifying : postgresql92-plperl-9.2.1-1PGDG.rhel6.i686Verifying : postgresql92-server-9.2.1-1PGDG.rhel6.i686Verifying : postgresql92-libs-9.2.1-1PGDG.rhel6.i686Verifying : postgresql92-contrib-9.2.1-1PGDG.rhel6.i686Verifying : postgresql92-9.2.1-1PGDG.rhel6.i686Installed:postgresql92.i686 0:9.2.1-1PGDG.rhel6 postgresql92-contrib.i686 0:9.2.1-1PGDG.rhel6 pospostgresql92-plperl.i686 0:9.2.1-1PGDG.rhel6 postgresql92-server.i686 0:9.2.1-1PGDG.rhel6Complete!
3. 初始化数据库并启动
安装操作顺利完成后,对数据库进行初始化,然后启动服务
[root@wardking ~]# service postgresql-9.2 initdbInitializing database: [ OK ]
[root@wardking ~]# service postgresql-9.2 startStarting postgresql-9.2 service: [ OK ]
如果有错误信息出现,检查日志文件 /var/lib/pgsql/9.2/data/pg_log,查找线索
4. 配置工作环境
PostgreSQL数据库操作的默认账户为postgres,其操作目录为/var/lib/pgsql,配置文件.bash_profile中的默认内容为
[ -f /etc/profile ] && source /etc/profilePGDATA=/var/lib/pgsql/9.1/dataexport PGDATA
以上配置中包括了数据库所在的路径,但是没有可执行命令文件所在的目录,为操作便利,更正如下:
[ -f /etc/profile ] && source /etc/profilePGDATA=/var/lib/pgsql/9.1/dataexport PGDATAPATH=$PATH:$HOME/bin:/usr/pgsql-9.1/binexport PATH
5. 设置默认账户postgres的操作密码
[root@wardking ~]# su – postgres-bash-4.1$ psql postgres postgrespsql (9.2.1)Type “help” for help.postgres=# alter user postgres with password ‘postgres’;ALTER ROLEpostgres=#
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 onlylocal 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重新载入配置,有多种方式可以实现:
第一种方式: 直接使用命令操作
[root@wardking ~]# su – postgres-bash-4.1$ pg_ctl reloadserver signaled-bash-4.1$
第二种方式:切换到postgres用户,通过psql命令,调用pg_reload_conf();
-bash-4.1$ psql postgres postgrespsql (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数据库
#——————————————————————————以上默认设置仅接受来自本地(Localhost)的连接,如果需要允许远程主机连接,需要将listen_addresses注释取消,并将localhost更改为*或者指定的网卡IP地址。此处还可以更改默认的监听端口5432
# 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)
修改完成后,需要重新启动postgresql-9.2系统服务,重新载入配置文件后方可生效。
如果修改该文件导致服务无法启动,可以查看/var/lib/pgsql/9.2/pg_log文件,查找线索进行排错。
重新启动完成后,进入postgres用户控制台,登录数据库操作界面,可以验证更改结果
-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=#