Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-07-16T10:35:48.317Z
Answers.Text: So you're recommending PostgresSQL over MySQL?
Answers.UserDisplayName: gioppe
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-07-17T05:24:19.597Z
Answers.Text: As always, "it depends". I personally prefer it.
Answers.UserDisplayName: Brad Bell
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-06-01T21:59:42.303Z
Answers.Text: 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!
Answers.UserDisplayName: Nabil Freeman
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2023-11-05T13:13:14.977Z
Answers.Text: 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
Answers.UserDisplayName: cballenar
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-07-15T22:04:42.207Z
Answers.LastActivityDate: 2020-07-15T22:04:42.207Z
Answers.UserDisplayName: Brad Bell
Body: <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>
  
Tags: <mysql><postgresql>
Title: Is there a way to convert a Craft MySQL database to PostgreSQL?
ThreadText: # 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](https://github.com/dimitri/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](https://github.com/dimitri/pgloader/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](https://github.com/dimitri/pgloader/)
  
  ## Steps
  
  1. Download and install pgloader. If you’re on a Mac, you can use `brew install pgloader`.
  2. Save and [customize](https://pgloader.readthedocs.io/en/latest/ref/mysql.html) 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`).
  3. Tell pgloader to read the MySQL database into PostgreSQL:
  
     ```
     pgloader /path/to/command-file
  
     ```
  
     Example conversion output, which includes useful stats and warnings.
  
  [![enter image description here](https://i.sstatic.net/9a4V1.jpg)](https://i.sstatic.net/9a4V1.jpg)
  
  ---
  
  \***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  
  
  ---
  
  