PostgreSQL使用（一）安装

安装依赖

Ubuntu

apt install libreadline-dev zlib1g  zlib1g-dev gcc make
apt install libsystemd-dev openssl libssl-dev libsystemd-dev

Centos7

yum -y install -y readline-devel zlib-devel systemd-devel gcc gcc-c++
yum install -y docbook-dtds docbook-style-xsl fop libxslt openssl openssl-devel

编译并安装

useradd -r -m -s /bin/bash postgres
./configure --prefix=/home/postgres/postgres143 --with-openssl --with-systemd
make -j 4
make install

初始化数据

1、设置环境变量

# vi /home/postgres/.bashr
export PGHOME=/home/postgres/pgsql
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH

2、初始化数据库

mkdir /home/postgres/data
chown -R postgres.postgres /home/postgres/data
su - postgres
postgres143/bin/initdb --pgdata=/home/postgres/data

配置监听

#vi /opt/postgresql/data/postgresql.conf
listen_addresses = '*'

配置systemd

# vim /lib/systemd/system/postgresql.service

[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
Type=forking

User=postgres
Group=postgres

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
# ... but allow it still to be effective for child processes
# (note that these settings are ignored by Postgres releases before 9.5)
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

# Maximum number of seconds pg_ctl will wait for postgres to start.  Note that
# PGSTARTTIMEOUT should be less than TimeoutSec value.
Environment=PGSTARTTIMEOUT=270

Environment=PGDATA=/home/postgres/data

ExecStart=/home/postgres/postgres143/bin/pg_ctl start -D ${PGDATA} -s -w -t ${PGSTARTTIMEOUT}
ExecStop=/home/postgres/postgres143/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/home/postgres/postgres143/bin/pg_ctl reload -D ${PGDATA} -s

# Give a reasonable amount of time for the server to start up/shut down.
# Ideally, the timeout for starting PostgreSQL server should be handled more
# nicely by pg_ctl in ExecStart, so keep its timeout smaller than this value.
TimeoutSec=300

[Install]
WantedBy=multi-user.target

# systemctl enable postgresql
# systemctl start postgresql
# systemctl status postgresql

连接测试

注意：在没有改变任何配置前只允许127.0.0.1访问
# postgres143/bin/psql -h localhost -p 5432 -U postgres -w