search

dimitri / pgloader

Migrate to PostgreSQL in a single command!
http://pgloader.io
Other
5.46k stars 548 forks source link

pgloader 3.4.1 Hanging on an empty table? #604

Closed leematos closed 7 years ago

leematos commented 7 years ago 
LOAD DATABASE
        FROM mysql://
        INTO postgresql://

WITH    batch rows = 1000,
        batch size = 512 MB,
        prefetch rows = 20000,
      include no drop, truncate, disable triggers, create no tables,
      create no indexes, preserve index names, no foreign keys,
      data only

CAST type int to integer,
     type int with extra auto_increment to integer,
     type datetime to timestamp using zero-dates-to-null,
     type bigint to bigint

ALTER SCHEMA 'gitlabhq_production' RENAME TO 'public'

;

We noticed it stops here, and if you strace the pgloader process it's just sleeping.

2017-07-27T13:31:19.493000-07:00 DEBUG SET client_encoding TO 'utf8'

2017-07-27T13:31:19.493000-07:00 DEBUG SET application_name TO 'pgloader'

2017-07-27T13:31:19.493000-07:00 NOTICE SET search_path TO public;

2017-07-27T13:31:19.493000-07:00 NOTICE ALTER TABLE namespace_statistics DISABLE TRIGGER ALL;

2017-07-27T13:31:19.493000-07:00 INFO pgsql:copy-rows-from-queue[0]: public.namespace_statistics

shared_runners_seconds

shared_runners_seconds_last_reset)

2017-07-27T13:31:19.493000-07:00 DEBUG stop public.web_hooks | 1105756 .. 1106305 = 0.54

We confirmed that this table is empty in mysql, so I suspect that it's something to do with that?

We attempted to exclude it, to see if we could get further, but due to the DB structure pgloader was upset because other tables depended on namespace_statistics existing.

I assume pgloader should handle empty tables?

dimitri commented 7 years ago

I just tested locally, in case, and it works without a glitch. So it's not because it's empty, any other clue? Can you share the create table statement so that I can reproduce here maybe?

leematos commented 7 years ago

Nothing else sticks out to me, but would love any insight. I've attached the db schema here:

gitlab_mysql_structure.txt

dimitri commented 7 years ago

Just tried locally with your data structure, and it went pretty well. It must be still something else. You have nothing more interesting in the logs output, maybe a backtrace?

2017-08-09T11:56:54.008158+02:00 WARNING PostgreSQL warning: identifier "idx_197351_index_chat_names_on_service_id_and_team_id_and_chat_id" will be truncated to "idx_197351_index_chat_names_on_service_id_and_team_id_and_chat_"
2017-08-09T11:56:54.213419+02:00 WARNING PostgreSQL warning: identifier "idx_197369_index_ci_builds_on_commit_id_and_stage_idx_and_created_at" will be truncated to "idx_197369_index_ci_builds_on_commit_id_and_stage_idx_and_creat"
2017-08-09T11:56:54.213912+02:00 WARNING PostgreSQL warning: identifier "idx_197369_index_ci_builds_on_commit_id_and_type_and_name_and_ref" will be truncated to "idx_197369_index_ci_builds_on_commit_id_and_type_and_name_and_r"
2017-08-09T11:56:54.217039+02:00 WARNING PostgreSQL warning: identifier "idx_197389_index_ci_pipeline_schedules_on_next_run_at_and_active" will be truncated to "idx_197389_index_ci_pipeline_schedules_on_next_run_at_and_activ"
2017-08-09T11:56:54.436597+02:00 WARNING PostgreSQL warning: identifier "idx_197451_index_deployments_on_project_id_and_environment_id_and_iid" will be truncated to "idx_197451_index_deployments_on_project_id_and_environment_id_a"
2017-08-09T11:56:55.307398+02:00 WARNING PostgreSQL warning: identifier "idx_197648_index_merge_requests_closing_issues_on_merge_request_id" will be truncated to "idx_197648_index_merge_requests_closing_issues_on_merge_request"
2017-08-09T11:56:55.309042+02:00 WARNING PostgreSQL warning: identifier "idx_197663_index_merge_request_metrics_on_first_deployed_to_production_at" will be truncated to "idx_197663_index_merge_request_metrics_on_first_deployed_to_pro"
2017-08-09T11:56:55.314977+02:00 WARNING PostgreSQL warning: identifier "idx_197678_index_namespaces_on_ldap_sync_last_successful_update_at" will be truncated to "idx_197678_index_namespaces_on_ldap_sync_last_successful_update"
2017-08-09T11:56:55.316008+02:00 WARNING PostgreSQL warning: identifier "idx_197678_index_namespaces_on_require_two_factor_authentication" will be truncated to "idx_197678_index_namespaces_on_require_two_factor_authenticatio"
2017-08-09T11:56:55.321641+02:00 WARNING PostgreSQL warning: identifier "idx_197712_index_notifications_on_user_id_and_source_id_and_source_type" will be truncated to "idx_197712_index_notifications_on_user_id_and_source_id_and_sou"
2017-08-09T11:56:55.322016+02:00 WARNING PostgreSQL warning: identifier "idx_197712_index_notification_settings_on_source_id_and_source_type" will be truncated to "idx_197712_index_notification_settings_on_source_id_and_source_"
2017-08-09T11:56:55.527021+02:00 WARNING PostgreSQL warning: identifier "idx_197750_fk_oauth_openid_requests_oauth_access_grants_access_grant_id" will be truncated to "idx_197750_fk_oauth_openid_requests_oauth_access_grants_access_"
2017-08-09T11:56:55.536308+02:00 WARNING PostgreSQL warning: identifier "idx_197810_index_project_authorizations_on_user_id_project_id_access_level" will be truncated to "idx_197810_index_project_authorizations_on_user_id_project_id_a"
2017-08-09T11:56:55.745290+02:00 WARNING PostgreSQL warning: identifier "idx_197859_index_protected_branch_merge_access_levels_on_user_id" will be truncated to "idx_197859_index_protected_branch_merge_access_levels_on_user_i"
2017-08-09T11:56:55.966847+02:00 WARNING PostgreSQL warning: identifier "idx_197992_index_subscriptions_on_subscribable_and_user_id_and_project_id" will be truncated to "idx_197992_index_subscriptions_on_subscribable_and_user_id_and_"
2017-08-09T11:56:56.171280+02:00 WARNING PostgreSQL warning: identifier "idx_198004_index_taggings_on_taggable_id_and_taggable_type_and_context" will be truncated to "idx_198004_index_taggings_on_taggable_id_and_taggable_type_and_"
2017-08-09T11:56:56.386373+02:00 WARNING PostgreSQL warning: identifier "idx_198101_index_user_agent_details_on_subject_id_and_subject_type" will be truncated to "idx_198101_index_user_agent_details_on_subject_id_and_subject_t"
2017-08-09T11:56:56.606677+02:00 LOG report summary reset
                                 table name       read   imported     errors      total time
-------------------------------------------  ---------  ---------  ---------  --------------
                            fetch meta data        470        470          0          0.238s 
                             Create Schemas          0          0          0          0.001s 
                           Create SQL Types          0          0          0          0.000s 
                              Create tables        188        188          0          0.423s 
                             Set Table OIDs         94         94          0          0.003s 
-------------------------------------------  ---------  ---------  ---------  --------------
                       gitlab.abuse_reports          0          0          0          0.385s 
                         gitlab.appearances          0          0          0          0.385s 
                gitlab.application_settings          0          0          0          0.038s 
                           gitlab.approvals          0          0          0          0.030s 
                           gitlab.approvers          0          0          0          0.036s 
                     gitlab.approver_groups          0          0          0          0.032s 
                        gitlab.audit_events          0          0          0          0.045s 
                         gitlab.award_emoji          0          0          0          0.045s 
                              gitlab.boards          0          0          0          0.039s 
                  gitlab.broadcast_messages          0          0          0          0.038s 
                          gitlab.chat_names          0          0          0          0.038s 
                          gitlab.chat_teams          0          0          0          0.038s 
                           gitlab.ci_builds          0          0          0          0.088s 
                        gitlab.ci_pipelines          0          0          0          0.050s 
               gitlab.ci_pipeline_schedules          0          0          0          0.096s 
                          gitlab.ci_runners          0          0          0          0.061s 
                  gitlab.ci_runner_projects          0          0          0          0.051s 
                         gitlab.ci_triggers          0          0          0          0.062s 
                 gitlab.ci_trigger_requests          0          0          0          0.038s 
                        gitlab.ci_variables          0          0          0          0.054s 
              gitlab.container_repositories          0          0          0          0.051s 
                         gitlab.deployments          0          0          0          0.046s 
                gitlab.deploy_keys_projects          0          0          0          0.042s 
                              gitlab.emails          0          0          0          0.045s 
                        gitlab.environments          0          0          0          0.044s 
                              gitlab.events          0          0          0          0.070s 
                gitlab.forked_project_links          0          0          0          0.046s 
                           gitlab.geo_nodes          0          0          0          0.091s 
                     gitlab.historical_data          0          0          0          0.092s 
                          gitlab.identities          0          0          0          0.037s 
                      gitlab.index_statuses          0          0          0          0.031s 
                              gitlab.issues          0          0          0          0.040s 
                     gitlab.issue_assignees          0          0          0          0.037s 
                       gitlab.issue_metrics          0          0          0          0.226s 
                                gitlab.keys          0          0          0          0.139s 
                              gitlab.labels          0          0          0          0.113s 
                         gitlab.label_links          0          0          0          0.059s 
                    gitlab.label_priorities          0          0          0          0.061s 
                    gitlab.ldap_group_links          0          0          0          0.059s 
                         gitlab.lfs_objects          0          0          0          0.053s 
                gitlab.lfs_objects_projects          0          0          0          0.038s 
                            gitlab.licenses          0          0          0          0.045s 
                               gitlab.lists          0          0          0          0.051s 
                             gitlab.members          0          0          0          0.046s 
                      gitlab.merge_requests          0          0          0          0.058s 
       gitlab.merge_requests_closing_issues          0          0          0          0.060s 
                 gitlab.merge_request_diffs          0          0          0          0.093s 
               gitlab.merge_request_metrics          0          0          0          0.062s 
                          gitlab.milestones          0          0          0          0.040s 
                          gitlab.namespaces          0          0          0          0.041s 
                gitlab.namespace_statistics          0          0          0          0.055s 
                               gitlab.notes          0          0          0          0.059s 
               gitlab.notification_settings          0          0          0          0.052s 
                 gitlab.oauth_access_grants          0          0          0          0.059s 
                 gitlab.oauth_access_tokens          0          0          0          0.052s 
                  gitlab.oauth_applications          0          0          0          0.064s 
               gitlab.oauth_openid_requests          0          0          0          0.062s 
                       gitlab.pages_domains          0          0          0          0.041s 
                          gitlab.path_locks          0          0          0          0.036s 
              gitlab.personal_access_tokens          0          0          0          0.038s 
                            gitlab.projects          0          0          0          0.042s 
              gitlab.project_authorizations          0          0          0          0.067s 
                    gitlab.project_features          0          0          0          0.080s 
                 gitlab.project_group_links          0          0          0          0.049s 
                 gitlab.project_import_data          0          0          0          0.053s 
                  gitlab.project_statistics          0          0          0          0.038s 
                  gitlab.protected_branches          0          0          0          0.041s 
gitlab.protected_branch_merge_access_levels          0          0          0          0.035s 
 gitlab.protected_branch_push_access_levels          0          0          0          0.042s 
                      gitlab.protected_tags          0          0          0          0.041s 
  gitlab.protected_tag_create_access_levels          0          0          0          0.040s 
                          gitlab.push_rules          0          0          0          0.039s 
                     gitlab.redirect_routes          0          0          0          0.050s 
                            gitlab.releases          0          0          0          0.053s 
                      gitlab.remote_mirrors          0          0          0          0.045s 
                              gitlab.routes          0          0          0          0.041s 
                   gitlab.schema_migrations          0          0          0          0.039s 
                  gitlab.sent_notifications          0          0          0          0.039s 
                            gitlab.services          0          0          0          0.034s 
                            gitlab.snippets          0          0          0          0.035s 
                           gitlab.spam_logs          0          0          0          0.044s 
                       gitlab.subscriptions          0          0          0          0.044s 
                gitlab.system_note_metadata          0          0          0          0.038s 
                            gitlab.taggings          0          0          0          0.038s 
                                gitlab.tags          0          0          0          0.050s 
                            gitlab.timelogs          0          0          0          0.049s 
                               gitlab.todos          0          0          0          0.042s 
                   gitlab.trending_projects          0          0          0          0.040s 
                   gitlab.u2f_registrations          0          0          0          0.070s 
                             gitlab.uploads          0          0          0          0.070s 
                               gitlab.users          0          0          0          0.080s 
                 gitlab.users_star_projects          0          0          0          0.060s 
                  gitlab.user_agent_details          0          0          0          0.091s 
                           gitlab.web_hooks          0          0          0          0.075s 
-------------------------------------------  ---------  ---------  ---------  --------------
                    COPY Threads Completion          4          4          0          2.510s 
                             Create Indexes        332        332          0          1.615s 
                     Index Build Completion        332        332          0          0.014s 
                            Reset Sequences         91         91          0          0.091s 
                               Primary Keys         91         91          0          0.061s 
                        Create Foreign Keys         44         44          0          0.082s 
                            Create Triggers          0          0          0          0.000s 
                           Install Comments          0          0          0          0.000s 
-------------------------------------------  ---------  ---------  ---------  --------------
                          Total import time          0          0          0          4.607s
dimitri commented 7 years ago

Closing for triage. Open with new data if you have some and still need a fix here.