PgSql在CentOs下编译安装

最近在学习PgSql相关的东西这里记录一下如何进行编译安装一些错误的解决

下载源码并解压

下载源码: wget https://ftp.postgresql.org/pub/source/v9.6.0/postgresql-9.6.0.tar.gz

如果提示无法解析的主机地址请尝试 wget -4 https://ftp.postgresql.org/pub/source/v9.6.0/postgresql-9.6.0.tar.gz 指定IPV4协议
解压源码: tar -xvzf postgresql-9.6.0.tar.gz

编译安装

切换到目录: cd postgresql-9.6.0
检查安装环境: ./configure --prefix=/opt/pgsql
编译并安装: make -j4 && make install 其中-j4代表CPU核心

错误解决

错误: configure: error: could not determine flags for linking embedded Perl.
- 安装依赖: yum install perl-ExtUtils-Embed
错误: configure: error: readline library not found
- 安装依赖: yum install readline-devel
错误: configure: error: header file <Python.h> is required for Python
- 安装依赖: yum install python-devel
错误: configure: error: zlib library not found
- 安装依赖: yum install zlib-devel

添加数据库用户

添加用户: useradd postgresql -s /bin/bash
设置密码: passwd postgresql
添加权限: chown postgresql:postgresql /opt/pgsql -R 如果数据目录不在一起数据目录也要给权限
切换用户: su postgresql

注意下列需要在 postgresql 用户操作

创建数据目录: mkdir home/postgresql/data

设置环境变量:

进入用户配置: vi ~/.bash_profile

添加变量:

PGHOME=/opt/pgsql #数据库安装目录
export PGDATA=/opt/pgsql/data #数据库数据目录
PATH=$PATH:$HOME/bin:$PGHOME/bin

export PATH

应用变量: source ~/.bash_profile

初始化启动数据库

注意需要在 postgresql 用户操作

初始化数据库: initdb
启动数据库: pg_ctl -D $PGDATA -l logfile start
进入数据库: psql -h 127.0.0.1
如故遇到错误提示: psql: FATAL: database "postgresql" does not exist
- 进入临时库: psql -d template1
- 创建数据库: create database postgresql;
- 然后按照上一步操作
- 注意: 其中的 postgresql 不同的用户会有不同的提示请使用提示的用户名
退出: Ctrl+D

操作简化

添加命令别名方便使用 alias pgc='pg_ctl -D $PGDATA -l logfile'
然后输入 pgc [start|stop|restart|reload] 就可以直接执行

开启日志记录

注意: PgSql 默认没有开启操作日志记录需要手动开启

打开日志文件 vi $PGDATA/postgresql.conf 找到如下节点并修改

#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

#log_destination = 'stderr'             # Valid values are combinations of
                                        # stderr, csvlog, syslog, and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.
# 这里去掉注释 修改为on
# This is used when logging to stderr:
logging_collector = on                  # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)

# These are only used if logging_collector is on:
log_directory = 'pg_log'               # 日志保存的目录 可以为绝对路径或者
                                        # 或者保存于$PGDATA下的目录
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
                                        # 日志名称格式,
                                        # can include strftime() escapes
#log_file_mode = 0600                   # creation mode for log files,
                                        # begin with 0 to use octal notation
#log_truncate_on_rotation = off         # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
#log_rotation_age = 1d                  # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
#log_rotation_size = 10MB               # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.