mysql:
hostname: xxxxx
port: 3306
username: xxxxx
password: xxxxx
database: xxxxx
compress: false
destination:
file: /root/sql.sql
postgres:
hostname: xxxxx
port: 5432
username: xxxxx
password: xxxxx
database: xxxxx
only_tables:
- aliasassignments
- aliases
- customers
- assignments
- cash
- cashsources
- cashimport
- cashreglog
- cashregs
- cashrights
- countries
- customergroups
- customerassignments
- customercontacts
- daemonconfig
- daemoninstances
- dbinfo
- debitnotecontents
- divisions
- docrights
- documentcontents
- documents
- domains
- eventassignments
- events
- ewx_channels
- ewx_pt_config
- ewx_stm_channels
- ewx_stm_nodes
- excludedgroups
- hosts
- imessengers
- invoicecontents
- liabilities
- location_boroughs
- location_cities
- location_districts
- location_states
- location_streets
- location_street_types
- nodes
- macs
- managementurls
- messageitems
- messages
- nastypes
- netdevices
- netlinks
- networks
- nodeassignments
- nodegroupassignments
- nodegroups
- nodelocks
- nodesessions
- numberplanassignments
- numberplans
- passwd
- payments
- pna
- promotionassignments
- promotions
- promotionschemas
- receiptcontents
- records
- rtqueues
- rttickets
- users
- rtnotes
- rtmessages
- rtattachments
- rtcategories
- rtcategoryusers
- rtrights
- rtticketcategories
- sessions
- sourcefiles
- states
- stats
- supermasters
- tariffs
- taxes
- uiconfig
- up_customers
- up_help
- up_info_changes
- up_rights
- up_rights_assignments
- voipaccounts
- zipcodes
#exclude_tables:
#- customersview
#- teryt_simc
#- teryt_terc
#- teryt_ulic
#- vmacs
#- vnodes
#- nas
#- vnodes_mac
# if supress_data is true, only the schema definition will be exported/migrated, and not the data
supress_data: false
# if supress_ddl is true, only the data will be exported/imported, and not the schema
supress_ddl: true
# if force_truncate is true, forces a table truncate before table loading
force_truncate: true
# if timezone is true, forces to append/convert to UTC tzinfo mysql data
timezone: true
################################################
Ważne jest:
- żeby zachować dobrą kolejność tabel przy wypisywaniu ich w confingu (chodzi o zależności w kluczach),
- żeby nie dopisywać do listy widoków.
Widziałem, że w GIT są już nowe tabele, więc trzeba je dodać, jeśli chcemy je również przenieść.
Taki plik obrabiałem jeszcze czymś takim (Chodzi o błędy 'invalid input syntax for integer: "f"'):
sed -i 's/\tf\t/\t0\t/g' /root/sql.sql
sed -i 's/\tt\t/\t1\t/g' /root/sql.sql
Zamieniało mi to wszystkie 't' na 1, 'f' na 0.
Wynikowy plik wrzucałem do PostgreSQLa i na końcu zostało już tylko zaktualizować sekwencje (też trzeba dopisać nowe sekwencje z GIT'a):
################################################
LISTA_SEQ="aliasassignments_id_seq aliases_id_seq assignments_id_seq cash_id_seq cashimport_id_seq cashreglog_id_seq cashregs_id_seq cashrights_id_seq cashsources_id_seq countries_id_seq customerassignments_id_seq customercontacts_id_seq customergroups_id_seq customers_id_seq daemonconfig_id_seq daemoninstances_id_seq debitnotecontents_id_seq divisions_id_seq docrights_id_seq documents_id_seq domains_id_seq events_id_seq ewx_channels_id_seq ewx_pt_config_id_seq ewx_stm_channels_id_seq ewx_stm_nodes_id_seq excludedgroups_id_seq hosts_id_seq imessengers_id_seq liabilities_id_seq location_boroughs_id_seq location_cities_id_seq location_districts_id_seq location_states_id_seq location_street_types_id_seq location_streets_id_seq macs_id_seq managementurls_id_seq messageitems_id_seq messages_id_seq nastypes_id_seq netdevices_id_seq netlinks_id_seq networks_id_seq nodeassignments_id_seq nodegroupassignments_id_seq nodegroups_id_seq nodelocks_id_seq nodes_id_seq nodesessions_id_seq numberplanassignments_id_seq numberplans_id_seq passwd_id_seq payments_id_seq pna_id_seq promotionassignments_id_seq promotions_id_seq promotionschemas_id_seq records_id_seq rtcategories_id_seq rtcategoryusers_id_seq rtmessages_id_seq rtnotes_id_seq rtqueues_id_seq rtrights_id_seq rtticketcategories_id_seq rttickets_id_seq sourcefiles_id_seq states_id_seq supermasters_id_seq tariffs_id_seq taxes_id_seq uiconfig_id_seq up_customers_id_seq up_help_id_seq up_info_changes_id_seq up_rights_assignments_id_seq up_rights_id_seq users_id_seq voipaccounts_id_seq zipcodes_id_seq"
for SEQ in $LISTA_SEQ; do
KROTKA="id"
SEQ_TABELA=`echo "$SEQ" | sed 's/_id_seq//'`
echo "SELECT setval('${SEQ}', coalesce((select max(${KROTKA})+1 from ${SEQ_TABELA}), 1), true);"
done
################################################