centos v6.5 yum 部署 最新版PostgreSQL (9.3)

目前最新的CentOS 6.5中，仍然使用的是PostgreSQL 8.4版本。为了满足另一应用系统的需求，必须安装PostgreSQL 9及以上版本。本文将叙述如何使用PostgreSQL官方软件仓库和YUM工具，实现自动安装PostgeSQL 9.3。我使用的CentOS版本为6.5，该操作方法同样适用于使用CentOS 5, RedHat或者Fedora版本的朋友，只是需要注意下载文件时有对应的版本选择。
 
1、下载并安装PostgreSQL官方软件仓库
　　访问http://yum.pgrpms.org/repopackages.php，选择对应的操作系统版本，下载repo rpm文件。对应我的操作系统CentOS 6.5 64位版本，使用wget命令下载，操作如下：
 
1
2
wget http://yum.pgrpms.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
    
输出如下:
[root@mycentos ~]# wget http://yum.pgrpms.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
--2014-03-31 14:41:30-- http://yum.pgrpms.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
Resolving yum.pgrpms.org... 98.129.198.114, 2001:4800:7903:3::114
Connecting to yum.pgrpms.org|98.129.198.114|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5240 (5.1K) [application/x-redhat-package-manager]
Saving to: 鈥減gdg-centos93-9.3-1.noarch.rpm鈥
 
100%[=======================================================================================================================================>] 5,240 --.-K/s in 0s
 
2014-03-31 14:41:32 (26.2 MB/s) - 鈥減gdg-centos93-9.3-1.noarch.rpm鈥saved [5240/5240]
 
安装软件仓库
1
rpm -ivh pgdg-centos93-9.3-1.noarch.rpm 
输出如下:
warning: pgdg-centos93-9.3-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
 
Preparing… ########################################### [100%]
 
1:pgdg-centos93 ########################################### [100%]
 
编辑CentOS-Base.repo，将目前的CentOS仓库中的版本排除
1
vi /etc/yum.repos.d/CentOS-Base.repo
添加如下红色部分代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[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出现在可用软件列表中
1
yum list postgres*
输出如下
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
 * base: centos.mirror.cdnetworks.com
 * extras: centos.mirror.cdnetworks.com
 * updates: centos.mirror.cdnetworks.com
Installed Packages
postgresql93.i686                                                                         9.3.4-1PGDG.rhel6                      @pgdg93
postgresql93-contrib.i686                                                                 9.3.4-1PGDG.rhel6                      @pgdg93
postgresql93-devel.i686                                                                   9.3.4-1PGDG.rhel6                      @pgdg93
postgresql93-libs.i686                                                                    9.3.4-1PGDG.rhel6                      @pgdg93
postgresql93-plperl.i686                                                                  9.3.4-1PGDG.rhel6                      @pgdg93
postgresql93-server.i686                                                                  9.3.4-1PGDG.rhel6                      @pgdg93
Available Packages
postgresql93-debuginfo.i686                                                               9.3.4-1PGDG.rhel6                      pgdg93 
postgresql93-docs.i686                                                                    9.3.4-1PGDG.rhel6                      pgdg93 
postgresql93-odbc.i686                                                                    09.02.0100-1PGDG.rhel6                 pgdg93 
postgresql93-odbc-debuginfo.i686                                                          09.02.0100-1PGDG.rhel6                 pgdg93 
postgresql93-plpython.i686                                                                9.3.4-1PGDG.rhel6                      pgdg93 
postgresql93-pltcl.i686                                                                   9.3.4-1PGDG.rhel6                      pgdg93 
postgresql93-test.i686                                                                    9.3.4-1PGDG.rhel6                      pgdg93 
postgresql_autodoc.noarch                                                                 1.41-1.rhel6                           pgdg93 
 
将旧版本中的postgresql软件包全部删除
1
yum remove postgresql postgresql-contrib postgresql-libs postgresql-devel postgresql-plperl postgr postgresql-devel
Loaded plugins: fastestmirror, security
Setting up Remove Process
No Match for argument: postgr
Loading mirror speeds from cached hostfile
 * base: centos.mirror.cdnetworks.com
 * extras: centos.mirror.cdnetworks.com
 * updates: centos.mirror.cdnetworks.com
Resolving Dependencies
--> Running transaction check
---> Package postgresql93.i686 0:9.3.4-1PGDG.rhel6 will be erased
--> Processing Dependency: postgresql93 = 9.3.4-1PGDG.rhel6 for package: postgresql93-server-9.3.4-1PGDG.rhel6.i686
---> Package postgresql93-contrib.i686 0:9.3.4-1PGDG.rhel6 will be erased
---> Package postgresql93-devel.i686 0:9.3.4-1PGDG.rhel6 will be erased
---> Package postgresql93-libs.i686 0:9.3.4-1PGDG.rhel6 will be erased
---> Package postgresql93-plperl.i686 0:9.3.4-1PGDG.rhel6 will be erased
--> Running transaction check
---> Package postgresql93-server.i686 0:9.3.4-1PGDG.rhel6 will be erased
--> Finished Dependency Resolution
 
Dependencies Resolved
 
=================================================================================================================================================================================
 Package                                           Arch                              Version                                         Repository                            Size
=================================================================================================================================================================================
Removing:
 postgresql93                                      i686                              9.3.4-1PGDG.rhel6                               @pgdg93                              5.1 M
 postgresql93-contrib                              i686                              9.3.4-1PGDG.rhel6                               @pgdg93                              1.6 M
 postgresql93-devel                                i686                              9.3.4-1PGDG.rhel6                               @pgdg93                              5.9 M
 postgresql93-libs                                 i686                              9.3.4-1PGDG.rhel6                               @pgdg93                              616 k
 postgresql93-plperl                               i686                              9.3.4-1PGDG.rhel6                               @pgdg93                              159 k
Removing for dependencies:
 postgresql93-server                               i686                              9.3.4-1PGDG.rhel6                               @pgdg93                               15 M
 
Transaction Summary
=================================================================================================================================================================================
Remove        6 Package(s)
 
Installed size: 29 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Erasing    : postgresql93-contrib-9.3.4-1PGDG.rhel6.i686                                                                           1/6 
  Erasing    : postgresql93-devel-9.3.4-1PGDG.rhel6.i686                                                                             2/6 
  Erasing    : postgresql93-plperl-9.3.4-1PGDG.rhel6.i686                                                                            3/6 
  Erasing    : postgresql93-server-9.3.4-1PGDG.rhel6.i686                                                                            4/6 
  Erasing    : postgresql93-9.3.4-1PGDG.rhel6.i686                                                                                   5/6 
  Erasing    : postgresql93-libs-9.3.4-1PGDG.rhel6.i686                                                                              6/6 
  Verifying  : postgresql93-9.3.4-1PGDG.rhel6.i686                                                                                   1/6 
  Verifying  : postgresql93-devel-9.3.4-1PGDG.rhel6.i686                                                                             2/6 
  Verifying  : postgresql93-plperl-9.3.4-1PGDG.rhel6.i686                                                                            3/6 
  Verifying  : postgresql93-contrib-9.3.4-1PGDG.rhel6.i686                                                                           4/6 
  Verifying  : postgresql93-libs-9.3.4-1PGDG.rhel6.i686                                                                              5/6 
  Verifying  : postgresql93-server-9.3.4-1PGDG.rhel6.i686                                                                            6/6 
 
Removed:
  postgresql93.i686 0:9.3.4-1PGDG.rhel6                    postgresql93-contrib.i686 0:9.3.4-1PGDG.rhel6               postgresql93-devel.i686 0:9.3.4-1PGDG.rhel6              
  postgresql93-libs.i686 0:9.3.4-1PGDG.rhel6               postgresql93-plperl.i686 0:9.3.4-1PGDG.rhel6               
 
Dependency Removed:
  postgresql93-server.i686 0:9.3.4-1PGDG.rhel6                                                                                      
 
Complete!
 
2、 开始安装最新版PostgreSQL
　　现在已经可以使用yum直接安装
1
yum -y install postgresql postgresql-contrib postgresql-libs postgresql-plperl postgresql-server postgresql-devel
Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
 * base: centos.mirror.cdnetworks.com
 * extras: centos.mirror.cdnetworks.com
 * updates: centos.mirror.cdnetworks.com
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql93.i686 0:9.3.4-1PGDG.rhel6 will be installed
---> Package postgresql93-contrib.i686 0:9.3.4-1PGDG.rhel6 will be installed
---> Package postgresql93-devel.i686 0:9.3.4-1PGDG.rhel6 will be installed
---> Package postgresql93-libs.i686 0:9.3.4-1PGDG.rhel6 will be installed
---> Package postgresql93-plperl.i686 0:9.3.4-1PGDG.rhel6 will be installed
---> Package postgresql93-server.i686 0:9.3.4-1PGDG.rhel6 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
=================================================================================================================================================================================
 Package                                           Arch                              Version                                         Repository                           Size
=================================================================================================================================================================================
Installing:
 postgresql93                                      i686                              9.3.4-1PGDG.rhel6                               pgdg93                              998 k
 postgresql93-contrib                              i686                              9.3.4-1PGDG.rhel6                               pgdg93                              480 k
 postgresql93-devel                                i686                              9.3.4-1PGDG.rhel6                               pgdg93                              1.5 M
 postgresql93-libs                                 i686                              9.3.4-1PGDG.rhel6                               pgdg93                              193 k
 postgresql93-plperl                               i686                              9.3.4-1PGDG.rhel6                               pgdg93                               55 k
 postgresql93-server                               i686                              9.3.4-1PGDG.rhel6                               pgdg93                              4.0 M
 
Transaction Summary
=================================================================================================================================================================================
Install       6 Package(s)
 
Total download size: 7.2 M
Installed size: 29 M
Downloading Packages:
(1/6): postgresql93-9.3.4-1PGDG.rhel6.i686.rpm                                                                                       | 998 kB     00:01     
(2/6): postgresql93-contrib-9.3.4-1PGDG.rhel6.i686.rpm                                                                               | 480 kB     00:00     
(3/6): postgresql93-devel-9.3.4-1PGDG.rhel6.i686.rpm                                                                                 | 1.5 MB     00:00     
(4/6): postgresql93-libs-9.3.4-1PGDG.rhel6.i686.rpm                                                                                  | 193 kB     00:00     
(5/6): postgresql93-plperl-9.3.4-1PGDG.rhel6.i686.rpm                                                                                |  55 kB     00:00     
(6/6): postgresql93-server-9.3.4-1PGDG.rhel6.i686.rpm                                                                                | 4.0 MB     00:01     
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                1.2 MB/s | 7.2 MB     00:06     
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : postgresql93-libs-9.3.4-1PGDG.rhel6.i686                                                                              1/6 
  Installing : postgresql93-9.3.4-1PGDG.rhel6.i686                                                                                   2/6 
  Installing : postgresql93-server-9.3.4-1PGDG.rhel6.i686                                                                            3/6 
  Installing : postgresql93-plperl-9.3.4-1PGDG.rhel6.i686                                                                            4/6 
  Installing : postgresql93-devel-9.3.4-1PGDG.rhel6.i686                                                                             5/6 
  Installing : postgresql93-contrib-9.3.4-1PGDG.rhel6.i686                                                                           6/6 
  Verifying  : postgresql93-9.3.4-1PGDG.rhel6.i686                                                                                   1/6 
  Verifying  : postgresql93-devel-9.3.4-1PGDG.rhel6.i686                                                                             2/6 
  Verifying  : postgresql93-plperl-9.3.4-1PGDG.rhel6.i686                                                                            3/6 
  Verifying  : postgresql93-contrib-9.3.4-1PGDG.rhel6.i686                                                                           4/6 
  Verifying  : postgresql93-libs-9.3.4-1PGDG.rhel6.i686                                                                              5/6 
  Verifying  : postgresql93-server-9.3.4-1PGDG.rhel6.i686                                                                            6/6 
 
Installed:
  postgresql93.i686 0:9.3.4-1PGDG.rhel6                    postgresql93-contrib.i686 0:9.3.4-1PGDG.rhel6               postgresql93-devel.i686 0:9.3.4-1PGDG.rhel6               
  postgresql93-libs.i686 0:9.3.4-1PGDG.rhel6               postgresql93-plperl.i686 0:9.3.4-1PGDG.rhel6                postgresql93-server.i686 0:9.3.4-1PGDG.rhel6              
 
Complete!
　3、初始化数据库并启动
　　安装操作顺利完成后，对数据库进行初始化，然后启动服务
1
2
3
4
service postgresql-9.3 initdb
Initializing database:                                     [  OK  ]
service postgresql-9.3 start
Starting postgresql-9.3 service:                           [  OK  ]
    将数据库服务设置为开机自启动
1
2
3
chkconfig postgresql-9.3 on
chkconfig | grep postgres
postgresql-9.3  0:off   1:off   2:on    3:on    4:on    5:on    6:off
如果有错误信息出现，检查日志文件 /var/lib/pgsql/9.2/data/pg_log，查找线索
　　4、 配置工作环境
　　PostgreSQL数据库操作的默认账户为postgres，其操作目录为/var/lib/pgsql，配置文件.bash_profile中的默认内容为
1
2
3
[ -f /etc/profile ] && source /etc/profile
PGDATA=/var/lib/pgsql/9.1/data
export PGDATA

        以上配置中包括了数据库所在的路径，但是没有可执行命令文件所在的目录，为操作便利，更正如下：
1
2
3
4
5
[ -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 PATH
5、设置默认账户postgres的操作密码
1
2
3
4
5
6
7
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
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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             192.168.1.0/24(输入你想远程的网段)            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重新载入配置，有多种方式可以实现：
　　第一种方式： 直接使用命令操作
1
2
3
service postgresql-9.3 restart
Stopping postgresql-9.3 service: [  OK  ]
Starting postgresql-9.3 service: [  OK  ]

允许远程连接PostgreSQL数据库
　　PostgreSQL的主配置文件postgresql.conf位于目录/var/lib/pgsql/9.2/data，其中CONNECTIONS AND AUTHENTICATION段中的配置如下
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#——————————————————————————
 
# 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地址。此处还可以更改默认的监听端口5432
　　修改完成后，需要重新启动postgresql-9.2系统服务，重新载入配置文件后方可生效。
　　如果修改该文件导致服务无法启动，可以查看/var/lib/pgsql/9.2/pg_log文件，查找线索进行排错。
　　重新启动完成后，进入postgres用户控制台，登录数据库操作界面，可以验证更改结果
1
2
3
4
5
6
7
8
9
10
11
12
su - postgres
-bash-4.1$ psql
psql (9.3.4)
Type "help" for help.
 
postgres=# show listen_addresses;
 listen_addresses 
------------------
 localhost
(1 row)
postgres=# 

 
posted @ 2014-03-31 14:29  金零壹  阅读(1382)  评论(0)  编辑  收藏  举报