Porting a DDEV MySQL/MariaDB database to PostgreSQL with pgloader

When you develop projects with DDEV, you usually don’t need to install services such as PHP and Databases on your Host system (your local operating system). They run inside docker containers. Check the documentation of your CMS if it uses stored routines, triggers, or events. pgloader does not support them. That said, I can’t name a single CMS that actually uses them out of my head. If you know that your application uses them, you probably created them yourself. Read more on how to add them manually to Postgres here.

In order to run pgloader locally on your machine, we will need two things though:

A local PostgreSQL instance with an empty database. This is where pgloader will import our old MySQL/MariaDB database into.
And at least a MySQL or MariaDB Client installed, so that pgloader can access the old database that is running in our DDEV database container from our host (the aforementioned local operating system).

The steps will roughly cover:

Install pgloader and other requirements. Configure a local Postgres install.
We will use pgloader to convert our MariaDB/MySQL database to PostgreSQL.
We want pgloader to access and read our MariaDB/MySQL database directly inside the running DDEV container. We will have pgloader write the postgres database into PostgreSQL that we install on our Mac/PC. We will then dump our Postgres port of the database to a file.
We will change the DDEV configuration to use Postgres 17, and restart DDEV.
And finally import our Postgres dump into the newly created postgres container of our DDEV project.

1. Prerequisites

You have a running DDEV project with a MariaDB or MySQL database. Start it now and, for safety reasons, create a backup of your current MariaDB/MySQL database – you never know if you might need it at some point.

ddev start
ddev export-db -f ~/Documents/better-safe-than-sorry.sql.gz

Now let’s Install Postgres, MariaDB Client and pgloader on your Mac or Linux OS and make sure they are running. We do this locally on our host, not inside any DDEV container.

# on Linux
sudo apt install postgresql mariadb-client pgloader
sudo systemctl start postgresql
sudo systemctl enable postgresql

# or on macOS
brew install postgresql@17 mariadb-client pgloader

Find out the port number that your MariaDB/MySQL database container exposes to the host system. You can either see that in Docker Desktop, or in VSCode if you have the Containers or Docker extension. You should see two port numbers, it’s the one that is not 3306 (because 3306 is the default port of the database that is available only from within the running container).
In order for pgloader to create and import your database, you need a way for pgloader to authenticate. If you havent’t created a user yet, you can use the default postgres user for this. By design, PostgreSQL creates a user postgres on your system that can access Databases passwordless. Since we don’t want to execute all commands as postgres user, we rather define a password for it.
Connect to postgres: sudo -i -u postgres and type psql

Then set a password for postgres user and also create an empty database, that pgloader can import into

ALTER USER postgres WITH PASSWORD 'strongpassword'
CREATE DATABASE db;

Edit your pg_hba.conf file. Change peer to md5 for the postgres user. This allows us to login as postgres user with a password.
Now restart postgres sudo systemctl restart postgresql or brew services restart postgresql in order for the changes to take effect.

2. Migration command

The following command accesses the current MariaDB/MySQL database within your DDEV db container, creates the corresponding PostgreSQL tables and imports all rows into it. Make sure to replace the Port number with the one DDEV exposes to your host system.

SBCL_DYNAMIC_SPACE_SIZE=16384 pgloader --with "prefetch rows = 1000" mysql://db:db@localhost:<exposed-mariadb-port>/db postgresql://postgres:strongpassword@localhost:5432/db

During this step I saw some warnings, that some index names were truncated. None looked critical (duplicated) to me. This will highly depend on what CMS or Application you are actually running. On my first attempt, the command failed and I ended up at a lbc prompt. Increasing the SBCL_DYNAMIC_SPACE_SIZE helped a little. Adding --with "prefetch rows=1000" helped a lot.

3. Create a database dump from your new PostgreSQL db

pg_dump -U postgres db > ~/postgres-db.sql

We need this file in order to import it to our new DDEV PostgreSQL container later on.

4. Adjust DDEV settings

Stop your DDEV project with ddev stop.

If you didn’t make a database backup in step 1, do it now. Otherwise go ahead and delete your current database container with ddev delete --omit-snapshot

Open your .ddev/config.yaml and replace the following lines:

database:
    type: mariadb
    version: "11.4"

with:

database:
    type: postgres
    version: "17"

It’s very likely, that you also need to update database connection credentials in your CMS. Depending on what CMS you use, that might be a .env file, or some YAML configuration files inside a config folder.

In my case (a Symfony-based CMS), I only needed to update driver, port and version variables in an .env file:

DATABASE_DRIVER=pdo_mysql
DATABASE_PORT=3306
DATABASE_VERSION=11.4.0-mariadb

# if your CMS uses a DSN 
DATABASE_URL="mysql://db:db@db:3306/db?sslmode=disable&charset=utf8mb4&serverVersion=11.4.0-mariadb"

to:

DATABASE_DRIVER=pdo_pgsql
DATABASE_PORT=5432
DATABASE_VERSION=17

# if your CMS uses a DSN 
DATABASE_URL="postgresql://db:db@db:5432/db?sslmode=disable&serverVersion=17"

Everything else stayed the same: user, password, database name, host name etc.

And finally run ddev restart.

This has DDEV detect the change, pull the new container image and start it with an empty database.

At this point, your CMS will likely still show database errors, since no tables and rows are present, yet.

5. Import PostgreSQL database into DDEV

Importing the file, we created in step 3, via ddev import-db didn’t work for me. So I needed to lookup the port number, that the new PostgreSQL container exposes and resorted to <strong>psql</strong> to import the db dump directly into the DDEV PostgreSQL container.

psql -U db -h localhost -p <exposed-postgres-port> -d db -f ~/postgres-db.sql

Here I did see some warnings scroll by, complaining about the role postgres not being available. But since everything worked right away, I did not see a need to further investigate these warnings.

Test test test.

Make sure everything works as it’s supposed to in your Application or CMS. It did in my case and I now I could also export my DDEV database via ddev export-db -f ~/Documents/postgres-backup.sql.gz

If you no longer need it, you can remove the temporary PostgreSQL, MariaDB Client and pgloader installations we made in step 1 on our host system.

One could probably avoid the local PostgreSQL Installation by cloning your DDEV project and making the config adjustments in the project clone, start both projects and have pgloader communicate with the source and target containers directly. Nonetheless, taking baby steps worked for me.

And finally, wouldn’t it be great if DDEV was able to do these steps automagically for us, whenever it detects, that I changed my database platform?