CentOS源码安装PostgreSQL

安装前环境准备

• make—4.2.1（至少3.8）
• gmake—4.2.1（至少3.8）
• gcc—8.3.1
• tar—1.30
• readline
• zlib

创建postgres用户组和用户

[root@CentOS /]# groupadd postgres
[root@CentOS /]# useradd -g postgres postgres
[root@CentOS /]# passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.

创建安装路径并授权给postgres

-P表示递归创建。

[root@CentOS ~]# mkdir -p /data/postgres/10.14/
[root@CentOS ~]# chown -R postgres:postgres /data/
[root@CentOS ~]# ll /data/
total 0
drwxr-xr-x 3 postgres postgres 18 May  9 11:41 postgres

获取官方源码

官方路径：https://www.postgresql.org/ftp/source/v10.0/
在对应gz上右键复制链接地址。例如PostgreSQL10.14的链接地址为：https://ftp.postgresql.org/pub/source/v10.14/postgresql-10.14.tar.gz

[root@CentOS ~]# su - postgres
[postgres@CentOS ~]$ wget https://ftp.postgresql.org/pub/source/v10.14/postgresql-10.14.tar.gz
--2021-05-09 13:54:19--  https://ftp.postgresql.org/pub/source/v10.14/postgresql-10.14.tar.gz
Resolving ftp.postgresql.org (ftp.postgresql.org)... 147.75.85.69, 87.238.57.227, 72.32.157.246, ...
Connecting to ftp.postgresql.org (ftp.postgresql.org)|147.75.85.69|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 25007394 (24M) [application/octet-stream]
Saving to: ‘postgresql-10.14.tar.gz’
postgresql-10.14.tar.gz            100%[===============================================================>]  23.85M  7.66MB/s    in 3.1s
2021-05-09 13:54:23 (7.66 MB/s) - ‘postgresql-10.14.tar.gz’ saved [25007394/25007394]

解压源码

[postgres@CentOS ~]$ tar -zxvf postgresql-10.14.tar.gz
...
...
[postgres@CentOS ~]$ ll
total 24424
drwxrwxr-x 6 postgres postgres      273 Aug 11  2020 postgresql-10.14
-rw-rw-r-- 1 postgres postgres 25007394 Aug 11  2020 postgresql-10.14.tar.gz
[postgres@CentOS ~]$ cd postgresql-10.14/
[postgres@CentOS postgresql-10.14]$ ll
total 684
-rw-r--r--  1 postgres postgres    493 Aug 11  2020 aclocal.m4
drwxrwxr-x  2 postgres postgres   4096 Aug 11  2020 config
-rwxr-xr-x  1 postgres postgres 510855 Aug 11  2020 configure
-rw-r--r--  1 postgres postgres  80199 Aug 11  2020 configure.in
drwxrwxr-x 55 postgres postgres   4096 Aug 11  2020 contrib
-rw-r--r--  1 postgres postgres   1192 Aug 11  2020 COPYRIGHT
drwxrwxr-x  3 postgres postgres    107 Aug 11  2020 doc
-rw-r--r--  1 postgres postgres   3911 Aug 11  2020 GNUmakefile.in
-rw-r--r--  1 postgres postgres    284 Aug 11  2020 HISTORY
-rw-r--r--  1 postgres postgres  73032 Aug 11  2020 INSTALL
-rw-r--r--  1 postgres postgres   1665 Aug 11  2020 Makefile
-rw-r--r--  1 postgres postgres   1212 Aug 11  2020 README
drwxrwxr-x 16 postgres postgres    328 Aug 11  2020 src

configure配置安装路径

[postgres@CentOS postgresql-10.14]$ ./configure --prefix=/data/postgres/10.14/

执行./configure命令，要先进入到源码解压出来的路径下，cd postgresql-10.14/

gmake编译安装

[postgres@CentOS postgresql-10.14]$ gmake world
...
...
PostgreSQL, contrib, and documentation successfully made. Ready to install.
[postgres@CentOS postgresql-10.14]$ gmake install-world
...
...
PostgreSQL, contrib, and documentation installation complete.

初始化数据库

[postgres@CentOS postgresql-10.14]$ /data/postgres/10.14/bin/initdb -D /data/postgres/10.14/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.
creating directory /data/postgres/10.14/data ... 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
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:
    /data/postgres/10.14/bin/pg_ctl -D /data/postgres/10.14/data -l logfile start

启动数据库

[postgres@CentOS postgresql-10.14]$ /data/postgres/10.14/bin/pg_ctl -D /data/postgres/10.14/data -l logfile start
waiting for server to start.... done
server started

修改环境变量

CentOS8之后的环境变量在~/.bashrc文件中（8以下在~/.bash_profile中）。
修改后结果如下：

[postgres@CentOS postgresql-10.14]$ cat ~/.bashrc
# .bashrc
# Source global definitions
if [ -f /etc/bashrc ]; then
        . /etc/bashrc
fi
# User specific environment
if ! [[ "$PATH" =~ "$HOME/.local/bin:$HOME/bin:" ]]
then
    PATH="$HOME/.local/bin:$HOME/bin:$PATH:/data/postgres/10.14/bin"
    PGDATA="/data/postgres/10.14/data"
fi
export PATH PGDATA
# Uncomment the following line if you don't like systemctl's auto-paging feature:
# export SYSTEMD_PAGER=
# User specific aliases and functions

使用如下命令重新加载环境变量。

[postgres@CentOS postgresql-10.14]$ source ~/.bashrc

修改数据库参数

修改参数前请使用 pg_ctl stop -m fast停止数据库，修改后再使用pg_ctl start启动数据库

• 修改pg_hba.conf文件（文件在/data/postgres/10.14/data下）补充一行。

# IPv4 local connections:
host    all             all             0.0.0.0/0               md5

• 修改postgresql.conf 文件（文件在/data/postgres/10.14/data下）找到#listen_addresses = 'localhost'，修改如下，删除前边的#注释。

listen_addresses = '*'

测试连接

本地：

[root@CentOS /]# su - postgres
Last login: Sun May  9 13:54:00 CST 2021 on pts/0
[postgres@CentOS ~]$ psql
psql (10.14)
Type "help" for help.
postgres=# 

远程

C:\Users\zhangjiqiang>psql -h 121.199.166.95 -p 5432 -d postgres -U postgres
用户 postgres 的口令：
psql (13.2, 服务器 10.14)
输入 "help" 来获取帮助信息.
postgres=#

常用数据库服务器维护命令

• 查看进程

[postgres@CentOS ~]$ ps -ef|grep postgres
postgres   46394       1  0 May09 ?        00:00:05 /data/postgres/10.14/bin/postgres
postgres   46396   46394  0 May09 ?        00:00:00 postgres: checkpointer process
postgres   46397   46394  0 May09 ?        00:00:02 postgres: writer process
postgres   46398   46394  0 May09 ?        00:00:02 postgres: wal writer process
postgres   46399   46394  0 May09 ?        00:00:01 postgres: autovacuum launcher process
postgres   46400   46394  0 May09 ?        00:00:02 postgres: stats collector process
postgres   46401   46394  0 May09 ?        00:00:00 postgres: bgworker: logical replication launcher
root       82975   82941  0 13:39 pts/1    00:00:00 su - postgres
postgres   82976   82975  0 13:39 pts/1    00:00:00 -bash
postgres   83008   82976  0 13:41 pts/1    00:00:00 ps -ef
postgres   83009   82976  0 13:41 pts/1    00:00:00 grep --color=auto postgres

• 查看端口

[postgres@CentOS ~]$ nmap localhost
Nmap scan report for localhost (127.0.0.1)
Host is up (0.000087s latency).
Other addresses for localhost (not scanned): ::1
Not shown: 998 closed ports
PORT     STATE SERVICE
22/tcp   open  ssh
5432/tcp open  postgresql

• 查看服务状态

[postgres@CentOS ~]$ pg_ctl status
pg_ctl: server is running (PID: 46394)
/data/postgres/10.14/bin/postgres

• 停止服务

[postgres@CentOS ~]$ pg_ctl stop -m fast
waiting for server to shut down.... done
server stopped

• 启动服务

[postgres@CentOS ~]$ pg_ctl start
waiting for server to start....2021-05-13 13:44:31.718 CST [83020] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2021-05-13 13:44:31.718 CST [83020] LOG:  listening on IPv6 address "::", port 5432
2021-05-13 13:44:31.723 CST [83020] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-05-13 13:44:31.744 CST [83021] LOG:  database system was shut down at 2021-05-13 13:44:06 CST
2021-05-13 13:44:31.749 CST [83020] LOG:  database system is ready to accept connections
 done
server started

卸载PostgreSQL数据库

1. 源码路径gmake uninstall

[postgres@CentOS postgresql-10.14]$ pwd
/home/postgres/postgresql-10.14
[postgres@CentOS postgresql-10.14]$ gmake uninstall
...
rm -f '/data/postgres/10.14/lib/pgxs/config/install-sh'
rm -f '/data/postgres/10.14/lib/pgxs/config/missing'
gmake[1]: Leaving directory '/home/postgres/postgresql-10.14/config'

2. 删除安装路径

[root@CentOS ~]# cd /
[root@CentOS /]# rm -rf /data
[root@CentOS /]# ll
total 28
lrwxrwxrwx.   1 root     root        7 Nov  3  2020 bin -> usr/bin
dr-xr-xr-x.   5 root     root     4096 Apr 20 12:09 boot
drwxr-xr-x   19 root     root     2880 May  9 00:07 dev
drwxr-xr-x.  96 root     root     8192 May  9 19:25 etc
drwxr-xr-x.   3 root     root       22 May  9 19:25 home
lrwxrwxrwx.   1 root     root        7 Nov  3  2020 lib -> usr/lib
lrwxrwxrwx.   1 root     root        9 Nov  3  2020 lib64 -> usr/lib64
drwxr-xr-x.   2 root     root        6 Nov  3  2020 media
drwxr-xr-x.   2 root     root        6 Nov  3  2020 mnt
drwxr-xr-x.   3 root     root       24 May  8 16:28 opt
dr-xr-xr-x  105 root     root        0 May  9 00:06 proc
dr-xr-x---.   5 root     root     4096 May 10 09:35 root
drwxr-xr-x   30 root     root      880 May  9 00:07 run
lrwxrwxrwx.   1 root     root        8 Nov  3  2020 sbin -> usr/sbin
drwxr-xr-x.   2 root     root        6 Nov  3  2020 srv
dr-xr-xr-x   13 root     root        0 May  9 08:06 sys
drwxrwxrwt.   9 postgres postgres 4096 May 13 13:49 tmp
drwxr-xr-x.  12 root     root      144 May  8 23:40 usr
drwxr-xr-x.  21 root     root     4096 May  9 00:06 var

3. 删除postgres用户

[root@CentOS /]# userdel -r postgres
[root@CentOS /]# ll /home
total 0