PostgreSQL 多数据库集簇配置及多数据库复制方法【流程+代码实例】

server/2025/3/17 18:39:06/

PostgreSQL 多数据库集簇配置及多数据库复制方法

1. 多数据库集簇配置

安装下载完postgresql后，系统此时包含一个postgres用户和一个名为postgres的默认数据库。

PostgreSQL 基本命令

  • 服务管理命令

    # 停止和启动及重启PostgreSQL服务
sudo systemctl stop postgresql
sudo systemctl start postgresql
sudo systemctl restart postgresql# 查看服务当前状态
sudo systemctl status postgresql# 设置服务开机自启动
sudo systemctl enable postgresql
# 禁止服务开机自启动
sudo systemctl disable postgresql

    systemctl 是 Linux 系统中用于管理系统服务的工具

  • 客服端交互命令

    # 连接到数据库
sudo -u postgres psql# 连接指定数据库
sudo -u postgres psql -d <database_name># 退出
\q

  • 数据库操作命令（psql客户端）

    # 创建新数据库
CREATE DATABASE <database_name>;# 删除
DROP DATABASE <database_name>;# 列出
\l# 切换
\c <database_name>

  • 用户及权限管理

    # 创建
CREATE USER your_username WITH PASSWORD 'your_password';# 删除
DROP USER your_username;# 授权
GRANT ALL PRIVILEGES ON DATABASE your_database_name TO your_username;# 撤销
REVOKE ALL PRIVILEGES ON DATABASE your_database_name FROM your_username;

自定义端口与数据库集簇

配置要设置端口等基本信息，需要先停止服务

sudo systemctl stop postgresql

初始化集簇步骤：

# 创建一个数据库集簇的数据目录 -p 递归创建
sudo mkdir -p /data/postgresql
# 赋权
sudo chown postgres:postgres /data/postgresql
# 当前用户使用postgres用户的身份初始化数据库集簇 -u 指定用户 -D 指定数据目录 -E 指定默认字符编码
sudo -u postgres /usr/lib/postgresql/<version>/bin/initdb -D /data/postgresql -E UTF8

initdb 是 PostgreSQL 数据库管理系统中的一个关键命令，主要用于初始化一个新的数据库集簇。

修改配置文件：

sudo -u postgres vim <PG_HOME>/postgresql.conf
# 本例中<PG_HOME>为 /data/postgresql 即数据目录# 设置值
listen_addresses = '*'   # 允许远程访问
port = 5439    # 改成任意空闲端口          
max_connections = 100 # 设置最大连接数

等价于:

# -a 追加模式 tee 将标准输入复制到每个指定的文件
sudo -u postgres tee -a <PG_HOME>/postgresql.conf << EOF
listen_addresses = '*'   
port = 5439            
max_connections = 100   
EOF

EOF（Here Document）是一种在脚本中向文件追加内容或传递多行输入的便捷方法。

两种启动数据库集簇方式

1.手动
# pg_ctl 根据-D 后的数据目录来定位要启动的数据库集簇。
sudo -u postgres /usr/lib/postgresql/<PostgreSQL-Version>/bin/pg_ctl start -D /data/postgresql

pg_ctl 是 PostgreSQL 提供的一个实用工具，用于控制 PostgreSQL 服务器的启动、停止、重启、重新加载配置等操作。

注意和systemctl的比较：

  • pg_ctl 是 PostgreSQL 自带的命令行工具，主要用于对 PostgreSQL 数据库集簇进行精细的控制和管理。直接与 PostgreSQL 服务器进行交互，操作的核心是数据库集簇。
  • systemctl 是 Linux 系统中 systemd 系统和服务管理器的命令行工具，用于管理系统服务。它是操作系统层面的服务管理工具，对各种系统服务（包括 PostgreSQL 服务）进行统一管理。（见2）

2.一键启动

首先创建Systemd服务文件

sudo vim /etc/systemd/system/postgresql-custom.service
# postgresql-custom 自定义名称，一个名称对应与一个数据库集簇

加入以下内容：

[Unit]
Description=PostgreSQL Custom Instance  # 对该服务的简要描述，方便用户了解服务的功能
After=network.target   # 确保在网络服务启动后再启动 PostgreSQL 服务[Service]
Type=forking  # 服务使用 fork() 方式启动，即父进程启动子进程后退出
User=postgres
Group=postgres # 指定服务以 postgres 用户和 postgres 用户组的身份运行
Restart=on-failure  # 服务因异常退出时，systemd 会自动尝试重启该服务
ExecStart=/usr/lib/postgresql/14/bin/pg_ctl start -D /data/postgresql  # 启动服务时执行的命令
ExecStop=/usr/lib/postgresql/14/bin/pg_ctl stop -D /data/postgresql    # 停止服务时执行的命令[Install]
WantedBy=multi-user.target  # 该服务在多用户模式下被启动

保存退出，命令行输入：

sudo systemctl daemon-reload   # 重新加载 systemd 配置
sudo systemctl start postgresql-custom # 若启动其他集簇，编辑对应systemd文件并启动即可

监听服务状态

# 监听的端口号； ss 显示套接字统计信息的工具 -t tcp -u udp -l 显示处于监听状态的套接字 -n 数字形式显示地址和端口号 -p 显示使用该套接字的进程信息  | grep <port_id>  筛选出监听 5439 端口的套接字信息
sudo ss -tulnp | grep 5439

对应端口显示listen 即可。

2. 多数据库异步流复制

速通Docker安装

  • 更换apt源

     sudo apt updatesudo apt install apt-transport-https \ca-certificates \curl \software-properties-common \gnupg \lsb-release# **阿里源**curl -fsSL https://mirrors.aliyun.com/docker-ce/linux/ubuntu/gpg \| sudo gpg --dearmor -o /usr/share/keyrings/docker-archive-keyring.gpg# 添加 apt 源:# 阿里 apt 源
echo "deb [arch=$(dpkg --print-architecture) \
signed-by=/usr/share/keyrings/docker-archive-keyring.gpg] \
https://mirrors.aliyun.com/docker-ce/linux/ubuntu \
$(lsb_release -cs) stable" \
| sudo tee /etc/apt/sources.list.d/docker.list > /dev/nullsudo apt update
sudo apt-get update

    问题总结：

    1. sudo apt update 报错E: 仓库 “https://apt.postgresql.org/pub/repos/apt xenial-pgdg Release” 没有 Release 文件

    解决：sudo rm /etc/apt/sources.list.d/pgdg.list

    1. 安装curl依赖报错： curl : 依赖: libcurl3-gnutls (= 7.47.0-1ubuntu2) 但是 7.47.0-1ubuntu2.15 正要被安装

    解决：强制安装指定版本：sudo apt-get install libcurl3-gnutls=7.47.0-1ubuntu2

  • 安装Docker Engine

    插件先不装

     sudo apt-get install docker-ce \docker-ce-cli \containerd.io

  • 更换镜像

    # 设置registry mirror
sudo vim /etc/docker/daemon.json# 加入以下内容 （最新镜像源 可以网上找资源）
{"registry-mirrors": ["https://docker.1ms.run","https://docker.xuanyuan.me"]
}## 重启Docker
systemctl daemon-reload
systemctl restart docker

  • 测试

    sudo docker run hello-world
# 本地找不到镜像就会去镜像源拉去相关镜像 输出对应镜像信息

异步流复制

动机

简单来说就是为了防止大哥出事，搞几个备份顺便还能分担大哥的压力。

准备工作

下载pg的docker镜像：

docker pull postgres

挂载数据卷同步数据：

ocker volume create postgre-data
创建主库容器
docker run -id --name=pg1 -v postgre-data:/var/lib/postgresql/data -p 5432:5432 -e POSTGRES_PASSWORD=123456 -e LANG=C.UTF-8 postgres
# dock run :基于指定的镜像创建并启动一个新的容器
# -d 保持后台运行 -i 保持标准输入流处于打开状态 --name 命名
# -v 挂载数据卷 -v <column_name>:<data_path> 数据卷名称及容器内数据目录
# -p <home_port>:<container_port> 将容器内部的端口映射到宿主机的端口 能通过宿主机的 5432 端口访问容器内运行的 PostgreSQL 服务
# postgres 镜像名称
相关配置（挂载数据卷内配置，不进入容器）

获取主机ip

hostname  # 主机名
hostname -i # 主机ip

打开配置文件,加入配置信息

vim /var/lib/docker/volumes/postgre-data/_data/postgresql.conf# 添加信息 普通模式 G 跳转底部
wal_level = replica         
max_wal_senders = 3        
hot_standby = on  
# host replication all 127.0.0.1/32 md5# pg_hba.conf 文件定义了哪些客户端可以连接到 PostgreSQL 服务器，以及它们使用何种认证方法进行身份验证。
sudo vim /var/lib/postgresql/data/pg_hba.conf
#添加
host replication all 127.0.0.1/16 md5
# 退出重启容器 docker ps -a 查看容器状态
sudo docker restart pg1

在主库中插入测试数据：

# 进入容器并使用 psql 连接数据库
docker exec -it pg1 psql -U postgres

-- 创建学生表
CREATE TABLE students (student_id SERIAL PRIMARY KEY,name VARCHAR(100) NOT NULL,age INT,gender CHAR(1),enrollment_date DATE);-- 插入示例数据
INSERT INTO students (name, age, gender, enrollment_date)VALUES 
('张三', 20, 'M', '2024-09-01'),('李四', 21, 'F', '2023-09-01'),('王五', 22, 'M', '2022-09-01'),('赵六', 19, 'F', '2025-01-15'),('孙七', 23, 'M', '2022-03-20'),('周八', 20, 'F', '2024-07-10'),('吴九', 22, 'M', '2023-11-05'),('郑十', 21, 'F', '2024-04-25'),('王十一', 18, 'M', '2025-02-01'),('李十二', 24, 'F', '2021-09-30'),('张十三', 20, 'M', '2024-09-12'),('刘十四', 22, 'F', '2023-06-18'),('陈十五', 19, 'M', '2025-03-08');# \q 退出psql
创建备库（pg_basebackup 初始化）

创建备库

sudo docker run -d --name pg2 -e POSTGRES_PASSWORD=123456 postgres:latest bash -c "while true; do sleep 1; done" 
# PostgreSQL 需要一个数据目录来存储数据库文件。如果没有挂载数据卷，PostgreSQL 服务将无法启动，因为默认的数据目录是空的。
# 当 PostgreSQL 服务无法启动时，容器会因为没有主进程而退出 无限循环脚本阻止容器退出
# bash -c：在容器内启动一个 Bash Shell，并执行后面的命令

查看pg1ip：

sudo docker exec -it pg1apt-get update
apt-get install net-tools
ifconfigexit

执行pg_basebackup创建基础备份:

# -h 主库ip或主机名  -U 连接到主库使用的用户名 -D 基础备份数据存储的目标目录  -R 自动生成恢复配置文件 备库在启动时连接到主库，并开始接收 WAL（预写式日志）流，从而实现流复制。 -X 指定如何处理WAL  stream 通过流复制的方式实时获取主节点的 WAL 日志，确保备份数据的一致性。sudo docker exec pg2 pg_basebackup -h <pg1_ip> -U postgres -D /var/lib/postgresql/data -P -R -X stream -v    
# 172.17.0.2

pg_basebackup 是 PostgreSQL 提供的一个工具，用于创建 PostgreSQL 数据库集群的基础备份。基础备份是创建流复制环境的第一步，它会复制主节点上的所有数据文件，为从节点提供一个初始的数据副本。-R 选项，备库会尝试通过网络连接到主库，连接成功后发送请求，主库将新生成的WAL日志持续发送给备库（流复制），备库根据WAL日志顺序执行，实现和主库的同步。

启动备库：

sudo docker exec pg2 su postgres -c "pg_ctl start -D /var/lib/postgresql/data"

主备一致性

接下来就是验收结果

  • 主库检查

    # 进入主库容器 psql进入pg交互
SELECT client_addr, state, sync_state, sent_lsn, write_lsn  FROM pg_stat_replication;
# 输出应备库IP等信息

  • 备库检查

    # 备库同理
SELECT pg_is_in_recovery(); # t 备库模式
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn() # 与主库一致

  • 数据一致性

    # 主库写入
CREATE TABLE test (id SERIAL PRIMARY KEY, data TEXT);
INSERT INTO test (data) VALUES ('HA test');# 备库查询
SELECT * FROM test;  # 数据一致

  • 日志信息等


http://www.ppmy.cn/server/175757.html

相关文章

硬件驱动——51单片机：寄存器、LED、动态数码管

硬件驱动——51单片机：寄存器、LED、动态数码管

目录 一、51单片机 1.寄存器 二、LED点灯 1.原理 2.封装函数 3.顺序点灯 4.特定位点灯 三、动态数码管 1.原理 2.封装函数 3.0~9跳变 4.顺序移位0~9跳变 一、51单片机 1.寄存器 51单片机共40个引脚&#xff0c;其中P0,P1,P2,P3是四个有8引脚的寄存器&#xff0…
阅读更多...
如何在androidstudio开发环境中查看sqlite数据库（按新版本Android Studio Giraffe提供详细步骤和操作说明，附截图，代码）

如何在androidstudio开发环境中查看sqlite数据库（按新版本Android Studio Giraffe提供详细步骤和操作说明，附截图，代码）

如何在androidstudio开发环境中查看sqlite数据库&#xff08;按新版本Android Studio Giraffe提供详细步骤和操作说明&#xff0c;附截图&#xff0c;代码&#xff09;鹿溪IT工作室提供_android studio查看数据库-CSDN博客
阅读更多...
黑马node.js教程（nodejs教程）——AJAX-Day01-04.案例_地区查询——查询某个省某个城市所有地区（代码示例）

黑马node.js教程（nodejs教程）——AJAX-Day01-04.案例_地区查询——查询某个省某个城市所有地区（代码示例）

文章目录 代码示例效果 代码示例 axiosTest.html <!DOCTYPE html> <!-- 文档类型声明&#xff0c;告诉浏览器这是一个HTML5文档 --> <html lang"en"> <!-- HTML根元素&#xff0c;设置文档语言为英语 --><head> <!-- 头部区域&am…
阅读更多...
【ESP32】ESP-IDF开发 | 经典蓝牙开发 | 蓝牙串口协议（SPP） + 客户端和服务端例程

【ESP32】ESP-IDF开发 | 经典蓝牙开发 | 蓝牙串口协议（SPP） + 客户端和服务端例程

1. 简介 相信我们最早接触蓝牙&#xff0c;就是在某宝上买一个小巧的蓝牙模块&#xff0c;接到单片机上&#xff0c;通过AT指令进行简单配置&#xff0c;就可以用手机连接该模块&#xff0c;然后远程发送信息给单片机。这里面用到的就是SPP协议&#xff08;Serial Port Protoco…
阅读更多...
【JVM】GC 常见问题

【JVM】GC 常见问题

GC 常见问题 哪些情况新生代会进入老年代 新生代 GC 后幸存区&#xff08;survivor&#xff09;不够存放存活下来的对象&#xff0c;会通过内存担保机制晋升到老年代。大对象直接进入老年代&#xff0c;因为大对象再新生代之间来会复制会影响 GC 性能。由 -XX:PretenureSizeT…
阅读更多...
分享一个sql统计的客户需求

分享一个sql统计的客户需求

分享个最近的一个需求 领导想要知道各个时间段&#xff0c;各个科室&#xff0c;哪些是预出院的患者&#xff0c;哪些是临时出院的患者&#xff0c;在我看来对于系统来说好像没有什么好的界定方式&#xff0c;我就细问下应该如何界定&#xff08;防止查错背锅&#xff09; 在我…
阅读更多...
SpringBoot 集成logback日志链路追踪

SpringBoot 集成logback日志链路追踪

项目场景 有时候一个业务调用链场景&#xff0c;很长&#xff0c;调了各种各样的方法&#xff0c;看日志的时候&#xff0c;各个接口的日志穿插&#xff0c;确实让人头大。 为了解决这个痛点&#xff0c;就使用了 TraceId&#xff0c;根据 TraceId 关键字进入服务器查询日志中…
阅读更多...
【sql靶场】第15、16关-post提交盲注保姆级教程

【sql靶场】第15、16关-post提交盲注保姆级教程

目录 【sql靶场】第15、16关-post提交盲注保姆级教程 1.知识回顾 ‌GET请求‌ ‌POST请求‌ or与and 2.第十五关 1.布尔盲注的手动注入 1.判断 2.数据库名长度 3.数据库名字符 4.表名数 5.表名长度 6.表名符 7.字段数 8.字段长度 9.字段符 2.布尔盲注的脚本注入…
阅读更多...
最新文章

Copyright @ 2022~2023