PostgreSQL12.3安装操作手册_"the files belonging to this database system will

PG安装介质及官方安装指南

下载源码：PostgreSQL: File Browser

安装指南：PostgreSQL: Documentation: 12: Chapter 16. Installation from Source Code

OS环境:（操作系统最小化安装，并且安装开发工具包）

cat /etc/redhat-release 
CentOS Linux release 7.6.1810 (Core) 
1
2

1、更改HOSTNAME

hostnamectl set-hostname <host_name>
1

2、配置HOSTNAME解析

vi /etc/hosts
1

追加以下内容：

<IP> <HOSTNAME>
1

3、关闭防火墙及selinux

（1）关闭防火墙：

1. 防火墙状态：

   # systemctl status firewalld.service
   1

2. 关闭防火墙：

   #关闭防火墙：（临时关闭）
   systemctl stop firewalld.service
   #禁用防火墙：（永久关闭，需要重启生效）
   systemctl disable firewalld.service
   1
   2
   3
   4

   #禁用防火墙输出如下：
   Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
   Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
   1
   2
   3

3. 修改后查看防火墙状态：

   # systemctl list-unit-files|grep firewalld.service
   firewalld.service                             disabled
   1
   2

（2）关闭selinux：

1. 查看selinux状态：

   getenforce
   1

2. 临时关闭selinux：

   setenforce 0
   1

3. 禁用selinux：

   vi /etc/selinux/config
   1

   更改SELINUX=disabled（重启后生效）

   # This file controls the state of SELinux on the system.
   # SELINUX= can take one of these three values:
   #     enforcing - SELinux security policy is enforced.
   #     permissive - SELinux prints warnings instead of enforcing.
   #     disabled - No SELinux policy is loaded.
   SELINUX=disabled
   # SELINUXTYPE= can take one of these two values:
   #     targeted - Targeted processes are protected,
   #     mls - Multi Level Security protection.
   SELINUXTYPE=targeted
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10

4、创建用户组

groupadd -g 1000 postgres
useradd -u 1000 -g postgres postgres 
1
2

5、创建目录

mkdir -p /usr/pgsql12
mkdir /pgdata12
chown postgres:postgres /pgdata12
1
2
3

6、配置YUM

备份配置文件

cd /etc/yum.repos.d
mkdir repo
mv ./*.repo ./repo
1
2
3

创建yum配置文件

vi /etc/yum.repos.d/centos_7.6.repo
1

添加以下内容

[CentOS7.6]
name=CentOS7.6_YUM
baseurl=file:///mnt/cdrom
gpgcheck=0
enabled=1
1
2
3
4
5

创建挂载目录并清理YUM缓存

mkdir /mnt/cdrom
mount /dev/sr0 /mnt/cdrom
yum clean all
1
2
3

7、安装依赖包

yum install -y bison flex readline-devel zlib-devel gcc ntp
1

8、配置时钟同步（NTP）

1. 启动NTP服务：

   systemctl start ntpd
   1

2. 开机自启动：

   systemctl enable ntpd.service
   1

3. 编辑ntp.conf，注释 server 0. 1. 2.等等：

   vi /etc/ntp.conf
   1

4. 增加以下内容：(时钟同步服务器IP)：

   server <GATEWAY_IP> iburst
   1

5. 启用NTP微调：

   vi /etc/sysconfig/ntpd
   1

   编辑以上文件，添加 **-x **如下：

   # Command line options for ntpd
   OPTIONS="-x -g"
   1
   2

6. 重启NTP服务：

   systemctl restart ntpd.service
   1

7. 查看NTP同步:

   ntpq -p
   1

8. 查看NTP状态：

   systemctl status ntpd
   1

9. 关闭并禁用chronyd服务：

   systemctl stop chronyd.service
   systemctl disable chronyd.service
   1
   2

   不禁用在重启系统时自动启动会导致ntpd服务无法启动

9、配置环境变量

vi ~/.bash_profile
1

export PGHOME=/usr/pgsql12
export PATH=$PGHOME/bin:$PATH
export PGUSER=htuser				#配置默认登录user
export PGDATABASE=htdb				#配置默认登录db
export PGDATA=/pgdata12
LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
1
2
3
4
5
6

使环境变量生效

source  ~/.bash_profile
1

10、编译安装PG软件

上传源码包（postgresql-12.3.tar.gz）到/usr/pgsql12目录下，使用postgres用户进行安装操作

su - postgres

cd /usr/pgsql12

tar zxvf postgresql-12.3.tar.gz

cd postgresql-12.3

./configure --prefix=/usr/pgsql12/

make install

make world

make install-world

chown postgres:postgres -R /usr/pgsql12
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

输出如下：

...
make -C config all
make[1]: Entering directory `/usr/pgsql12/postgresql-12.3/config'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/usr/pgsql12/postgresql-12.3/config'
All of PostgreSQL successfully made. Ready to install.
1
2
3
4
5
6

11、启用数据库日志

修改配置文件

vi $PGDATA/postgresql.conf
1

logging_collector =on
1

重启数据库使参数生效：

pg_ctl restart -D /pgdata12/
1

日志生成默认路径：

$PGDATA/log
1

测试：

（1）初始化数据库

$ initdb -D /pgdata12/ -U postgres -E utf-8
1

输出如下：

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 text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /pgdata12 ... 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:

    /usr/pgsql12/bin/pg_ctl -D /pgdata12/ -l logfile start
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

（2）启动PG数据库

$ pg_ctl start -D /pgdata12/
waiting for server to start....2022-08-18 00:06:21.069 CST [39578] LOG:  starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2022-08-18 00:06:21.070 CST [39578] LOG:  listening on IPv6 address "::1", port 5432
2022-08-18 00:06:21.070 CST [39578] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-08-18 00:06:21.071 CST [39578] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-08-18 00:06:21.076 CST [39579] LOG:  database system was interrupted; last known up at 2022-08-17 23:54:52 CST
2022-08-18 00:06:21.081 CST [39579] LOG:  database system was not properly shut down; automatic recovery in progress
2022-08-18 00:06:21.082 CST [39579] LOG:  invalid record length at 0/163B548: wanted 24, got 0
2022-08-18 00:06:21.082 CST [39579] LOG:  redo is not required
2022-08-18 00:06:21.084 CST [39578] LOG:  database system is ready to accept connections
 done
server started
1
2
3
4
5
6
7
8
9
10
11
12

（3）登录数据库并查询版本

$ psql -Upostgres -dpostgres
psql (12.3)
Type "help" for help.

postgres=# 
postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

postgres=# \c
You are now connected to database "postgres" as user "postgres".
1
2
3
4
5
6
7
8
9
10
11
12
13

（4）关闭数据库（三种方式）

smart

$ pg_ctl -D /pgdata12/ -m smart stop
waiting for server to shut down....2022-08-17 23:52:28.126 CST [38758] LOG:  received smart shutdown request
2022-08-17 23:52:28.127 CST [38758] LOG:  background worker "logical replication launcher" (PID 38765) exited with exit code 1
2022-08-17 23:52:28.127 CST [38760] LOG:  shutting down
2022-08-17 23:52:28.132 CST [38758] LOG:  database system is shut down 
done
server stopped
1
2
3
4
5
6
7

smart: 等所有的连接中止后，关闭数据库。如果客户端连接不终止， 则无法关闭数据库

• 相当于oracle（shutdown normal）

fast

$ pg_ctl -D /pgdata12/ -m fast stop
2022-08-17 23:54:24.098 CST [38951] LOG:  received fast shutdown request
2022-08-17 23:54:24.099 CST [38951] LOG:  aborting any active transactions
2022-08-17 23:54:24.099 CST [38951] LOG:  background worker "logical replication launcher" (PID 38958) exited with exit code 1
2022-08-17 23:54:24.100 CST [38953] LOG:  shutting down
2022-08-17 23:54:24.103 CST [38951] LOG:  database system is shut down
done
server stopped
1
2
3
4
5
6
7
8

fast: 快速关闭数据库， 断开客户端的连接，让已有的事务回滚，然后正常关闭数据库

• 相当于oracle（shutdown immediate）

immediate

$ pg_ctl -D /pgdata12/ -m immediate stop
2022-08-17 23:54:57.250 CST [38990] LOG:  received immediate shutdown request
2022-08-17 23:54:57.252 CST [38995] WARNING:  terminating connection because of crash of another server process
2022-08-17 23:54:57.252 CST [38995] DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2022-08-17 23:54:57.252 CST [38995] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2022-08-17 23:54:57.253 CST [38990] LOG:  database system is shut down
done
server stopped
1
2
3
4
5
6
7
8

immediate： 立即关闭数据库，立即停止数据库进程，直接退出，下次启动时会进行实例恢复

• 相当于oracle（shutdown abort）

（初次学习、诸多不足、请多指教）