Database Migration

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.

September 11, 2019

database migration mysql postgresql

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.

References

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

FATAL error: 76 fell through ECASE expression

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 compiler
unzip: A de-archiver for .zip files
libsqlite3-dev: A collection of development files for SQLite 3
gawk: Short for “GNU awk”, a pattern scanning and processing language
curl: A command line tool for transferring data from a URL
make: A utility for managing package compilation
freetds-dev: A client library for MS SQL and Sybase databases
libzip-dev: A library for reading, creating, and modifying zip archives
pgloader: A tool to load data into PostgreSQL

Copy

1
2
3
4
5
6
7
8


docker exec -it postgresql bash

apt update
apt upgrade
apt install sbcl unzip libsqlite3-dev gawk curl make freetds-dev libzip-dev

# apt install pgloader
# apt install ca-certificates

Check the latest pgLoader Release

Copy

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12


# Transfer the tarball
postgres-server$ curl -fsSLO https://github.com/dimitri/pgloader/archive/v3.6.9.tar.gz
# Extract the tarball
postgres-server$ tar xvf v3.6.9.tar.gz
# Navigate into the new pgLoader parent directory
postgres-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 are
postgres-server$ mv ./build/bin/pgloader /usr/local/bin/
# Test that pgLoader was installed correctly by checking its version
postgres-server$ pgloader --version

mysql-ssl-docker

Copy

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48


docker exec -it mysql bash
mysql  -u root -p \
        --ssl-ca=/etc/certs/root-ca.pem \
        --ssl-cert=/etc/certs/client-cert.pem \
        --ssl-key=/etc/certs/client-key.pem 
Enter 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.pem
Enter password: secret

mysql> show global variables like '%ssl%';
mysql> show global variables like '%secure%';
mysql> status 
mysql> show databases;
mysql> show grants for usermysql@'%';

# usermysqlssl without password
mysql> 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 password
mysql> 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 \
                dbmysql
Enter password: pwdmysqlssl

postgresql-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:

Copy

1
2
3


cp /usr/local/share/ca-certificates/root-ca.pem /usr/local/share/ca-certificates/ca.pem.crt
cp /usr/local/share/ca-certificates/client-cert.pem /usr/local/share/ca-certificates/client-cert.pem.crt
update-ca-certificates

To connect mysql from postgres:

Copy

1
2
3
4
5
6
7
8


docker exec -it postgresql bash
apt install default-mysql-client
mysql -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 \
        dbmysql
Enter password: pwdmysqlssl

To create username, password and table for postgres:

Copy

1
2
3
4
5
6
7
8


apt install sudo
sudo -u postgres createuser --interactive -P
Enter name of role to add: pgloader_pg
Enter password for new role: postgresql_password
Enter it again: postgresql_password
Shall the new role be a superuser? (y/n) y

sudo -u postgres createdb new_db

Copy

1

pgloader mysql://usermysqlssl:pwdmysqlssl@mysql/dbmysql?useSSL=true postgresql://postgres:postgres@localhost/new_db

To test it:

Copy

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


# 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_db
new_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 : \q

Backup/Restore a dockerized PostgreSQL database

Copy

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


# The following command will dump all data from all tables:
docker exec <container_name> pg_dump -U <user> <schema_name> > backup
docker 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.sql
docker 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 postgresql
mkdir ./data/backups/
docker exec postgresql pg_dump -U postgres -F t new_db | gzip > ./data/backups/new_db-$(date +%Y-%m-%d).tar.gz

Backup database

generate sql:

Copy

1

docker exec -t your-db-container pg_dumpall -c -U your-db-user > dump_$(date +%Y-%m-%d_%H_%M_%S).sql

to reduce the size of the sql you can generate a compress:

Copy

1

docker exec -t your-db-container pg_dumpall -c -U your-db-user | gzip > ./dump_$(date +"%Y-%m-%d_%H_%M_%S").gz

Restore database

Copy

1
2


cat your_dump.sql | docker exec -i your-db-container psql -U your-db-user -d your-db-name
cat ./conf/init-scripts/inserts.sql | docker exec -i 9e2929203d0278cc944ee01bcefce58f6e5e222c26350ebff9c355e12365a265 psql -U your-db-user -d your-db-name

to restore a compressed sql:

Copy

1

gunzip < your_dump.sql.gz | docker exec -i your-db-container psql -U your-db-user -d your-db-name