Mi było wygodniej zrobić tak jak robisz przy pierwszym scenariuszu, mój plik :
################################################ 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 ################################################