Is there a recommended way to migrate a Frappe app from MariaDB to Postgres? If not, in case the tables are manually imported from CSV files or using a tool like pgloader, would there be any issues?
If you are familiar with the finer points of syntax changes within MariaDB and Postgres and can deal with some SQL errors related to data, the migration should be feasible but I do not think it will be as simple as a button click 
I was able to use pgloader to migrate between local MariaDB and Postgres instances. I ran into a problem with a default value in ‘tabBank Statement Settings’, which had no data in my case. I ran drop table tabBank Statement Settings; to remove it and then everything went fine.
LOAD DATABASE
FROM mysql://root:password@localhost/dbname
INTO pgsql://postgres:password@localhost/dbname
WITH include drop, create tables, create indexes, reset sequences
-- EXCLUDING TABLE NAMES MATCHING "tabBank Statement Settings"
BEFORE LOAD DO
$$ drop table if exists csv; $$;
The last two lines seem to be required by pgloader and they no effect as long as there’s no table in your db called csv, and there shouldn’t be in a normal ERPNext installation.
1 Like
Consider using AWS DMS. It’s designed for migration between different engines.
Yes, it’s an AWS service, but you can always just pg_dump once you migrate and stop using RDS.
On the other hand, is there any specific reason why you’d want to us PG over Maria? I’m generally very pro pg mysef but I checked out the issues on GH for PG and there seem to be quite a few. It feels like PG is supported, but not really QA’ed as much, because the usage is probably less. But take what I say with a grain of salt, as I am still a noob.
Frappe and Frappe ORM is ready for Postgres, which has been the case since V12. ERPNext (and the core applications) have some work to do and I’m not sure it will be backported to V14 at this point. There’s a lot of effort to make the queries cross compatible, which is being done with pypika. If this is something you need to do in your own app, consider: GitHub - pahwaranger/sql_to_pypika
1 Like