docker方式部署postgresql

1 安装docker

1.1 通过二进制包安装docker

到官网下载自己需要安装包

wget https://download.docker.com/linux/static/stable/x86_64/docker-19.03.4.tgz

1.2 将安装包解压到/home/docker目录下

chmod 777 /home/docker/docker-19.03.4/*
cp /home/docker/docker-19.03.4/* /usr/local/bin/

1.3 将docker加入到系统服务

生成docker.service的文件并设定到/usr/lib/systemd/system目录下

cat > /usr/lib/systemd/system/docker.service <<"EOF"
[Unit]
Description=Docker Application Container Engine
Documentation=http://docs.docker.io
[Service]
Environment="PATH=/usr/local/bin:/bin:/sbin:/usr/bin:/usr/sbin"
EnvironmentFile=-/run/flannel/docker
ExecStart=/usr/local/bin/dockerd --log-level=error $DOCKER_NETWORK_OPTIONS
ExecReload=/bin/kill -s HUP $MAINPID
Restart=on-failure
RestartSec=5
LimitNOFILE=infinity
LimitNPROC=infinity
LimitCORE=infinity
Delegate=yes
KillMode=process

[Install]
WantedBy=multi-user.target
EOF

1.4 重启docker

systemctl restart docker

2 docker 上部署postgresql

2.1制作postgresql的镜像

创建Dockerfile等制作镜像脚本的目录

mkdir /home/postgresql10_docker
cd /home/postgresql10_docker

创建Dockerfile

vim Dockerfile

Dockerfile 内容如下

FROM centos:7.6.1810

ENV PGDATA=/var/lib/pgsql/10/data
ENV PGHOME=/usr/pgsql-10/ 
ENV LANG en_US.UTF-8

RUN  set -eux; \
    echo "export LANG=en_US.utf8" >> /root/.bash_profile \
    && yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm -y  \
    && yum install postgresql10-contrib postgresql10-server postgresql10-devel.x86_64 -y \
    && echo "export PATH=/usr/pgsql-10/bin:$PATH" >> /etc/profile.d/pg_env.sh \
    && echo "export LD_LIBRARY_PATH=$PGHOME/lib" >> /etc/profile.d/pg_env.sh \
    && echo "export PGDATA=$PGDATA"  >> /etc/profile.d/pg_env.sh \
    && echo "export PGHOME=$PGHOME"  >> /etc/profile.d/pg_env.sh \
    && echo "export LANG=en_US.utf8" >> /etc/profile.d/pg_env.sh \
    && chmod 777 /etc/profile.d/pg_env.sh \
    && echo "source /etc/profile.d/pg_env.sh" >> /var/lib/pgsql/.bash_profile \
    && /etc/profile.d/pg_env.sh \
    && su - postgres -c "initdb -E utf8" \
    #&& systemctl enable postgresql-10.service \
    && yum  install wget net-tools epel-release -y \
    && yum install postgis25_10 postgis25_10-client -y \
    && yum install ogr_fdw10 -y \
    && yum install pgrouting_10 -y \
    && yum install osm2pgrouting_10 -y \
    && yum install gcc -y \
    && yum install make -y \
    && yum install git -y \
    && git clone git://git.postgresql.org/git/pldebugger.git \
    && mv pldebugger /usr/pgsql-10/share/contrib \
    && yum install openssl-devel -y \
    && yum install postgresql10-devel.x86_64 -y \
    && cd /usr/pgsql-10/share/contrib/pldebugger \
    && source /etc/profile.d/pg_env.sh \
    && USE_PGXS=1 make clean \
    && USE_PGXS=1 make  \
    && USE_PGXS=1 make install \
    && sed -i s/"#shared_preload_libraries = ''"/"shared_preload_libraries ='\/usr\/pgsql-10\/lib\/plugin_debugger.so'"/g $PGDATA/postgresql.conf \
    && yum install pgagent_10.x86_64 -y \
    && su - postgres -c "pg_ctl start" && psql -U postgres -d postgres -f /usr/share/pgagent_10-4.0.0/pgagent.sql \
    && yum install lbzip2.x86_64 -y \
    && cd /home \
    && wget -P /home http://www.xunsearch.com/scws/down/scws-1.2.3.tar.bz2 \
    && git clone https://github.com/amutu/zhparser.git \
    && tar -xf scws-1.2.3.tar.bz2 \
    && cd scws-1.2.3 && ./configure && make install \
    && export SCWS_HOME=/usr/local \
    && cd /home/zhparser &&  PG_CONFIG=/usr/pgsql-10/bin/pg_config make && make install \
    #&& yum -y install perl perl-devel \
    #&& yum install -y php php-devel \
    #&& yum install -y httpd \
    #&& yum -y install httpd-devel \
    #&& yum -y install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker \
    #&& yum -y install perl-CPAN \
    #&& cpan Text::CSV \
    #&& cpan JSON::XS \
    #&& wget -P /home/ https://github.com/darold/pgbadger/archive/v10.3.tar.gz \
    #&& cd /home/ && tar -xvf v10.3.tar.gz \
    #&& cd pgbadger-10.3 \
    #&& perl Makefile.PL \
    #&& make && make install \
    && yum install -y cmake3 && ln -s /usr/bin/cmake3 /usr/bin/cmake    \
    && wget -P /home/ https://github.com/timescale/timescaledb/archive/1.3.0.tar.gz \
    && cd /home/ && tar -xvf 1.3.0.tar.gz && cd timescaledb-1.3.0 \
    && ./bootstrap \
    && cd ./build && make \
    && make install \
    && sed -i s/"shared_preload_libraries ='\/usr\/pgsql-10\/lib\/plugin_debugger.so'"/"shared_preload_libraries ='\/usr\/pgsql-10\/lib\/plugin_debugger.so,timescaledb'"/g $PGDATA/postgresql.conf \
    &&  wget -P /home https://github.com/pipelinedb/pipelinedb/releases/download/1.0.0rev4/pipelinedb-postgresql-10-1.0.0-4.centos7.x86_64.rpm \
    && cd /home \
    && rpm -ivh pipelinedb-postgresql-10-1.0.0-4.centos7.x86_64.rpm \
    && sed -i s/"shared_preload_libraries ='\/usr\/pgsql-10\/lib\/plugin_debugger.so,timescaledb'"/"shared_preload_libraries ='\/usr\/pgsql-10\/lib\/plugin_debugger.so,timescaledb,pipelinedb'"/g $PGDATA/postgresql.conf  \
    && wget -P /home https://yum.postgresql.org/10/redhat/rhel-7-x86_64/citus_10-9.0.1-1.rhel7.x86_64.rpm \
    && cd /home \
    && rpm -ivh citus_10-9.0.1-1.rhel7.x86_64.rpm \
    && sed -i s/"shared_preload_libraries ='\/usr\/pgsql-10\/lib\/plugin_debugger.so,timescaledb,pipelinedb'"/"shared_preload_libraries ='citus,\/usr\/pgsql-10\/lib\/plugin_debugger.so,timescaledb,pipelinedb'"/g $PGDATA/postgresql.conf


COPY docker-entrypoint.sh /usr/local/bin/
RUN chmod -R 777 /usr/local/bin/docker-entrypoint.sh
RUN ln -s /usr/local/bin/docker-entrypoint.sh /
ENTRYPOINT ["docker-entrypoint.sh"]

VOLUME $PGDATA

EXPOSE 5432

CMD ["postgres"]

创建docker-entrypoint.sh

cd /home/postgresql10_docker
vim docker-entrypoint.sh

内容如下：

#!/bin/bash

if [ "$1" = "postgres" ];then
        echo "kernel.sem = 250 64000 32 256" >> /etc/sysctl.conf
        free -b | awk '/Mem/ {print "kernel.shmmax="$2/2}' >> /etc/sysctl.conf
        free -k | awk '/Mem/ {print "kernel.shmall="$2*0.8}' >> /etc/sysctl.conf
        echo "kernel.shmmni = 819200" >> /etc/sysctl.conf
        sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/g" $PGDATA/postgresql.conf
        echo "host      all     all     0.0.0.0/0       md5" >> $PGDATA/pg_hba.conf
        su - postgres -c "/usr/pgsql-10/bin/postgres -D $PGDATA -c config_file=$PGDATA/postgresql.conf"
fi

build 镜像

cd /home/postgresql10_docker
docke build -t postgres_10_centos7.6:1.0 .

查看镜像

docker images

输出结果：

REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
postgres_10_centos7.6   1.0                 232ef0628e1f        6 hours ago         952MB
centos              7.6.1810            f1cb7c7d58b7        7 months ago        202MB

2.2 启动一个postgresql容器，放后台运行

设置启动端口也为5432

docker run --name pgtest1 -d -p 5431:5432 postgres_10_centos7.6:1.0

检查启动的容器

docker ps -a

输出如下

CONTAINER ID        IMAGE                       COMMAND                  CREATED              STATUS              PORTS                    NAMES
534149dc7726        postgres_10_centos7.6:1.0   "docker-entrypoint.s…"   About a minute ago   Up About a minute   0.0.0.0:5432->5432/tcp   sharp_pasteur

进入容器查看postgres进程是否正常启动

[root@iZuf678t3hp8rp7xchc3aoZ ~]# docker exec -it 534149dc7726 ps -ef | grep postgres

输出结果如下

root        10     1  0 15:53 ?        00:00:00 su - postgres -c /usr/pgsql-10/b
postgres    11    10  0 15:53 ?        00:00:00 /usr/pgsql-10/bin/postgres -D /v
postgres    24    11  0 15:53 ?        00:00:00 postgres: logger process   
postgres    26    11  0 15:53 ?        00:00:00 postgres: checkpointer process  
postgres    27    11  0 15:53 ?        00:00:00 postgres: writer process   
postgres    28    11  0 15:53 ?        00:00:00 postgres: wal writer process   
postgres    29    11  0 15:53 ?        00:00:00 postgres: autovacuum launcher pr
postgres    30    11  0 15:53 ?        00:00:00 postgres: stats collector proces
postgres    31    11  0 15:53 ?        00:00:00 postgres: bgworker: scheduler   
postgres    32    11  0 15:53 ?        00:00:00 postgres: bgworker: TimescaleDB 
postgres    33    11  0 15:53 ?        00:00:00 postgres: bgworker: logical repl

宿主机上查看5432端口是否启动

netstat -nlap | grep 5432

tcp6       0      0 :::5432                 :::*                    LISTEN      22287/docker-proxy

此时一个基于docker的postgresql已经启动成功。

2.3 在一个宿主机上启动两个postgresql容器，并将

2.3.1 启动两个容器

创建docker的volume

docker volume create pgdata_5431
   docker volume create pgdata_5433

查看volume列表

[root@iZuf678t3hp8rp7xchc3aoZ ~]# docker volume list
DRIVER              VOLUME NAME
local               pgdata_5431
local               pgdata_5433

查看volume的存储位置

[root@iZuf678t3hp8rp7xchc3aoZ postgresql10_docker]# docker inspect pgdata_5431
[
    {
        "CreatedAt": "2019-11-16T19:23:40+08:00",
        "Driver": "local",
        "Labels": {},
        "Mountpoint": "/var/lib/docker/volumes/pgdata_5431/_data",
        "Name": "pgdata_5431",
        "Options": {},
        "Scope": "local"
    }
]

[root@iZuf678t3hp8rp7xchc3aoZ postgresql10_docker]# docker inspect pgdata_5433
[
    {
        "CreatedAt": "2019-11-16T19:24:38+08:00",
        "Driver": "local",
        "Labels": {},
        "Mountpoint": "/var/lib/docker/volumes/pgdata_5433/_data",
        "Name": "pgdata_5433",
        "Options": {},
        "Scope": "local"
    }
]

如果需要将数据盘单独挂载，可以新建个目录mount到分区上，然后将/var/lib/docker/volumes/pgdata_5433目录下的文件移动过来，使用软连接的方式

mkdir /data/pgsql
mv /var/lib/docker/volumes/pgdata_5433 /data/pgsql
ln -s /data/pgsql/pgdata_5433 /var/lib/docker/volumes/pgdata_5433

分别启动两个容器，端口分别为5431，5433

docker run --name pgtest_5431 -d -p 5431:5432 -v pgdata_5431:/var/lib/pgsql/10/data postgres_10_centos7.6:3.0 & disown
docker run --name pgtest_5433 -d -p 5433:5432 -v pgdata_5433:/var/lib/pgsql/10/data postgres_10_centos7.6:3.0 & disown

查看容器启动情况

[root@iZuf678t3hp8rp7xchc3aoZ ~]# docker ps -a
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS              PORTS                    NAMES
338f82d2e4e5        postgres_10_centos7.6:3.0   "docker-entrypoint.s…"   10 minutes ago      Up 10 minutes       0.0.0.0:5433->5432/tcp   pgtest_5433
3c027bee7482        postgres_10_centos7.6:3.0   "docker-entrypoint.s…"   11 minutes ago      Up 11 minutes       0.0.0.0:5431->5432/tcp   pgtest_5431

查看宿主机端口

[root@iZuf678t3hp8rp7xchc3aoZ ~]# netstat -nlap | grep 5431
tcp6       0      0 :::5431                 :::*                    LISTEN      26246/docker-proxy  
[root@iZuf678t3hp8rp7xchc3aoZ ~]# netstat -nlap | grep 5433
tcp6       0      0 :::5433                 :::*                    LISTEN      26367/docker-proxy

2.3.2 容器间数据库互相访问

分别进入两个容器，创建超级数据库用户

docker exec -it pgtest_5431 /bin/bash
su - postgres
psql
create user app_user with superuser password 'app_user';

docker exec -it pgtest_5433 /bin/bash
su - postgres
psql
create user app_user with superuser password 'app_user';

查看容器的ip

[root@iZuf678t3hp8rp7xchc3aoZ postgresql10_docker]# docker exec -it pgtest_5433 'ifconfig'
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.17.0.3  netmask 255.255.0.0  broadcast 172.17.255.255
        ether 02:42:ac:11:00:03  txqueuelen 0  (Ethernet)
        RX packets 37  bytes 3449 (3.3 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 42  bytes 3519 (3.4 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@iZuf678t3hp8rp7xchc3aoZ postgresql10_docker]# docker exec -it pgtest_5431 'ifconfig'
eth0: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 172.17.0.2  netmask 255.255.0.0  broadcast 172.17.255.255
        ether 02:42:ac:11:00:02  txqueuelen 0  (Ethernet)
        RX packets 43  bytes 3622 (3.5 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 37  bytes 3388 (3.3 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

登陆对端和本地数据库

root@iZuf678t3hp8rp7xchc3aoZ ~]# docker exec -it pgtest_5433 /bin/bash
-bash-4.2$ psql 'user=app_user password=app_user dbname=postgres host=172.17.0.2'
psql (10.11)
Type "help" for help.

postgres=# create table tab_test(id text);
CREATE TABLE


-bash-4.2$ psql 'user=app_user password=app_user dbname=postgres host=172.17.0.3'
psql (10.11)
Type "help" for help.

postgres=# create table tab_test(id text);
CREATE TABLE

补充说明

推荐将数据目录挂载在宿主机上

docker方式部署postgresqldocker方式部署postgresql

docker方式部署postgresql

1 安装docker

1.1 通过二进制包安装docker

1.2 将安装包解压到/home/docker目录下

1.3 将docker加入到系统服务

1.4 重启docker

2 docker 上部署postgresql

2.1制作postgresql的镜像

2.2 启动一个postgresql容器，放后台运行

2.3 在一个宿主机上启动两个postgresql容器，并将

2.3.1 启动两个容器

2.3.2 容器间数据库互相访问

补充说明

继续阅读

Docker-compose 进行Doris自动化编排部署

错误:the input device is not a TTY

docker run mysql 报错：ERROR 2002 (HY000)

RedHat8在线安装docker

服装信息化数字化变革

使用kubeadm+calico部署kubernetes v1.25.3

Perl与网络监控

云虚拟主机进入容器时代

Docker安装和部署WeCenter3.3.5开源问答平台

docker容器网络配置docker容器网络配置

因overlay2文件夹占用过大而引起的docker数据迁移之战（上）前言正文

Docker - Dockerfile之ADD、COPY、WORKDIR、USER、EXPOSE指令详解

为什么要选择UniDAC

(SpringBoot)日志种类：log、monitor、access、out、gc、backup

CentOS 7,docker安装

【Docker】端口映射问题操作步骤