Migração de MySQL para PostgreSQL

A migração de MySQL para PostgreSQL é facilmente (e rapidamente) alcançada com o script Perl mysql2pgsql.

http://pgfoundry.org/projects/mysql2pgsql/

Ele faz a conversão de um dump de MySQL (gerado pelo utilitário do MySQL mysqldump) em um arquivo com comandos SQL traduzidos para o PostgreSQL.

No exemplo abaixo, gerei o dump com o nome de mysql.sql, e pedi para gerar o arquivo para o PostgreSQL com o nome pg.sql.

Geração do dump em MySQL:

C:temp>mysqldump -u admin -pMinhaSenha -h 192.168.1.11 -d zabbix > zabbix.sql

Tradução do dump:

C:temp>perl mysql2pgsql.perl mysql.sql pg.sql
table "acknowledges" will be dropped CASCADE
table "actions" will be dropped CASCADE
table "alerts" will be dropped CASCADE
table "applications" will be dropped CASCADE
table "auditlog" will be dropped CASCADE
table "conditions" will be dropped CASCADE
table "config" will be dropped CASCADE
table "dchecks" will be dropped CASCADE
table "dhosts" will be dropped CASCADE
table "drules" will be dropped CASCADE
table "dservices" will be dropped CASCADE
table "escalations" will be dropped CASCADE
table "events" will be dropped CASCADE
table "functions" will be dropped CASCADE
table "graphs" will be dropped CASCADE
table "graphs_items" will be dropped CASCADE
table "groups" will be dropped CASCADE
table "help_items" will be dropped CASCADE
table "history" will be dropped CASCADE
table "history_log" will be dropped CASCADE
table "history_str" will be dropped CASCADE
table "history_str_sync" will be dropped CASCADE
table "history_sync" will be dropped CASCADE
table "history_text" will be dropped CASCADE
table "history_uint" will be dropped CASCADE
table "history_uint_sync" will be dropped CASCADE
table "hosts" will be dropped CASCADE
table "hosts_groups" will be dropped CASCADE
table "hosts_profiles" will be dropped CASCADE
table "hosts_profiles_ext" will be dropped CASCADE
table "hosts_templates" will be dropped CASCADE
table "housekeeper" will be dropped CASCADE
table "httpstep" will be dropped CASCADE
table "httpstepitem" will be dropped CASCADE
table "httptest" will be dropped CASCADE
table "httptestitem" will be dropped CASCADE
table "ids" will be dropped CASCADE
table "images" will be dropped CASCADE
table "items" will be dropped CASCADE
table "items_applications" will be dropped CASCADE
table "mappings" will be dropped CASCADE
table "media" will be dropped CASCADE
table "media_type" will be dropped CASCADE
table "node_cksum" will be dropped CASCADE
table "nodes" will be dropped CASCADE
table "opconditions" will be dropped CASCADE
table "operations" will be dropped CASCADE
table "profiles" will be dropped CASCADE
table "proxy_dhistory" will be dropped CASCADE
table "proxy_history" will be dropped CASCADE
table "rights" will be dropped CASCADE
table "screens" will be dropped CASCADE
table "screens_items" will be dropped CASCADE
table "scripts" will be dropped CASCADE
table "service_alarms" will be dropped CASCADE
table "services" will be dropped CASCADE
table "services_links" will be dropped CASCADE
table "services_times" will be dropped CASCADE
table "sessions" will be dropped CASCADE
table "slides" will be dropped CASCADE
table "slideshows" will be dropped CASCADE
table "sysmaps" will be dropped CASCADE
table "sysmaps_elements" will be dropped CASCADE
table "sysmaps_link_triggers" will be dropped CASCADE
table "sysmaps_links" will be dropped CASCADE
table "trends" will be dropped CASCADE
table "trends_uint" will be dropped CASCADE
table "trigger_depends" will be dropped CASCADE
table "triggers" will be dropped CASCADE
table "users" will be dropped CASCADE
table "users_groups" will be dropped CASCADE
table "usrgrp" will be dropped CASCADE
table "valuemaps" will be dropped CASCADE

Importação para o PostgreSQL:

-bash-3.2$ psql < pg.sql
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "acknowledges_pkey" for table "acknowledges"
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "actions_pkey" for table "actions"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "alerts_pkey" for table "alerts"
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "applications_pkey" for table "applications"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "applications_hostid_key" for table "applications"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "auditlog_pkey" for table "auditlog"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "conditions_pkey" for table "conditions"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "config_pkey" for table "config"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "dchecks_pkey" for table "dchecks"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "dhosts_pkey" for table "dhosts"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "drules_pkey" for table "drules"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "dservices_pkey" for table "dservices"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "escalations_pkey" for table "escalations"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "events_pkey" for table "events"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "functions_pkey" for table "functions"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "graphs_pkey" for table "graphs"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "graphs_items_pkey" for table "graphs_items"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "groups_pkey" for table "groups"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "help_items_pkey" for table "help_items"
CREATE TABLE
DROP TABLE
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "history_log_pkey" for table "history_log"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "history_log_itemid_key" for table "history_log"
CREATE TABLE
CREATE INDEX
DROP TABLE
CREATE TABLE
CREATE INDEX
DROP TABLE
DROP SEQUENCE
CREATE SEQUENCE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "history_str_sync_pkey" for table "history_str_sync"
CREATE TABLE
CREATE INDEX
DROP TABLE
DROP SEQUENCE
CREATE SEQUENCE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "history_sync_pkey" for table "history_sync"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "history_text_pkey" for table "history_text"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "history_text_itemid_key" for table "history_text"
CREATE TABLE
CREATE INDEX
DROP TABLE
CREATE TABLE
CREATE INDEX
DROP TABLE
DROP SEQUENCE
CREATE SEQUENCE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "history_uint_sync_pkey" for table "history_uint_sync"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "hosts_pkey" for table "hosts"
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "hosts_groups_pkey" for table "hosts_groups"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "hosts_profiles_pkey" for table "hosts_profiles"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "hosts_profiles_ext_pkey" for table "hosts_profiles_ext"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "hosts_templates_pkey" for table "hosts_templates"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "hosts_templates_hostid_key" for table "hosts_templates"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "housekeeper_pkey" for table "housekeeper"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "httpstep_pkey" for table "httpstep"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "httpstepitem_pkey" for table "httpstepitem"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "httpstepitem_httpstepid_key" for table "httpstepitem"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "httptest_pkey" for table "httptest"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "httptestitem_pkey" for table "httptestitem"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "httptestitem_httptestid_key" for table "httptestitem"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "ids_pkey" for table "ids"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "images_pkey" for table "images"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "items_pkey" for table "items"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "items_hostid_key" for table "items"
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "items_applications_pkey" for table "items_applications"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "mappings_pkey" for table "mappings"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "media_pkey" for table "media"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "media_type_pkey" for table "media_type"
CREATE TABLE
DROP TABLE
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "nodes_pkey" for table "nodes"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "opconditions_pkey" for table "opconditions"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "operations_pkey" for table "operations"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "profiles_pkey" for table "profiles"
CREATE TABLE
CREATE INDEX
DROP TABLE
DROP SEQUENCE
CREATE SEQUENCE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "proxy_dhistory_pkey" for table "proxy_dhistory"
CREATE TABLE
CREATE INDEX
DROP TABLE
DROP SEQUENCE
CREATE SEQUENCE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "proxy_history_pkey" for table "proxy_history"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "rights_pkey" for table "rights"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "screens_pkey" for table "screens"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "screens_items_pkey" for table "screens_items"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "scripts_pkey" for table "scripts"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "service_alarms_pkey" for table "service_alarms"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "services_pkey" for table "services"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "services_links_pkey" for table "services_links"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "services_links_serviceupid_key" for table "services_links"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "services_times_pkey" for table "services_times"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sessions_pkey" for table "sessions"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "slides_pkey" for table "slides"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "slideshows_pkey" for table "slideshows"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sysmaps_pkey" for table "sysmaps"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sysmaps_elements_pkey" for table "sysmaps_elements"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sysmaps_link_triggers_pkey" for table "sysmaps_link_triggers"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "sysmaps_link_triggers_linkid_key" for table "sysmaps_link_triggers"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "sysmaps_links_pkey" for table "sysmaps_links"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "trends_pkey" for table "trends"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "trends_uint_pkey" for table "trends_uint"
CREATE TABLE
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "trigger_depends_pkey" for table "trigger_depends"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "triggers_pkey" for table "triggers"
CREATE TABLE
CREATE INDEX
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_groups_pkey" for table "users_groups"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "usrgrp_pkey" for table "usrgrp"
CREATE TABLE
CREATE INDEX
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "valuemaps_pkey" for table "valuemaps"
CREATE TABLE
CREATE INDEX
-bash-3.2$

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.