Postgresql数据库主从备份教程


数据库主从热备

数据库安装

1.安装yum源

    打开https://yum.postgresql.org/repopackages.php ,找到自己需要的版本,右键复制链接地址。


    不放心是否复制成功的话可以粘贴出来看看。

    以root模式进入CentOS7,输入:yum install + 刚刚复制的链接

    回车。


    输入:y,回车。


2.安装PostgreSQL

    输入:yum install -y postgresql94-server postgresql94-contrib

    回车。(如果使用其他版本的PostgreSQL则需要把其中的两个94换成对应的数字)

    输入y,回车。


    输入:/usr/pgsql-9.4/bin/postgresql94-setup initdb

    回车,初始化数据库。(如果使用其他版本的PostgreSQL则需要把其中的9.4和94换成对应的数字)


    (如果未安装firewalld防火墙可跳过下面两步)

    输入:firewall-cmd --add-service=postgresql --permanent

    回车,开放防火墙。

    输入:firewall-cmd –reload

    回车,重启防火墙。

3. 修改默认PostgreSQL用户密码

    PostgreSQL安装后会创建一个用户,名为postgres。

    输入:su – postgres

    回车,切换至用户。

    输入:psql -U postgres

    回车,登录数据库。

    输入:ALTER USER postgres with encrypted password ‘abc123’;

    (不要漏了“;”)回车,设置默认用户postgre的密码,此处密码为abc123,可自行修改。

    输入:\q

    回车, 退出数据库。

    输入:exit

    回车,退出用户。


4. 配置远程访问

    输入:vi /var/lib/pgsql/9.4/data/postgresql.conf

    回车,进入文档内容。(如果使用其他版本的PostgreSQL则需要把其中的9.4换成对应的版本)

    光标下翻,找到listen_addresses。


    按 i 键进入“插入编辑模式”,去掉“listen_address”前面的 #,使该配置参数起效

    如果想对所有IP开放,则将 localhost 改为 * 即可

    如果想仅对部分IP开放,多个IP之间用 , (逗号+空格)隔开。


    编辑完成后,按Esc键,输入::wq

    回车,“保存退出”文档。

    输入:vi /var/lib/pgsql/9.4/data/pg_hba.conf

    回车,进入文档内容。(如果使用其他版本的PostgreSQL则需要把其中的9.4换成对应的版本)

    将光标移至底部。


    按 i 键进入“插入编辑模式”,在IPv4 local connections下方添加允许连接的IP。

    如果想允许所有IPv4地址,则加入一行:host all all 0.0.0.0/0 md5

    IPv6方法类似。


    编辑完成后,按Esc键,输入::wq

    回车,“保存退出”文档。

    输入:systemctl restart postgresql-9.4.service

    回车,重启postgressql服务。(如果使用其他版本的PostgreSQL则需要把其中的9.4换成对应的   版    本)

    此时postgreSQL数据库安装成功,用可视化工具连接一下吧。


PostgreSQL主从流复制部署

172.16.125.201 部署主服务器

 172.16.125.200 部署从服务器

主服务器配置

    1.首先需要创建一个数据库用户进行主从同步。创建用户replica,并赋予登录和复制的权限。

    输入:su – postgres

    回车,切换至用户。

    输入:psql -U postgres

    回车,登录数据库。

    输入:CREATE ROLE replica login replication encrypted password ‘replica’;

    2.修改pg_hba.conf,允许replica用户来同步。

    输入:vi /var/lib/pgsql/9.4/data/pg_hba.conf

    回车,进入文档内容。

    按 i 键进入“插入编辑模式”,在IPv4 local connections下方增加两行配置。

    允许200连接到主服务器:

    host all all 172.16.125.200/32 trust

    允许200使用replica用户来复制:

    host replication replica 172.16.125.200/32 md5

    如果不明白,技术文档支持


编辑完成后,按Esc键,输入::wq

回车,“保存退出”文档。

3.修改postgresql.conf

    输入:vi /var/lib/pgsql/9.4/data/postgresql.conf

    回车,进入文档内容。

    按 i 键进入“插入编辑模式”,修改以下配置。

    监听所有IP:

    listen_addresses = ‘*’

    允许归档:

    archive_mode = on

    用该命令来归档logfile segment:

    archive_command = ‘cp %p /opt/pgsql/pg_archive/%f’

    wal_level = hot_standby

    这个设置了可以最多有几个流复制连接,差不多有几个从,就设置几个:

    max_wal_senders = 2

    设置流复制保留的最多的xlog数目:

    wal_keep_segments = 256

    设置流复制主机发送数据的超时时间:

    wal_sender_timeout = 60s

    这个设置要注意下,从库的max_connections必须要大于主库的:

    max_connections = 100


    编辑完成后,按Esc键,输入::wq

    回车,“保存退出”文档。

    输入:systemctl restart postgresql-9.4.service

    回车,重启postgressql服务。

    4.测试201能否连接200数据库。在201上运行如下命令:

    输入:psql -h 172.16.125.200 -U postgres

    回车,然后输入200数据库的密码

    回车,连接上200数据库


从服务器配置

1.从主节点拷贝数据到从节点

    输入:su - postgres

    回车,切换至用户

    输入:rm -rf /var/lib/pgsql/9.4/data/*

    回车,将data目录下的数据都清空

    输入:pg_basebackup -h 172.16.125.201 -U replica –D /var/lib/pgsql/9.4/data -X stream -P

    回车,从201拷贝数据到201(基础备份)


    输入:mkdir /opt/pgsql

    回车,创建pgsql文件夹

    输入:mkdir /opt/pgsql/pg_archive

    回车,创建pg_archive文件夹


    2.配置recovery.conf

    输入:

    cp /usr/pgsql-9.4/share/recovery.conf.sample /var/lib/pgsql/9.4/data/recovery.conf

    回车,复制recovery.conf


    输入:vi /var/lib/pgsql/9.4/data/recovery.conf

    回车,进入文档内容。

    按 i 键进入“插入编辑模式”,修改以下配置。

    表明该节点是从服务器:

    standby_mode = on

    主服务器的信息以及连接的用户:

    primary_conninfo = ‘host=172.16.125.201 port=5432 user=replica password=replica’

    recovery_target_timeline = ‘latest’

    编辑完成后,按Esc键,输入::wq

    回车,“保存退出”文档。

    3.配置postgresql.conf

    输入:vi /var/lib/pgsql/9.4/data/postgresql.conf

    回车,进入文档内容。

    按 i 键进入“插入编辑模式”,修改以下配置。

    wal_level = hot_standby

    一般查多于写的应用从库的最大连接数要比较大:

    max_connections = 1000

    说明这台机器不仅仅是用于数据归档,也用于数据查询:

    hot_standby = on

    数据流备份的最大延迟时间:

    max_standby_streaming_delay = 30s

    多久向主报告一次从的状态,设置最长的间隔时间:

    wal_receiver_status_interval = 10s

    如果有错误的数据复制,是否向主进行反馈:

    hot_standby_feedback = on


    编辑完成后,按Esc键,输入::wq

    回车,“保存退出”文档。

    输入:systemctl restart postgresql-9.4.service

    回车,重启postgressql服务。

    至此,PostgreSQL主从流复制安装部署完成,用可视化工具测试一下吧。

    在主服务器(201)上插入数据或删除数据,

    在从服务器(200)上能看到相应的变化。

    从服务器(200)上只能查询,不能插入或删除。


推荐阅读更多精彩内容