PostgreSQL 11.5源码安装

PostgreSQL 11源码安装

PostgreSQL 11安装方式一样

测试环境：RedHat 7.2 Linux 64bit

PostgreSQL 11.5源码安装包下载，下载地址：
https://www.postgresql.org/ftp/source/

系统环境准备
需要安装PostgreSQL编译依赖包，如下：

yum -y install gcc bison gcc-c++ readline readline-devel zlib zlib-devel perl perl-devel perl-ExtUtils-Embed sysstat unzip zip net-tools tcl-devel python-devel

安装PostgreSQL
解压、编译、安装

tar -zxvf postgresql-11.5.tar.gz
cd postgresql-11.5
./configure --prefix=/opt/pgsql/11 --with-python --with-perl
make & make install

安装扩展集
cd postgresql-11.5/contrib
make & make install

创建postgres用户，并创建安装目录

useradd postgres
为了安全考虑，postgresql不允许使用root用户操作数据库，我们在系统中为了使用postgresql添加一个用户postgres：
也可以是其他用户名，比如pg11，但是习惯上大家都是创建postgres用户作为数据库的超级用户。
初始化数据库时，就以这个用户作为数据库的超级用户

mkdir -p /opt/pgsql/11/data
chown -R postgres:postgres /opt/pgsql/

特别注意：
同一台服务器安装多个postgresql需要配置不同用户(用户也可以相同只是配置开机启动和环境变量不方便，建议用不同用户名)、不同路径、不同端口
如果postgresql版本不同还需要源码安装，yum安装默认的

初始化数据库

su - postgres
 
[postgres@postgresql bin]$cd /opt/pgsql/11/bin
[postgres@postgresql bin]$ ./initdb -D /opt/pgsql/11/data/ -E UTF8

详细过程
[postgres@localhost bin]$ ./initdb -D /opt/pgsql/11/data/ -E UTF8
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /opt/pgsql/11/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... America/New_York
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    ./pg_ctl -D /opt/pgsql/11/data/ -l logfile start
    

配置环境变量
su - postgres
vi .bash_profile 添加，不同用户名需要修改PGUSER

export PG_HOME=/opt/pgsql/11
export PGDATA=/opt/pgsql/11/data
export PATH=${PG_HOME}/bin:$PATH
export PGPORT=8432
export PGUSER=postgres
export PGDATABASE=postgres
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.utf8
export DATE=`date +"%Y-%m-%d %H:%M:%S"`
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

source .bash_profile

修改监听地址 将listen_addresses的值设置成*，使其监听整个网络，端口号默认是5432，也可以自己设置。
vi /opt/pgsql/11/data/postgresql.conf
修改内容:
listen_addresses = '*'
port = 8432

修改客户端认证方式,远程使用密码登录
vi /opt/pgsql/11/data/pg_hba.conf
添加内容：
host    all             all             0.0.0.0/0               md5

设置防火墙规则或者关闭防火墙
#切换回root用户
[postgres@localhost ~]$ exit
[postgres@localhost ~]$# firewall-cmd --zone=public --add-port=5432/tcp --permanent
[postgres@localhost ~]$# firewall-cmd --reload

启动数据库
pg_ctl -D /opt/pgsql/11/data/ -l logfile start
[postgres@localhost ~]$ pg_ctl -D /opt/pgsql/11/data/ -l logfile start
waiting for server to start.... done
server started

查看5432端口是否已经启动
[root@localhost pgsql]# netstat -nltp|grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      14699/postgres      
tcp6       0      0 ::1:5432   

登录测试
[root@localhost contrib]# su - postgres
Last login: Thu Oct 24 10:11:00 EDT 2019 on pts/2
[postgres@localhost ~]$ psql
psql (11.5)
Type "help" for help.

postgres=# 
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 

设置postgres用户密码
[postgres@localhost ~]$ psql
postgres=# ALTER USER postgres WITH encrypted PASSWORD 'new password';
postgres=# \q
[postgres@localhost ~]$ psql -h localhost -p 8432 -U postgres 
如果是非postgres用户安装，这里的-U postgres用户名也需要替换 

psql -h localhost -p 5432 -U postgres

配置开机启动
Centios 7.x/Redhat 7.x systemctl启动方式
创建 postgresql-11.service 服务脚本

vi /usr/lib/systemd/system/postgresql-11.service   #修改对应用户、路径和端口

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

[Service]
Type=forking

User=postgres
Group=postgres

# Port number for server to listen on
Environment=PGPORT=8432

# Location of database directory
Environment=PGDATA=/opt/pgsql/11/data

# 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

#ExecStartPre=/opt/pgsql/11/bin/postgresql-check-db-dir ${PGDATA}
ExecStart=/opt/pgsql/11/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/opt/pgsql/11/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/opt/pgsql/11//bin/pg_ctl reload -D ${PGDATA} -s

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

[Install]
WantedBy=multi-user.target

设置权限

[root@localhost postgresql-11.5]# chmod 754 /usr/lib/systemd/system/postgresql-11.service

重新加载启动程序
systemctl daemon-reload

设置为开机自启动：

[root@localhost postgresql-11.5]# systemctl enable postgresql-11
常用指令
启动服务

[root@localhost postgresql-11.5]# systemctl  start  postgresql-11
停止服务

[root@localhost postgresql-11.5]# systemctl stop postgresql-11
重启服务

[root@localhost postgresql-11.5]# systemctl restart postgresql-11
[root@localhost postgresql-11.5]# service postgresql-11 restart
使服务自动启动

[root@localhost postgresql-11.5]# systemctl enable postgresql-11
使服务不自动启动

[root@localhost postgresql-11.5]# systemctl disable postgresql-11
检查服务状态

[root@localhost postgresql-11.5]# systemctl   status  postgresql-11
[root@localhost postgresql-11.5]# systemctl   is-active postgresql-11
显示所有已启动的服务

[root@localhost postgresql-11.5]# systemctl   list-units --type=service

Centios 6.x/Redhat 6.x service启动方式
cd postgresql-11.5/contrib/start-scripts
修改postgresql脚本中prefix、PGUSER和PGDATA的内容

chmod a+x linux.
cp linux /etc/init.d/postgresql-11

此时就可以使用 /etc/init.d/postgresql-11 stop 来停止postgresql-11
也可以使用：service postgresql-11 start 来启动postgresql

设置服务开机启动
chkconfig --add postgresql-11