PostgreSQL二进制安装和源码安装
一、下载
1.二进制包下载
https://www.postgresql.org/download/
2.源码安装包下载
https://www.postgresql.org/download/
The source code can be found in the main file browser or you can access the source control repository directly at git.postgresql.org. Instructions for building from source can be found in the documentation.
二、安装
1.二进制包安装
1.1 关闭防火墙
# systemctl stop firewalld.service
# systemctl disable firewalld.service
# systemctl status firewalld.service1.2 关闭Selinx
# setenforce 0
# sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
# getenforce1.3 安装依赖包
# yum install readline make zlib1.4 安装
- 安装完PostgreSQL后会自动创建Postgres用户和组
- 默认安装目录
/usr/pgsql-11数据目录默认/var/lib/pgsql/11
# yum install -y postgresql11-server
# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# id postgres
uid=26(postgres) gid=26(postgres) groups=26(postgres)
# rpm -qa|grep postgresql
postgresql11-server-11.19-1PGDG.rhel7.x86_64
postgresql11-libs-11.19-1PGDG.rhel7.x86_64
postgresql11-11.19-1PGDG.rhel7.x86_64
# rpm -ql postgresql11-server-11.19-1PGDG.rhel7.x86_64
# ll /usr/pgsql-11/
# ll /var/lib/pgsql/11
参考官方安装文档脚本:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql11-server
sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
sudo systemctl enable postgresql-11
sudo systemctl start postgresql-111.5 设置postgres用户环境变量
---修改postgres家目录位置
# grep -i postgres /etc/passwd
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
# usermod -m -d /home/postgres postgres
# grep -i postgres /etc/passwd
postgres:x:26:26:PostgreSQL Server:/home/postgres:/bin/bash
---添加环境变量
# su - postgres
Last login: Sun Apr 2 07:37:01 CST 2023 on pts/0
-bash-4.2$
-bash-4.2$ cp /etc/skel/.bashrc .
# su - postgres
Last login: Sun Apr 2 08:10:14 CST 2023 on pts/0
$ vim .bash_profile
#export LANG=en_US.UTF-8
export PS1="[\u@\h \W]\$ "
export PGHOME=/usr/pgsql-11
export PGDATA=/pgdata
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/pgsql-11/lib
export PATH=$PATH:$HOME/bin:/usr/pgsql-11/bin
$ source .bash_profile 1.6 修改Systemd目录位置
创建目录并授权：
# mkdir /pgdata
# chown postgres.postgres /pgdata
修改Systemd中数据路径：
# vim /usr/lib/systemd/system/postgresql-11.service
Environment=PGDATA=/pgdata/
重新加载Systemd：
# systemctl daemon-reload1.7 初始化数据
如果不修改Home家目录，直接执行postgresql-11-setup initdb保持默认即可，否则需要修改postgresql-11-setup脚本
--修改postgresql-11-setup路径：
# vim /usr/pgsql-11/bin/postgresql-11-setup
PREVDATADIR=/home/postgres/$PREVMAJORVERSION/data
PGUPLOG=/home/postgres/$PGMAJORVERSION/pgupgrade.log
PGLOG=/home/postgres/$PGMAJORVERSION/initdb.log
# mkdir /home/postgres/11
# /usr/pgsql-11/bin/postgresql-11-setup initdb
Initializing database ... OK
# more /home/postgres/11/initdb.log
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 /pgdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Asia/Shanghai
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Success. You can now start the database server using:
/usr/pgsql-11/bin/pg_ctl -D /pgdata/ -l logfile start
# ll /pgdata/
total 48
drwx------ 5 postgres postgres 41 Apr 2 06:27 base
drwx------ 2 postgres postgres 4096 Apr 2 06:27 global
drwx------ 2 postgres postgres 6 Apr 2 06:27 log
drwx------ 2 postgres postgres 6 Apr 2 06:27 pg_commit_ts
drwx------ 2 postgres postgres 6 Apr 2 06:27 pg_dynshmem
-rw------- 1 postgres postgres 4269 Apr 2 06:27 pg_hba.conf
-rw------- 1 postgres postgres 1636 Apr 2 06:27 pg_ident.conf
drwx------ 4 postgres postgres 68 Apr 2 06:27 pg_logical
drwx------ 4 postgres postgres 36 Apr 2 06:27 pg_multixact
drwx------ 2 postgres postgres 18 Apr 2 06:27 pg_notify
drwx------ 2 postgres postgres 6 Apr 2 06:27 pg_replslot
drwx------ 2 postgres postgres 6 Apr 2 06:27 pg_serial
drwx------ 2 postgres postgres 6 Apr 2 06:27 pg_snapshots
drwx------ 2 postgres postgres 6 Apr 2 06:27 pg_stat
drwx------ 2 postgres postgres 6 Apr 2 06:27 pg_stat_tmp
drwx------ 2 postgres postgres 18 Apr 2 06:27 pg_subtrans
drwx------ 2 postgres postgres 6 Apr 2 06:27 pg_tblspc
drwx------ 2 postgres postgres 6 Apr 2 06:27 pg_twophase
-rw------- 1 postgres postgres 3 Apr 2 06:27 PG_VERSION
drwx------ 3 postgres postgres 60 Apr 2 06:27 pg_wal
drwx------ 2 postgres postgres 18 Apr 2 06:27 pg_xact
-rw------- 1 postgres postgres 88 Apr 2 06:27 postgresql.auto.conf
-rw------- 1 postgres postgres 24019 Apr 2 06:27 postgresql.conf
或
---执行下面这个命令初始化，不需要修改postgresql-11-setup脚本
$ /usr/pgsql-11/bin/pg_ctl -D /pgdata initdb1.8 启动PG数据库
# systemctl start postgresql-111.9 设置开机自动启动PG
# systemctl enable postgresql-11
或
# pg_ctl -D /pgdata -l server.log start1.10 查看进程
# ps -ef | grep postgres
postgres 71725 1 0 06:35 ? 00:00:00 /usr/pgsql-11/bin/postmaster -D /pgdata/
postgres 71727 71725 0 06:35 ? 00:00:00 postgres: logger
postgres 71729 71725 0 06:35 ? 00:00:00 postgres: checkpointer
postgres 71730 71725 0 06:35 ? 00:00:00 postgres: background writer
postgres 71731 71725 0 06:35 ? 00:00:00 postgres: walwriter
postgres 71732 71725 0 06:35 ? 00:00:00 postgres: autovacuum launcher
postgres 71733 71725 0 06:35 ? 00:00:00 postgres: stats collector
postgres 71734 71725 0 06:35 ? 00:00:00 postgres: logical replication launcher
root 71748 68683 0 06:36 pts/0 00:00:00 grep --color=auto postgres
# lsof -i:5432
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
postmaste 71725 postgres 3u IPv6 155963 0t0 TCP localhost:postgres (LISTEN)
postmaste 71725 postgres 4u IPv4 155964 0t0 TCP localhost:postgres (LISTEN)
[root@server01 ~]# netstat -nltp|grep 5432
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 71725/postmaster
tcp6 0 0 ::1:5432 :::* LISTEN 71725/postmaster
[root@server01 ~]# netstat -an|grep 5432
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN
tcp6 0 0 ::1:5432 :::* LISTEN
unix 2 [ ACC ] STREAM LISTENING 155975 /tmp/.s.PGSQL.5432
unix 2 [ ACC ] STREAM LISTENING 155973 /var/run/postgresql/.s.PGSQL.5432
1.11 修改监听IP、端口、连接数
# find / -name "postgresql.conf"
/pgdata/postgresql.conf
---修改IP、端口、连接数
listen_addresses = '192.168.1.68' # what IP address(es) to listen on;
port = 1314 # (change requires restart)
max_connections = 600
# systemctl stop postgresql-11
# systemctl start postgresql-111.12 开启远程登录数据库
# psql -h 192.168.1.68 -d postgres -U postgres -p 1314
psql: FATAL: no pg_hba.conf entry for host "192.168.1.68", user "postgres", database "postgres", SSL off
---此报错是因为需要开启远程连接
# vim /pgdata/pg_hba.conf
host all all 192.168.1.68/32 trust
host replication all 192.168.1.68/32 trust
# systemctl stop postgresql-11
# systemctl start postgresql-11
[root@server01 ~]# psql -h 192.168.1.68 -d postgres -U postgres -p 1314
psql (11.19)
Type "help" for help.
postgres=# 
2.源码安装
2.1 关闭防火墙
# systemctl stop firewalld.service
# systemctl disable firewalld.service
# systemctl status firewalld.service2.2 关闭Selinx
# setenforce 0
# sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
# getenforce2.3 安装依赖包
# yum instal gcc make readline readline-devel zlib-devel2.4 安装
# cd /opt/soft/
# tar xvf postgresql-10.18.tar.gz
[root@server01 soft]# cd postgresql-10.18/
[root@server01 postgresql-10.18]# ll
total 692
-rw-r--r-- 1 1107 1107 493 Aug 10 2021 aclocal.m4
drwxrwxrwx 2 1107 1107 4096 Aug 10 2021 config
-rwxr-xr-x 1 1107 1107 511461 Aug 10 2021 configure
-rw-r--r-- 1 1107 1107 80458 Aug 10 2021 configure.in
drwxrwxrwx 55 1107 1107 4096 Aug 10 2021 contrib
-rw-r--r-- 1 1107 1107 1192 Aug 10 2021 COPYRIGHT
drwxrwxrwx 3 1107 1107 107 Aug 10 2021 doc
-rw-r--r-- 1 1107 1107 4197 Aug 10 2021 GNUmakefile.in
-rw-r--r-- 1 1107 1107 284 Aug 10 2021 HISTORY
-rw-r--r-- 1 1107 1107 74046 Aug 10 2021 INSTALL
-rw-r--r-- 1 1107 1107 1665 Aug 10 2021 Makefile
-rw-r--r-- 1 1107 1107 1212 Aug 10 2021 README
drwxrwxrwx 16 1107 1107 328 Aug 10 2021 src
# ./configure --prefix=/opt/pgsql
# make
# make install
# ll /opt/pgsql/
total 16
drwxr-xr-x 2 root root 4096 Apr 2 01:08 bin
drwxr-xr-x 6 root root 4096 Apr 2 01:08 include
drwxr-xr-x 4 root root 4096 Apr 2 01:08 lib
drwxr-xr-x 6 root root 4096 Apr 2 01:08 share
---参考官方文档
./configure
make
su
make install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test2.5 创建postgre用户及目录
# useradd postgres
# id postgres
uid=1001(postgres) gid=1001(postgres) groups=1001(postgres)
# mkdir /pgdata
# chown -R postgres.postgres /opt/pgsql
# chown postgres.postgres /pgdata2.6 设置postgres用户环境变量
# su - postgres
$ vim .bash_profile
#export LANG=en_US.UTF-8
export PS1="[\u@\h \W]\$ "
export PGHOME=/opt/pgsql
export PGDATA=/pgdata
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGHOME/lib
export PATH=$PATH:$HOME/bin:$PGHOME/bin
$ source .bash_profile 2.7 初始化数据
使用postgres用户初始化数据
$ initdb
或
$ initdb -D /pgdata
或
$ pg_ctl initdb -D /pgdata2.8 修改监听IP、端口、连接数
# find / -name "postgresql.conf"
/pgdata/postgresql.conf
---修改IP、端口、连接数
# vim /pgdata/postgresql.conf
listen_addresses = '192.168.1.68' # what IP address(es) to listen on;
port = 1314 # (change requires restart)
max_connections = 600
2.9 启动PG数据库
$ pg_ctl start
waiting for server to start.... done
server started
或
$ pg_ctl -D /pgdata -l postgresql.log start
---登录
$ psql -p 13142.10 查看进程
[postgres@server01 ~]$ ps -ef|grep postgres
root 82245 20471 0 01:40 pts/1 00:00:00 su - postgres
postgres 82246 82245 0 01:40 pts/1 00:00:00 -bash
postgres 82506 1 0 01:44 pts/1 00:00:00 /opt/pgsql/bin/postgres
postgres 82508 82506 0 01:44 ? 00:00:00 postgres: checkpointer process
postgres 82509 82506 0 01:44 ? 00:00:00 postgres: writer process
postgres 82510 82506 0 01:44 ? 00:00:00 postgres: wal writer process
postgres 82511 82506 0 01:44 ? 00:00:00 postgres: autovacuum launcher process
postgres 82512 82506 0 01:44 ? 00:00:00 postgres: stats collector process
postgres 82513 82506 0 01:44 ? 00:00:00 postgres: bgworker: logical replication launcher
postgres 82564 82246 0 01:44 pts/1 00:00:00 ps -ef
postgres 82565 82246 0 01:44 pts/1 00:00:00 grep --color=auto postgres
[postgres@server01 ~]$ lsof -i:1314
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
postgres 82506 postgres 3u IPv4 179089 0t0 TCP server01:pdps (LISTEN)2.11 开启远程登录数据库
$ psql -h 192.168.1.68 -d postgres -U postgres -p 1314
psql: FATAL: no pg_hba.conf entry for host "192.168.1.68", user "postgres", database "postgres", SSL off
---此报错是因为需要开启远程连接，允许192.168.1网段所有机器都能连接
# vim /pgdata/pg_hba.conf
host all all 192.168.1.0/24 trust
host replication all 192.168.1.0/24 trust
host all all 172.26.114.88/32 md5 # 允许单个机器登录，使用md5密码。
host all all 0.0.0.0/0 md5 # 允许所有机器登录，使用md5密码。
host all all 127.0.0.1/32 trust # 允许本地机器登录，不使用密码。
host all all 192.168.1.0/24 trust # 允许192.168.1网段机器登录，不使用密码。
$ pg_ctl -D /pgdata -l postgresql.log reload
$ psql -h 192.168.1.68 -d postgres -U postgres -p 1314
psql (11.19)
Type "help" for help.
postgres=# 
2.12 设置开机自动启动PG
# vim /usr/lib/systemd/system/postgresql-10.service
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
#Environment=PGPORT=5432
Environment=PGPORT=1314
Environment=PGDATA=/pgdata/
OOMScoreAdjust=-1000
ExecStart=/opt/pgsql/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/opt/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/opt/pgsql/bin/pg_ctl reload -D ${PGDATA} -s
TimeoutSec=300
[Install]
WantedBy=multi-user.target
---可以使用postgres用户以下命令启动关闭数据库
# systemctl daemon-reload
# systemctl start postgresql-10.service
# systemctl enable postgresql-10.service
# systemctl stop postgresql-10.service