INT 21h

Hi, I am Vladimir Smagin, SysAdmin and Kaptain. Telegram Email / GIT / Микроблог / Thingiverse / RSS / GPG

Zabbix: MySQL to Postgresql migration

№ 9662 В разделе "Sysadmin" от December 28th, 2018,
В подшивках: , , ,

All services containerized by Docker, MySQL and Zabbix containers connected to network “zabbix”.

Start Postgresql container and add it to zabbix network group in Docker, set parameters as you need

docker run --name postgres -d --restart=Always \
    -e POSTGRES_USER=postgres
    -e POSTGRES_PASSWORD=postgres
    postgres
docker network connect zabbix postgres

Now create database and user

docker exec -it postgres su -c "createuser -P zabbix" postgres
docker exec -it postgres su -c "createdb -E UTF-8 -O zabbix zabbix" postgres

Ok, database online and accessible for Zabbix services.

Now you can stop old containers with Zabbix services for MySQL and run migration

docker stop zabbix-server-mysql zabbix-panel-mysql
docker run --network zabbix --rm -v /tmp/zabbix:/zabbix dimitri/pgloader:latest \
    bash -c "pgloader --debug --verbose \
    mysql://root:pa55w0rd@mariadb/zabbix \
    postgresql://zabbix:zabbix@postgres/zabbix"

Lets create script to start new Zabbix containers for Postgres DB

!/bin/bash

alertscripts="/srv/docker/zabbix/alertscripts:/usr/lib/zabbix/alertscripts"
externalscripts="/srv/docker/zabbix/externalscripts:/usr/lib/zabbix/externalscripts"

docker run --name zabbix-server-pg -p 10051:10051 \
    -v $alertscripts \
    -v $externalscripts \
    -e DB_SERVER_HOST="postgres" \
    -e DB_SERVER_PORT=5432 \
    -e POSTGRES_USER="zabbix" \
    -e POSTGRES_PASSWORD="zabbix" \
    -e POSTGRES_DB="zabbix" \
    -d --restart=always \
    --network zabbix \
    zabbix/zabbix-server-pgsql:ubuntu-latest

docker run --name zabbix-panel-pg -p 5001:80 \
    -e DB_SERVER_HOST="postgres" \
    -e POSTGRES_USER="zabbix" \
    -e POSTGRES_PASSWORD="zabbix" \
    -e POSTGRES_DB="zabbix" \
    -e ZBX_SERVER_HOST="zabbix-server-pg" \
    -e ZBX_SERVER_PORT="10051" \
    -e PHP_TZ="Asia/Novosibirsk" \
    -d --restart=always \
    --network zabbix \
    zabbix/zabbix-web-nginx-pgsql:ubuntu-latest

You did it! Take a cookie from shelf.

Нет комментариев »

Postgres backup to FTP from Docker or directly, see examples

№ 9473 В разделе "Sysadmin" от October 23rd, 2018,
В подшивках: , ,

You can easily dump your Postgresql database from Docker container or directly and you have 2 ways to do it. First variant more dangerous, depends on internet connection, but not requires a lot of disk space. Second variant more stable, but if free space suddenly is out your backup will be failed. You can add error checking and reporting to Zabbix like here.

Variant 1: stream backing up file to FTP (directly)

#!/bin/bash
source /etc/profile
weekday=$(date '+%w')
pg_dump postgresql://pguser:pgpassword@mydatabase.server.com:5433/database | gzip | curl -u ftplogin:ftppassword \
ftp://backup-ftp.server.com/postgres-backups/$weekday/db_backup.sql.gz --ftp-create-dirs -T -

Variant 2: dump to local host, upload and delete local file (from Docker)

#!/bin/bash
source /etc/profile

db_user="postgres"
db_pass="lkfgjs4573234"
db_host="127.0.0.1"
db_port="5432"
db_name="my_precious_db"

ftp_user="useruseruser"
ftp_pass="klksdjfa;lkjdf"
ftp_serv="ftp.server.ru"

weekday=$(date '+%w') # 7 days history
history=${weekday}

docker exec -it pg-slave-server su -c "pg_dump postgresql://${db_user}:${db_pass}@${db_host}:${db_port}/${db_name}" \
    postgres |gzip -7 > /tmp/db_backup_${db_name}_${history}.sql.gz
curl --upload-file /tmp/db_backup_${db_name}_${history}.sql.gz ftp://${ftp_user}:${ftp_pass}@${ftp_serv}/
rm -f /tmp/db_backup_${db_name}_${history}.sql.gz

Нет комментариев »

Яндекс.Метрика

Fortune cookie: Today's spam: stop unwanted emails today