Cenos7环境PostgreSQ16.0离线安装部署

Cenos7环境PostgreSQ16.0离线安装部署

一、简介

1、访问官网

https://www.postgresql.org/

2、下载

https://ftp.postgresql.org/pub/source/v16.0/postgresql-16.0.tar.gz

二、环境准备

1、机器

centos7 x86

2、部署用户

• 添加用户

sudo useradd postgres

• 设置用户密码

sudo passwd postgres

• 设置root权限

sudo vim /etc/sudoers

3、依赖下载

sudo yum -y install readline-devel
sudo yum -y install zlib-devel

4、通过ftp将下载的postgresql安装包放在指定的目录，并解压。解压后的目录为/opt/software/postgresql-16.0

tar -xvf ./postgresql-16.0.tar.gz

5、部署目录

• 创建部署目录

mkdir /opt/postgresql-16.0

• 创建数据目录

mkdir /opt/postgresql-16.0/data

-创建日志文件

touch /opt/postgresql-16.0/pgsql.log

三、安装步骤

1、进入解压后的目录

cd /opt/software/postgresql-16.0

2、指定安装到/opt/postgresql-16.0目录

./configure --prefix=/opt/postgresql-16.0 --without-icu

3、编译并安装

make && make install

4、检查是否安装成功

cd /opt/postgresql-16.0/
sh ./bin/pg_config

出现如下配置信息

BINDIR = /opt/postgresql-16.0/bin
DOCDIR = /opt/postgresql-16.0/share/doc
HTMLDIR = /opt/postgresql-16.0/share/doc
INCLUDEDIR = /opt/postgresql-16.0/include
PKGINCLUDEDIR = /opt/postgresql-16.0/include
INCLUDEDIR-SERVER = /opt/postgresql-16.0/include/server
LIBDIR = /opt/postgresql-16.0/lib
PKGLIBDIR = /opt/postgresql-16.0/lib
LOCALEDIR = /opt/postgresql-16.0/share/locale
MANDIR = /opt/postgresql-16.0/share/man
SHAREDIR = /opt/postgresql-16.0/share
SYSCONFDIR = /opt/postgresql-16.0/etc
PGXS = /opt/postgresql-16.0/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = ‘–prefix=/opt/postgresql-16.0’ ‘–without-icu’
CC = gcc -std=gnu99
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,–as-needed -Wl,-rpath,‘/opt/postgresql-16.0/lib’,–enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lz -lreadline -lpthread -lrt -ldl -lm
VERSION = PostgreSQL 16.0

5、数据库初始化

./bin/initdb -D /opt/postgresql-16.0/data/

显示如下内容：

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/postgresql-16.0/data … ok
creating subdirectories … ok
selecting dynamic shared memory implementation … posix
selecting default max_connections … 100
selecting default shared_buffers … 128MB
selecting default time zone … Asia/Shanghai
creating configuration files … ok
running bootstrap script … ok
performing post-bootstrap initialization … ok
syncing data to disk … ok

initdb: warning: enabling “trust” authentication for local connections
initdb: hint: 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/postgresql-16.0/data/ -l logfile start

6、启动postgresql，并指定数据目录和日志文件

cd /opt/postgresql-16.0/bin

• 启动postgresql

[postgres@hadoop3 bin]$ ./pg_ctl -D /opt/postgresql-16.0/data/ -l /opt/postgresql-16.0/pgsql.log start
waiting for server to start.... done
server started

• 停止postgresql

[postgres@hadoop3 bin]$ ./pg_ctl -D /opt/postgresql-16.0/data/ -l /opt/postgresql-16.0/pgsql.log stop
waiting for server to shut down.... done
server stopped

• 查看postgresql状态

[postgres@hadoop3 bin]$ ./pg_ctl -D /opt/postgresql-16.0/data/ -l /opt/postgresql-16.0/pgsql.log status
pg_ctl: server is running (PID: 6868)
/opt/postgresql-16.0/bin/postgres "-D" "/opt/postgresql-16.0/data"

7、查看进程

[postgres@hadoop3 bin]$ ps -aux | grep postgres
root      5781  0.0  0.0 241332  4628 pts/1    S    17:07   0:00 sudo su - postgres
root      5782  0.0  0.0 191884  2348 pts/1    S    17:07   0:00 su - postgres
postgres  5783  0.0  0.0 116052  2628 pts/1    S+   17:07   0:00 -bash
root      6012  0.0  0.0 241332  4632 pts/2    S    17:10   0:00 sudo su postgres
root      6013  0.0  0.0 191880  2348 pts/2    S    17:10   0:00 su postgres
postgres  6014  0.0  0.0 116060  2664 pts/2    S    17:10   0:00 bash
postgres  6868  0.0  0.1 276432 13508 ?        Ss   17:27   0:00 /opt/postgresql-16.0/bin/postgres -D /opt/postgresql-16.0/data
postgres  6869  0.0  0.0 276564  1136 ?        Ss   17:27   0:00 postgres: checkpointer 
postgres  6870  0.0  0.0 276588  2188 ?        Ss   17:27   0:00 postgres: background writer 
postgres  6872  0.0  0.0 276432  5092 ?        Ss   17:27   0:00 postgres: walwriter 
postgres  6873  0.0  0.0 278028  2152 ?        Ss   17:27   0:00 postgres: autovacuum launcher 
postgres  6874  0.0  0.0 278012  1904 ?        Ss   17:27   0:00 postgres: logical replication launcher 
postgres  7066  0.0  0.0 155476  1884 pts/2    R+   17:30   0:00 ps -aux
postgres  7067  0.0  0.0 112812   980 pts/2    S+   17:30   0:00 grep --color=auto postgres

8、配置远程连接

• 修改pg_hba.conf

vim /opt/postgresql-16.0/data/pg_hba.conf

加上末尾一行

host    all             all             0.0.0.0/0               md5

效果如下：

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
host    all             all             0.0.0.0/0               md5

• 修改postgresql.conf

vim /opt/postgresql-16.0/data/postgresql.conf

将第60行的#listen_addresses = 'localhost'改为listen_addresses = '*'

修改之前

60 #listen_addresses = ‘localhost’ # what IP address(es) to listen on;

修改之后

listen_addresses = ‘*’ # what IP address(es) to listen on;

四、使用postgresql

• 第一次进入postgresql

/opt/postgresql-16.0/bin/psql

• 为postgresql用户设置密码（这个密码得设置，默认为空）

postgres=# \password postgres
Enter new password for user "postgres": 
Enter it again: 

• 重置postgresql密码（这个好像未用到）

alter user postgres with encrypted password 'postgres123";

• 创建用户pgdba

create user pgdba with encrypted password 'pgdba';

• 修改pgdba用户吃的角色

alter role pgdba createrole superuser;

• 创建数据库mytest

create database mytest;

• 授予pgdba用户操作mytest数据库的权限

grant all privileges on database mytest to pgdba;

• 配置环境变量

vim ~/.bashrc

写入如下内容

export POSTGRESQL_HOME=/opt/postgresql-16.0
export PATH=$POSTGRESQL_HOME/bin:$PATH

新打开一个会话，查看效果

which psql

• 修改密码后需要使用

psql -U postgres -h localhost -d postgres -p 5432

-U是用户，-h是host的ip，-d是数据库名称，-p是端口号

五、系统配置

1、postgresql数据库的开机启动脚本在源安装包的postgresql-16.0/contrib/start-scripts目录下

/opt/software/postgresql-16.0/contrib/start-scripts/linux

2、将Linux文件复制到/etc/init.d目录下，并且将其重名为postgresql

sudo cp /opt/software/postgresql-16.0/contrib/start-scripts/linux /etc/init.d/postgresql

3、进入/etc/init.d目录下，修改postgresql文件

sudo vim /etc/init.d/postgresql

修改之前：

# Installation prefix
prefix=/usr/local/pgsql

# Data directory
PGDATA="/usr/local/pgsql/data"

# Who to run postgres as, usually "postgres".  (NOT "root")
PGUSER=postgres

# Where to keep a log file
PGLOG="$PGDATA/serverlog"

修改之后：

# Installation prefix
prefix=/opt/postgresql-16.0

# Data directory
PGDATA="/opt/postgresql-16.0/data"

# Who to run postgres as, usually "postgres".  (NOT "root")
PGUSER=postgres

# Where to keep a log file
PGLOG="/opt/postgresql-16.0/pgsql.log"

4、添加到开机启动

sudo chmod a+x /etc/init.d/postgresql
sudo chkconfig --add /etc/init.d/postgresql

往/etc/init.d目录下放启动脚本与sudo systemctl enable postgresql是两种不同的方式，二选一。