Database migration
Migrating a MySQL database to PostgreSQL with pgloader, including SSL connections and Docker.
Database migration is the process of migrating data from one or more source databases to one or more target databases by using a database migration service. When a migration is finished, the dataset in the source databases resides fully, though possibly restructured, in the target databases.
graph LR
M[(MySQL<br/>in Docker)] -- SSL --> P[pgloader]
P -- schema + data --> G[(PostgreSQL)]
G -. pg_dump .-> B[Backup]
References
- Connect with SSL to MySQL in Docker container
- How To Migrate a MySQL Database to PostgreSQL Using pgLoader
Prerequisites
- Docker is a set of platform as a service products that use OS-level virtualization to deliver software in packages called containers. Download Docker
- MySQL is the most popular open source database. Download MySQL
- PostgreSQL is the world’s most advanced open source relational database. Download PostgreSQL
Build pgloader from sources
Fix the issue is to build pgloader from sources.
To take advantage of pgLoader’s useSSL option, a feature that allows for migrations from MySQL over an SSL connection. This feature is only available in versions 3.5.1 and newer of pgLoader.
Before installing pgLoader, you will need to install its dependencies.
sbcl: A Common Lisp compilerunzip: A de-archiver for .zip fileslibsqlite3-dev: A collection of development files for SQLite 3gawk: Short for “GNU awk”, a pattern scanning and processing languagecurl: A command line tool for transferring data from a URLmake: A utility for managing package compilationfreetds-dev: A client library for MS SQL and Sybase databaseslibzip-dev: A library for reading, creating, and modifying zip archivespgloader: A tool to load data into PostgreSQL
docker exec -it postgresql bash
apt updateapt upgradeapt install sbcl unzip libsqlite3-dev gawk curl make freetds-dev libzip-dev
# apt install pgloader# apt install ca-certificatesCheck the latest pgLoader Release
# Transfer the tarballpostgres-server$ curl -fsSLO https://github.com/dimitri/pgloader/archive/v3.6.9.tar.gz# Extract the tarballpostgres-server$ tar xvf v3.6.9.tar.gz# Navigate into the new pgLoader parent directorypostgres-server$ cd pgloader-3.6.9/# Then use the make utility to compile the pgloader binary:postgres-server$ make pgloader# Move the binary file into the /usr/local/bin directory where executable files arepostgres-server$ mv ./build/bin/pgloader /usr/local/bin/# Test that pgLoader was installed correctly by checking its versionpostgres-server$ pgloader --versionmysql-ssl-docker
docker exec -it mysql bashmysql -u root -p \ --ssl-ca=/etc/certs/root-ca.pem \ --ssl-cert=/etc/certs/client-cert.pem \ --ssl-key=/etc/certs/client-key.pemEnter password: secret
docker run --rm -it --network migration_wpnet -v `pwd`/conf/certs:/etc/certs \ imega/mysql-client \ mysql -h mysql -u root -p \ --ssl-ca=/etc/certs/root-ca.pem \ --ssl-cert=/etc/certs/client-cert.pem \ --ssl-key=/etc/certs/client-key.pemEnter password: secret
mysql> show global variables like '%ssl%';mysql> show global variables like '%secure%';mysql> statusmysql> show databases;mysql> show grants for usermysql@'%';
# usermysqlssl without passwordmysql> CREATE USER 'usermysqlssl'@'%' REQUIRE SSL;mysql> GRANT ALL PRIVILEGES ON `dbmysql`.* TO 'usermysqlssl'@'%';mysql> exit
docker run --rm -it --network migration_wpnet -v `pwd`/conf/certs:/etc/certs \ imega/mysql-client \ mysql -h mysql -u usermysqlssl \ --ssl-ca=/etc/certs/root-ca.pem \ --ssl-cert=/etc/certs/client-cert.pem \ --ssl-key=/etc/certs/client-key.pem \ dbmysql
# usermysqlssl with passwordmysql> CREATE USER 'usermysqlssl'@'%' IDENTIFIED BY 'pwdmysqlssl' REQUIRE SSL;mysql> GRANT ALL ON `dbmysql`.* TO 'usermysqlssl'@'%';mysql> FLUSH PRIVILEGES;mysql> exit
docker run --rm -it --network migration_wpnet -v `pwd`/conf/certs:/etc/certs \ imega/mysql-client \ mysql -h mysql -u usermysqlssl -p \ --ssl-ca=/etc/certs/root-ca.pem \ --ssl-cert=/etc/certs/client-cert.pem \ --ssl-key=/etc/certs/client-key.pem \ dbmysqlEnter password: pwdmysqlsslpostgresql-docker
useSSL=false
2023-01-15T21:00:11.194000Z ERROR mysql: Failed to connect to mysql at "mysql" (port 3306) as user "usermysql": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.
useSSL=true
2023-01-15T21:00:39.164000Z ERROR mysql: Failed to connect to mysql at "mysql" (port 3306) as user "usermysql": SSL verify error: 19 X509_V_ERR_SELF_SIGNED_CERT_IN_CHAIN
If you try to migrate your MySQL database using SSL the attempt would fail.
The reason for this is that pgLoader isn’t able to read MySQL’s configuration files, and thus doesn’t know where to look for the CA certificate or client certificate that you copied to your PostgreSQL server in the prerequisite SSL/TLS configuration guide. Rather than ignoring SSL requirements, though, pgLoader requires the use of trusted certificates in cases where SSL is needed to connect to MySQL. Accordingly, you can resolve this issue by adding the ca.pem and client-cert.pem files to trusted certificate store.
To do this, copy over the ca.pem and client-cert.pem files to the /usr/local/share/ca-certificates/ directory. Note that you must also rename these files so they have the .crt file extension. If you don’t rename them, your system will not be able to recognize that you’ve added these new certificates:
cp /usr/local/share/ca-certificates/root-ca.pem /usr/local/share/ca-certificates/ca.pem.crtcp /usr/local/share/ca-certificates/client-cert.pem /usr/local/share/ca-certificates/client-cert.pem.crtupdate-ca-certificatesTo connect mysql from postgres:
docker exec -it postgresql bashapt install default-mysql-clientmysql -h mysql -u usermysqlssl -p \ --ssl-ca=/usr/local/share/ca-certificates/root-ca.pem \ --ssl-cert=/usr/local/share/ca-certificates/client-cert.pem \ --ssl-key=/usr/local/share/ca-certificates/client-key.pem \ dbmysqlEnter password: pwdmysqlsslTo create username, password and table for postgres:
apt install sudosudo -u postgres createuser --interactive -PEnter name of role to add: pgloader_pgEnter password for new role: postgresql_passwordEnter it again: postgresql_passwordShall the new role be a superuser? (y/n) y
sudo -u postgres createdb new_dbpgloader mysql://usermysqlssl:pwdmysqlssl@mysql/dbmysql?useSSL=true postgresql://postgres:postgres@localhost/new_dbTo test it:
# First, connect to PostgreSQL using the psql tool. Second, enter the password for the user postgres and press the Enter keyboard:$ psql -U postgres -W# Third, switch to a database e.g.., dbmysql:postgres=# \c dbmysql
# Note that you can connect to a specific database when you log in to the PostgreSQL database server:$ psql -U pgloader_pg -d new_dbnew_db=# \dt# Use the \dt command from the PostgreSQL command prompt to show tables in the dbmysql database:new_db=# \dt+
# to show databases : \l# to show tables : \dt# to show data in table x : SELECT * FROM x;# to exit : \qBackup/Restore a dockerized PostgreSQL database
# The following command will dump all data from all tables:docker exec <container_name> pg_dump -U <user> <schema_name> > backupdocker exec postgresql pg_dump -U postgres new_db > ./data/backups/backup.sql
# The following command will dump only inserts from all tables:docker exec <container_name> pg_dump --column-inserts --data-only -U <user> <schema_name> > inserts.sqldocker exec postgresql pg_dump --column-inserts --data-only -U postgres new_db > ./data/backups/inserts.sql
# To backup gziped DB new_db in container named postgresqlmkdir ./data/backups/docker exec postgresql pg_dump -U postgres -F t new_db | gzip > ./data/backups/new_db-$(date +%Y-%m-%d).tar.gzBackup database
generate sql:
docker exec -t your-db-container pg_dumpall -c -U your-db-user > dump_$(date +%Y-%m-%d_%H_%M_%S).sqlto reduce the size of the sql you can generate a compress:
docker exec -t your-db-container pg_dumpall -c -U your-db-user | gzip > ./dump_$(date +"%Y-%m-%d_%H_%M_%S").gzRestore database
cat your_dump.sql | docker exec -i your-db-container psql -U your-db-user -d your-db-namecat ./conf/init-scripts/inserts.sql | docker exec -i 9e2929203d0278cc944ee01bcefce58f6e5e222c26350ebff9c355e12365a265 psql -U your-db-user -d your-db-nameto restore a compressed sql:
gunzip < your_dump.sql.gz | docker exec -i your-db-container psql -U your-db-user -d your-db-name