Postgresql学习笔记（1）

PostgreSQL In BigData BigSQL（整合了pg和hadoop的开源项目） ：http://www.bigsql.org/se/ Hadoopdb（耶鲁大学开源项目）SQL to MapReduce to SQL :http://hadoopdb.sourceforge.net/guide/ http://db.cs.yale.edu/hadoopdb/hadoopdb.pdf

postgreSQL9.4的编译安装 安装前有一个依赖包需要安装较新版本的。：flex,如果没有则需要安装，我这里是2.6.*的版本 1)， ./configure --prefix=/opt/postgresql 9.4

遇到一个错误： configure: error: readline library not found If you have readline already installed,see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-readline to disable readline support. [web@web postgresql-9.4beta1]$ ./configure --prefix=/opt/postgreSQL9.4 --without-realine 检查系统是否安装readline: [web@web postgresql-9.4beta1]$ rpm -qa | grep readline readline-6.0-4.el6.i686 通过yum search查一查系统包

[web@web postgresql-9.4beta1]$ yum search readline

compat-readline5-devel.i686 : Files needed to develop programs which use the : readline library compat-readline5-static.i686 : Static libraries for the readline library libreadline-java-javadoc.i686 : Javadoc for libreadline-java readline-devel.i686 : Files needed to develop programs which use the readline : library readline-static.i686 : Static libraries for the readline library compat-readline5.i686 : A library for editing typed command lines libreadline-java.i686 : Java wrapper for the EditLine library perl-Term-UI.i686 : Term::ReadLine UI made easy readline.i686 : A library for editing typed command lines

Name and summary matches only,use "search all" for everything. 可能需要readline-devel包： [web@web postgresql-9.4beta1]$sudo yum -y install -y readline-devel 安装完成之后再运行：./configure --prefix=/opt/postgreSQL9.4 就成功了

2）， gmake world

3),sudo gmake install-world

安装完成之后，给postgreSQL创建一个用户sec [web@web lib]$ sudo useradd sec

修改环境变量： [sec@web ~]$ vi ~/.bash_profile 添加下面这段 export PGPORT=5432 #监听端口 export PGDATA=/home/sec/pg_root #数据存放目录 export LANG=en_US.utf8 export PGHOME=/opt/postgreSQL9.4 #安装目录 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=sec export PGHOST=$PGDATA alias rm='rm -i' alias ll='ls -lh' export PGDATABASE=sec

最后让环境变量生效[sec@web ~]$ source ~/.bash_profile 出现下面就可以了 [sec@web ~]$ psql -V psql (PostgreSQL) 9.4beta1

初始化数据库

[sec@web ~]$ initdb -D $POSTDATA -E --locale=C -U sec -W -D是指定数据存放目录 -E是指定数据库编码 -U是值得用户 -W初始化时输入超级用户密码

做为之后可以对pgsql进行一些配置 首先配置linux参数： 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

配置完成之后需要应用： [root@web ~]# sysctl -p 然后配置防火墙 [root@web ~]# iptables -L -v -n 先可以看看是否开通pgsql的端口 我这里是5432 [root@web ~]# vi /etc/sysconfig/iptables 添加（允许任意IP）：-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT 限制IP：-A INPUT -s 192.168.0.0/16 -j ACCEPT

改完重启防火墙

最后还有一项优化： [root@web ~]# 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 保存即可 修改配置文件 vi /home/sec/pg_root/postgresql.conf 第一条： 修改#listen_addresses = 'localhost' # what IP address(es) to listen on; 为listen_addresses = '0.0.0.0' # what IP address(es) to listen on; 第二条： 修改#port = 5432 # (change requires restart) 为port = 5432 # (change requires restart) 第三条： 修改超级用户连接数(我设置为13个)： #superuser_reserved_connections = 3 # (change requires restart) 为superuser_reserved_connections = 13 # (change requires restart) 第四条： 修改#unix_socket_directories = '/tmp' # comma-separated list of directories 为(存放在$PGDATA根目录下)unix_socket_directories = '.' # comma-separated list of directories 第五条： 修改#unix_socket_permissions = 0777 # begin with 0 to use octal notation 为unix_socket_permissions = 0700 # begin with 0 to use octal notation

第六条： 修改 #tcp_keepalives_idle = 0
#tcp_keepalives_interval = 0
#tcp_keepalives_count = 0
为 tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10

第七条： 修改#bgwriter_delay = 200ms # 10-10000ms between rounds 为bgwriter_delay = 10ms # 10-10000ms between rounds 第八条 修改#vacuum_cost_delay = 0 # 0-100 milliseconds 为vacuum_cost_delay = 10 # 0-100 milliseconds 第十条（这一条与share_buffer有关，我这里是128MB,所以除以16等于8） 修改#checkpoint_segments = 3 # in logfile segments,min 1,16MB each 为checkpoint_segments = 8 # in logfile segments,16MB each 第十一条 修改#wal_writer_delay = 100ms # 1-10000 milliseconds 为wal_writer_delay = 10ms # 1-10000 milliseconds

第十二条(日志存放位置)： 修改#log_destination = 'stderr' # Valid values are combinations of 为log_destination = 'csvlog' # Valid values are combinations of 第十三条（如过没有修改十二条则不用改） 修改#logging_collector = off # Enable capturing of stderr and csvlog 为logging_collector = on # Enable capturing of stderr and csvlog

第十四条： 修改 #log_directory = 'pg_log' # directory where log files are written,
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
#log_file_mode = 0600 # creation mode for log files,#log_truncate_on_rotation = off # If on,an existing log file with the #log_rotation_age = 1d # Automatic rotation of logfiles will #log_rotation_size = 10MB # Automatic rotation of logfiles will 为 log_directory = 'pg_log' # directory where log files are written,log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,log_file_mode = 0600 # creation mode for log files,log_truncate_on_rotation = on # If on,an existing log file with the log_rotation_age = 1d # Automatic rotation of logfiles will log_rotation_size = 10MB # Automatic rotation of logfiles will

第十五条： 修改： #log_checkpoints = off #log_connections = off #log_disconnections = off #log_duration = off #log_error_verbosity = default # terse,default,or verbose messages 为 log_checkpoints = on log_connections = on log_disconnections = on #log_duration = off log_error_verbosity = verbose # terse,or verbose messages

最后修改pg的配置文件/home/sec/pg_root/pg_hba.conf 添加一条（允许任意IP连接）：host all all 0.0.0.0/0 md5

修改完成之后，就可以启动数据库了： [sec@web ~]$ pg_ctl start server starting 然后尝试连接数据库： [sec@web ~]$ psql -h 127.0.0.1 -p5432 -U sec postgres psql (9.4beta1) Type "help" for help.

postgres=# db List of tablespaces Name | Owner | Location ------------+-------+---------- pg_default | sec | pg_global | sec | (2 rows)

关于postmaster.pid文件 [sec@web pg_root]$ cat postmaster.pid 1494 #pid进程号 /home/sec/pg_root #data目录 1403656634 #时间戳 5432 #监听端口 . #指代Unix socket目录,这里指代$PGDATA 0.0.0.0 #监听IP 5432001 32768 #共享内存地址信息

安装第三方插件，进入源码包，进入/home/web/soft/postgresql-9.4beta1/contrib 如果是安装所有直接运行gmake & gmake install 如果只安装其中某一个，譬如lo,则进入lo目录，运行make & make install 之后在使用超级用户（以pg_stat_statements为例子）： postgres=# create extension pg_stat_statements; CREATE EXTENSION 添加之后需要注意，是否需要配置，这里的pg_stat_statements是需要的。具体查看http://www.postgresql.org/docs/current/static/pgstatstatements.html 例如这里需要修改postgresql.conf里面的#shared_preload_libraries = ' ' # (change requires restart) 为shared_preload_libraries = 'pg_stat_statements' # (change requires restart),多个加逗号 还添加pg_stat_statements.max = 10000 pg_stat_statements.track = all 然后重启数据库：[sec@web pg_root]$ pg_ctl restart -m fast

切换数据库: postgres=# c kanbox sec

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!