Centos7.9安装Postgresql

一、下载

1.二进制包下载

https://yum.postgresql.org/rpmchart/

1	https://www.postgresql.org/download/

2.源码安装包下载

https://www.postgresql.org/ftp/source/

1
2
3

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 关闭防火墙

1
2
3

# systemctl stop firewalld.service
# systemctl disable firewalld.service
# systemctl status firewalld.service

1.2 关闭Selinux

1
2
3

# setenforce 0
# sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
# getenforce

1.3 安装依赖包

1	# yum install readline make zlib

1.4 安装

先安装PostgreSQL Repository RPM
安装完PostgreSQL后会自动创建Postgres用户和组
默认安装目录/usr/pgsql-11 数据目录默认/var/lib/pgsql/11

(1)安装postgresql
# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# yum install -y postgresql11-server

(2)初始化数据，初始化数据后,默认会在/var/lib/pgsql/11下生成数据文件
# /usr/pgsql-11/bin/postgresql-11-setup initdb

(3)查看安装
# 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

(4)设置postgresql开启自动启动
systemctl enable postgresql-11

(5)启动pg
# systemctl start postgresql-11

(6)登录pg
# su - postgres
$ psql

参考官方安装文档脚本:

1.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-reload

1.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 initdb

1.8 启动PG数据库

1	# systemctl start postgresql-11

1.9 设置开机自动启动PG

1
2
3

# systemctl enable postgresql-11
或
# pg_ctl -D /pgdata -l server.log start

1.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-11

1.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 关闭防火墙

1
2
3

# systemctl stop firewalld.service
# systemctl disable firewalld.service
# systemctl status firewalld.service

2.2 关闭Selinux

1
2
3

# setenforce 0
# sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
# getenforce

2.3 安装依赖包

1	# yum install gcc make readline readline-devel zlib-devel

2.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 test

2.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 /pgdata

2.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 PGPORT=1314
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 /pgdata

2.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 1314

2.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

Don's Blog

Centos7.9安装Postgresql

一、下载

1.二进制包下载

2.源码安装包下载

二、安装

1.二进制包安装

1.1 关闭防火墙

1.2 关闭Selinux

1.3 安装依赖包

1.4 安装

1.5 设置postgres用户环境变量

1.6 修改Systemd目录位置

1.7 初始化数据

1.8 启动PG数据库

1.9 设置开机自动启动PG

1.10 查看进程

1.11 修改监听IP、端口、连接数

1.12 开启远程登录数据库

2.源码安装

2.1 关闭防火墙

2.2 关闭Selinux

2.3 安装依赖包

2.4 安装

2.5 创建postgre用户及目录

2.6 设置postgres用户环境变量

2.7 初始化数据

2.8 修改监听IP、端口、连接数

2.9 启动PG数据库

2.10 查看进程

2.11 开启远程登录数据库

2.12 设置开机自动启动PG