PG的安装与配置
安装方式很多种：RPM包，YUM安装，源码安装
1.RPM包安装
1.1下载RPM包
wget http://yum.postgresql.org/9.4/redhat/rhel-5-x86_64/postgresql94-9.4.4-1PGDG.rhel5.x86_64.rpm
wget http://yum.postgresql.org/9.4/redhat/rhel-5-x86_64/postgresql94-server-9.4.4-1PGDG.rhel5.x86_64.rpm
wget http://yum.postgresql.org/9.4/redhat/rhel-5-x86_64/postgresql94-contrib-9.4.4-1PGDG.rhel5.x86_64.rpm
wget http://yum.postgresql.org/9.4/redhat/rhel-5-x86_64/postgresql94-libs-9.4.4-1PGDG.rhel5.x86_64.rpm
当然版本可以去官网上下载最新的。1.2安装包；
# rpm -ivh postgresql94-libs-9.4.4-1PGDG.rhel6.i686.rpm
# rpm -ivh postgresql94-9.4.4-1PGDG.rhel6.i686.rpm
# rpm -ivh postgresql94-server-9.4.4-1PGDG.rhel6.i686.rpm
# rpm -ivh postgresql94-contrib-9.4.4-1PGDG.rhel6.i686.rpm
如果出现缺少包的情况，则根据源码安装的包去安装一下即可。
装完，数据库就装好了。剩下就是初始化数据库。
1.3初始化数据库
# service postgresql-9.4 initdb
或者
initdb -D /var/lib/pgsql/9.4/data
pg_ctl -D /var/lib/pgsql/9.4/data
1.4启动服务# service postgresql-9.4 start
或者
#pg_ctl start
同样停止数据库
就是#pg_ctl stop
1.5对postgres用户的.bash_profile文件进行设置
和源码安装一样。
1.6进入数据库
[postgres@prod ~]$ psql
psql (9.2.2)
Type "help" for help.
postgres=#
1.7还有对postgresql.conf的设置，参考官方文档。
2.YUM安装
2.1首先配置一些yum源
在【Base】和[Update]之间，添加一句exclude=postgresql*。根据官方文档解释：是为了解决依赖性问题：
Configure your YUM repository
Locate and edit your distributions .repo file, located:
- On Fedora: /etc/yum.repos.d/fedora.repo and /etc/yum.repos.d/fedora-updates.repo, [fedora] sections
- On CentOS: /etc/yum.repos.d/CentOS-Base.repo, [base] and [updates] sections
- On Red Hat: /etc/yum/pluginconf.d/rhnplugin.conf [main] section
To the section(s) identified above, you need to append a line (otherwise dependencies might resolve to the postgresql supplied by the base repository):
exclude=postgresql*
yum local install http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm
2.3安装数据库
yum list postgre*查看相关postgresql相关的包，根据需要安装。
yum install postgresql94-server
会自动安装相关的包，这些前面的配置yum就用上了。前几天我安装网上一些做法怎么搞都解决不了依赖性问题。这下去官网上，一下搞定。。呵呵
到此基本数据库已经安装完毕。
2.4老活，初始化数据库。不再多说。
2.4老活，初始化数据库。不再多说。
3.源码安装
2.1必要的包的安装
yum -y install lrzsz sysstat e4fsprogs ntp readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc make smartmontools flex bison perl perl-devel perl-ExtUtils* OpenIPMI-tools systemtap-sdt-devel perl-ExtUtils-Embed
2.2下载一些安装文件
2.2下载一些安装文件
$ wget http://http://www.postgresql.org/ftp/source/v9.4.4/postgresql-9.4.4.tar.bz2
postgresql:http://www.postgresql.org/ftp/source/v9.4.4/postgresql-9.4.4.tar.bz2
postgresql:http://www.postgresql.org/ftp/source/v9.4.4/postgresql-9.4.4.tar.bz2
2.3安装flex
解压:xz -d flex-2.5.39.tar.xz
tar xvf flex-2.5.39.tar
，./configure,make,make install
查看版本：flex -V
2.4修改一些系统参数
vi /etc/sysctl.conf
kernel.shmmni = 4096
kernel.sem = 50100 64128000 50100 1280
fs.file-max = 7672460
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.core.netdev_max_backlog = 10000
vm.overcommit_memory = 0
net.ipv4.ip_conntrack_max = 655360
fs.aio-max-nr = 1048576
net.ipv4.tcp_timestamps = 0
sysclt -p
vi /etc/security/limits.conf
* soft nofile 131072
* hard nofile 131072
* soft nproc 131072
* hard nproc 131072
* soft core unlimited
* hard core unlimited
* soft memlock 50000000
* hard memlock 50000000
关闭selinux
vi /etc/sysconfig/selinux
SELINUX=disable
查看状态：/usr/sbin/sestatus -v
防火墙关闭或者设置一下只对局域网访问。
查看状态：service iptables status
vi /etc/sysconfig/iptables
-A INPUT -s 192.168.0.0/5432 -j ACCEPT
service network restart
添加用户
useradd postgres
cd /opt/soft_bak/
tar -jxvf postgresql-9.4.4.tar.bz2
tar -zxvf pgfincore.tar.gz
mv pgfincore postgresql-9.4.4/contrib/
配置postgres用户的参数文件
vi /home/postgres/.bash_profile
export PGPORT=5432
export PGDATA=/home/postgres/pgdata
export LANG=en_US.utf8
export PGHOME=/opt/pgsql
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGUSER=postgres
export PGHOST=$PGDATA
export PGDATABASE=postgres
安装systemtap环境
http://debuginfo.centos.org/6/x86_64/
vi /etc/yum.repos.d/CentOS-Debuginfo.repo
enabled=1
# uname -r
2.6.32-358.el6.x86_64
yum install -y kernel-devel-2.6.32-358.el6.x86_64 kernel-debuginfo-2.6.32-358.el6.x86_64 kernel-debuginfo-common-x86_64-2.6.32-358.el6.x86_64 systemtap
stap -v -e 'probe vfs.read {printf("read performed\n"); exit()}'
https://sourceware.org/systemtap/ftp/releases/ 或下载systemtap源码编译
2.4.安装数据库
cd /opt/soft_bak/postgresql-9.4.4
./configure --prefix=/opt/pgsql9.4.4 --with-pgport=5432 --with-perl --with-tcl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enablethread-safety --with-wal-blocksize=8 --with-blocksize=8 --enable-dtrace --enable-debug --enable-cassert #调试用的。
gmake world
gmake install-world
ln -s /opt/pgsql9.4.4 /opt/pgsql(软连接)
初始化数据库： initdb -D $PGDATA -E UTF8 --locale=C -U postgres -W
然后psql进入数据库，
如果报错：
[postgres@prod pgdata]$ psql
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/home/postgres/pgdata/.s.PGSQL.5432"?
错误原因是没有找到相应的文件：
第一,可以通过：
[postgres@prod pgdata]$ psql -h 127.0.0.1
psql (9.4.4)
Type "help" for help.
postgres=# \q
第二：修改postgresql.conf中unix_socket_directories = '/home/postgres/pgdata/'参数
然后：
[postgres@prod pgdata]$ pg_ctl stop -m fast -D $PGDATA
waiting for server to shut down....LOG: received fast shutdown request
LOG: aborting any active transactions
LOG: autovacuum launcher shutting down
LOG: shutting down
LOG: database system is shut down
done
server stopped
[postgres@prod pgdata]$ pg_ctl start -D $PGDATA
server starting
[postgres@prod pgdata]$ LOG: database system was shut down at 2015-08-24 12:50:41 CST
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
[postgres@prod pgdata]$ psql
psql (9.4.4)
Type "help" for help.
postgres=# \q
2.5安装pgfincore
cd /opt/soft_bak/postgresql-9.4.4/contrib/pgfincore
. /home/postgres/.bash_profile
make clean
make
make install
2.6设置postgresql.conf
详细见附件postgresql.conf
其中log_statement = 'ddl' 这参数，会记录创建的用户和密码到日志的。
所以要暂时设定set log_statement =none；临时不会记录到日志了。
3.PGADMIN III安装使用
windows下载安装pgadmin III，可以直接官网下载http://www.postgresql.org/ftp/pgadmin3/release/v1.18.1/win32/
然后直接安装，连接远程pg数据库
其中端口号和数据库中设置的端口号要一直。。
3.1如果连接不上，要查看pg_hba.conf和postgresql.conf文件的配置
如：vi pg_hba.conf
host all all 192.168.0.0/0 md5 ---这个的意思就是192.168的这个网段的ip都可以访问
vi postgresql.conf
listen_addresses = '192.168.0.0' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 1234 # (change requires restart)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25954236/viewspace-1809282/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
PG一些资料网站
请登录后发表评论
登录
全部评论
