4 ms·

I'm stuck on MySQL because converting to postgres is hard, the tool everyone recommends (pgloader) doesn't work with current MySQL (https://github.com/dimitri/p

I'm stuck on MySQL because converting to postgres is hard, the tool everyone recommends (pgloader) doesn't work with current MySQL (https://github.com/dimitri/pgloader/issues/782 https://github.com/dimitri/pgloader/issues/782), anyone know another way?

captain_coffee 9mo ago
How big of a DB are we talking about? You might need to recreate the whole DB schema / structure manually from scratch in PostgreSQL and then dump the data and load it in PG via standard SQL file exports in the correct table order to avoid failures due to FK constraints. This is a gross oversimplification but you get the gist
- 1over137 9mo ago
  In fact I don't get the gist :) but thanks for your reply. All I know about databases is following instructions to set one up as part of a LAMP/FAMP installation.
  - mlinster 9mo ago
    Unless you use stored procedures in MySQL, recreating the tables and schemas in Postgres should be straightforward. Postgres now offers a 'MySQL Adapter', a.k.a. Foreign Data Wrapper (https://github.com/EnterpriseDB/mysql_fdw https://github.com/EnterpriseDB/mysql_fdw), that makes it straightforward to load data from MySQL into Postgres. I know you mentioned that all you know about databases is following instructions, but maybe the folks at EDB or Percona can give you a hand. Postgres will be around for a long time, and I think it's pretty obvious that MySQL won't.
    - 1over137 9mo ago
      I'll read up on that, thanks!
  - jbverschoor 9mo ago
    Prepare to be replaced by ai
    - 1over137 9mo ago
      haha. I have lots of other valuable skills. There's more to programming & IT than just db administration you know.
- afiori 9mo ago
  What happens if there are cycles of FK constraints between rows?
homebrewer 9mo ago
It does work, I just used pgloader to migrate a 16 GB database from MySQL 8.4 to PostgreSQL 18 last week (around 700 tables). It's not big, but their problems seem to be with authentication, not database size or functionality. Have you tried it doing it yourself?
- 1over137 9mo ago
  I have tried. It fails in authentication like that ticket describes, so I seem to be stuck on square one.

captain_coffee 9mo ago

How big of a DB are we talking about? You might need to recreate the whole DB schema / structure manually from scratch in PostgreSQL and then dump the data and load it in PG via standard SQL file exports in the correct table order to avoid failures due to FK constraints. This is a gross oversimplification but you get the gist

1over137 9mo ago
In fact I don't get the gist :) but thanks for your reply. All I know about databases is following instructions to set one up as part of a LAMP/FAMP installation.
- mlinster 9mo ago
  Unless you use stored procedures in MySQL, recreating the tables and schemas in Postgres should be straightforward. Postgres now offers a 'MySQL Adapter', a.k.a. Foreign Data Wrapper (https://github.com/EnterpriseDB/mysql_fdw https://github.com/EnterpriseDB/mysql_fdw), that makes it straightforward to load data from MySQL into Postgres. I know you mentioned that all you know about databases is following instructions, but maybe the folks at EDB or Percona can give you a hand. Postgres will be around for a long time, and I think it's pretty obvious that MySQL won't.
  - 1over137 9mo ago
    I'll read up on that, thanks!
- jbverschoor 9mo ago
  Prepare to be replaced by ai
  - 1over137 9mo ago
    haha. I have lots of other valuable skills. There's more to programming & IT than just db administration you know.
afiori 9mo ago
What happens if there are cycles of FK constraints between rows?

homebrewer 9mo ago

It does work, I just used pgloader to migrate a 16 GB database from MySQL 8.4 to PostgreSQL 18 last week (around 700 tables). It's not big, but their problems seem to be with authentication, not database size or functionality. Have you tried it doing it yourself?

1over137 9mo ago
I have tried. It fails in authentication like that ticket describes, so I seem to be stuck on square one.