docker容器上搭建postgresql流复制

docker容器上搭建postgresql流复制

实验环境

  • 主机宿主机:

    IP: 192.168.167.129
    操作系统: CentOS Linux release 7.3.1611 (Core)

  • 从机宿主机:

    IP: 192.168.167.130
    操作系统: CentOS Linux release 7.3.1611 (Core)

配置宿主机环境

在主从机器上都要执行:

关闭防火墙:

[root@localhost data]# systemctl stop firewalld.service 
[root@localhost data]# systemctl disable firewalld.service 
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
Removed symlink /etc/systemd/system/basic.target.wants/firewalld.service.

关闭SELINUX

[root@localhost ~]# vim /etc/selinux/config

SELINUX=disabled

需要重启机器

安装并启动docker

[root@localhost ~]# yum install docker -y

[root@localhost ~]# systemctl start docker
[root@localhost ~]# systemctl enable docker

创建容器相关文件夹

[root@localhost ~]# mkdir -p /backup/postgresql
[root@localhost ~]# mkdir -p /storage/docker/postgresql
[root@localhost ~]# mkdir /docker

配置基础版容器

在主宿主机:

下载ubuntu镜像

[root@localhost ~]# docker pull ubuntu

启动并进入容器

[root@localhost ~]# docker run -it --name build_pg_img \
> -v /storage/docker/postgresql:/var/lib/postgresql \
> -v /backup/postgresql:/backup \
> ubuntu \
> /bin/bash
root@3f0917a3328c:/# 

查看容器状态

退出容器并查看容器状态:

root@3f0917a3328c:/# exit

[root@localhost ~]# docker ps -a
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS                       PORTS               NAMES
3f0917a3328c        ubuntu              "/bin/bash"         5 minutes ago       Exited (130) 2 minutes ago                       build_pg_img

启动容器:

[root@localhost ~]# docker start build_pg_img
build_pg_img

验证容器状态并进入容器

[root@localhost ~]# docker ps -a
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS               NAMES
3f0917a3328c        ubuntu              "/bin/bash"         5 minutes ago       Up 3 seconds                            build_pg_img
[root@localhost ~]# docker exec -it build_pg_img /bin/bash
root@3f0917a3328c:/#

安装postgresql

更新容器包

root@3f0917a3328c:/# apt-get update -y
root@3f0917a3328c:/# apt-get install vim -y

安装lsb-release插件

root@3f0917a3328c:/# apt-get install lsb-release

修改pgdg.list文件

root@3f0917a3328c:/# vim /etc/apt/sources.list.d/pgdg.list

写入:

deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main

获取安装地址:

root@3f0917a3328c:/# sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

执行环境安装:

root@3f0917a3328c:/# apt-get install wget ca-certificates

root@3f0917a3328c:/# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -

再次更新容器包:

root@3f0917a3328c:/# apt-get update -y
root@3f0917a3328c:/# apt-get upgrade -y

安装postgresql包:

root@3f0917a3328c:/# apt-get install postgresql -y

确认postgresql安装状态:

root@3f0917a3328c:/# dpkg -l |grep post
ii  postgresql                 10+190                            all          object-relational SQL database (supported version)
ii  postgresql-10              10.3-1                            amd64        object-relational SQL database, version 10 server
ii  postgresql-client-10       10.3-1                            amd64        front-end programs for PostgreSQL 10
ii  postgresql-client-common   190                               all          manager for multiple PostgreSQL client versions
ii  postgresql-common          190                               all          PostgreSQL database-cluster manager

配置环境变量

创建.bash_profile文件

root@3f0917a3328c:/# su - postgres
postgres@3f0917a3328c:~$ pwd               
/var/lib/postgresql
postgres@3f0917a3328c:~$ vim .bash_profile

写入

#!/bin/bash    
PGHOME=/usr/lib/postgresql/10    
export PGHOME    
export PGPORT=5432    
export PGDATA=/var/lib/postgresql/data    
PATH=$PGHOME/bin:$PATH:$HOME/bin    
export PATH    
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGHOME/lib    
export LD_LIBRARY_PATH

使配置生效:

postgres@3f0917a3328c:~$ source .bash_profile

配置postgresql数据库

创建自己的数据存储空间data:

postgres@3f0917a3328c:~$ cd /var/lib/postgresql/   
postgres@3f0917a3328c:~$ ls
10
postgres@3f0917a3328c:~$ mv 10 10_bak

postgres@3f0917a3328c:~$ mkdir data

初始化数据库:

postgres@3f0917a3328c:~$ /usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/data

postgres@3f0917a3328c:~$ cd /var/lib/postgresql/data

postgres@3f0917a3328c:~/data$ mkdir pg_log pg_archlog

编辑postgresql.conf文件:

postgres@3f0917a3328c:~$ vim $PGDATA/postgresql.conf

修改:

listen_addresses = '*'                                            
port = 5432                           
max_connections = 1000

shared_buffers = 1280MB
wal_level = hot_standby
max_wal_senders = 5

hot_standby = on

编辑pg_hba.conf文件

postgres@3f0917a3328c:~$ vim $PGDATA/pg_hba.conf

加入:

host all all 0.0.0.0 0.0.0.0 trust
host replication all 0.0.0.0 0.0.0.0 trust

启动数据库并打上补丁:

postgres@3f0917a3328c:~$ /usr/lib/postgresql/10/bin/pg_ctl start -D /var/lib/postgresql/data
postgres@3f0917a3328c:~$ psql
psql (10.3 (Ubuntu 10.3-1))
Type "help" for help.

postgres=# create extension pg_stat_statements;
CREATE EXTENSION

退出容器:

postgres-# \q
postgres@3f0917a3328c:~$ /usr/lib/postgresql/10/bin/pg_ctl stop -D /var/lib/postgresql/data -m fast
postgres@3f0917a3328c:~$ exit
logout
root@3f0917a3328c:/# exit
exit

[root@localhost ~]# 

创建模板镜像包

查看当前容器状态:

[root@localhost ~]# docker ps -a |grep build_pg_img
3f0917a3328c        ubuntu              "/bin/bash"         55 minutes ago      Up 50 minutes                           build_pg_img

创建镜相包:

[root@localhost docker]# docker commit build_pg_img pg10_template

创建dockerfile:

[root@localhost ~]# cd /docker
[root@localhost docker]# vim dockerfile

写入:

FROM  pg10_template    
USER postgres    
ENV PGHOME /usr/lib/postgresql/10    
ENV PGPORT 5432    
ENV PGDATA /var/lib/postgresql/data    
ENV PATH $PGHOME/bin:$PATH    
ENV LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH    
EXPOSE 5432    
CMD ["/usr/lib/postgresql/10/bin/postgres", "-D", "/var/lib/postgresql/data"]

创建模板镜像包:

[root@localhost docker]# docker commit build_pg_img pg10_template

确认镜像包状态:

[root@localhost docker]# docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
eg_postgresql       latest              005c41338f69        25 seconds ago      272 MB
pg10_template       latest              4cee38ccccdf        4 minutes ago       272 MB
docker.io/ubuntu    latest              452a96d81c30        4 weeks ago         79.6 MB

配置流复制

主宿主机端:

创建pg_pri容器:

[root@localhost docker]# docker run --name pg_pri \
> -v /storage/docker/postgresql:/var/lib/postgresql \
> -v /backup/postgresql:/backup \
> -p 5436:5432 \
> -d eg_postgresql
36e0ffa8837ebe27637e2a949aa8d977454ecfd0204b416682ac3208135a901e

进入容器并查看状态:

[root@localhost docker]# docker exec -it pg_pri /bin/bash 
postgres@36e0ffa8837e:/$  ps -ef|grep post 
postgres      1      0  0 16:45 ?        00:00:00 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/data
postgres      6      1  0 16:45 ?        00:00:00 postgres: checkpointer process   
postgres      7      1  0 16:45 ?        00:00:00 postgres: writer process   
postgres      8      1  0 16:45 ?        00:00:00 postgres: wal writer process   
postgres      9      1  0 16:45 ?        00:00:00 postgres: autovacuum launcher process   
postgres     10      1  0 16:45 ?        00:00:00 postgres: stats collector process   
postgres     11      1  0 16:45 ?        00:00:00 postgres: bgworker: logical replication launcher   
postgres     12      0  0 16:45 ?        00:00:00 /bin/bash
postgres     19     12  0 16:46 ?        00:00:00 ps -ef
postgres     20     12  0 16:46 ?        00:00:00 grep post

postgres@36e0ffa8837e:/$ psql
psql (10.3 (Ubuntu 10.3-1))
Type "help" for help.

postgres=#exit

打包镜像:

[root@localhost docker]# docker save -o eg_postgresql.tar eg_postgresql

[root@localhost docker]# ls
dockerfile  eg_postgresql.tar

将镜相包发送至从宿主机:

[root@localhost docker]# scp eg_postgresql.tar root@192.168.167.130:/docker
root@192.168.167.130's password: 
eg_postgresql.tar                                                             100%  268MB  12.2MB/s   00:22  

从机端:

进入指定路径,解压镜像:

[root@localhost ~]# cd /docker
[root@localhost docker]# ls
eg_postgresql.tar
[root@localhost docker]# docker load -i eg_postgresql.tar
65bdd50ee76a: Loading layer [==================================================>] 82.09 MB/82.09 MB
ec75999a0cb1: Loading layer [==================================================>] 15.87 kB/15.87 kB
67885e448177: Loading layer [==================================================>] 8.192 kB/8.192 kB
8db5f072feec: Loading layer [==================================================>] 5.632 kB/5.632 kB
059ad60bcacf: Loading layer [==================================================>] 3.072 kB/3.072 kB
d5aa63428b37: Loading layer [==================================================>] 198.6 MB/198.6 MB
Loaded image: eg_postgresql:latest

主机端:

进入容器,并开启归档模式:

[root@localhost docker]# docker exec -it pg_pri /bin/bash
postgres@36e0ffa8837e:/$ psql                                     
psql (10.3 (Ubuntu 10.3-1))
Type "help" for help.

postgres=# select pg_start_backup('Replition work');
 pg_start_backup 
-----------------
 0/2000060
(1 row)

推出容器,并备份数据文件:

postgres=# \q
postgres@36e0ffa8837e:/$ exit
exit
[root@localhost docker]# cd /storage/docker/postgresql/
[root@localhost postgresql]# ls
10_bak  data
[root@localhost postgresql]# tar cvf data.tar data
[root@localhost postgresql]# ls
10_bak  data  data.tar

将备份的数据文件传输至从宿主机:

[root@localhost postgresql]# scp data.tar root@192.168.167.130:/storage/docker/postgresql
root@192.168.167.130's password: 
data.tar                                                                      100%   55MB  55.2MB/s   00:00

从库端:

解压收到的数据备份:

[root@localhost docker]# cd /storage/docker/postgresql/
[root@localhost postgresql]# ls
data.tar
[root@localhost postgresql]# tar xvf data.tar

删除pid:

[root@localhost postgresql]# rm ./data/postmaster.pid 
rm: remove regular file ‘./data/postmaster.pid’? y

配置recovery.conf

[root@localhost postgresql]# vim ./data/recovery.conf

写入:

standby_mode = 'on'
primary_conninfo = 'host=192.168.167.129 port=5436'
trigger_file = '/var/lib/postgresql/trigger_file'

启动从库容器:

[root@localhost postgresql]# docker run --name pg_slv \
> -v /storage/docker/postgresql:/var/lib/postgresql \
> -v /backup/postgresql:/backup \
> -p 5436:5432 \
> -d eg_postgresql

主库端:

进入容器,结束备份模式:

[root@localhost postgresql]# docker exec -it pg_pri /bin/bash
postgres@36e0ffa8837e:/$ psql
psql (10.3 (Ubuntu 10.3-1))
Type "help" for help.

postgres=# select pg_stop_backup(), current_timestamp;
NOTICE:  WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup
 pg_stop_backup |       current_timestamp       
----------------+-------------------------------
 0/2000168      | 2018-06-01 10:39:52.827137+08
(1 row)

从库端:

进入容器:

[root@localhost data]# docker exec -it pg_slv /bin/bash

postgres@7736d94d8734:/$ ps -ef|grep post 
postgres      1      0  0 11:03 ?        00:00:00 /usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/data
postgres      5      1  0 11:03 ?        00:00:00 postgres: startup process   recovering 000000010000000000000005
postgres      6      1  0 11:03 ?        00:00:00 postgres: checkpointer process   
postgres      7      1  0 11:03 ?        00:00:00 postgres: writer process   
postgres      9      1  0 11:03 ?        00:00:00 postgres: stats collector process   
postgres    115      1  0 14:52 ?        00:00:00 postgres: wal receiver process   
postgres    116      0  0 14:54 ?        00:00:00 /bin/bash
postgres    122    116  0 14:54 ?        00:00:00 ps -ef
postgres    123    116  0 14:54 ?        00:00:00 grep post


postgres@7736d94d8734:/$ psql
psql (10.3 (Ubuntu 10.3-1))
Type "help" for help.

postgres=#

测试

主库端:

postgres=# create table t(id int);
CREATE TABLE

从库端:

postgres=# select * from t;
 id 
----
(0 rows)
阅读更多

扫码向博主提问

aladdin_sun

战个痛
去开通我的Chat快问
个人分类: PostgreSQL
上一篇组合索引位置的使用测试
下一篇postgresql树形结构层次化叠加查询
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭