Changes between Version 9 and Version 10 of HowToMoveFromMySQLToPostgres

Timestamp:

Jul 13, 2009, 12:28:28 AM (15 years ago)

Author:

heikki

Comment:

--

HowToMoveFromMySQLToPostgres

@@ -102 +102 @@
 Many thanks to Thijs Brentjens who gave this excellent tip, I've found a relatively straightforward way of doing it using the tool [http://www.gdal.org/ogr2ogr.html ogr2ogr] which is part of the [http://www.gdal.org/index.html GDAL] tool suite. The ogr2ogr build I found on their web site does not include drivers for MySQL and Postgress though. Downloading the tool suite [http://fwtools.maptools.org/ FWTools], which includes GDAL, solved this.
 
+Using ogr2ogr, you can simply replicate a MySQL database into a Postgres database. The end result needs some massaging, but not much.
+
+Assuming you already created a Postgres database 'geonetwork' and a Postgres user 'postgressusername', you issue a command like
+
+{{{
+   ogr2ogr -overwrite -update -f "PostgreSQL" PG:"host=localhost user=postgressusername dbname=geonetwork password=postgressuserpassword" MYSQL:"geonetworkdbname,host=localhost,user=mysqlusername,password=mysqluserpassword,port=3306"
+}}}
+
+This creates the table structure in Postgres and copies the data into it. 
+
+There are only 2 little tweaks necessary to make things work :
+
+ * each table in Postgres gets a column {{{wkb_geometry}}} that was not there in your original, MySQL table
+ * Primary key column {{{id}}} gets called {{{ogc_fid}}} in Postgres
+
+The {{{wkb_geometry}}} columns can be fixed using this sql (thanks Thijs Brentjens) :
+
+{{{
+-- In pgsql:
+-- create the SQL commands:
+
+select 'ALTER TABLE ' || table_name || ' drop column wkb_geometry;' from information_schema.tables where table_schema='public';
+
+-- save the results to file and/or execute it directly:
+ALTER TABLE isolanguages drop column wkb_geometry;
+ALTER TABLE categories drop column wkb_geometry;
+ALTER TABLE categoriesdes drop column wkb_geometry;
+ALTER TABLE groups drop column wkb_geometry;
+ALTER TABLE groupsdes drop column wkb_geometry;
+ALTER TABLE languages drop column wkb_geometry;
+ALTER TABLE isolanguagesdes drop column wkb_geometry;
+ALTER TABLE monitoringrequests drop column wkb_geometry;
+ALTER TABLE monitoringservices drop column wkb_geometry;
+ALTER TABLE metadata drop column wkb_geometry;
+ALTER TABLE metadatacateg drop column wkb_geometry;
+ALTER TABLE metadatarating drop column wkb_geometry;
+ALTER TABLE operationallowed drop column wkb_geometry;
+ALTER TABLE monitoringlog drop column wkb_geometry;
+ALTER TABLE monitoringstatsgroups drop column wkb_geometry;
+ALTER TABLE operations drop column wkb_geometry;
+ALTER TABLE operationsdes drop column wkb_geometry;
+ALTER TABLE regions drop column wkb_geometry;
+ALTER TABLE usergroups drop column wkb_geometry;
+ALTER TABLE regionsdes drop column wkb_geometry;
+ALTER TABLE relations drop column wkb_geometry;
+ALTER TABLE settings drop column wkb_geometry;
+ALTER TABLE users drop column wkb_geometry;
+ALTER TABLE sources drop column wkb_geometry;
+}}}
+
+
+
+
+
 === Links to related information ===