MySQL Forums - PostgreSQL

Yelp: Migrating in-place from PostgreSQL to MySQL (no replies)

Edwin Desouza — Mon, 07 Oct 2024 16:54:42 +0000

Yelp: Migrating in-place from PostgreSQL to MySQL
- https://x.com/YelpEngineering/status/1843375083311755615
- https://engineeringblog.yelp.com/2024/10/migrating-from-postgres-to-mysql.html

Migrating from Postgres to MySQL (no replies)

Edwin Desouza — Thu, 09 Feb 2023 23:10:49 +0000

https://planetscale.com/blog/migrating-from-postgres-to-mysql

Why Uber Engineering Switched from Postgres to MySQL (no replies)

Edwin Desouza — Thu, 27 Oct 2022 23:37:39 +0000

Why Uber Engineering Switched from Postgres to MySQL
- https://www.uber.com/blog/postgres-to-mysql-migration/

Free database comparison tool (no replies)

Yonatan Sion — Mon, 21 Mar 2022 19:24:44 +0000

Hi
We have recently completed our PostgreSQL comparison tool. It compares databases – both scheme and data – and migrates them. We are giving it for free. No tricks, no Gimmicks, its just a brand new tool and we’d like to see what people think. We would be happy if you drop us a line saying what you think about it – good, bad and ugly. In which case yours for life. Meaning not just the current version which is free – but all versions we will ever release.

Our ‘Nob Hill Database Compare’ can be downloaded from
https://www.nobhillsoft.com/#prod-3

Any questions? We are at support@nobhillsoft.com

Thank You

How to Import data from Amazon Aurora PostgreSQL to MySQL Database Service in OCI (no replies)

Edwin Desouza — Mon, 13 Sep 2021 17:11:10 +0000

https://blogs.oracle.com/mysql/post/import-data-from-amazon-aurora-postgresql-to-mysql-heatwave-database-service-in-oci

https://blogs.oracle.com/mysql/post/how-to-import-data-from-amazon-redshift-to-mysql-heatwave-database-service

https://blogs.oracle.com/mysql/post/how-to-import-data-from-postgresql-to-mysql-heatwave-database-service

https://blogs.oracle.com/mysql/post/mysql-shell-generate-table-csv

How to import data from Amazon Redshift to MySQL Database Service (no replies)

Edwin Desouza — Wed, 08 Sep 2021 03:04:08 +0000

How to import data from PostgreSQL to MySQL Database Service (no replies)

Edwin Desouza — Tue, 24 Aug 2021 19:46:07 +0000

How difficult is to migrate from Postgres to MySQL (1 reply)

Vuk Bajcetic — Mon, 13 Sep 2021 17:18:27 +0000

My company is thinking about migrating to MySQL from Postgres. Im unsure if its worth or not. Are problems and difficulties common when migrating this way?

Migration from POSTGRES TO MYSQL Row size too large error in mysql create table query (1 reply)

RAJESH UPADHYAYA — Fri, 21 Sep 2018 12:52:12 +0000

Hi all,

While migrating from postgres to mysql via MYSQL WORKBENCH. I am getting this error with several tables.

Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs.
SQL Error: 1118
- Creating table local.name_datadictionary
Execute statement:
CREATE TABLE IF NOT EXISTS `local`.`name_datadictionary` (
`id` INT NOT NULL AUTO_INCREMENT,
`column_name` VARCHAR(255) NULL,
`column_format` VARCHAR(255) NULL,
`column_type` VARCHAR(255) NULL,
`field_name` VARCHAR(255) NULL,
`column_max_length` INT NULL,
`rows_with_data` INT NOT NULL,
`intrinsic_values_count` INT NULL,
`warnings` VARCHAR(1024) NULL,
`actions` VARCHAR(50) NULL,
`sufficiency_test` INT NOT NULL,
`mapping_test` TINYINT NOT NULL,
`format_test` VARCHAR(255) NULL,
`intrinsic_test` VARCHAR(255) NULL,
`datafile_id` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `name_datadictionary_eb3dffdfc66` (`datafile_id` ASC),
CONSTRAINT `name_datadictionary_datafile_id_c243sdsds5_fk_name_datafiles_id`
FOREIGN KEY (`datafile_id`)
REFERENCES `local`.`name_datafiles` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
- Creating table local.name_datadictionary_intrinsic_values
Execute statement:

Please help me how to fix this..

Thanks and Regards,
Rajesh Upadhyaya

Failed to migrate from postgresql to mysql (no replies)

Slose Lin — Fri, 05 Dec 2014 11:41:12 +0000

I have a table 'blog' in postgresql, and there are 7 columns in this table.
When migrate this table to mysql, I encourage an error as follows:

ERROR: Could not successfully convert USC-2 string to UTF-8 in table "blog" (column content).
Original string: "<"

the type of column "content" in postgresql is TEXT, and this column has HTML code as data. It seems that mysql migration tool can not perfectly convert HTML code to UTF-8 encoded string?

have any idea? thank you

postgresql migration to mysql - odbc - undefined symbol (1 reply)

David Mongan — Tue, 29 Oct 2013 21:10:33 +0000

Trying to use mySQL workbench to migrate a postgresql database to mysql.

postgresql server is 8.4.13-1 on Scientific Linux 6.4

Followed the instructions from http://dev.mysql.com/doc/workbench/en/wb-migration-database-postgresql.html

Installed psqlodbc library driver, opened the workbench GUI and entered the parameters for the source posgresql database but I get the following error when I test the connection....

Could not connect to Source DBMS.
00000] [iODBC][Driver Manager]/usr/local/lib/psqlodbcw.so: undefined symbol: PQconnectdbParams (0) (SQLDriverConnect)
ODBC connection string: DRIVER=psqlodbc;SERVER=127.0.0.1;PORT=5432;DATABASE=tracsat;UID=dwills

Any ideas?

Postgresql -> MySql: Empty Schema Selection (5 replies)

Daniel Roß — Thu, 06 Jun 2013 22:24:13 +0000

Dear all,

Iam trying to migrate my postgresql database to my mysql database.For that Iam using MySQL Workbench. After the source and target selection he didn't fetch a schemata list. On the "Schemata Selection" Page the List is empty.

What could be the reason for that? I didnt see any option could influence that.

Error occured when "reverse engineer selected schemata" for PostgreSQL 9.2.2 (4 replies)

Xi Bai — Tue, 12 Feb 2013 18:53:40 +0000

Hi,

When I tried migrating data stored on PostgreSQL with the version 9.2.2 to MySQL, I got the following error at the step of "Reverse engineer selected schemata" for both 5.2.45 and 5.2.44 on Mac OS 10.6.8:

Starting...
Connect to source DBMS...
- Connecting...
Connecting to ...
Opening ODBC connection to DSN=LocalPostgreSQLDB...
Connected
Connect to source DBMS done
Reverse engineer selected schemata....
Reverse engineering public from cssmetaselector
- Reverse engineering catalog information
- Preparing...
- Gathered stats for public
Reverse engineering tables from public
- Retrieving table public.static...
Traceback (most recent call last):
File "/Applications/MySQLWorkbench.app/Contents/PlugIns/db_postgresql_re_grt.py", line 335, in reverseEngineer
return PostgresqlReverseEngineering.reverseEngineer(connection, catalog_name, schemata_list, context)
File "/Applications/MySQLWorkbench.app/Contents/PlugIns/db_generic_re_grt.py", line 257, in reverseEngineer
cls.reverseEngineerTables(connection, schema)
File "/Applications/MySQLWorkbench.app/Contents/PlugIns/db_generic_re_grt.py", line 393, in reverseEngineerTables
cls.reverseEngineerTableIndices(connection, table)
File "/Applications/MySQLWorkbench.app/Contents/PlugIns/db_postgresql_re_grt.py", line 114, in reverseEngineerTableIndices
index_rows = cls.execute_query(connection, all_indices_query).fetchall()
File "/Applications/MySQLWorkbench.app/Contents/PlugIns/db_generic_re_grt.py", line 56, in execute_query
return cls.get_connection(connection_object).cursor().execute(query, *args, **kwargs)
pyodbc.Error: ('ODBC data type 1111 is not supported. Cannot read column indkey.', 'HY000')

Traceback (most recent call last):
File "/Applications/MySQLWorkbench.app/Contents/Resources/libraries/workbench/wizard_progress_page_widget.py", line 191, in thread_work
self.func()
File "/Applications/MySQLWorkbench.app/Contents/PlugIns/migration_schema_selection.py", line 160, in task_reveng
self.main.plan.migrationSource.reverseEngineer()
File "/Applications/MySQLWorkbench.app/Contents/PlugIns/migration.py", line 332, in reverseEngineer
self.state.sourceCatalog = self._rev_eng_module.reverseEngineer(self.connection, self.selectedCatalogName, self.selectedSchemataNames, self.state.applicationData)
SystemError: Error("('ODBC data type 1111 is not supported. Cannot read column indkey.', 'HY000')"): error calling Python module function DbPostgresqlRE.reverseEngineer
ERROR: Reverse engineer selected schemata: Error("('ODBC data type 1111 is not supported. Cannot read column indkey.', 'HY000')"): error calling Python module function DbPostgresqlRE.reverseEngineer
Failed

The error above does not give my any clue. It'll be most appreciated if someone can help me with this.

All the best

Xi

Postgre Challenge (postgreSQL to mYsql both working together. (no replies)

walter catter — Wed, 10 Oct 2012 08:57:32 +0000

Hello all, I'm having a problem planning this idea, obviously exist somewhere. I tried searching here but maybe I inserted the wrong arguments...

I got a postgre v8.3 DB server used only for basic storage like, name, surname, email, comments. This is available only for my intranet, so, access denied from outside.

I need to set up forms using this data but my postgreSQL is old and I can't change it or upgrade it(internal polices...)

I'm planning to rent a cPanel with DB. and use that mYsql as my database server.

BUT I need the data from the older PostgreSQL and I need to keep using the old for my actual old systems.

So, this is the question, can I create the mYsql database to work as a mirror? all the data inserted on PostgreSQL automatically go to mYsql and is ready to use it.

The use of this "new data" is only queries to the database to show the specific fields.

I'm not planning to feed the Database through mYsql, only on PosgreSQL.

Can I do that?, do you have the any help for this?

Much appreciated.

Let me know if it is not clear.

Converting Postgre Function to MySql (no replies)

ayuba isa — Sun, 12 Aug 2012 12:03:57 +0000

hi! i need help on converting this postgre function to mysql:

CREATE OR REPLACE FUNCTION ins_Periods() RETURNS trigger AS $$
BEGIN
INSERT INTO Period_Tax_Types (Period_ID, Tax_Type_ID, Period_Tax_Type_Name, Formural, tax_relief, percentage, linear, Employer, Employer_PS, Tax_Type_order, In_Tax, account_number)
SELECT NEW.Period_ID, Tax_Type_ID, Tax_Type_Name, Formural, tax_relief, percentage, linear, Employer, Employer_PS, Tax_Type_order, In_Tax, account_number
FROM Tax_Types
WHERE (active = true);

INSERT INTO Employee_Month (Period_ID, pay_group_ID, Entity_ID, Bank_Branch_ID, Department_Role_ID, Bank_Account, Basic_Pay)
SELECT NEW.Period_ID, 0, Entity_ID, Bank_Branch_ID, Department_Role_ID, Bank_Account, basic_salary
FROM Employees
WHERE (active = true);

PERFORM updTax(employee_month_id, Period_id)
FROM employee_month
WHERE (period_id = NEW.period_id);

RETURN NULL;
END;

thanks in anticipation

Database Migration: PostgreSQL to MySQL (no replies)

Edwin Desouza — Wed, 25 Jul 2012 03:37:58 +0000

MySQL Workbench: Database Migration
- Download: http://dev.mysql.com/downloads/workbench/
- Docs: http://dev.mysql.com/doc/workbench/en/wb-migration.html
- Migration from PostgreSQL (versions: )

PostgreSQL migration:
http://mysqlworkbench.org/2012/11/how-to-migrate-postgresql-databases-to-mysql-using-the-mysql-workbench-migration-wizard/
http://mysqlworkbench.org/2012/11/set-up-and-configure-postgresql-odbc-drivers-for-the-mysql-workbench-migration-wizard/

Presentations:
http://www.slideshare.net/mdg149/migrating-from-postgresql-to-mysql-without-downtime

Uber Migration from PostgreSQL to MySQL:
- https://eng.uber.com/postgres-to-mysql-migration/
- https://news.ycombinator.com/item?id=12166585
- https://news.ycombinator.com/item?id=17280239

Etsy Migration from PostgreSQL to MySQL:
- https://www.slideshare.net/mdg149/migrating-from-postgresql-to-mysql-without-downtime

importing database from postgres to mysql (no replies)

archana k — Wed, 06 Jun 2012 05:26:10 +0000

Hello,

I have designed a database in postgres but now i want to import the same to mysql.When i tried to import i got the following error

"/SQL query:
--
-- PostgreSQL database dump
--
SET statement_timeout =0;
MySQL said:
#1193 - Unknown system variable 'statement_timeout' /"

I dont know what is the error and how to solve it.Plzz help me

migration postgres to mysql (3 replies)

Francesco Itollo — Fri, 27 Apr 2012 16:09:58 +0000

hi ,
i've created the virtual machine with postgres and mysql ,
the wizard of migration tooltoolkit say me that general jdbc ,i download the jar of postgres where should I put ,and there are another parameters such as the connection string ?
can you help me?
Thanks you
Regards
Francesco

Postgres to mysql (2 replies)

Francesco Itollo — Sat, 02 Jun 2012 17:06:23 +0000

Hi all.

I'm trying to migrate my db in Postgres in mysql.
But i have a problem
When i must choosing the database doesn't found the "Postgres" .

How i can to migrate Postgres in Mysql using mysql migraton tools?

thank you for help
regards
Francesco

migrating mysql++ to postgres (no replies)

Qadeer Ghouri — Tue, 04 Jan 2011 07:34:20 +0000

I have been assigned a task of migrating mysql++ to postgres by creating a Data Access Layer between the C++ application and the database it is interacting with. I haven't done anything like this ever before so I am totally a novice. These are the exact wordings of my Boss.

"transition of the system from MySQL to postgres, better would be to make the database connection abstract completely, so if a client decides to buy it and connect it to an oracle database, we don't care."

The program currently uses simple queries like:

query << "delete from " << MN << "import";

query << "load data local infile '" << impFile << "' into table " << MN << "import fields terminated by ','";

query << "select count(distinct SLIP), count(distinct SLP_ID) from " << MN << "import";

query << "select TRAD from " << MN << "import where SLIP =" << clslip;

There are no joins etc anywhere involved in the current application and above are the only queries used in the application that are to be converted to postgres.

I am in real trouble .. Please Help me out . i be greatful to you.
regards,
A.Q.Ghouri

Perl script to parse Postgresql database into MySQL at XX intervals (1 reply)

Martin Thorpe — Fri, 17 Dec 2010 07:26:18 +0000

Hey all

I was hoping someone could give me some advice, I have a requirement to "parse" a Postgresql (not migrate) database (single database) into an equivalent MySQL database to be used offline for another purpose, the way that I have it working now is like this:

1) Perl (DBI) script executes every 40mins performs a SELECT on the SOURCE tables and INSERTS into MySQL (DESTINATION)

Problems I have and "not sures":

1) In my script I had to specify ALL of the column names to SELECT on (big long list) and ALL of the column names again to INSERT on (can I not just perform a SELECT * and then INSERT *?? I couldn't get this to work)

2) The SOURCE DATABASE automatically updates once every 30mins, so my script runs just after this is complete (different scheduler, not the same, so we hope they don't overlap - this is not ideal)

3) Must I use SELECT // INSERT? is this the only way? it is very slow (at the Postgresql end :-) ) is there a more efficient method? (I am using filters in my TSQL on the POSTGRESQL end but I dont need to, I only did this to cut down the query time!)

I did consider using ODBC but I'm not seeing any benefit of using this instead of the above.

Thanks all - any input would be greatfully appreciated

[OT] Curso a distancia de Administración Postgresql 9.0 Nivel 1 (no replies)

Emanuel Calvo Franco — Sun, 07 Nov 2010 15:55:40 +0000

Se realizará el 27 de Noviembre y 7 de Diciembre un curso a distancia Nivel 1 de Administración de BBDD PostgreSQL 9.0. Tiene una duración de 8hs más 4 hs de consultas repartidas en dos tandas.

Algunos de los temas aproximados a ver son:

Conocimientos Escenciales:
Introducción al Modelo Relacional, Arquitectura Cliente/Servidor
Breves comparaciones, Postgresql
Procesamiento interno y Arquitectura
Instalación
Configuración Básica del servidor y conexiones

Elementos y Administración Básicos:
Herramientas y programas estándar: PgAdmin III y psql
Migraciones de versión
Monitoreo
Mantenimiento
Respaldos
Transacciones
Entendiendo el funcionamiento de la WAL
Control de concurrencias
PITR y nociones de replicación básicas

SQL y lenguajes:
SQL Estándar (DDL y DML), PL/pgsql, objetos y jerarquia
Funciones
Introducción PL/Perl y PL/Pythonu

Elementos de Seguridad:
Seguridad en PostgreSQL
Roles: Usuarios y Grupos
Esquemas y Permisos
Vistas y Reglas
Seguridad de Datos
Comunicaciones y conexiones remotas
Restricciones a través de la configuración
Autenticación y encriptación

Elementos de Performance:
Entendiendo el EXPLAIN
Estadísticas
Afinando servidores dedicados y de desarrollo
Tuning de Consultas SQL
Optimización y técnicas de performance
Conceptos básicos de Full Text Search

Se recomienda que los que asistan tengan conocimientos de SQL básico. Es recomendable que también tengan conocimientos básicos de Linux o Windows (es decir, una plataforma de Sistema Operativo).

El día Viernes 22 de Octubre se dió una breve introducción al curso y a Postgresql 9.0. Si querés ver la charla completa acccede al siguiente link: http://connectpro65421913.emea.acrobat.com/p67117135/

Interesados en más información, enviar un mail a silvia.igon [en] gmail.com o silvia(at)emanuelcalvofranco(at)com(at)ar coordinadora de cursos, referenciando como instructor a Emanuel Calvo Franco.

También estamos organizando una serie de charlas técnicas gratuitas y abiertas. Esten al tanto de las fechas en: http://www.emanuelcalvofranco.com.ar/livestream.html#

How to transfer some data from postgresql db to mysql db? (2 replies)

Akash Chandran — Tue, 09 Nov 2010 23:08:53 +0000

I want to transfer at least posts,threads and members of my another forum postgresql db into my forum in sql db?
How can i do that?
If i completely convert postgresql db to mysql db ,then it is also good..How to do that?

PostgreSQL generate_series function (1 reply)

charmie maniar — Fri, 17 Dec 2010 07:29:10 +0000

Hi, I am migrating from postgresql to mysql and I wanted to know if there is any equivalent function to postgre's generate_series() function in mysql? If not how can I get the same functionality?

Thanks,
Charmie

Change from Postgre to MySQL query error (3 replies)

Quang Nguyen — Mon, 06 Sep 2010 07:18:53 +0000

Hi there,

I tried to switch my application from postgres to mysql. The problem is i don't know how to change below sql query to mysql.

UPDATE datavalue as d1 SET value=d2.value,storedby=d2.storedby,lastupdated=d2.lastupdated,comment=d2.comment,followup=d2.followup FROM datavalue AS d2 WHERE d1.periodid=d2.periodid AND d1.sourceid=d2.sourceid AND d1.lastupdated
I was wondering if you guys can help me out.
Thank you very much!
Best regards,
Quang

Nested query problem - ERROR 1054 (42S22): Unknown column 'users.id' in 'on clause' (no replies)

Joachim Bartosik — Fri, 30 Jul 2010 13:29:37 +0000

I had a pretty long query that worked on Postgres but doesn't work on MySQL. (Very) simplified query reproducing my problem is

SELECT * FROM users WHERE EXISTS (SELECT * FROM questions JOIN answers ON answers.owner_id = users.id);

I results in

ERROR 1054 (42S22): Unknown column 'users.id' in 'on clause'

Note: I can make the simplified query work by changing it to

SELECT * FROM users WHERE EXISTS (SELECT * FROM questions JOIN answers WHERE answers.owner_id = users.id);

However that's not possible for my original query - I'm making LEFT OUTER JOIN with a few conditions and select only records where answers.id IS NULL.

In case it matters I'm using MySQL 5.1.46 on Gentoo Linux. Could you help me to fix this?

Migration from PgSQL database to MySQL (slightly different schema though) (6 replies)

Greg Hermo — Fri, 11 Dec 2009 08:52:59 +0000

hi,

i would like to know if there is a way i could migrate data from my old PgSQL database to a new MySQL database with a slightly different schema.

i am aware that i can use scripts to convert from pgsql to mysql, however, as i've already mentioned, there has been a slight change in the database schema and i'm also using an old pgsql version.

any advice on how i could migrate my data? Thanks.

Migrating PgSQL database to MySQL (slightly different schema though) (1 reply)

Greg Hermo — Fri, 27 Nov 2009 08:46:37 +0000

Migration of postgresql to mysql (2 replies)

Mahendra Bagul — Mon, 02 Nov 2009 07:47:53 +0000

Hi,
I want to migrate our database from postgresql to mysql.If you know any migration tool or any methodology then please reply.......

It's possible to make a backup of PostgreSQL and to download in Mysql (no replies)

PIERRAT François — Wed, 26 Aug 2009 07:06:44 +0000

Hello,

I does not arrive a backup of PostgreSQL and to recover in Mysql.
I have generate a script in PostgreSQL but it function not in Mysql.
How to make?

Excuse me for the syntax errors, I'am french