目前最新的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
|
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
|
第一种方式: 直接使用命令操作
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)
|
修改完成后,需要重新启动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=
#
|