本文档详细介绍了在RHEL7系统上安装PostgreSQL 12.7的过程，包括系统准备、依赖包安装、配置文件编辑、初始化数据库、设置权限、启动服务以及用户权限设置等步骤。同时，文章还涵盖了通过源码包和YUM在线安装的方法，以及安装后的测试和维护操作。整个过程强调了权限管理和安全设置，确保了数据库系统的稳定和安全。
PostgreSQL 安装部署
安装前准备
文件系统建议使用LVM管理。
不同的包下载地址不一样。
操作系统选择
推荐使用RHEL 7版本，本文采用RHEL 7.6
使用如下2个命令确认操作系统版本
[root@postgre ~]# uname -osr
Linux 3.10.0-957.el7.x86_64 GNU/Linux
[root@postgre ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.6 (Maipo)
系统基本检查和调整
检查以下项目，不符合的需要调整：
- 用uname -r 命令检查是否是x86_64 位系统
- 关闭SELinux：修改/etc/selinux/config 文件，将SELINUX=后面的值改为disabled。
这一改动需要重启操作系统才生效。
安装依赖包
在依赖的系统包当中，readline 是命令行上下翻页的包，zlib 是备份压缩选项用的包。除
了下面列出的依赖包，其他按需安装。
⚫ GNU make version 3.80 or newer is required
⚫ GCC
⚫ tar
⚫ Readline
⚫ zlib
⚫ Perl 5.8.3 or later is needed
yum -y install readline readline-devel zlib zlib-devel gettext gettext-devel openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel
perl perl-devel tcl-devel uuid-devel gcc gcc-c++ make flex bison perl-ExtUtils*
解释，后面编译会用到make和gmake2个编译工具：
gmake是GNU Make的缩写。 Linux系统环境下的make就是GNU Make，之所以有gmake，是因为在别的平台上，make一般被占用，GNU make只好叫gmake了。 比如在安装二进制文件进行编译时要使用make命令,但如果在Solaris或其他非GNU系统中运行，必须使用GNU make，而不是使用系统自带的make版本，这时要用gmake代替make进行编译。
gmake是指GNU make。在linux环境下gmake和make是一样的。
具体步骤如下：
[root@postgre yum.repos.d]# mount /dev/sr0 /mnt/
[root@postgre ~]# cd /etc/yum.repos.d/
[root@postgre yum.repos.d]# ll
total 4
-rw-r--r--. 1 root root 358 Mar 30 18:18 redhat.repo
[root@postgre yum.repos.d]# rm -rf redhat.repo
[root@postgre yum.repos.d]# cat base.repo
[base]
name=base
baseurl=file:///mnt
enabled=1
gpgcheck=0
[root@postgre yum.repos.d]# yum clean all
[root@postgre yum.repos.d]# yum -y install readline readline-devel zlib zlib-devel gettext gettext-devel openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel perl perl-devel tcl-devel uuid-devel gcc gcc-c++ make flex bison perl-ExtUtils*
编辑hosts文件
将主机名和ip 解析写到/etc/hosts 文件。
如下所示：
[root@postgre ~]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
10.1.11.30 postgre
内核参数调整
echo “fs.file-max = 76724200
kernel.sem = 10000 10240000 10000 1024
kernel.shmmni = 4096
kernel.shmall = 253702
kernel.shmmax = 1039163392
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.wmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_max = 1048576
fs.aio-max-nr = 40960000
vm.dirty_ratio=20
vm.dirty_background_ratio=3
vm.dirty_writeback_centisecs=100
vm.dirty_expire_centisecs=500
vm.swappiness=10
vm.min_free_kbytes=524288”>>/etc/sysctl.conf && sysctl --system
时间和时间同步
查看时区
timedatectl status
确认时区，并且时间同步。建议主机工程师设置时间同步。
修改时区：
timedatectl set-timezone Asia/Shanghai
用户limits设置
echo “postgres soft nofile 1048576
postgres hard nofile 1048576
postgres soft nproc 131072
postgres hard nproc 131072
postgres soft stack 10240
postgres hard stack 32768
postgres soft core 6291456
postgres hard core 6291456” >>/etc/security/limits.conf
创建用户和组
groupadd dba -g 1001
useradd -g dba -u 1001 -d /home/postgres -s /bin/bash -c “PostgerSql Software Owner” postgres
echo “postgres” | passwd --stdin postgres
目录规划
mkdir /soft
chmod 777 /soft
mkdir -p /opt/pg12.7
chown -R postgres:dba /opt/pg12.7
mkdir -p /opt/pg12.7/pgdata/log
chmod 700 /opt/pg12.7/pgdata/log
sudo权限配置
#visudo -f /etc/sudoers
最后一行添加：
postgres ALL = NOPASSWD:/usr/bin/systemctl stop postgres-12.service,/usr/bin/systemctl start postgres-12.service,/usr/bin/systemctl restart postgres-12.service,/usr/bin/systemctl reload postgres-12.service,/usr/bin/systemctl status postgres-12.service
注意：一定保证是一行，不能跨行。
下载安装包
源码包下载
https://www.postgresql.org/ftp/source/
RPM包下载
下载地址：https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/
选择具体对应的版本和os版本：
postgresql12-12.7-1PGDG.rhel7.x86_64.rpm
postgresql12-contrib-12.7-1PGDG.rhel7.x86_64.rpm
postgresql12-devel-12.7-1PGDG.rhel7.x86_64.rpm
postgresql12-libs-12.7-1PGDG.rhel7.x86_64.rpm
postgresql12-server-12.7-1PGDG.rhel7.x86_64.rpm
上面5个包必须的，解释如下：
postgresql12 - PostgreSQL client programs and libraries
postgresql12-contrib - Contributed source and binaries distributed with PostgreSQL
postgresql12-libs - The shared libraries required for any PostgreSQL clients
postgresql12-server - The programs needed to create and run a PostgreSQL server
YUM在线安装参考
参考地址：https://www.postgresql.org/download/linux/redhat/
下载地址：https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
二进制包下载
二进制文本安装
下载地址：https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
绿色解压版
下载地址：https://www.enterprisedb.com/download-postgresql-binaries
这两者的区别如官网所说：
The links below allow you to download archives of the files installed by our PostgreSQL installers. These archives are provided as a convenience for expert users; unless you specifically need these files, you should download an installer instead.
除非你很需要绿色解压版文件，否则你应该使用二进制文本安装。
安装
源码包安装
解压安装包
[root@postgre ~]# cd /soft/
[root@postgre soft]# ll
total 26592
-rw-r--r--. 1 root root 27229235 Jul 8 10:39 postgresql-12.7.tar.gz
[root@postgre soft]# chown postgres:dba postgresql-12.7.tar.gz
[root@postgre soft]# chmod 755 postgresql-12.7.tar.gz
[root@postgre soft]# su - postgres
Last login: Thu Jul 8 12:42:05 CST 2021 on pts/0
[postgres@postgre ~]$ cd /soft/
[postgres@postgre soft]$ ll
total 26592
-rwxr-xr-x. 1 postgres dba 27229235 Jul 8 10:39 postgresql-12.7.tar.gz
[postgres@postgre soft]$ tar -zxvf postgresql-12.7.tar.gz
编译安装
$ cd /soft/postgresql-12.7/
$ ./configure --prefix=/opt/pg12.7 --with-pgport=6000
使用make 或者make world编译，使用make install 或者make install-world 进行安装
$ make world -j 8
当看到最后一行显示为：
PostgreSQL, contrib, and documentation successfully made. Ready to install.
说明已经编译成功
$ make install-world
当看到最后一行显示为：
PostgreSQL, contrib, and documentation installation complete.
说明已经安装成功
$ make -j8
当看到最后一行显示为：
All of PostgreSQL successfully made. Ready to install.
说明已经编译成功
$ make install
当看到最后一行显示为：
PostgreSQL installation complete.
说明已经安装成功
说明：
1、make编译的时候可以加并行：
-j [N], --jobs[=N] Allow N jobs at once; infinite jobs with no arg.
-j8和-j 8，都可以，不管中间有没有空格。
[root@postgre ~]# ps -ef|grep make
postgres 12701 8390 0 12:49 pts/0 00:00:00 make world -j 8
postgres 12860 12701 0 12:49 pts/0 00:00:00 make -C src all
postgres 13402 12860 0 12:49 pts/0 00:00:00 make -C backend all
postgres 14358 13402 0 12:49 pts/0 00:00:00 make -C nodes all
postgres 14486 13402 0 12:50 pts/0 00:00:00 make -C optimizer all
postgres 14514 14486 0 12:50 pts/0 00:00:00 make -C plan all
postgres 14625 13402 0 12:50 pts/0 00:00:00 make -C postmaster all
postgres 14662 13402 0 12:50 pts/0 00:00:00 make -C regex all
postgres 14678 14486 0 12:50 pts/0 00:00:00 make -C util all
root 14707 14282 0 12:50 pts/1 00:00:00 grep --color=auto make
会有8个并行进程
2、make install的时候不用并行，因为安装很快，只是在编译的时候耗时。
3、make world && make install-world比make && make install编译安装的东西多，从上面的结果输出就可以看出。 如果你希望编译所有能编译的东西，包括文档（HTML和手册页）以及附加模块（contrib），使用make world && make install-world。源码安装postgresql时，而make时又没有make world，就会导致的pg最终没有类似pg_stat_statements的扩展功能
查看版本
$ cd /opt/pg12.7/bin/
$ ./postgres --version
postgres (PostgreSQL) 12.7
设置软链接
$ cd /opt
$ ln -s /opt/pg12.7 /opt/pgsql
$ cd /opt/
$ ll
total 4
drwxr-xr-x. 1 postgres dba 36 Jul 8 13:04 pg12.7
lrwxrwxrwx. 1 postgres dba 11 Jul 8 13:14 pgsql -> /opt/pg12.7
drwxr-xr-x. 1 root root 0 Jun 18 2018 rh
设置软连接的目的，为了使用方便，当你有升级需求时，升级完后，环境变量都不需要更改了。
yum在线安装
由于安装用户的不同，安装文件的权限属组，数据库的权限和属组都不相同，从以下几个方面测试：
1、 普通用户安装
1)postgres sudo用户安装
2)非postgres sudo用户安装
2、root用户安装
1）不创建postgres用户安装
2）创建postgres（但是用户名可以变下，如pg）用户安装
观察：1、启动进程的用户
2、安装目录的属组
3、数据库目录的属组
4、环境变量
5、启停的用户
postgres sudo用户安装
- sudo权限配置
#visudo -f /etc/sudoers
最后一行更改：
postgres ALL = NOPASSWD:ALL
postgres用户执行所有root的命令，都不用输入密码。
- 新建用户
#groupadd dba -g 1001
#useradd -g dba -u 1001 -d /home/postgres -m -s /bin/bash postgres
#echo “postgres” | passwd --stdin postgres
- 安装
#su - postgres
$id postgres
uid=1001(postgres) gid=1001(dba) groups=1001(dba)
[postgres@source ~]$sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Loaded plugins: fastestmirror
pgdg-redhat-repo-latest.noarch.rpm | 7.5 kB 00:00:00
Examining /var/tmp/yum-root-cpJyrE/pgdg-redhat-repo-latest.noarch.rpm: pgdg-redhat-repo-42.0-18.noarch
Marking /var/tmp/yum-root-cpJyrE/pgdg-redhat-repo-latest.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package pgdg-redhat-repo.noarch 0:42.0-18 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==========================================================================================================================================================================================
Package Arch Version Repository Size
==========================================================================================================================================================================================
Installing:
pgdg-redhat-repo noarch 42.0-18 /pgdg-redhat-repo-latest.noarch 11 k
Transaction Summary
==========================================================================================================================================================================================
Install 1 Package
Total size: 11 k
Installed size: 11 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : pgdg-redhat-repo-42.0-18.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-18.noarch 1/1
Installed:
pgdg-redhat-repo.noarch 0:42.0-18
Complete!
[postgres@source ~]$rpm -qa|grep post
postfix-2.10.1-7.el7.x86_64
[postgres@source ~]$sudo yum install -y postgresql12-server postgresql12 postgresql12-contrib
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
epel/x86_64/metalink | 9.1 kB 00:00:00
* base: mirrors.aliyun.com
* epel: mirrors.ustc.edu.cn
* extras: mirrors.aliyun.com
* updates: mirrors.aliyun.com
base | 3.6 kB 00:00:00
debuginfo | 2.5 kB 00:00:00
epel | 4.7 kB 00:00:00
extras | 2.9 kB 00:00:00
pgdg-common/7/x86_64/signature | 198 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-18.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg-common/7/x86_64/signature | 2.9 kB 00:00:00 !!!
pgdg10/7/x86_64/signature | 198 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-18.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg10/7/x86_64/signature | 3.6 kB 00:00:00 !!!
pgdg11/7/x86_64/signature | 198 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-18.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg11/7/x86_64/signature | 3.6 kB 00:00:00 !!!
pgdg12/7/x86_64/signature | 198 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-18.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg12/7/x86_64/signature | 3.6 kB 00:00:00 !!!
pgdg13/7/x86_64/signature | 198 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-18.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg13/7/x86_64/signature | 3.6 kB 00:00:00 !!!
pgdg96/7/x86_64/signature | 198 B 00:00:00
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-18.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
pgdg96/7/x86_64/signature | 3.6 kB 00:00:00 !!!
updates | 2.9 kB 00:00:00
(1/18): epel/x86_64/updateinfo | 1.0 MB 00:00:00
(2/18): epel/x86_64/group_gz | 96 kB 00:00:00
(3/18): extras/x86_64/primary_db | 242 kB 00:00:01
(4/18): epel/x86_64/primary_db | 6.9 MB 00:00:01
pgdg10/7/x86_64/group_gz FAILED 36% [========================- ] 2.1 MB/s | 9.9 MB 00:00:08 ETA
https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/repodata/2cd836c03a9a90fb5dadc7e588362f26da171bcb8ae6eac9b2e75744c4f9c926-postgresqldbserver-10.xml.gz: [Errno 14] curl#35 - "TCP connection reset by peer"
Trying other mirror.
pgdg11/7/x86_64/group_gz FAILED
https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/repodata/b7d0e8818c335fe56713a8c5569b7c43af195739ccbeb25405acf9cf973ef37d-postgresqldbserver-11.xml.gz: [Errno 14] curl#35 - "TCP connection reset by peer"
Trying other mirror.
pgdg11/7/x86_64/primary_db FAILED 37% [========================= ] 2.1 MB/s | 10 MB 00:00:08 ETA
https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/repodata/fac57968e172d0ad6b17641e6fb7382a410984724a563cf66d40e10c9436937d-primary.sqlite.bz2: [Errno 14] curl#35 - "TCP connection reset by peer"
Trying other mirror.
pgdg12/7/x86_64/group_gz FAILED 38% [========================== ] 2.0 MB/s | 10 MB 00:00:08 ETA
https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/repodata/321321fb85e82fa8974da1255ae28b1c1355e4c9697ac8cfa2b64c5f93c711eb-postgresqldbserver-12.xml.gz: [Errno 14] curl#35 - "TCP connection reset by peer"
Trying other mirror.
(5/18): pgdg12/7/x86_64/primary_db | 209 kB 00:00:02
(6/18): pgdg13/7/x86_64/group_gz | 246 B 00:00:00
(7/18): pgdg13/7/x86_64/primary_db | 119 kB 00:00:01
(8/18): pgdg96/7/x86_64/group_gz | 249 B 00:00:00
(9/18): base/x86_64/primary_db | 6.1 MB 00:00:07
(10/18): pgdg-common/7/x86_64/primary_db | 133 kB 00:00:08
(11/18): pgdg96/7/x86_64/primary_db | 321 kB 00:00:02
(12/18): pgdg10/7/x86_64/primary_db | 331 kB 00:00:12
(13/18): updates/x86_64/primary_db | 8.8 MB 00:00:10
(14/18): debuginfo/x86_64/primary_db | 2.6 MB 00:01:19
pgdg11/7/x86_64/primary_db | 351 kB 00:00:58
Resolving Dependencies
--> Running transaction check
---> Package postgresql12.x86_64 0:12.7-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql12-libs(x86-64) = 12.7-1PGDG.rhel7 for package: postgresql12-12.7-1PGDG.rhel7.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql12-12.7-1PGDG.rhel7.x86_64
---> Package postgresql12-contrib.x86_64 0:12.7-1PGDG.rhel7 will be installed
---> Package postgresql12-server.x86_64 0:12.7-1PGDG.rhel7 will be installed
--> Running transaction check
---> Package postgresql12-libs.x86_64 0:12.7-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==========================================================================================================================================================================================
Package Arch Version Repository Size
==========================================================================================================================================================================================
Installing:
postgresql12 x86_64 12.7-1PGDG.rhel7 pgdg12 1.6 M
postgresql12-contrib x86_64 12.7-1PGDG.rhel7 pgdg12 611 k
postgresql12-server x86_64 12.7-1PGDG.rhel7 pgdg12 5.1 M
Installing for dependencies:
postgresql12-libs x86_64 12.7-1PGDG.rhel7 pgdg12 371 k
Transaction Summary
==========================================================================================================================================================================================
Install 3 Packages (+1 Dependent package)
Total download size: 7.6 M
Installed size: 32 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/pgdg12/packages/postgresql12-contrib-12.7-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY ] 17 kB/s | 895 kB 00:06:36 ETA
Public key for postgresql12-contrib-12.7-1PGDG.rhel7.x86_64.rpm is not installed
(1/4): postgresql12-contrib-12.7-1PGDG.rhel7.x86_64.rpm | 611 kB 00:01:13
(2/4): postgresql12-libs-12.7-1PGDG.rhel7.x86_64.rpm | 371 kB 00:00:51
(3/4): postgresql12-12.7-1PGDG.rhel7.x86_64.rpm | 1.6 MB 00:02:21
postgresql12-server-12.7-1PGDG FAILED 65% [============================================ ] 4.3 B/s | 5.0 MB 180:29:28 ETA
https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/postgresql12-server-12.7-1PGDG.rhel7.x86_64.rpm: [Errno 12] Timeout on https://download.postgresql.org/pub/repos/yum/12/redhat/rhel-7-x86_64/postgresql12-server-12.7-1PGDG.rhel7.x86_64.rpm: (28, 'Operation too slow. Less than 1000 bytes/sec transferred the last 30 seconds')
Trying other mirror.
(4/4): postgresql12-server-12.7-1PGDG.rhel7.x86_64.rpm | 5.1 MB 00:06:17
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 9.9 kB/s | 7.6 MB 00:13:09
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
Package : pgdg-redhat-repo-42.0-18.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql12-libs-12.7-1PGDG.rhel7.x86_64 1/4
Installing : postgresql12-12.7-1PGDG.rhel7.x86_64 2/4
Installing : postgresql12-server-12.7-1PGDG.rhel7.x86_64 3/4
Installing : postgresql12-contrib-12.7-1PGDG.rhel7.x86_64 4/4
Verifying : postgresql12-libs-12.7-1PGDG.rhel7.x86_64 1/4
Verifying : postgresql12-12.7-1PGDG.rhel7.x86_64 2/4
Verifying : postgresql12-server-12.7-1PGDG.rhel7.x86_64 3/4
Verifying : postgresql12-contrib-12.7-1PGDG.rhel7.x86_64 4/4
Installed:
postgresql12.x86_64 0:12.7-1PGDG.rhel7 postgresql12-contrib.x86_64 0:12.7-1PGDG.rhel7 postgresql12-server.x86_64 0:12.7-1PGDG.rhel7
Dependency Installed:
postgresql12-libs.x86_64 0:12.7-1PGDG.rhel7
Complete!
[postgres@source ~]$rpm -qa|grep post
postgresql12-libs-12.7-1PGDG.rhel7.x86_64
postfix-2.10.1-7.el7.x86_64
postgresql12-12.7-1PGDG.rhel7.x86_64
postgresql12-contrib-12.7-1PGDG.rhel7.x86_64
postgresql12-server-12.7-1PGDG.rhel7.x86_64
- 查看安装路径
rpm有两个参数-l和-c可以帮助我们查看具体的安装路径。
-l 显示软件包中的文件列表
-c 显示配置文件列表
那么我们可以使用“rpm -ql 包名”来查看具体的安装路径。
[postgres@source ~]$rpm -qa|grep post
postgresql12-contrib-12.7-1PGDG.rhel7.x86_64
postfix-2.10.1-7.el7.x86_64
postgresql12-libs-12.7-1PGDG.rhel7.x86_64
postgresql12-server-12.7-1PGDG.rhel7.x86_64
postgresql12-12.7-1PGDG.rhel7.x86_64
[postgres@source ~]$rpm -ql postgresql12-server-12.7-1PGDG.rhel7.x86_64
/etc/pam.d/postgresql
/etc/sysconfig/pgsql
/usr/bin/postgresql-12-setup
/usr/lib/systemd/system/postgresql-12.service
/usr/lib/tmpfiles.d/postgresql-12.conf
/usr/pgsql-12/bin/initdb
/usr/pgsql-12/bin/pg_checksums
/usr/pgsql-12/bin/pg_controldata
/usr/pgsql-12/bin/pg_ctl
/usr/pgsql-12/bin/pg_resetwal
。。。。。。。。
/var/lib/pgsql/12/backups
/var/lib/pgsql/12/data
/var/run/postgresql
[postgres@source ~]$rpm -ql postgresql12
可见，postgresql安装在了目录/usr/pgsql-12下。
查看安装文件的属组和权限
[postgres@source ~]$cd /usr/
[postgres@source /usr]$ll
total 132
dr-xr-xr-x. 2 root root 28672 Jul 10 16:29 bin
drwxr-xr-x. 2 root root 6 Apr 11 2018 etc
drwxr-xr-x. 2 root root 6 Apr 11 2018 games
drwxr-xr-x. 46 root root 8192 Jul 18 2020 include
dr-xr-xr-x. 28 root root 4096 Jul 17 2020 lib
dr-xr-xr-x. 43 root root 28672 Jul 18 2020 lib64
drwxr-xr-x. 24 root root 4096 Jul 17 2020 libexec
drwxr-xr-x. 14 root root 155 Jul 18 2020 local
drwxr-xr-x 6 root root 52 Jul 10 16:29 pgsql-12
dr-xr-xr-x. 2 root root 12288 Jul 18 2020 sbin
drwxr-xr-x. 90 root root 4096 Jul 18 2020 share
drwxr-xr-x. 4 root root 34 Jul 17 2020 src
lrwxrwxrwx. 1 root root 10 Jul 17 2020 tmp -> ../var/tmp
[postgres@source /usr]$cd pgsql-12/
[postgres@source /usr/pgsql-12]$ll
total 16
drwxr-xr-x 2 root root 4096 Jul 10 16:29 bin
drwxr-xr-x 3 root root 23 Jul 10 16:29 doc
drwxr-xr-x 3 root root 4096 Jul 10 16:29 lib
drwxr-xr-x 8 root root 4096 Jul 10 16:29 share
[postgres@source /usr/pgsql-12]$cd bin
[postgres@source /usr/pgsql-12/bin]$ll
total 10864
-rwxr-xr-x 1 root root 67360 May 13 22:02 clusterdb
-rwxr-xr-x 1 root root 67344 May 13 22:02 createdb
-rwxr-xr-x 1 root root 71896 May 13 22:02 createuser
-rwxr-xr-x 1 root root 63056 May 13 22:02 dropdb
-rwxr-xr-x 1 root root 63016 May 13 22:02 dropuser
-rwxr-xr-x 1 root root 130440 May 13 22:02 initdb
-rwxr-xr-x 1 root root 41800 May 13 22:02 oid2name
-rwxr-xr-x 1 root root 41432 May 13 22:02 pg_archivecleanup
-rwxr-xr-x 1 root root 118208 May 13 22:02 pg_basebackup
-rwxr-xr-x 1 root root 155512 May 13 22:02 pgbench
-rwxr-xr-x 1 root root 62560 May 13 22:02 pg_checksums
-rwxr-xr-x 1 root root 41240 May 13 22:02 pg_config
-rwxr-xr-x 1 root root 58064 May 13 22:02 pg_controldata
-rwxr-xr-x 1 root root 71048 May 13 22:02 pg_ctl
-rwxr-xr-x 1 root root 405224 May 13 22:02 pg_dump
-rwxr-xr-x 1 root root 97288 May 13 22:02 pg_dumpall
-rwxr-xr-x 1 root root 62952 May 13 22:02 pg_isready
-rwxr-xr-x 1 root root 80336 May 13 22:02 pg_receivewal
-rwxr-xr-x 1 root root 84536 May 13 22:02 pg_recvlogical
-rwxr-xr-x 1 root root 62640 May 13 22:02 pg_resetwal
-rwxr-xr-x 1 root root 172120 May 13 22:02 pg_restore
-rwxr-xr-x 1 root root 100656 May 13 22:02 pg_rewind
-rwxr-xr-x 1 root root 37096 May 13 22:02 pg_standby
-rwxr-xr-x 1 root root 45800 May 13 22:02 pg_test_fsync
-rwxr-xr-x 1 root root 37272 May 13 22:02 pg_test_timing
-rwxr-xr-x 1 root root 138536 May 13 22:02 pg_upgrade
-rwxr-xr-x 1 root root 96328 May 13 22:02 pg_waldump
-rwxr-xr-x 1 root root 7743992 May 13 22:02 postgres
-rwxr-xr-x 1 root root 2175 May 13 22:02 postgresql-12-check-db-dir
-rwxr-xr-x 1 root root 9413 May 13 22:02 postgresql-12-setup
lrwxrwxrwx 1 root root 8 Jul 10 16:29 postmaster -> postgres
-rwxr-xr-x 1 root root 631720 May 13 22:02 psql
-rwxr-xr-x 1 root root 71584 May 13 22:02 reindexdb
-rwxr-xr-x 1 root root 76032 May 13 22:02 vacuumdb
-rwxr-xr-x 1 root root 37448 May 13 22:02 vacuumlo
安装文件是root:root
- 初始化数据库，并启动
[postgres@source /usr/pgsql-12/bin]$/usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... chown: changing ownership of ‘/var/lib/pgsql/12/initdb.log’: Operation not permitted
chown: changing ownership of ‘/var/lib/pgsql/12/data//log’: Operation not permitted
failed, see /var/lib/pgsql/12/initdb.log
[postgres@source /usr/pgsql-12/bin]$sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... mkdir: cannot create directory ‘/var/lib/pgsql/12/data//log’: File exists
failed, see /var/lib/pgsql/12/initdb.log
[postgres@source /usr/pgsql-12/bin]$cd /var/lib/pgsql/12/
[postgres@source /var/lib/pgsql/12]$ll
total 4
drwx------ 2 postgres postgres 6 May 13 22:02 backups
drwx------ 3 postgres postgres 17 Jul 10 16:31 data
-rw------- 1 postgres dba 649 Jul 10 16:31 initdb.log
[postgres@source /var/lib/pgsql/12]$cd data/
[postgres@source /var/lib/pgsql/12/data]$ll
total 0
drwx------ 2 postgres postgres 6 Jul 10 16:31 log
[postgres@source /var/lib/pgsql/12/data]$rm -rf log/
[postgres@source /var/lib/pgsql/12/data]$/usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... chown: changing ownership of ‘/var/lib/pgsql/12/data//log’: Operation not permitted
failed, see /var/lib/pgsql/12/initdb.log
[postgres@source /var/lib/pgsql/12/data]$ll
total 0
drwx------ 2 postgres dba 6 Jul 10 16:32 log
[postgres@source /var/lib/pgsql/12/data]$rm -rf log/
[postgres@source /var/lib/pgsql/12/data]$sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... OK
[postgres@source /var/lib]$sudo systemctl enable postgresql-12
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-12.service to /usr/lib/systemd/system/postgresql-12.service.
[postgres@source /var/lib] s u d o s y s t e m c t l s t a r t p o s t g r e s q l − 12 [ p o s t g r e s @ s o u r c e / v a r / l i b ] sudo systemctl start postgresql-12 [postgres@source /var/lib] sudosystemctlstartpostgresql−12[postgres@source/var/lib]sudo systemctl status postgresql-12
● postgresql-12.service - PostgreSQL 12 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)
Active: active (running) since Sat 2021-07-10 16:33:27 CST; 11s ago
Docs: https://www.postgresql.org/docs/12/static/
Process: 7085 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 7090 (postmaster)
CGroup: /system.slice/postgresql-12.service
├─7090 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
├─7092 postgres: logger
├─7094 postgres: checkpointer
├─7095 postgres: background writer
├─7096 postgres: walwriter
├─7097 postgres: autovacuum launcher
├─7098 postgres: stats collector
└─7099 postgres: logical replication launcher
Jul 10 16:33:27 source systemd[1]: Starting PostgreSQL 12 database server...
Jul 10 16:33:27 source postmaster[7090]: 2021-07-10 16:33:27.345 CST [7090] LOG: starting PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Re...-44), 64-bit
Jul 10 16:33:27 source postmaster[7090]: 2021-07-10 16:33:27.346 CST [7090] LOG: listening on IPv6 address "::1", port 5432
Jul 10 16:33:27 source postmaster[7090]: 2021-07-10 16:33:27.346 CST [7090] LOG: listening on IPv4 address "127.0.0.1", port 5432
Jul 10 16:33:27 source postmaster[7090]: 2021-07-10 16:33:27.347 CST [7090] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Jul 10 16:33:27 source postmaster[7090]: 2021-07-10 16:33:27.348 CST [7090] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
Jul 10 16:33:27 source postmaster[7090]: 2021-07-10 16:33:27.355 CST [7090] LOG: redirecting log output to logging collector process
Jul 10 16:33:27 source postmaster[7090]: 2021-07-10 16:33:27.355 CST [7090] HINT: Future log output will appear in directory "log".
Jul 10 16:33:27 source systemd[1]: Started PostgreSQL 12 database server.
Hint: Some lines were ellipsized, use -l to show in full.
[postgres@source /var/lib]$ps -ef|grep post
root 6481 1 0 16:11 ? 00:00:00 /usr/libexec/postfix/master -w
postfix 6482 6481 0 16:11 ? 00:00:00 pickup -l -t unix -u
postfix 6483 6481 0 16:11 ? 00:00:00 qmgr -l -t unix -u
root 6667 6633 0 16:12 pts/0 00:00:00 su - postgres
postgres 6668 6667 0 16:12 pts/0 00:00:00 -bash
postgres 7090 1 0 16:33 ? 00:00:00 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
postgres 7092 7090 0 16:33 ? 00:00:00 postgres: logger
postgres 7094 7090 0 16:33 ? 00:00:00 postgres: checkpointer
postgres 7095 7090 0 16:33 ? 00:00:00 postgres: background writer
postgres 7096 7090 0 16:33 ? 00:00:00 postgres: walwriter
postgres 7097 7090 0 16:33 ? 00:00:00 postgres: autovacuum launcher
postgres 7098 7090 0 16:33 ? 00:00:00 postgres: stats collector
postgres 7099 7090 0 16:33 ? 00:00:00 postgres: logical replication launcher
postgres 7102 6668 0 16:33 pts/0 00:00:00 ps -ef
postgres 7103 6668 0 16:33 pts/0 00:00:00 grep --color=auto post
可以看出，pg数据库的所有关键进程都是postgres:postgres。
[postgres@source /var/lib]$sudo systemctl restart postgresql-12
[postgres@source /var/lib]$ps -ef|grep post
root 6481 1 0 16:11 ? 00:00:00 /usr/libexec/postfix/master -w
postfix 6482 6481 0 16:11 ? 00:00:00 pickup -l -t unix -u
postfix 6483 6481 0 16:11 ? 00:00:00 qmgr -l -t unix -u
root 6667 6633 0 16:12 pts/0 00:00:00 su - postgres
postgres 6668 6667 0 16:12 pts/0 00:00:00 -bash
postgres 7117 1 0 16:34 ? 00:00:00 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
postgres 7119 7117 0 16:34 ? 00:00:00 postgres: logger
postgres 7121 7117 0 16:34 ? 00:00:00 postgres: checkpointer
postgres 7122 7117 0 16:34 ? 00:00:00 postgres: background writer
postgres 7123 7117 0 16:34 ? 00:00:00 postgres: walwriter
postgres 7124 7117 0 16:34 ? 00:00:00 postgres: autovacuum launcher
postgres 7125 7117 0 16:34 ? 00:00:00 postgres: stats collector
postgres 7126 7117 0 16:34 ? 00:00:00 postgres: logical replication launcher
postgres 7127 6668 0 16:35 pts/0 00:00:00 ps -ef
postgres 7128 6668 0 16:35 pts/0 00:00:00 grep --color=auto post
重启之后，进程的属组还是postgres用户。
- 观察进程信息
[postgres@source ~]$pwd
/home/postgres
[postgres@source ~]$ls -ltra
total 12
-rw-r–r-- 1 postgres dba 231 Oct 31 2018 .bashrc
-rw-r–r-- 1 postgres dba 193 Oct 31 2018 .bash_profile
-rw-r–r-- 1 postgres dba 18 Oct 31 2018 .bash_logout
drwxr-xr-x. 4 root root 35 Jul 10 16:12 …
drwx------ 2 postgres dba 62 Jul 10 16:12 .
[postgres@source ~]$cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
[postgres@source ~]$cd /var/lib/
[postgres@source /var/lib]$cd pgsql/
[postgres@source /var/lib/pgsql]$ll
total 0
drwx------ 4 postgres postgres 51 Jul 10 16:31 12
[postgres@source /var/lib/pgsql]$ls -ltra
total 8
drwxr-xr-x. 27 root root 4096 Jul 10 16:29 ..
-rwx------ 1 postgres dba 266 Jul 10 16:29 .bash_profile
drwx------ 3 postgres postgres 37 Jul 10 16:29 .
drwx------ 4 postgres postgres 51 Jul 10 16:31 12
[postgres@source /var/lib/pgsql]$cat .bash_profile
[ -f /etc/profile ] && source /etc/profile
PGDATA=/var/lib/pgsql/12/data
export PGDATA
# If you want to customize your settings,
# Use the file below. This is not overridden
# by the RPMS.
[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile
[postgres@source /var/lib/pgsql]$cat /etc/passwd
root:x:0:0:root:/root:/bin/bash
bin:x:1:1:bin:/bin:/sbin/nologin
daemon:x:2:2:daemon:/sbin:/sbin/nologin
adm:x:3:4:adm:/var/adm:/sbin/nologin
lp:x:4:7:lp:/var/spool/lpd:/sbin/nologin
sync:x:5:0:sync:/sbin:/bin/sync
shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
halt:x:7:0:halt:/sbin:/sbin/halt
mail:x:8:12:mail:/var/spool/mail:/sbin/nologin
operator:x:11:0:operator:/root:/sbin/nologin
games:x:12:100:games:/usr/games:/sbin/nologin
ftp:x:14:50:FTP User:/var/ftp:/sbin/nologin
nobody:x:99:99:Nobody:/:/sbin/nologin
systemd-network:x:192:192:systemd Network Management:/:/sbin/nologin
dbus:x:81:81:System message bus:/:/sbin/nologin
polkitd:x:999:998:User for polkitd:/:/sbin/nologin
tss:x:59:59:Account used by the trousers package to sandbox the tcsd daemon:/dev/null:/sbin/nologin
sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
postfix:x:89:89::/var/spool/postfix:/sbin/nologin
mysql:x:1000:1000::/home/mysql:/bin/bash
postgres:x:1001:1001::/home/postgres:/bin/bash
postgres是我们初始手动创建的，id都是一致的
特点：
环境变量设置好了，但是家目录设置在了/var/lib/psql下面。沿用了postgres用户。安装文件属组是root，数据库文件属组是postgres，进程属组是postgres。postgres用户可以启停，但是要sudo权限，重启后仍然是postgres用户属组，这应该是脚本初始话后，写死的。
登录测试
[root@source /usr/pgsql-12]
p
s
q
l
p
s
q
l
:
e
r
r
o
r
:
F
A
T
A
L
:
r
o
l
e
"
r
o
o
t
"
d
o
e
s
n
o
t
e
x
i
s
t
[
r
o
o
t
@
s
o
u
r
c
e
/
u
s
r
/
p
g
s
q
l
−
12
]
psql psql: error: FATAL: role "root" does not exist [root@source /usr/pgsql-12]
psqlpsql:error:FATAL:role"root"doesnotexist[root@source/usr/pgsql−12]su - postgres
Last login: Thu Jul 8 18:59:03 CST 2021 on pts/0
[postgres@source ~]$psql
psql (12.7)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# exit
[postgres@source ~]$psql -U postgres -h 127.0.0.1 -d postgres -p5432
psql (12.7)
Type "help" for help.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------------+------+----------
public | pg_stat_statements | view | postgres
(1 row)
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
安装插件
– 本地登陆
su - postgres
psql
– 安装插件
create extension pageinspect;
create extension pg_stat_statements;
select * from pg_extension ;
select * from pg_available_extensions order by name;
– 修改postgres密码
alter user postgres with encrypted password ‘lhr’; 或 \password
select * from pg_tables;
select version();
初始化数据库
[postgres@postgre pg12.7]$ /opt/pgsql/bin/initdb -D /opt/pg12.7/pgdata -E UTF8 --locale=en_US.utf8 -U postgres
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.utf8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /opt/pg12.7/pgdata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... PRC
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
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:
/opt/pgsql/bin/pg_ctl -D /opt/pg12.7/pgdata -l logfile start
修改数据库参数
参考pgtune 网站https://pgtune.leopard.in.ua/#/配置如下，实际环境需要进行替换。
修改/opt/pgsql/pgdata/postgresql.conf 文件，值如下：
cluster_name=‘pg_6000’
listen_addresses = ‘*’
port = 6000
unix_socket_directories=’$PGDATA’
unix_socket_group=‘dba’
unix_socket_permissions=‘0700’
logging_collector=on
log_destination=‘csvlog’
log_filename=‘postgresql-%u.log’
log_file_mode=0600
log_truncate_on_rotation=on
log_rotation_age=1d
log_rotation_size=0
log_checkpoints=on
log_lock_waits=on
log_duration=on
log_statement_sample_rate=0.2
log_min_duration_sample=100ms
log_min_duration_statement=500ms
log_statement= ‘mod’
log_min_messages=warning
wal_level=replica
archive_mode=always
archive_command=‘cp %p /opt/archive6000/%f’
min_wal_size = 2GB
max_wal_size = 8GB
9 / 10
wal_keep_segments=128
wal_log_hints = on
max_connections = 2000
superuser_reserved_connections = 10
shared_buffers = 64GB
maintenance_work_mem = 2GB
wal_buffers = 16MB
work_mem = 8388kB
idle_in_transaction_session_timeout=300000
max_worker_processes = 128
max_parallel_workers_per_gather = 4
max_parallel_workers = 128
max_parallel_maintenance_workers = 4
max_wal_senders=10
checkpoint_completion_target = 0.9
random_page_cost = 1.1
effective_io_concurrency = 200
effective_cache_size = 192GB
default_statistics_target = 100
修改客户端认证参数
修改/opt/pgsql/pgdata/pg_hba.conf文件，值如下：
local all all trust
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 md5
host replication all 0.0.0.0/0 md5
配置环境变量
vi /home/postgres/.bash_profile
export LANG=en_US.UTF-8
export PS1="[\u@\h \W]\$ "
export PGPORT=6000
export PGDATA=/opt/pgsql/pgdata
export PGHOME=/opt/pgsql
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.
export DATE=`date +"%Y%m%d%H%M"`
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
alias psql='rlwrap psql'
配置开机启动
root 用户配置开机启动
vi /usr/lib/systemd/system/postgres-12.service
[Unit]
Description=PostgreSQL 12 database server
After=syslog.target network.target
[Service]
Type=forking
TimeoutSec=120
User=postgres
Environment="PGHOME=/opt/pgsql"
Environment="PGDATA=/opt/pgsql/pgdata"
Environment="PGPORT=6000"
Environment="LD_LIBRARY_PATH=/opt/pgsql/lib:/usr/local/lib:/usr/local/lib64:/usr/lib64"
ExecStart=/bin/bash -c '${PGHOME}/bin/pg_ctl start -w -D ${PGDATA} -l ${PGDATA}/log/startup.log'
ExecStop=/bin/bash -c '${PGHOME}/bin/pg_ctl stop -m fast -w -D ${PGDATA}'
ExecReload=/bin/bash -c '${PGHOME}/bin/pg_ctl reload -D ${PGDATA}'
[Install]
WantedBy=multi-user.target
chmod 644 /usr/lib/systemd/system/postgres-12.service
systemctl daemon-reload
systemctl enable postgres-12.service
systemctl start postgres-12.service
服务管理
postgres普通用户执行：
启动数据库
$ sudo systemctl start postgres-12
查看数据库运行状态
$ sudo systemctl status postgres-12
关闭数据库
$ sudo systemctl stop postgres-12
重启数据库
$ sudo systemctl restart postgres-12
reload数据库参数
$ sudo systemctl reload postgres-12
如：
[postgres@postgre ~]$ sudo systemctl status postgres-12.service
● postgres-12.service - PostgreSQL 12 database server
Loaded: loaded (/usr/lib/systemd/system/postgres-12.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2021-07-08 16:50:17 CST; 5min ago
Process: 10228 ExecStop=/bin/bash -c ${PGHOME}/bin/pg_ctl stop -m fast -w -D ${PGDATA} (code=exited, status=0/SUCCESS)
Process: 10231 ExecStart=/bin/bash -c ${PGHOME}/bin/pg_ctl start -w -D ${PGDATA} -l ${PGDATA}/log/startup.log (code=exited, status=0/SUCCESS)
Main PID: 10233 (postgres)
Tasks: 7
CGroup: /system.slice/postgres-12.service
├─10233 /opt/pg12.7/bin/postgres -D /opt/pgsql/pgdata
├─10235 postgres: checkpointer
├─10236 postgres: background writer
├─10237 postgres: walwriter
├─10238 postgres: autovacuum launcher
├─10239 postgres: stats collector
└─10240 postgres: logical replication launcher
Jul 08 16:50:16 postgre systemd[1]: Stopped PostgreSQL 12 database server.
Jul 08 16:50:16 postgre systemd[1]: Starting PostgreSQL 12 database server...
Jul 08 16:50:17 postgre systemd[1]: Started PostgreSQL 12 database server.
测试登录
查看pg进程：
[postgres@postgre ~]$ ps -ef|grep post
avahi 7185 1 0 15:04 ? 00:00:00 avahi-daemon: running [postgre.local]
root 7678 1 0 15:04 ? 00:00:00 /usr/libexec/postfix/master -w
postfix 7682 7678 0 15:04 ? 00:00:00 qmgr -l -t unix -u
postfix 10052 7678 0 16:43 ? 00:00:00 pickup -l -t unix -u
postgres 10233 1 0 16:50 ? 00:00:00 /opt/pg12.7/bin/postgres -D /opt/pgsql/pgdata
postgres 10235 10233 0 16:50 ? 00:00:00 postgres: checkpointer
postgres 10236 10233 0 16:50 ? 00:00:00 postgres: background writer
postgres 10237 10233 0 16:50 ? 00:00:00 postgres: walwriter
postgres 10238 10233 0 16:50 ? 00:00:00 postgres: autovacuum launcher
postgres 10239 10233 0 16:50 ? 00:00:00 postgres: stats collector
postgres 10240 10233 0 16:50 ? 00:00:00 postgres: logical replication launcher
root 10308 8051 0 16:54 pts/0 00:00:00 su - postgres
postgres 10309 10308 0 16:54 pts/0 00:00:00 -bash
postgres 10443 10309 0 16:55 pts/0 00:00:00 ps -ef
postgres 10444 10309 0 16:55 pts/0 00:00:00 grep --color=auto post
[postgres@postgre ~]$ ps -ef|grep pgsql
postgres 10233 1 0 16:50 ? 00:00:00 /opt/pg12.7/bin/postgres -D /opt/pgsql/pgdata
postgres 10446 10309 0 16:55 pts/0 00:00:00 grep --color=auto pgsql
注意 pg_hba.conf和postgresql.conf 文件中的内容。
[postgres@postgre ~]$ psql -U postgres -h 127.0.0.1 -d postgres -p6000
psql (12.7)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
558
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
