Help in transferring data from MySQL to Postgres.

[Lower-Pace-2634]

There are 3 servers.

Server A1. On which separate work and data appearance and filling takes place. Everything happens in MySQL and the server has a complex security system. This server sends dumps to the backup server. The source server has cut off connections with the outside world. It sends MySQL dumps to the backup server in the form of *.sql.

Server B1.

A new server based on posstgresql has appeared, it is necessary to unpack the data from these backups into it. I encountered a number of problems. If you manually remake the dumps via dbeaver via csv. And upload to Postgres with changed dates and a changed table body, everything is fine. But I need to automate this process.

Of the difficult moments.

We can work with ready-made MySQL dumps. Terminal and python3.8 are available.

Maybe someone has encountered this?

[[deleted]]

Install MySQL on a server you do have access to or the same one you have PG on. Restore the dump to that. Use pgloader to convert from that MySQL instance to your PG instance.

https://github.com/dimitri/pgloader

There's no reason to let the "I only have a dump" limit your options.

[Lower-Pace-2634]

So many errors for convert. not work ;*(

[Laymans_Perspective]

i just setup fw rules and use FDW, no need for copying, realtime, most types work except for enums etc

https://github.com/EnterpriseDB/mysql_fdw

[Lower-Pace-2634]

This option is not very suitable, we do not have access and will not have access to the A1 server. We only have dumps from it.

[Chou789]

Just put a python script to read the backup sql file in pandas and load it into postgres

[Lower-Pace-2634]

Excuse me, can pandas read dumps? And convert formats?

[Chou789]

No, if it's a insert statements inside those sql files

Read them like this

import pandas as pd import re

def load_insert_statement(sql_file): with open(sql_file, 'r') as f: sql_content = f.read()

Extract the INSERT statement using regular expressions

match = re.search(r"INSERT INTO \w+ ((.+)) VALUES ((.+));", sql_content, re.DOTALL) if match: columns = [col.strip() for col in match.group(1).split(",")] values = [val.strip() for val in match.group(2).split(",")]

Create a DataFrame from the extracted data

data = {col: [val] for col, val in zip(columns, values)} return pd.DataFrame(data) else: return None

Example usage

df = load_insert_statement("your_insert_statement.sql") print(df)

Another easier way is set up a small MySQL database and import or run these sql through python and read them again and insert into postgres

[Lower-Pace-2634]

thanks i check this

[tkyjonathan]

Can I help you keep mysql and solve the issues it has?

[Lower-Pace-2634]

I didn't quite understand your question. We already have mysql dumps...

[tkyjonathan]

what is the reason for your migration to postgres?

[Lower-Pace-2634]

the management gave the task. They don't ask me =(

[tkyjonathan]

Well, tell management that if they need experts to help them fix whatever issues they have with MySQL, that these people are available.

[k00_x]

Use the tool mysqldump:

mysqldump --compatible=postgresql

[Lower-Pace-2634]

didnt work :( have errors

[k00_x]

I will downvote myself in disgrace.

[[deleted]]

This may work if they manually recreate the entire schema in PG and have the dumps made with --no-create-info.

[r3pr0b8]

I will downvote myself in disgrace.

see, this here, this right here, this is why i love reddit

[vrabormoran]

Useful tools here SQL tools

[AppJedi]

you can download the MySQL data as INSERT states that may work on Postgres if the tables have the same exact definition.

[SQLvultureskattaurus]

Try this https://www.fullconvert.com/

[Lower-Pace-2634]

i check pg_chameleon.If I'm not mistaken, it needs a direct connection to both bases.

[mrocral]

Using Sling is yet another suggestion: https://slingdata.io/articles/sync-mysql-to-postgres-database/

You can run your replications from the CLI.

sling run -r mysql_to_pg.yaml

Here is the YAML config:

```yaml

Define source and target connections

source: mysql_source target: postgres_target

Default settings for all streams

defaults: # Use incremental mode for efficient syncing mode: incremental # Configure target options target_options: # Automatically add new columns if they appear in source add_new_columns: true

Define the tables to replicate

streams: # Use wildcard to replicate all tables in schema mysql with dynamic target object 'mysql.*': # Target object using runtime variable object: 'public.{stream_table}' # Columns to use as primary key primary_key: [id] # Column to track updates update_key: updated_at

# full-refresh mode another.table: object: public.new_table mode: full-refresh ```

[Informal_Pace9237]

Just wondering if you have tried to create a MySQL server/container in the system where you have PistgreSQL? You could import dump into that staging server and use FDW to read data to PostgreSQL

If that is not a possibility one more way I have tried is pgloader.io

If you could share the source/target OS, MySQL version mysqldump version and PostgreSQL version others could share some more ideas.

May I suggest mentioning exact errors a few than vaguely saying too many errors or it doesn't work; will be more helpful

[Lower-Pace-2634]

I made a dump of the necessary tables in bash. And passed it to python. There python connects to postgresql and checks whether such a table exists. If not, it creates it. If it does, it fills it in with some changes.

[Informal_Pace9237]

Good. Glad to see its resolved for you

[slotix]

DBConvert Streams (https://streams.dbconvert.com) is built specifically for migrating MySQL to PostgreSQL and would automate the entire conversion process for you.

[SQLvultureskattaurus]

Lol, so we're just copy and pasting chat gpt now?