whiplash2486/pgloader repository overview

docker run --rm
-e MYSQL_USER=zabbix
-e MYSQL_PASSWORD=zabbix_password
-e MYSQL_HOST=172.19.0.2
-e MYSQL_DB=zabbix
-e POSTGRES_USER=postgres
-e POSTGRES_PASSWORD=postgres
-e POSTGRES_HOST=172.19.0.3
-e POSTGRES_DB=zabbix
--network=zabbix_pg_zabbix_network
pgloader tail -f /dev/null

---

⁠File structure

. ├── Dockerfile ├── entrypoint.sh └── pgloader.conf.template

---

⁠Dockerfile:

FROM ubuntu:22.04 RUN apt-get update && apt-get install -y
postgresql-client
mysql-client
curl
wget
build-essential
sbcl
libsqlite3-dev
libmysqlclient-dev
libpq-dev
unzip
pgloader
gettext
&& apt-get clean RUN mkdir /etc/pgloader COPY pgloader.conf.template /etc/pgloader/pgloader.conf.template COPY entrypoint.sh /usr/local/bin/entrypoint.sh RUN chmod +x /usr/local/bin/entrypoint.sh WORKDIR /etc/pgloader

ENTRYPOINT ["entrypoint.sh"]

---

⁠entrypoint.sh:

#!/bin/bash set -e required_vars=( MYSQL_USER MYSQL_PASSWORD MYSQL_HOST MYSQL_PORT MYSQL_DB POSTGRES_USER POSTGRES_PASSWORD POSTGRES_HOST POSTGRES_PORT POSTGRES_DB )

for var in "${required_vars[@]}"; do if [ -z "${!var}" ]; then echo "Error: Missing environment variable $var" exit 1 fi done

echo "Generating pgloader.conf from template..." envsubst < /etc/pgloader/pgloader.conf.template > /etc/pgloader/pgloader.conf

#psql "host=$POSTGRES_IP port=$POSTGRES_PORT user=$POSTGRES_USER password=$POSTGRES_PASSWORD" PGPASSWORD=$POSTGRES_PASSWORD psql -h $POSTGRES_HOST -p $POSTGRES_PORT -U $POSTGRES_USER
-tc "SELECT 1 FROM pg_database WHERE datname = 'zabbix'" | grep -q 1 ||
PGPASSWORD=$POSTGRES_PASSWORD psql -h $POSTGRES_HOST -p $POSTGRES_PORT -U $POSTGRES_USER
-c "CREATE DATABASE zabbix;"

echo "Starting pgloader..." pgloader /etc/pgloader/pgloader.conf tail -f /dev/null

---

⁠pgloader.conf.template:

LOAD DATABASE FROM mysql://$MYSQL_USER:$MYSQL_PASSWORD@$MYSQL_HOST:$MYSQL_PORT/$MYSQL_DB INTO postgresql://$POSTGRES_USER:$POSTGRES_PASSWORD@$POSTGRES_HOST:$POSTGRES_PORT/$POSTGRES_DB

WITH include no drop, truncate, create tables, create indexes, no foreign keys, reset sequences, prefetch rows = 1000, batch rows = 1000, batch concurrency = 1, on error stop

SET PostgreSQL PARAMETERS maintenance_work_mem to '128MB', work_mem to '12MB'

ALTER SCHEMA 'zabbix' RENAME TO 'public' ;

---

⁠Docker-compose.yml

version: '3.3'

services:
migration_tool:
container_name: pgloader
image: pgloader
environment:
MYSQL_USER: ansibleuser
MYSQL_PASSWORD: ansiblepass
MYSQL_HOST: 192.168.1.10
MYSQL_PORT: 3308
MYSQL_DB: zabbix
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
POSTGRES_HOST: 192.168.1.10
POSTGRES_PORT: 5432
POSTGRES_DB: zabbix