<p>First, I'm glad you've realized what a mistake you made.</p>
<p>There's not a fool-proof way to do this, other than to do a fresh installation of Craft using PostgreSQL and migrate the content over.</p>
<p>There is a tool called <a href="https://github.com/dimitri/pgloader" rel="noreferrer">pgloader</a> that purports to be able to convert a MySQL/MariaDB database into PostgreSQL and it gives it the old college try.</p>
<p>My experience is that it works a good 70% of the time depending on things like environmental configurations and the size/complexity of the original database, so here's hoping luck is on your side.</p>
<p>Be sure and check their <a href="https://github.com/dimitri/pgloader/issues" rel="noreferrer">issues</a> if you run into any errors.</p>
<p>Make backups. YMMV. UAYOR. Test the #@*! out of the resulting database.</p>
<hr />
<h2>What You’ll Need</h2>
<ul>
<li>An existing Craft CMS install that uses MySQL or MariaDB</li>
<li>Running MySQL and PostgreSQL servers, and the DSN for each</li>
<li><a href="https://github.com/dimitri/pgloader/" rel="noreferrer">pgloader</a></li>
</ul>
<h2>Steps</h2>
<ol>
<li><p>Download and install pgloader. If you’re on a Mac, you can use <code>brew install pgloader</code>.</p>
</li>
<li><p>Save and <a href="https://pgloader.readthedocs.io/en/latest/ref/mysql.html" rel="noreferrer">customize</a> a local command file* for pgloader:</p>
<pre><code>LOAD DATABASE
    FROM mysql://root:root@mariadb/craft3
    INTO pgsql://craft3:secret@postgres/craft3
    WITH quote identifiers
CAST type datetime to timestamp
ALTER SCHEMA 'craft3' RENAME TO 'public'

AFTER LOAD DO
    $$ ALTER TABLE craft_searchindex ADD COLUMN keywords_vector tsvector; $$,
    $$ UPDATE craft_searchindex SET keywords_vector = CAST (keywords as tsvector); $$,
    $$ ALTER TABLE craft_searchindex ALTER COLUMN keywords_vector SET NOT NULL; $$
;
</code></pre>
<p>This one assumes your MySQL username and password are <code>root</code>, the host is <code>mariadb</code>, and the source database is <code>craft3</code>. The PostgreSQL username and password are <code>craft3</code> and <code>secret</code>, the host is <code>postgres</code>, and the target PGSQL database is <code>craft3</code>.</p>
<p>Be sure to adjust the  <code>craft_</code> table prefix to match whatever you’re using on your MySQL database.</p>
<p>If you need to specify a port for either DSN, append a colon and that number to the hostname. (For example, specifying port 3306 for mysql would become <code>mysql://root:root@mariadb:3306/craft3</code>).</p>
</li>
<li><p>Tell pgloader to read the MySQL database into PostgreSQL:</p>
<pre><code>pgloader /path/to/command-file
</code></pre>
<p>Example conversion output, which includes useful stats and warnings.</p>
</li>
</ol>
<p><a href="https://i.sstatic.net/9a4V1.jpg" rel="noreferrer"><img src="https://i.sstatic.net/9a4V1.jpg" alt="enter image description here" /></a></p>
<hr />
<p>*<strong>Why use a command file?</strong></p>
<p>You <em>could</em> run <code>pgloader mysql://root:root@mariadb/craft3 postgresql://craft3:secret@postgres/postgres/craft3</code>, but column names would all be lowercased, some critical type conversions and search index updates would be missing, and using a password in any terminal command is usually a bad idea since it will be visible in that shell’s command history.</p>

CC BY-SA 4.0
2020-07-16T10:35:48.317Z
So you're recommending PostgresSQL over MySQL?
gioppe
CC BY-SA 4.0
2020-07-17T05:24:19.597Z
As always, "it depends". I personally prefer it.
Brad Bell
CC BY-SA 4.0
2023-06-01T21:59:42.303Z
You have my sincerest thanks for this guide, especially the extra column fixes. I finally got this flaming Craft site migrated after days of hacking. Hopefully this is the last time I will ever need to go through this!
Nabil Freeman
CC BY-SA 4.0
2023-11-05T13:13:14.977Z
I attempted to use this method but I'm running into multiple errors which I'm not sure are related to postgres or the data. Anyone could take a look? https://craftcms.stackexchange.com/questions/41258/multiple-errors-attempting-to-migrate-mariadb-craft-cms-3-database-to-postgresql
cballenar
CC BY-SA 4.0
2020-07-15T22:04:42.207Z
2020-07-15T22:04:42.207Z
Brad Bell
<p>I have an existing Craft installation using MySQL and I've only recently realized the error of my ways and would like to convert the database to PostgreSQL.</p>
<p>Is this possible?</p>

<mysql><postgresql>
Is there a way to convert a Craft MySQL database to PostgreSQL?
# Is there a way to convert a Craft MySQL database to PostgreSQL?

(Asked by: username_0 on 2020-07-15)

I have an existing Craft installation using MySQL and I've only recently realized the error of my ways and would like to convert the database to PostgreSQL.

Is this possible?

**Tags:** mysql, postgresql

---

## 1 answers

### Answer by username_0 on 2020-07-15. Score: 7

First, I'm glad you've realized what a mistake you made.
There's not a fool-proof way to do this, other than to do a fresh installation of Craft using PostgreSQL and migrate the content over.
There is a tool called pgloader that purports to be able to convert a MySQL/MariaDB database into PostgreSQL and it gives it the old college try.
My experience is that it works a good 70% of the time depending on things like environmental configurations and the size/complexity of the original database, so here's hoping luck is on your side.
Be sure and check their issues if you run into any errors.
Make backups. YMMV. UAYOR. Test the #@*! out of the resulting database.

What You’ll Need

An existing Craft CMS install that uses MySQL or MariaDB
Running MySQL and PostgreSQL servers, and the DSN for each
pgloader

Steps

Download and install pgloader. If you’re on a Mac, you can use brew install pgloader.

Save and customize a local command file* for pgloader:
LOAD DATABASE
    FROM mysql://root:root@mariadb/craft3
    INTO pgsql://craft3:secret@postgres/craft3
    WITH quote identifiers
CAST type datetime to timestamp
ALTER SCHEMA 'craft3' RENAME TO 'public'

AFTER LOAD DO
    $$ ALTER TABLE craft_searchindex ADD COLUMN keywords_vector tsvector; $$,
    $$ UPDATE craft_searchindex SET keywords_vector = CAST (keywords as tsvector); $$,
    $$ ALTER TABLE craft_searchindex ALTER COLUMN keywords_vector SET NOT NULL; $$
;

This one assumes your MySQL username and password are root, the host is mariadb, and the source database is craft3. The PostgreSQL username and password are craft3 and secret, the host is postgres, and the target PGSQL database is craft3.
Be sure to adjust the  craft_ table prefix to match whatever you’re using on your MySQL database.
If you need to specify a port for either DSN, append a colon and that number to the hostname. (For example, specifying port 3306 for mysql would become mysql://root:root@mariadb:3306/craft3).

Tell pgloader to read the MySQL database into PostgreSQL:
pgloader /path/to/command-file

Example conversion output, which includes useful stats and warnings.




*Why use a command file?
You could run pgloader mysql://root:root@mariadb/craft3 postgresql://craft3:secret@postgres/postgres/craft3, but column names would all be lowercased, some critical type conversions and search index updates would be missing, and using a password in any terminal command is usually a bad idea since it will be visible in that shell’s command history.

> **Comments:**
> **username_1**: So you're recommending PostgresSQL over MySQL?  
> **username_0**: As always, "it depends". I personally prefer it.  
> **username_2**: You have my sincerest thanks for this guide, especially the extra column fixes. I finally got this flaming Craft site migrated after days of hacking. Hopefully this is the last time I will ever need to go through this!  
> **username_3**: I attempted to use this method but I'm running into multiple errors which I'm not sure are related to postgres or the data. Anyone could take a look? https://craftcms.stackexchange.com/questions/41258/multiple-errors-attempting-to-migrate-mariadb-craft-cms-3-database-to-postgresql  

---

