PostgreSQL安装初始化

PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的BSD许可证下发行。

PostgreSQL 开发者把它念作 post-gress-Q-L。

PostgreSQL 的 Slogan 是 “世界上最先进的开源关系型数据库”。

数据库排名：DB-Engines Ranking - popularity ranking of database management systems

PostgreSQL 与 MySQL 对比：PostgreSQL与MySQL比较 - PostgreSQL-Chinaunix

PostgreSQL 安装初始化

二进制安装包

Rocky8 用官方源安装 PostgreSQL 15，参考官方文档：PostgreSQL: Downloads

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
##  安装仓库 repository RPM
[root@Rocky8-1 ~]# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Installed:
  pgdg-redhat-repo-42.0-32.noarch                                                                                                                     
Complete!

##  禁用内置的postgresql
[root@Rocky8-1 ~]# dnf -qy module disable postgresql

##  安装postgresql
[root@Rocky8-1 ~]# dnf install -y postgresql15-server
Installed:
  lz4-1.8.3-3.el8_4.x86_64  postgresql15-15.3-2PGDG.rhel8.x86_64  postgresql15-libs-15.3-2PGDG.rhel8.x86_64  postgresql15-server-15.3-2PGDG.rhel8.x86_64 
Complete!

##  初始化数据库并设置自动启动
[root@Rocky8-1 ~]# /usr/pgsql-15/bin/postgresql-15-setup initdb
Initializing database ... OK

[root@Rocky8-1 ~]# systemctl enable postgresql-15
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-15.service → /usr/lib/systemd/system/postgresql-15.service.

[root@Rocky8-1 ~]# systemctl start postgresql-15

##  验证
[root@Rocky8-1 ~]# sudo -u postgres psql -c "select version();"
could not change directory to "/root": Permission denied
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit
(1 row)

##  切换postgres用户，进入数据库
[root@Rocky8-1 ~]# su - postgres
[postgres@Rocky8-1 ~]$ psql
psql (15.3)
Type "help" for help.

postgres=# help 
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit
postgres=# 

##  PostgreSQL数据库目录
[root@Rocky8-1 ~]# ls /var/lib/pgsql/15/data/
base              log           pg_hba.conf    pg_multixact  pg_serial     pg_stat_tmp  pg_twophase  pg_xact               postmaster.opts
current_logfiles  pg_commit_ts  pg_ident.conf  pg_notify     pg_snapshots  pg_subtrans  PG_VERSION   postgresql.auto.conf  postmaster.pid
global            pg_dynshmem   pg_logical     pg_replslot   pg_stat       pg_tblspc    pg_wal       postgresql.conf

源码编译安装

参考官方文档：PostgreSQL: Documentation: 15: 17.4. Installation Procedure

安装步骤
  1. 下载源码：https://www.postgresql.org/ftp/source/
  2. 编译：
    • ./configure
    • make
    • makeinstall
  3. 初始化数据库，创建启动服务
环境初始化
1
2
3
4
5
6
7
8
9
10
11
##  关闭防火强和SElinux
[root@Rocky8-2 ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)
[root@Rocky8-2 ~]# getenforce 
Disabled

##  内核优化
[root@Rocky8-2 ~]#
安装依赖包
1
[root@Rocky8-2 ~]# yum install -y  gcc make readline-devel zlib-devel
下载解压
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[root@Rocky8-2 ~]# wget https://ftp.postgresql.org/pub/source/v15.3/postgresql-15.3.tar.gz
[root@Rocky8-2 ~]# tar xf postgresql-15.3.tar.gz 

[root@Rocky8-2 ~]# cd postgresql-15.3/
[root@Rocky8-2 postgresql-15.3]# ll
total 780
-rw-r--r--  1 1107 1107    397 May  9 05:13 aclocal.m4
drwxrwxrwx  2 1107 1107   4096 May  9 05:25 config
-rwxr-xr-x  1 1107 1107 601977 May  9 05:13 configure
-rw-r--r--  1 1107 1107  89369 May  9 05:13 configure.ac
drwxrwxrwx 61 1107 1107   4096 May  9 05:24 contrib
-rw-r--r--  1 1107 1107   1192 May  9 05:13 COPYRIGHT
drwxrwxrwx  3 1107 1107     87 May  9 05:25 doc
-rw-r--r--  1 1107 1107   4264 May  9 05:13 GNUmakefile.in
-rw-r--r--  1 1107 1107    277 May  9 05:13 HISTORY
-rw-r--r--  1 1107 1107  63842 May  9 05:26 INSTALL
-rw-r--r--  1 1107 1107   1875 May  9 05:13 Makefile
-rw-r--r--  1 1107 1107   1213 May  9 05:13 README
drwxrwxrwx 16 1107 1107   4096 May  9 05:26 src
查看官方安装说明
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[root@Rocky8-2 postgresql-15.3]# cat INSTALL | less
....
Short Version

    ./configure
    make
    su
    make install
    adduser postgres
    mkdir -p /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/pg_ctl -D /usr/local/pgsql/data -l logfile start
    /usr/local/pgsql/bin/createdb test
    /usr/local/pgsql/bin/psql test

The long version is the rest of this document.
....
开始编译安装三步曲
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
##  编译时加上安装路径（默认安装在/usr/local/pgsql下）、端口等设置（默认端口5432，也可在编译后修改配置文件）
[root@Rocky8-2 postgresql-15.3]# ./configure --prefix=/apps/pgsql --with-pgport=5432

##  查看CPU个数，-j指定编译的CPU数量，world 同时编译帮助文档
[root@Rocky8-2 postgresql-15.3]# lscpu
Architecture:        x86_64
CPU op-mode(s):      32-bit, 64-bit
Byte Order:          Little Endian
CPU(s):              4

[root@Rocky8-2 postgresql-15.3]# make -j 4 world

##  安装时同步安装帮助文档
[root@Rocky8-2 postgresql-15.3]# make install-world 

[root@Rocky8-2 postgresql-15.3]# tree /apps/pgsql
创建pgsql的数据库用户和组
1
2
3
##  注意用户需要可以交互登录
[root@Rocky8-2 postgresql-15.3]# useradd -s /bin/bash -m -d /home/postgres postgres
[root@Rocky8-2 postgresql-15.3]# echo -e '123456\n123456' | passwd postgres
创建数据目录并授权
1
2
3
4
5
6
7
8
9
10
11
12
13
14
##  数据目录
[root@Rocky8-2 postgresql-15.3]# mkdir -pv /pgsql/data/
mkdir: created directory '/pgsql'
mkdir: created directory '/pgsql/data/'
[root@Rocky8-2 postgresql-15.3]# chown postgres.postgres /pgsql/data/

##  日志目录
[root@Rocky8-2 ~]# mkdir -pv /pgsql/log
[root@Rocky8-2 ~]# chown postgres.postgres /pgsql/log/

[root@Rocky8-2 ~]# ll /pgsql/
total 4
drwx------ 19 postgres postgres 4096 Aug  7 14:06 data
drwxr-xr-x  2 postgres postgres    6 Aug  7 14:12 log
设置环境变量
1
2
3
4
5
6
7
8
9
10
11
12
[root@Rocky8-2 postgresql-15.3]# vim /etc/profile.d/pgsql.sh
export PGHOME=/apps/pgsql
export PATH=$PGHOME/bin/:$PATH
export PGDATA=/pgsql/data
export PGUSER=postgres
export MANPATH=/apps/pgsql/share/man:$MANPATH

[root@Rocky8-2 postgresql-15.3]# . /etc/profile.d/pgsql.sh

## 验证
[root@Rocky8-2 postgresql-15.3]# psql --version
psql (PostgreSQL) 15.3
初始化数据库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
##  生产环境建议初始化方式
[root@Rocky8-2 postgresql-15.3]# su - postgres 
[postgres@Rocky8-2 ~]$ initdb  -A md5  -D $PGDATA  -E utf8  --locale=C  -U postgres -W 
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 "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

Enter new superuser password: 
Enter it again: 

fixing permissions on existing directory /pgsql/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

Success. You can now start the database server using:

    pg_ctl -D /pgsql/data -l logfile start

-A 指定local connections默认的身份验证方法

-D 指定数据目录

-E 指定字符集

–local=C 指定语言环境

-U 指定数据库superuser用户名

-W 指定数据库superuser密码

启动和关闭服务

需要使用 postgres 用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
##  启动数据库
[postgres@Rocky8-2 ~]$ pg_ctl -D /pgsql/data -l /pgsql/log/logfile  start
waiting for server to start.... done
server started

##  关闭数据库
1、smart 关闭：等所有连接中止后，关闭数据库。如果客户端连接不终止，则无法关闭数据库
[postgres@Rocky8-2 ~]$ pg_ctl  stop  -D /pgsql/data/ -ms

2、fast 关闭：快速关闭数据库，断开客户端的连接，让已有的事务回滚，然后正常关闭数据库。此项为默认值，建议使用
[postgres@Rocky8-2 ~]$ pg_ctl  stop  -D /pgsql/data/ -mf
waiting for server to shut down.... done
server stopped

3、immediate: 相当于 kill -9,立即关闭数据库，数据库进程立即停止，直接退出，下次启动数据库需要进行恢复。
[postgres@Rocky8-2 ~]$ pg_ctl  stop  -D /pgsql/data/ -mi

##  重启数据库 
[postgres@Rocky8-2 ~]$ pg_ctl  restart  -l /pgsql/log/logfile  -mf
pg_ctl: PID file "/pgsql/data/postmaster.pid" does not exist
Is server running?
trying to start server anyway
waiting for server to start.... done
server started

##  修改配置后重新加载配置
[postgres@Rocky8-2 ~]$ pg_ctl  reload  -D /pgsql/data
开机启动脚本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
##  源码包中自带的启动服务脚本
[root@Rocky8-2 ~]# cat ./postgresql-15.3/contrib/start-scripts/linux 

[root@Rocky8-2 ~]# cp ./postgresql-15.3/contrib/start-scripts/linux /etc/init.d/postgresql
[root@Rocky8-2 ~]# ll /etc/init.d/postgresql
-rw-r--r-- 1 root root 3552 Aug  7 14:54 /etc/init.d/postgresql
[root@Rocky8-2 ~]# chmod +x /etc/init.d/postgresql
[root@Rocky8-2 ~]# chkconfig --add postgresql 

##  修改启动参数
[root@Rocky8-2 ~]# vim /etc/init.d/postgresql
# Installation prefix
prefix=/apps/pgsql

# Data directory
PGDATA="/pgsql/data"

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

# Where to keep a log file
PGLOG="/pgsql/log/logfile"


[root@Rocky8-2 ~]# service postgresql start 
Starting PostgreSQL: ok
service 启动文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
[root@Rocky8-2 ~]# cat  > /lib/systemd/system/postgresql.service <<EOF
[Unit]
Description=PostgreSQL database server

[Service]
Type=forking
User=postgres
Group=postgres
ExecStart=/apps/pgsql/bin/pg_ctl start -D /pgsql/data -l /pgsql/log/logfile  -s -w -t 300
ExecStop=/apps/pgsql/bin/pg_ctl stop -D /pgsql/data -s -m fast
ExecReload=/apps/pgsql/bin/pg_ctl reload -D /pgsql/data -s
TimeoutSec=300

[Install]
WantedBy=multi-user.target

EOF


[root@Rocky8-2 ~]# cat /usr/lib/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server

[Service]
Type=forking
User=postgres
Group=postgres
ExecStart=/apps/pgsql/bin/pg_ctl start -D /pgsql/data -l /pgsql/log/logfile  -s -w -t 300
ExecStop=/apps/pgsql/bin/pg_ctl stop -D /pgsql/data -s -m fast
ExecReload=/apps/pgsql/bin/pg_ctl reload -D /pgsql/data -s
TimeoutSec=300

[Install]
WantedBy=multi-user.target

[root@Rocky8-2 ~]# systemctl daemon-reload 
[root@Rocky8-2 ~]# ss -ntl 
State                 Recv-Q                Send-Q                               Local Address:Port                                Peer Address:Port                Process                
LISTEN                0                     128                                        0.0.0.0:111                                      0.0.0.0:*                                          
LISTEN                0                     128                                        0.0.0.0:22                                       0.0.0.0:*                                          
LISTEN                0                     128                                           [::]:111                                         [::]:*                                          
LISTEN                0                     128                                           [::]:22                                          [::]:*                                          
[root@Rocky8-2 ~]# systemctl start postgresql.service 
[root@Rocky8-2 ~]# systemctl status  postgresql.service 
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2023-08-16 17:09:56 CST; 10s ago
  Process: 2433 ExecStop=/apps/pgsql/bin/pg_ctl stop -D /pgsql/data -s -m fast (code=exited, status=0/SUCCESS)
  Process: 2430 ExecReload=/apps/pgsql/bin/pg_ctl reload -D /pgsql/data -s (code=exited, status=0/SUCCESS)
  Process: 2439 ExecStart=/apps/pgsql/bin/pg_ctl start -D /pgsql/data -l /pgsql/log/logfile -s -w -t 300 (code=exited, status=0/SUCCESS)
 Main PID: 2441 (postgres)
    Tasks: 6 (limit: 12160)
   Memory: 14.8M
   CGroup: /system.slice/postgresql.service
           ├─2441 /apps/pgsql/bin/postgres -D /pgsql/data
           ├─2442 postgres: checkpointer 
           ├─2443 postgres: background writer 
           ├─2445 postgres: walwriter 
           ├─2446 postgres: autovacuum launcher 
           └─2447 postgres: logical replication launcher 

Aug 16 17:09:56 Rocky8-2.xcjyc.top systemd[1]: Starting PostgreSQL database server...
Aug 16 17:09:56 Rocky8-2.xcjyc.top systemd[1]: Started PostgreSQL database server.
[root@Rocky8-2 ~]# ss -ntlp 
State           Recv-Q          Send-Q                   Local Address:Port                   Peer Address:Port         Process                       
LISTEN          0               128                            0.0.0.0:111                         0.0.0.0:*             users:(("rpcbind",pid=832,fd=4),("systemd",pid=1,fd=88))          
LISTEN          0               128                            0.0.0.0:22                          0.0.0.0:*             users:(("sshd",pid=900,fd=3))                                     
LISTEN          0               244                          127.0.0.1:5432                        0.0.0.0:*             users:(("postgres",pid=2441,fd=6))                                
LISTEN          0               128                               [::]:111                            [::]:*             users:(("rpcbind",pid=832,fd=6),("systemd",pid=1,fd=90))          
LISTEN          0               128                               [::]:22                             [::]:*             users:(("sshd",pid=900,fd=4))                                     
LISTEN          0               244                              [::1]:5432                           [::]:*             users:(("postgres",pid=2441,fd=5))

查看编译参数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[root@Rocky8-2 ~]# pg_config 
BINDIR = /apps/pgsql/bin
DOCDIR = /apps/pgsql/share/doc
HTMLDIR = /apps/pgsql/share/doc
INCLUDEDIR = /apps/pgsql/include
PKGINCLUDEDIR = /apps/pgsql/include
INCLUDEDIR-SERVER = /apps/pgsql/include/server
LIBDIR = /apps/pgsql/lib
PKGLIBDIR = /apps/pgsql/lib
LOCALEDIR = /apps/pgsql/share/locale
MANDIR = /apps/pgsql/share/man
SHAREDIR = /apps/pgsql/share
SYSCONFDIR = /apps/pgsql/etc
PGXS = /apps/pgsql/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE =  '--prefix=/apps/pgsql' '--with-pgport=5432'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/apps/pgsql/lib',--enable-new-dtags
LDFLAGS_EX = 
LDFLAGS_SL = 
LIBS = -lpgcommon -lpgport -lz -lreadline -lpthread -lrt -ldl -lm 
VERSION = PostgreSQL 15.3
PostgreSQL
#init #PostgreSQL
PostgreSQL安装初始化
https://www.xcjyc.top/2023/07/18/PostgreSQL安装初始化/
作者
XCJYC
发布于
2023年7月18日
许可协议