Back to Index
Documentation

Here’s a clear, copy-pasteable guide you can keep in your repo as docs/migrate-mariadb-to-postgres.md.

Migrating a Drupal 10/11 site from MariaDB/MySQL to PostgreSQL

TL;DR

  • Drupal 11 requires PostgreSQL ≥ 16 (Drupal 10 works with older, but upgrade to PG16+ to be future-proof).
  • Do NOT “flip” the driver on the same DB. Create a clean Drupal on PostgreSQL, then migrate config and content.
  • Use Migrate API to copy data from the old MySQL/MariaDB database to the new PostgreSQL database.
  • Plan a rehearsal → content freeze → final delta import → cutover → rollback ready.

1) Requirements

Platform

  • PostgreSQL: 16+ (install pg_trgm; optionally btree_gin)
  • PHP extensions: pdo_pgsql (and pgsql if available)
  • Drush: 13+ (matching your Drupal core)
  • Drupal core: 10.x or 11.x (if on 10, you can still migrate to PG16)

Access & artifacts

  • Admin access to the current site.
  • Credentials for both databases (source MariaDB/MySQL, target PostgreSQL).
  • A config sync directory with a clean drush cex -y export.
  • File system copies (public:// and private:// if used).

2) Risks & what to check first

  • Custom SQL: Scan code for MySQL-specific functions (GROUP_CONCAT, FIND_IN_SET, ON DUPLICATE KEY, etc.). Replace with Drupal Database API or driver-specific conditionals.
  • Case sensitivity: MySQL collations are often case-insensitive; PostgreSQL is case-sensitive. If you rely on case-insensitive uniqueness (e.g., usernames), use functional indexes on PG (e.g., LOWER()).
  • Search & LIKE performance: enable pg_trgm for text search / fuzzy LIKE in PG.
  • Profiles & UUID: Config import requires matching site UUID. You cannot change install profile after install.

3) Migration strategy (recommended)

  1. Rehearsal locally or in staging:

    • Spin up PG16.
    • Build a clean Drupal schema on PG from your current code + config.
    • Migrate content from MariaDB → PG with Migrate API.
    • Verify parity and behavior.

  2. Production cutover:

    • Freeze edits on the old site.
    • Run a delta (only changed items) or a quick full re-import if small.
    • Switch application DB connection to PG.
    • Monitor; keep rollback ready.

4) Environment setup (target)

Create the PostgreSQL DB

-- psql as a superuser:
CREATE ROLE drupal WITH LOGIN PASSWORD '********';
CREATE DATABASE drupal_db OWNER drupal;
\c drupal_db
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS btree_gin; -- optional but useful

Drupal database settings

Keep MySQL as source_mysql, point default at PG:

// sites/default/settings.local.php

// TARGET (default): PostgreSQL
$databases['default']['default'] = [
  'driver' => 'pgsql',
  'database' => 'drupal_db',
  'username' => 'drupal',
  'password' => '********',
  'host' => '127.0.0.1',
  'port' => 5432,
  'prefix' => '',
];

// SOURCE: MySQL / MariaDB
$databases['source_mysql']['default'] = [
  'driver' => 'mysql',
  'database' => 'drupal_src',
  'username' => 'src_user',
  'password' => '********',
  'host' => '127.0.0.1',
  'port' => 3306,
  'prefix' => '',
];

5) Build the PG site structure

You want the PG target to have the same entity types/fields/views as your live site before migrating content.

Option A (most reliable with Standard profile)

  1. Install PG site with the same profile as source (e.g., standard):
drush si standard -y --db-url=pgsql://drupal:PASS@127.0.0.1:5432/drupal_db \
  --account-name=admin --account-pass=admin
  1. Set the UUID to match the source, then import config:
# On the source (MySQL) site:
drush cex -y
drush cget system.site uuid --format=string   # copy UUID

# On the PG site:
drush cset system.site uuid "PASTE-UUID-HERE" -y

# Remove seeded defaults that collide with your export (created by Standard):
# shortcuts, article/page, tags vocab, contact forms, comment type, basic block type
# (see “Seed cleanup” snippet in Appendix below)

drush cim -y
drush cr

Option B (--existing-config)

Only viable for profiles without hook_install() (Standard profile won’t work). If you use a config-only profile, you can:

drush si --existing-config -y --db-url=pgsql://drupal:PASS@127.0.0.1:5432/drupal_db

6) Migrate content (MariaDB → PostgreSQL)

Enable tools:

drush en -y migrate migrate_plus migrate_tools

Create a small custom module (e.g., your_site_migrate) with:

  • A migration group pointing to source_mysql

  • Individual migrations:

    • Users (first)
    • Taxonomy terms
    • Files (ensure files exist on disk)
    • Media
    • Paragraphs / referenced entities
    • Nodes (last, so lookups can resolve)

Example snippets

Group (config/install/migrate_plus.migration_group.site.yml)

id: site
label: Site migrations
shared_configuration:
  source:
    key: source_mysql

Users (migrate_plus.migration.site_users.yml)

id: site_users
label: Users from MySQL
migration_group: site
source:
  plugin: sql
  key: source_mysql
  query: "SELECT uid, name, mail, status, created, changed FROM users_field_data WHERE uid > 0"
  ids: { uid: { type: integer } }
process:
  uid: uid            # keep IDs if you want stable refs
  name: name
  mail: mail
  status: status
  created: created
  changed: changed
destination:
  plugin: entity:user

Files (migrate_plus.migration.site_files.yml)

id: site_files
label: Files
migration_group: site
source:
  plugin: sql
  key: source_mysql
  query: "SELECT fid, filename, uri, filemime, created, changed, status FROM file_managed"
  ids: { fid: { type: integer } }
process:
  fid: fid
  filename: filename
  uri: uri
  filemime: filemime
  created: created
  changed: changed
  status: status
destination:
  plugin: entity:file

Nodes (bundle page) (migrate_plus.migration.site_nodes_page.yml)

id: site_nodes_page
label: Nodes: Basic page
migration_group: site
source:
  plugin: sql
  key: source_mysql
  query: >
    SELECT n.nid, n.uid, n.title, n.status, n.created, n.changed,
           b.body_value, b.body_format
    FROM node_field_data n
    LEFT JOIN node__body b ON b.entity_id = n.nid
    WHERE n.type = 'page'
  ids: { nid: { type: integer } }
process:
  nid: nid
  type:
    plugin: default_value
    default_value: page
  title: title
  uid: uid
  status: status
  created: created
  changed: changed
  body/value: body_value
  body/format: body_format
destination:
  plugin: entity:node

Run in order:

drush en -y your_site_migrate
drush ms                            # list migrations
drush mim site_users -y
drush mim site_terms -y             # if you created it
drush mim site_files -y
drush mim site_media -y             # if applicable
drush mim site_paragraphs -y        # if applicable
drush mim site_nodes_page -y        # repeat per bundle

For Paragraphs or entity references, use migration_lookup to map source IDs to destination IDs from earlier migrations.

7) Verification checklist

Counts

-- On PG site:
SELECT COUNT(*) FROM users_field_data WHERE uid>0;
SELECT vid, COUNT(*) FROM taxonomy_term_field_data GROUP BY vid;
SELECT COUNT(*) FROM file_managed;
SELECT type, COUNT(*) FROM node_field_data GROUP BY type;

Functional

  • Login / session / CSRF.
  • Create/edit nodes, file uploads, media embeds.
  • Views filtering & sorting (text contains; case sensitivity).
  • Cron, queues, search indexing.

Performance

  • VACUUM (ANALYZE) after big imports.
  • Ensure pg_trgm is in place for text filters.
  • Add functional indexes for case-insensitive uniqueness, e.g.:
CREATE UNIQUE INDEX IF NOT EXISTS users_name_lower_idx ON users_field_data (LOWER(name));

8) Cutover plan (production)

  1. Content freeze on the old site (maintenance mode or editor freeze).
  2. Run delta migrations (limit by changed >= :timestamp) or rerun with --update.
  3. Switch application DB connection to PG (config management / secrets).
  4. Monitor logs, slow queries, and error rate.
  5. Keep the old MySQL site read-only and ready for rollback (flip connection back).

9) Rollback plan

  • Keep the old MySQL site reachable/read-only for 24–48h.
  • Keep a copy of the previous settings.php/secrets.
  • Revert the DB connection variables and clear caches to go back instantly.

10) Common pitfalls

  • Trying to drush si --existing-config with Standard profile → fails (uses hook_install()).
  • Not matching the UUID, causing cim failure.
  • Importing config before cleaning seeded entities (shortcuts, article/page, tags, etc.) that clash with your export.
  • Leaving mysql: in core.extension.yml when deploying to PG.
  • MySQL-only SQL in custom code; fix to DB API.
  • Not enabling pg_trgm, leading to slow LIKE/contains queries.

Appendix A – “Seed cleanup” snippet (Standard profile)

Run these on the fresh PG site before drush cim if your export doesn’t include Standard’s defaults:

# Remove shortcuts
drush php:eval '
$etm=\Drupal::entityTypeManager();
$s=$etm->getStorage("shortcut");$ids=$s->getQuery()->accessCheck(FALSE)->execute();if($ids){$s->delete($s->loadMultiple($ids));}
$ss=$etm->getStorage("shortcut_set");$ids=$ss->getQuery()->accessCheck(FALSE)->execute();if($ids){$ss->delete($ss->loadMultiple($ids));}
'

# Remove Article/Page body instances, bundles, then field storage
drush php:eval '
$etm=\Drupal::entityTypeManager();
$delFI=function($bundle){$s=$etm->getStorage("field_config")->load("node.$bundle.body"); if($s){$s->delete();}};
$delFI("article"); $delFI("page");
if($t=$etm->getStorage("node_type")->load("article")){$t->delete();}
if($t=$etm->getStorage("node_type")->load("page")){$t->delete();}
if($fs=$etm->getStorage("field_storage_config")->load("node.body")){$fs->delete();}
'

# Remove other seeded defaults
drush php:eval '
$etm=\Drupal::entityTypeManager();
if($v=$etm->getStorage("taxonomy_vocabulary")->load("tags")){$v->delete();}
foreach(["feedback","personal"] as $cf){ if($f=$etm->getStorage("contact_form")->load($cf)){$f->delete();} }
if($ct=$etm->getStorage("comment_type")->load("comment")){$ct->delete();}
if($bt=$etm->getStorage("block_content_type")->load("basic")){$bt->delete();}
'

Appendix B – Delta runs / high-water marks

In a migration YAML:

high_water_property:
  name: changed
  alias: src

Then add WHERE changed >= :high_water to your query or rely on Migrate’s internal tracking. For a final cutover, freeze content and re-run the migrations with --update.

Appendix C – Quick grep for MySQL-isms

rg -n --pcre2 -S 'GROUP_CONCAT|FIND_IN_SET|IFNULL|ON DUPLICATE KEY|UNHEX\(|SELECT\s+.*\s+FOR\s+UPDATE' \
  -t php --glob '!vendor/**' --glob '!node_modules/**' .

Done

This process keeps risk low and gives you repeatability:

  • rehearse locally,
  • validate parity,
  • execute a short freeze + delta,
  • and keep a fast rollback.

If you’d like, I can generate a starter your_site_migrate module with users/terms/files and one node bundle based on your site’s actual bundles to speed up your rehearsal.

Back to Index
Contents