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?
Craft CMS Stack Exchange is a question and answer site for administrators, end users, developers and designers for Craft CMS. It only takes a minute to sign up.Sign up to join this community
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.
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
secret, the host is
postgres, and the target PGSQL database is
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
Tell pgloader to read the MySQL database into PostgreSQL:
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.