目前最新的CentOS 中仍然使用的是PostgreSQL 版本为了满足另一应用系统的需求必须安装PostgreSQL 及以上版本本文将叙述如何使用PostgreSQL官方软件仓库和YUM工具实现自动安装PostgeSQL 我使用的CentOS版本为该操作方法同样适用于使用CentOS RedHat或者Fedora版本的朋友只是需要注意下载文件时有对应的版本选择
下载并安装PostgreSQL官方软件仓库
访问选择对应的操作系统版本下载repo rpm文件对应我的操作系统CentOS 位版本使用wget命令下载操作如下
[root@wardking ~]# wget – ::– http://yumpgrpmsorg//redhat/rheli/pgdgcentosnoarchrpmResolving yumpgrpmsorg… Connecting to yumpgrpmsorg||:… connectedHTTP request sent awaiting response… OKLength: (K) [application/xredhatpackagemanager]Saving to: pgdgcentosnoarchrpm %[==============================================================================================================================>] K/s in s :: ( KB/s) – “pgdgcentosnoarchrpmâ€
安装软件仓库
[root@wardking ~]# rpm ivh pgdgcentosnoarchrpmwarning: pgdgcentosnoarchrpm: Header V DSA/SHA Signature key ID dff: NOKEYPreparing… ########################################### [%] :pgdgcentos ########################################### [%]
编辑CentOSBaserepo将目前的CentOS仓库中的版本排除
[root@wardking ~]# vi /etc/yumreposd/CentOSBaserepo# remarked out baseurl= line instead [base]name=CentOS$releasever – Basemirrorlist=http://mirrorlistcentosorg/?release=$releasever&arch=$basearch&repo=os#baseurl=http://mirrorcentosorg/centos/$releasever/os/$basearch/gpgcheck=gpgkey=file:///etc/pki/rpmgpg/RPMGPGKEYCentOSexclude=postgresql* #released updates[updates]name=CentOS$releasever – Updatesmirrorlist=http://mirrorlistcentosorg/?release=$releasever&arch=$basearch&repo=updates#baseurl=http://mirrorcentosorg/centos/$releasever/updates/$basearch/gpgcheck=gpgkey=file:///etc/pki/rpmgpg/RPMGPGKEYCentOSexclude=postgresql*
试一下应该可以看到最新的PostgreSQL出现在可用软件列表中
[root@wardking ~]# yum list postgres*Loaded plugins: fastestmirrorLoading mirror speeds from cached hostfile * base: mirrorsbttenet * epel: mirrorsustceducn * extras: mirrorsbttenet * rpmforge: mirrorfairwaynejp * updates: mirrorsbttenetbaseextraspgdgpgdg/primary_dbupdatesInstalled Packagespostgresqli el_postgresqlcontribi el_postgresqldeveli el_postgresqllibsi el_postgresqlplperli el_postgresqlserveri el_Available Packagespostgresqlipri elpostgresqlpgpoolIIi elpostgresqlpgpoolIIdeveli elpostgresqlpgpoolIIrecoveryi elpostgresqlplparroti elpostgresqlplrubyi elpostgresqlplrubydoci elpostgresqlrelayi elrfpostgresqli PGDGrhelpostgresqlcontribi PGDGrhelpostgresqldebuginfoi PGDGrhelpostgresqldeveli PGDGrhelpostgresqldocsi PGDGrhelpostgresqljdbci PGDGrhelpostgresqljdbcdebuginfoi PGDGrhelpostgresqllibsi PGDGrhelpostgresqlodbci PGDGrhelpostgresqlodbcdebuginfoi PGDGrhelpostgresqlplperli PGDGrhelpostgresqlplpythoni PGDGrhelpostgresqlpltcli PGDGrhelpostgresqlserveri PGDGrhelpostgresqltcli rhelpostgresqltcldebuginfoi rhelpostgresqltesti PGDGrhel
将旧版本中的postgresql软件包全部删除
[root@wardking ~]# yum remove postgresql postgresqlcontrib postgresqllibs postgresqldevel postgresqlplperl postgrLoaded plugins: fastestmirrorSetting up Remove ProcessResolving Dependencies–> Running transaction check—> Package postgresqli :el_ will be erased—> Package postgresqlcontribi :el_ will be erased—> Package postgresqldeveli :el_ will be erased—> Package postgresqllibsi :el_ will be erased–> Processing Dependency: libpqso for package: libdbidbdpgsqleli–> Processing Dependency: postgresqllibs for package: libdbidbdpgsqleli—> Package postgresqlplperli :el_ will be erased—> Package postgresqlserveri :el_ will be erased–> Running transaction check—> Package libdbidbdpgsqli :el will be erased–> Finished Dependency ResolutionDependencies Resolved====================================================================================================================== Package Arch Version======================================================================================================================Removing: postgresql i el_ postgresqlcontrib i el_ postgresqldevel i el_ postgresqllibs i el_ postgresqlplperl i el_ postgresqlserver i el_Removing for dependencies: libdbidbdpgsql i elTransaction Summary======================================================================================================================Remove Package(s)Installed size: MIs this ok [y/N]: yDownloading Packages:Running rpm_check_debugRunning Transaction TestTransaction Test SucceededRunning TransactionWarning: RPMDB altered outside of yum Erasing : postgresqldevelel_i Erasing : postgresqlcontribel_i Erasing : postgresqlplperlel_i Erasing : postgresqlserverel_i Erasing : postgresqlel_i Erasing : libdbidbdpgsqleli Erasing : postgresqllibsel_i Verifying : postgresqlel_i Verifying : postgresqlserverel_i Verifying : postgresqlcontribel_i Verifying : libdbidbdpgsqleli Verifying : postgresqldevelel_i Verifying : postgresqlplperlel_i Verifying : postgresqllibsel_iRemoved: postgresqli :el_ postgresqlcontribi :el_ postgresqldeveli : postgresqlplperli :el_ postgresqlserveri :el_Dependency Removed: libdbidbdpgsqli :elComplete!
开始安装最新版PostgreSQL
现在已经可以使用yum直接安装
yum y install postgresql postgresqlcontrib postgresqllibs postgresqlplperl postgresqlserverLoaded plugins: fastestmirrorLoading mirror speeds from cached hostfile * base: centosustceducn * epel: ftpjaistacjp * extras: centosustceducn * rpmforge: mirrorhmcedu * updates: ftpiijadjpSetting up Install ProcessResolving Dependencies–> Running transaction check—> Package postgresqli :PGDGrhel will be installed—> Package postgresqlcontribi :PGDGrhel will be installed—> Package postgresqllibsi :PGDGrhel will be installed—> Package postgresqlplperli :PGDGrhel will be installed—> Package postgresqlserveri :PGDGrhel will be installed–> Finished Dependency ResolutionDependencies Resolved====================================================================================================================== Package Arch Version======================================================================================================================Installing: postgresql i PGDGrhel postgresqlcontrib i PGDGrhel postgresqllibs i PGDGrhel postgresqlplperl i PGDGrhel postgresqlserver i PGDGrhelTransaction Summary======================================================================================================================Install Package(s)Total download size: MInstalled size: MDownloading Packages:(/): postgresqlPGDGrhelirpm(/): postgresqlcontribPGDGrhelirpm(/): postgresqllibsPGDGrhelirpm(/): postgresqlplperlPGDGrhelirpm(/): postgresqlserverPGDGrhelirpm———————————————————————————————————————TotalRunning rpm_check_debugRunning Transaction TestTransaction Test SucceededRunning Transaction Installing : postgresqllibsPGDGrheli Installing : postgresqlPGDGrheli Installing : postgresqlserverPGDGrheli Installing : postgresqlplperlPGDGrheli Installing : postgresqlcontribPGDGrheli Verifying : postgresqlplperlPGDGrheli Verifying : postgresqlserverPGDGrheli Verifying : postgresqllibsPGDGrheli Verifying : postgresqlcontribPGDGrheli Verifying : postgresqlPGDGrheliInstalled: postgresqli :PGDGrhel postgresqlcontribi :PGDGrhel pos postgresqlplperli :PGDGrhel postgresqlserveri :PGDGrhelComplete!
初始化数据库并启动
安装操作顺利完成后对数据库进行初始化然后启动服务
[root@wardking ~]# service postgresql initdbInitializing database: [ OK ][root@wardking ~]# service postgresql startStarting postgresql service: [ OK ]
如果有错误信息出现检查日志文件 /var/lib/pgsql//data/pg_log查找线索
配置工作环境
PostgreSQL数据库操作的默认账户为postgres其操作目录为/var/lib/pgsql配置文件bash_profile中的默认内容为
[ f /etc/profile ] && source /etc/profilePGDATA=/var/lib/pgsql//dataexport PGDATA
以上配置中包括了数据库所在的路径但是没有可执行命令文件所在的目录为操作便利更正如下
[ f /etc/profile ] && source /etc/profilePGDATA=/var/lib/pgsql//dataexport PGDATAPATH=$PATH:$HOME/bin:/usr/pgsql/binexport PATH
设置默认账户postgres的操作密码
[root@wardking ~]# su – postgresbash$ psql postgres postgrespsql ()Type “help” for helppostgres=# alter user postgres with password ‘postgres’;ALTER ROLEpostgres=#
提升postgreSQL安全
默认配置中postgreSQL使用明文存储密码非常不安全需要修改配置文件更改密码验证方式为md
[root@wardking ~]# vi /var/lib/pgsql//data/pg_hbaconf# Put your actual configuration here# ———————————## If you want to allow nonlocal connections you need to add more# “host” records In that case you will also need to make PostgreSQL# listen on a nonlocal 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 md# IPv local connections:host all all / md# IPv local connections:host all all ::/ md# Allow replication connections from localhost by a user with the# replication privilege#local replication postgres peer#host replication postgres / ident#host replication postgres ::/ ident
为了使配置文件生效必须让postgreSQL重新载入配置有多种方式可以实现
第一种方式 直接使用命令操作
[root@wardking ~]# su – postgresbash$ pg_ctl reloadserver signaledbash$
第二种方式切换到postgres用户通过psql命令调用pg_reload_conf();
bash$ psql postgres postgrespsql ()Type “help” for helppostgres=# select pg_reload_conf(); pg_reload_conf————— t( row)postgres=#
第三种方式切换到postgres用户同样使用psql命令但无须登录PostgreSQL控制台
bash$ psql postgres postgres c “select pg_reload_conf();”Password for user postgres: pg_reload_conf————— t( row)bash$
允许远程连接PostgreSQL数据库
PostgreSQL的主配置文件postgresqlconf位于目录/var/lib/pgsql//data其中CONNECTIONS AND AUTHENTICATION段中的配置如下
#——————————————————————————# CONNECTIONS AND AUTHENTICATION#—————————————————————————— # – Connection Settings #listen_addresses = ‘localhost’ # what IP address(es) to listen on; # commaseparated list of addresses; # defaults to ‘localhost’ ‘*’ = all # (change requires restart)#port = # (change requires restart)
以上默认设置仅接受来自本地(Localhost)的连接如果需要允许远程主机连接需要将listen_addresses注释取消并将localhost更改为*或者指定的网卡IP地址此处还可以更改默认的监听端口
修改完成后需要重新启动postgresql系统服务重新载入配置文件后方可生效
如果修改该文件导致服务无法启动可以查看/var/lib/pgsql//pg_log文件查找线索进行排错
重新启动完成后进入postgres用户控制台登录数据库操作界面可以验证更改结果
bash$ psqlPassword:psql ()Type “help” for help postgres=# show listen_addresses; listen_addresses—————— *( row) postgres=# show port; port—— ( row) postgres=#
原文地址%e%b%bacentos%e%ae%%e%a%%e%c%%e%%b%e%%postgresql/
一个人负心，或许是因为他的记忆力不好。
