# PostgreSQLInstall

**Repository Path**: brianchou/postgresql-install

## Basic Information

- **Project Name**: PostgreSQLInstall
- **Description**: 数据库PostgreSQL安装部署，服务器需要访问外网
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: main
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2025-05-19
- **Last Updated**: 2025-05-21

## Categories & Tags

**Categories**: Uncategorized

**Tags**: Postgresql

## README

# PostgreSQL 编译安装

[toc]

## 说明

> 此脚本要求服务器可访问外网，如果服务器无法访问外网，则只能通过离线方式。下载对应版本，并将233行左右的安装依赖包命令注释，同时在可访问外网的相同发行版本的服务器上下载这些包到本地（yum/dnf install --downloadonly --destdir），并在对应服务器上离线安装相关包。（16和17版本需要libicu libicu-devel）
>
> perl-ExtUtils-Embed readline-devel zlib zlib-devel lz4 bison flex perl-devel openssl openssl-devel python3-devel libicu libicu-devel
>
> postgresq的包下载地址：https://www.postgresql.org/ftp/source/
>
> 阿里云上的下载地址：https://brianhsiung.oss-cn-hangzhou.aliyuncs.com/deploy/postgresql/postgresql-version.tar.gz 其中version的值为 14.18或15.13或16.9或17.5

## 脚本变量

根据实际情况修改脚本`postgresql_install.sh`下的内容，其它变量可保持默认。

| 变量名                | 说明                                                         |
| --------------------- | ------------------------------------------------------------ |
| PG_VERSION            | 将要部署的版本，默认14.18 可选15.13，16.9，17.5              |
| PG_NODE_ROLE          | 部署的模式，默认为single，表示单机模式。如果是主从复制部署，那么主节点上改成master 从节点上改成slave |
| PG_MASTER_HOST        | 单机模式部署时忽略，主从复制部署时主节点的IP                 |
| PG_SLAVE_HOST         | 单机模式部署时忽略，主从复制部署时从节点的IP                 |
| PG_DATA_BASE          | 数据库数据存储目录，默认为/data/pgsql                        |
| PG_MAX_CONNECTIONS    | 数据库的最大连接数，默认为3000                               |
| PG_POSTGRES_PASSWORD  | 账号postgres的密码                                           |
| PG_REPL_USER_PASSWORD | 主从复制部署时，账号repl_user的密码                          |
| PG_ADMIN_PASSWORD     | 账号admin的密码，拥有创建角色和数据库的权限，日常管理账号    |

## 执行部署

将脚本上传到服务器，本例以`/root`为例，并执行以下命令安装。

```shell
bash postgresql_install.sh
```

## 校验

脚本执行完后，执行以下命令查看服务是否启动。

```shell
systemctl status postgresql
```

日志文件默认存于`/data/pgsql/log`目录。

## 主从复制部署

> 主节点IP：172.26.85.156，从节点IP：172.26.85.157 建议先在主库上执行脚本，随后在从库上执行脚本。

### 主节点

将`PG_NODE_ROLE`的值修改为`master` 

![image-20250521094137575](README.assets/image-20250521094137575.png)

执行以下命令

```shell
bash postgresql_install.sh
```

执行记录如下，编译需要较长时间，需要耐心等待。

![image-20250521093718216](README.assets/image-20250521093718216.png)

执行命令`systemctl status postgresql` 查看运行状态。

![image-20250521093743509](README.assets/image-20250521093743509.png)

### 从节点

将`PG_NODE_ROLE`的值修改为`slave` 

![image-20250521094237688](README.assets/image-20250521094237688.png)

执行以下命令

```shell
bash postgresql_install.sh
```

执行记录如下，编译需要较长时间，需要耐心等待。

![image-20250521093917800](README.assets/image-20250521093917800.png)

执行命令`systemctl status postgresql` 查看运行状态。

![image-20250521094004664](README.assets/image-20250521094004664.png)

查看日志，日志文件默认存于`/data/pgsql/log`目录。

![image-20250521094326171](README.assets/image-20250521094326171.png)

### 校验正确性

#### 主库

执行以下命令

```shell
su postgres
/usr/local/pgsql/bin/psql -c "select * from pg_stat_replication;"
```

可以看到主从复制状态

![image-20250521094839525](README.assets/image-20250521094839525.png)

```shell
/usr/local/pgsql/bin/psql -c "select pg_is_in_recovery();"
```

当前值为`f`，表示主库。

![image-20250521095036977](README.assets/image-20250521095036977.png)

主库执行命令创建数据库

```shell
/usr/local/pgsql/bin/psql -c "create database demo owner admin;"
/usr/local/pgsql/bin/psql -c "\l"
```

![image-20250521095501566](README.assets/image-20250521095501566.png)

#### 从库

执行以下命令，`t` 表示从库。

```shell
su postgres
/usr/local/pgsql/bin/psql -c "select pg_is_in_recovery();"
```

![image-20250521095120187](README.assets/image-20250521095120187.png)

执行以下命令查看从库是否存在`demo`库。

```shell
/usr/local/pgsql/bin/psql -c "\l"
```

![image-20250521095548668](README.assets/image-20250521095548668.png)

## 安装失败

当安装失败时，处理对应错误，并执行以下命令重新安装，`/data/pgsql`为`PG_DATA_BASE`的值。

```shell
rm -rf /data/pgsql
bash postgresql_install.sh
```

## 附录

```shell
su - postgres -c '/usr/local/pgsql/bin/psql -c "select pg_is_in_recovery();"'
su - postgres -c '/usr/local/pgsql/bin/psql -c "select * from pg_stat_replication;"'
```

从库日志

```shell
2025-05-21 10:41:54.749 CST [42362] LOG:  starting PostgreSQL 16.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 10.2.1 20200825 (Alibaba 10.2.1-3.8 2.32), 64-bit
2025-05-21 10:41:54.749 CST [42362] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2025-05-21 10:41:54.749 CST [42362] LOG:  listening on IPv6 address "::", port 5432
2025-05-21 10:41:54.754 CST [42362] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-05-21 10:41:54.761 CST [42366] LOG:  database system was interrupted; last known up at 2025-05-21 10:41:54 CST
2025-05-21 10:41:54.813 CST [42366] LOG:  entering standby mode
2025-05-21 10:41:54.813 CST [42366] LOG:  starting backup recovery with redo LSN 0/2000028, checkpoint LSN 0/2000098, on timeline ID 1
2025-05-21 10:41:54.817 CST [42366] LOG:  redo starts at 0/2000028
2025-05-21 10:41:54.820 CST [42366] LOG:  completed backup recovery with redo LSN 0/2000028 and end LSN 0/2000138
2025-05-21 10:41:54.820 CST [42366] LOG:  consistent recovery state reached at 0/2000138
2025-05-21 10:41:54.820 CST [42362] LOG:  database system is ready to accept read-only connections
2025-05-21 10:41:54.894 CST [42367] LOG:  started streaming WAL from primary at 0/3000000 on timeline 1
2025-05-21 10:46:54.858 CST [42364] LOG:  restartpoint starting: time
2025-05-21 10:46:54.969 CST [42364] LOG:  restartpoint complete: wrote 2 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.103 s, sync=0.002 s, total=0.112 s; sync files=1, longest=0.002 s, average=0.002 s; distance=16384 kB, estimate=16384 kB; lsn=0/3000098, redo lsn=0/3000060
2025-05-21 10:46:54.969 CST [42364] LOG:  recovery restart point at 0/3000060
```