PG数据库安装-虚拟机模式

一、安装前的准备

1、安装依赖组件

yum install keyutils-libs-devel krb5-devel libcom_err-devel libkadm5 libselinux-devel libsepol-devel libverto-devel pcre-devel krb5-libs openssl-libs openssl

二、安装过程

1、新建用户及组

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

2、新建文件夹

setup为存放pg及后续要安装的repmgr源安装文件 pg15.5为数据库安装后的执行文件和数据文件
[postgres@fl-prod-pg01 ~]$ mkdir setup
[postgres@fl-prod-pg01 ~]$ mkdir pg15.5

3、更新环境变量

使用用户：root

vim /etc/profile

–指定bin文件路径
–指定data文件路劲 在初始化时会将data装载这个路径
export PATH=/home/postgres/pg15.5/bin:$PATH
export PGDATA=/data/postgres/data
加载环境变量内容，使之生效 source /etc/profile

4、上传安装文件

1）上传pg（15.5版本）安装文件
下载地址：https://www.postgresql.org/ftp/source/v15.5/
先上传到 setup/
2）tar -zxvf postgresql-15.5.tar.gz --解压压缩包

5、更新pg数据库安装的配置信息

更新配置，拟安装至/home/postgres/pg15.5 ./configure --prefix=/home/postgres/pg15.5 --with-openssl

6、编译安装

编译和安装 make && make install

7、初始化数据库

初始化数据库 使用用户：postgres ./initdb -D $PGDATA

8、启动数据库

启动数据库 pg_ctl start 查看数据库运行状态

[postgres@fl-prod-pg01 ~]$ pg_ctl status
pg_ctl: server is running (PID: 15172)
/home/postgres/pg15.5/bin/postgres “-D” “/home/postgres/pg15.5/data”

9、修改数据库配置

1）修改配置文件，监听所有主机
vim /home/postgres/pg15.5/data/postgresql.conf
将原本的#listen_addresses = 'localhost’换成

listen_addresses = '*'
lc_monetary = 'zh_CN.UTF-8' # locale for monetary formatting
lc_time = 'zh_CN.UTF-8'                         # locale for time formatting

2）修改配置文件，接受远程机器连接 vim /data/postgres/data/pg_hba.conf

host    all             all             0.0.0.0/0               trust  

重启数据库

[postgres@fl-prod-pg01 ~]$ pg_ctl restart
waiting for server to shut down… done
server stopped
waiting for server to start…2024-03-08 13:20:05.113 CST [29975] LOG: starting PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2024-03-08 13:20:05.113 CST [29975] LOG: listening on IPv4 address “0.0.0.0”, port 5432
2024-03-08 13:20:05.114 CST [29975] LOG: listening on IPv6 address “::”, port 5432
2024-03-08 13:20:05.115 CST [29975] LOG: listening on Unix socket “/tmp/.s.PGSQL.5432”
2024-03-08 13:20:05.118 CST [29978] LOG: database system was shut down at 2024-03-08 13:20:04 CST
2024-03-08 13:20:05.122 CST [29975] LOG: database system is ready to accept connections
done
server started

三、测试安装结果

1、验证数据库-访问

[postgres@fl-prod-worker01 bin]$ psql
psql (15.5)
Type “help” for help.
postgres=#

2、查看所有的数据库：

postgres-# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------±---------±---------±------------±------------±-----------±----------------±----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(3 rows)

3、创建表，插入数据：

postgres=# create table test_table (varchar name);
CREATE TABLE
postgres=# insert into test_table values(‘zhw’);
INSERT 0 1

4、查看表数据：

postgres=#select * from test_table;
varchar

zhw
(1 row)

5、删除测试表：

postgres=# drop table test_table ;
DROP TABLE

6、如果访问出现问题，涉及端口的，按照如下方式处理 1）查询防火墙是否开启，running代表开启

[root@fl-prod-pg02 ~]# firewall-cmd --state
running
2）开启防火墙命令：

systemctl start firewalld
3）查看端口5432是否开启

[root@fl-prod-pg02 ~]# firewall-cmd --zone=public --query-port=5432/tcp
no
4）开启5432端口：

[root@fl-prod-pg02 ~]# firewall-cmd --zone=public --add-port=5432/tcp --permanent
success
5）​刷新防火墙

[root@fl-prod-pg02 ~]# firewall-cmd --reload
success