PG数据库读写分离高可用方案

最新推荐文章于 2024-08-20 15:41:29 发布

码农~三少

最新推荐文章于 2024-08-20 15:41:29 发布

阅读量6k

点赞数 21

分类专栏： postgresql 文章标签： postgresql

本文链接：https://blog.csdn.net/weixin_41352552/article/details/109675027

版权

postgresql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

本文介绍了如何配置PostgreSQL的主从复制，通过读写分离确保数据安全。详细步骤包括设置主从服务器、修改配置文件、启动数据库服务，并验证主从同步。接着，利用Keepalived实现主从数据库的高可用，当主库故障时，能自动切换到从库。最后，展示了Spring Data JPA中动态数据源的配置，允许在Java应用中灵活选择数据源。

摘要由CSDN通过智能技术生成

1、读写分离
1、首先准备两台服务器，2个虚拟机做测试，分别是：
1.主库（master） 192.168.138.129 postgresql 11
2.从库（standby）192.168.138.128 postgresql 11
安装步骤参考 https://www.cnblogs.com/chendian0/p/11232307.html
（默认已经初始化数据库并配置好远程访问）

2、配置主库（192.168.138.129）
vim postgresql.conf（不清楚文件在哪可以使用 find / -name “postgresql.conf” 查找）
在这里插入图片描述
wal_level：主从复制模式
max_wal_senders：需要设置为一个大于0的数，它表示主库最多可以有多少个并发的standby（从）数据库
wal_keep_segments：WAL日志文件个数
vim pg_hba.conf 修改pg_hba.conf文件：

host replication postgres 192.168.138.128/32 md5 （配置从库的ip）
这句话的意思允许从数据库连接主数据库去拖wal日志数据

3、配置从库（192.168.138.128）
执行cd /var/lib/pgsql/11 进入从数据库的data文件夹
执行mv data data_back 备份数据
执行pg_basebackup -h 192.168.138.129 -U postgres -F p -P -R -D /var/lib/pgsql/11/data/
输入主数据库postgres 用户的密码

将主数据库的data目录同步过来，并生成recovery.conf恢复文件
在基础备份时，初始化文件是从主库复制来的，所以配置文件一致，
执行 vim postgresql.conf
注释掉wal_level 、max_wal_senders、wal_keep_segments
在这里插入图片描述

打开如下参数：
在这里插入图片描述
hot_standby = on 在备份的同时允许查询
max_standby_streaming_delay = 30s 可选，流复制最大延迟
wal_receiver_status_interval = 10s 可选，从向主报告状态的最大间隔时间
hot_standby_feedback = on 可选，查询冲突时向主反馈

给主从库配置权限
cd /var/lib/pgsql/11 进入/var/lib/pgsql/11 目录
chown -R postgres:postgres data 给postgres 用户data目录权限
chmod -R 0700 data 修改data目录权限

4、启动主从数据库
systemctl start postgresql-11.service

5、验证主从配置是否成功
systemctl status postgresql-11.service -l
（1）查看主库
在这里插入图片描述
（2）查看从库

(3)在主库中创建表，看看从库是否同步
在这里插入图片描述
(4)从库不可以修改，添加，删除数据

2、使用Keepalived实现PGSQL数据库高可用
1、Keepalived的作用是检测服务器的状态，如果有一台服务器宕机，或工作出现故障，Keepalived将检测到，并将有故障的服务器从系统中剔除，同时使用其它服务器代替该服务器的工作，当服务器工作正常后Keepalived自动将服务器加入到服务器群中，这些工作全部自动完成，不需要人工干涉，需要人工做的只是修复故障的服务器。

2、环境和架构
192.168.138.129主库
192.168.138.128 从库
192.168.11.210 VIP （虚拟IP）

在这里插入图片描述

注意：以下操作主从服务器一致
3、使用yum 源安装Keepalived
执行 yum install -y Keepalived 安装

4、修改keepalived.conf 配置文件,
执行 cd /ect/keepalived 进入/ect/keepalived 目录
修改keepalived.conf 权限，执行chomd 644 keepalived.conf
执行vim /ect/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
router_id PGSQL-HA #此处注意router_id为负载均衡标识，在局域网内应该是唯一的。
#notification_email { #指定keepalived在发生切换时需要发送email到的对象，一行一个
# qq@qq.com #指定收件人邮箱
#}
#notification_email_from Alexandre.Cassen@firewall.loc #指定发件人
#smtp_server 192.168.200.1 #指定smtp服务器地址
#smtp_connect_timeout 30 #指定smtp连接超时时间

}
vrrp_script check_pg_alived {# 检查pg数据库是否运行
script “/etc/keepalived/scripts/check_pg.sh”
interval 5 # 设置脚本执行的时间间隔，这里为每5秒执行一次
fall 3 # 3次失败认为数据库不在运行，会发送邮件到指定发件人

}

vrrp_instance VI_1 {
state BACKUP #状态只有MASTER和BACKUP
interface ens33 #通信所使用的网络接口
virtual_router_id 61 #虚拟路由的ID号
priority 100 #此节点的优先级，主节点的优先级需要比其他节点高
nopreempt #设置为不抢占注：这个配置只能设置在backup主机上，而且这个主机优先级要比另外一台高
advert_int 1 #通告的间隔时间
authentication { #认证配置
auth_type PASS
auth_pass 1234
}

track_script {
    check_pg_alived # 配置业务进程监控脚本
}

virtual_ipaddress { # 虚拟ip
    192.168.138.111
}

}

5、创建文件夹scripts, log
执行mkdir scripts ,mkdir log, vim check_pg.sh
赋予运行权限 chomd 755 check_pg.sh
check_pg.sh脚本 – 判断pg是否还运行
#!/bin/bash

export PGDATABASE=postgres
export PGPORT=5432
export PGUSER=postgres
export PGPW=postgres
export PGHOME=/usr/pgsql-11/bin
export PATH= $P A T H :$ PGHOME
export PGMIP=127.0.0.1
LOGFILE=/etc/keepalived/log/pg_status.log # 日志文件

SQL2=‘update sr_delay set sr_date = now() where id =1;’# 时间

SQL1=‘SELECT pg_is_in_recovery from pg_is_in_recovery();’# 判断数据库是主还是备 f 为主

SQL3=‘SELECT 1;’# 判断pg数据库是否运行

db_role=echo $SQL1 |PGPASSWORD=$PGPW $PGHOME/psql -d $PGDATABASE -U $PGUSER -At

if [ $db_role = ‘t’ ];then
echo -e date +"%F %T" “Attention1:the current database is standby DB!” >> $LOGFILE
exit 0
fi

备库不检查存活,主库更新状态

echo $S Q L 3 ∣ P G P A S S W O R D =$ PGPW psql -p $PGPORT -d $PGDATABASE -U $PGUSER -At

if [ $? -eq 0 ] ;then
echo $SQL2 | psql -p $PGPORT -d $PGDATABASE -U $PGUSER -At -w
echo -e date +"%F %T" “Success: update the master sr_delay successed!” >> $LOGFILE
exit 0
else
echo -e date +"%F %T" “Error:Is the server is running?” >> $LOGFILE
exit 1
fi

6、运行keepalived
执行systemctl start keepalived.service 启动程序，
执行systemctl enable keepalived.service 开机启动
执行systemctl status keepalived.service -l查看状态
在这里插入图片描述
注意：这里只是对数据库连接做负载，keepalived的简单使用，若想深入了解keepalived，还得keepalived论坛深造。

3、Spring Data JPA动态数据源的使用
在这里插入图片描述
2、数据源配置文件：application.properties。可以支持不同数据库类型例如mysql和pgsql

spring.datasource.master.url=jdbc:postgresql://192.168.138.129:5432/postgres
spring.datasource.master.username=postgres
spring.datasource.master.password=postgres
spring.datasource.master.driver-class-name=org.postgresql.Driver

#spring.datasource.node.url=jdbc:postgresql://192.168.138.128:5432/postgres
#spring.datasource.node.url=jdbc:postgresql://127.0.0.1:5432/postgres
#spring.datasource.node.username=postgres
#spring.datasource.node.password=postgres
#spring.datasource.node.driver-class-name=org.postgresql.Driver

spring.datasource.node.url=jdbc:mysql://localhost:3306/ssy?characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC
spring.datasource.node.username=root
spring.datasource.node.password=123456
spring.datasource.node.driver-class-name=com.mysql.cj.jdbc.Driver

3、创建DynamicDataSource动态数据源并继承AbstractRoutingDataSource，重写determineCurrentLookupKey方法。

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * @Description 动态数据源
 * AbstractRoutingDataSource(每执行一次数据库，动态获取DataSource)
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.getDataSourceType();
    }
}

查看AbstractRoutingDataSource源码得知 determineCurrentLookupKey方法是决定使用什么数据源
在这里插入图片描述
4、DataSourceConfig 创建多个数据源
（1）创建多个数据源
（2）将不同的数据源放入DynamicDataSource动态数据源类中

package com.redis.temp.kaiwen.conf;

import com.alibaba.druid.pool.DruidDataSource;
import com.redis.temp.kaiwen.jpa.DynamicDataSource;
import com.redis.temp.kaiwen.jpa.DynamicDataSourceContextHolder;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * @author: liangjinyin
 * @Date: 2020-11-09
 * @Description:
 */
@Configuration
public class DataSourceConfig {

    //master
    @Value("${spring.datasource.master.driver-class-name}")
    private String masterClassName;

    @Value("${spring.datasource.master.username}")
    private String masterUserName;

    @Value("${spring.datasource.master.password}")
    private String masterPassword;

    @Value("${spring.datasource.master.url}")
    private String masterUrl;

    //node
    @Value("${spring.datasource.node.driver-class-name}")
    private String nodeClassName;

    @Value("${spring.datasource.node.username}")
    private String nodeUserName;

    @Value("${spring.datasource.node.password}")
    private String nodePassword;

    @Value("${spring.datasource.node.url}")
    private String nodeUrl;

    @Bean(name = "masterDataSource")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().driverClassName(masterClassName)
                .password(masterPassword).url(masterUrl)
                .username(masterUserName).type(DruidDataSource.class).build();
    }

    @Bean(name = "nodeDataSource")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().driverClassName(nodeClassName)
                .password(nodePassword).url(nodeUrl)
                .username(nodeUserName).build();
    }

    @Bean(name = "multipleDataSource")
    @Primary
    public DataSource MultipleDataSourceToChoose() {
        DataSource master = DataSourceBuilder.create().driverClassName(masterClassName)
                .password(masterPassword).url(masterUrl)
                .username(masterUserName).type(DruidDataSource.class).build();


        DataSource node = DataSourceBuilder.create().driverClassName(nodeClassName)
                .password(nodePassword).url(nodeUrl)
                .username(nodeUserName).type(DruidDataSource.class).build();

        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put("masterDataSource", master);
        targetDataSources.put("nodeDataSource", node);
        dynamicDataSource.setTargetDataSources(targetDataSources);
        dynamicDataSource.setDefaultTargetDataSource(master);
        DynamicDataSourceContextHolder.saveDataSourceTypeName("masterDataSource");
        DynamicDataSourceContextHolder.saveDataSourceTypeName("nodeDataSource");
        return dynamicDataSource;
    }

}

5、创建存储数据源信息的类

public class DynamicDataSourceContextHolder {
    /**
     * 当使用ThreadLocal维护变量时，ThreadLocal为每个使用该变量的线程提供独立的变量副本，
     * 所以每一个线程都可以独立地改变自己的副本，而不会影响其它线程所对应的副本。
     */
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
    /**
     * 使用setDataSourceType设置当前的
     * @param dataSourceType dataSourceType
     */
    public static void setDataSourceType(String dataSourceType) {
        contextHolder.set(dataSourceType);
    }
    /**
     * 获取数据源类型 默认使用主数据源
     * @return 数据源
     */
    public static String getDataSourceType() {
        return contextHolder.get() == null ? "masterDataSource" : contextHolder.get();
    }
    /**
     * 清除数据源
     */
    public static void clearDataSourceType() {
        contextHolder.remove();
    }


    public static void saveDataSourceTypeName(String name) {
        dataSourceList.add(name);
    }

    public static boolean checkDataSourceType(String name) {
        return dataSourceList.contains(name);
    }

    /**
     * 校验输入的数据库名称是否正确
     */
    private static List<String> dataSourceList = new ArrayList<>();
}

5、自定义注解默认使用主数据源，并利用aop实现动态数据源切换。

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD})
public @interface ChangDataSource {

    String name() default "masterDataSource";
}

AOP切面

@Aspect
@Order(-10)//保证该AOP在@Transactional之前执行
@Component
@Slf4j
public class DynamicDataSourceAspect {


    @Before(value = "@annotation(source)")
    public void changeDataSource(JoinPoint point, ChangDataSource source) throws Exception {
        String name=source.name();
        if(!DynamicDataSourceContextHolder.checkDataSourceType(name)){
            throw new Exception("没有该数据源！");
        }
        DynamicDataSourceContextHolder.setDataSourceType(name);
    }

    @AfterReturning(value = "@annotation(source)")
    public void restoreDataSource(JoinPoint point,ChangDataSource source) {
        //方法执行完毕之后，销毁当前数据源信息，进行垃圾回收。
        DynamicDataSourceContextHolder.clearDataSourceType();
    }

}

6、Java中的应用
（1）实体类

@Entity
@Data
@Table(name = "test_user")
public class User {
    @Id
    @GenericGenerator(name = "gid", strategy = "uuid")
    @GeneratedValue(generator = "id")
    @Column(name = "gid", length = 36, nullable = false, unique = true)
    private String id;
    @Column(name = "name", length = 50)
    private String name;
    @Column(name = "age")
    private int age;
}

（2）DAO层

@Repository
public interface TestDao extends JpaRepository<User, String>, JpaSpecificationExecutor<User> {


}

（3）数据库表
在这里插入图片描述
（3）调用，在方法上加上自定义注解，写上要操作的数据源名称，默认是主数据源可以不写

@RestController
@RequestMapping("/jpa")
@Slf4j
public class JpaTestController {

    @Resource
    private TestDao testDao;

    @Resource
    private Test1Dao test1Dao;

    @GetMapping("/aa")
    @ChangDataSource(name = "nodeDataSource")
    public String getTest() {
        List<User> all = testDao.findAll();
        return all.toString();
    }

参考链接
Postgresql实现主从复制，读写分离
 使用Keepalived实现MySQL主从高可用
 spring boot jpa动态切换数据库