Repairing damaged post_search_data (unable to create database dumps and execute search reindex)

Installation
1

The table post_search_data is damaged. I tried to repair it on different ways:

  1. using rake search:reindex:
    Output:

    root@xxx:/var/www/discourse# rake search:reindex
    Reindexing ‘default’

    Posts:
    rake aborted!
    PG::InternalError: ERROR: missing chunk number 0 for toast value 69779 in pg_toast_16766
    /var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-mini-profiler-0.10.1/lib/patches/db/pg.rb:50:in exec' /var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-mini-profiler-0.10.1/lib/patches/db/pg.rb:50:in exec’
    /var/www/discourse/lib/freedom_patches/active_record_base.rb:7:in exec_sql' /var/www/discourse/lib/tasks/search.rake:9:in reindex_search’
    /var/www/discourse/lib/tasks/search.rake:52:in block in reindex_search_all_sites' /var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rails_multisite-1.0.6/lib/rails_multisite/connection_management.rb:126:in block in each_connection’
    /var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rails_multisite-1.0.6/lib/rails_multisite/connection_management.rb:124:in each' /var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rails_multisite-1.0.6/lib/rails_multisite/connection_management.rb:124:in each_connection’
    /var/www/discourse/lib/tasks/search.rake:51:in reindex_search_all_sites' /var/www/discourse/lib/tasks/search.rake:2:in block in <top (required)>’
    /usr/local/bin/bundle:23:in load' /usr/local/bin/bundle:23:in ’
    Tasks: TOP => search:reindex
    (See full trace by running task with --trace)

  2. Reindex in the Postgres database:

    REINDEX table post_search_data;
    ERROR: missing chunk number 0 for toast value 69852 in pg_toast_17881

  3. Removing the table post_search_data, excluding it of the last working dump, restoring it in the discourse database and executing the reindex:

-> same like above

  1. Trying to remove damaged entries with:

    for ((i=0; i<13467; i++ )); do psql discourse -c “SELECT * FROM post_search_data LIMIT 1 offset $i” >/dev/null || echo $i; done
    select post_id from post_search_data limit 1 offset 5971;
    delete from post_search_data where post_id in (5981,6949,6944,6925,6918,6901,6900);
    VACUUM FULL post_search_data;

At first the database is saying it would be removed. But they exist after a short time of waiting.

What can we do that we can get new backups of the complete database?

1 Like
2

Additional to that the discourse backup is saying:

[2017-02-07 08:39:39] pg_dump: processing data for table "public.api_keys"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.api_keys"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET api_keys_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.application_requests"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.application_requests"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET application_requests_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.badge_groupings"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.badge_groupings"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET badge_groupings_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.badge_types"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.badge_types"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET badge_types_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.badges"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.badges"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET badges_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.categories"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.categories"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET categories_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.categories_web_hooks"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.categories_web_hooks"
[2017-02-07 08:39:39] pg_dump: processing data for table "public.category_custom_fields"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.category_custom_fields"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET category_custom_fields_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.category_featured_topics"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.category_featured_topics"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET category_featured_topics_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.category_featured_users"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.category_featured_users"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET category_featured_users_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.category_groups"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.category_groups"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET category_groups_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.category_search_data"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.category_search_data"
[2017-02-07 08:39:39] pg_dump: processing data for table "public.category_tag_groups"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.category_tag_groups"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET category_tag_groups_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.category_tags"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.category_tags"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET category_tags_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.category_users"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.category_users"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET category_users_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.color_scheme_colors"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.color_scheme_colors"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET color_scheme_colors_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.color_schemes"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.color_schemes"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET color_schemes_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.developers"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.developers"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET developers_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.directory_items"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.directory_items"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET directory_items_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.draft_sequences"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.draft_sequences"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET draft_sequences_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.drafts"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.drafts"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET drafts_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.email_change_requests"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.email_change_requests"
[2017-02-07 08:39:39] pg_dump: executing SEQUENCE SET email_change_requests_id_seq
[2017-02-07 08:39:39] pg_dump: processing data for table "public.email_logs"
[2017-02-07 08:39:39] pg_dump: dumping contents of table "public.email_logs"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET email_logs_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.email_tokens"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.email_tokens"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET email_tokens_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.embeddable_hosts"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.embeddable_hosts"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET embeddable_hosts_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.facebook_user_infos"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.facebook_user_infos"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET facebook_user_infos_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.github_user_infos"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.github_user_infos"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET github_user_infos_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.given_daily_likes"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.given_daily_likes"
[2017-02-07 08:39:42] pg_dump: processing data for table "public.google_user_infos"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.google_user_infos"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET google_user_infos_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.group_archived_messages"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.group_archived_messages"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET group_archived_messages_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.group_custom_fields"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.group_custom_fields"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET group_custom_fields_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.group_histories"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.group_histories"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET group_histories_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.group_mentions"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.group_mentions"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET group_mentions_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.group_users"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.group_users"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET group_users_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.groups"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.groups"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET groups_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.groups_web_hooks"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.groups_web_hooks"
[2017-02-07 08:39:42] pg_dump: processing data for table "public.incoming_domains"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.incoming_domains"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET incoming_domains_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.incoming_emails"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.incoming_emails"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET incoming_emails_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.incoming_links"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.incoming_links"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET incoming_links_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.incoming_referers"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.incoming_referers"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET incoming_referers_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.instagram_user_infos"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.instagram_user_infos"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET instagram_user_infos_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.invited_groups"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.invited_groups"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET invited_groups_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.invites"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.invites"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET invites_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.message_bus"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.message_bus"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET message_bus_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.muted_users"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.muted_users"
[2017-02-07 08:39:42] pg_dump: executing SEQUENCE SET muted_users_id_seq
[2017-02-07 08:39:42] pg_dump: processing data for table "public.notifications"
[2017-02-07 08:39:42] pg_dump: dumping contents of table "public.notifications"
[2017-02-07 08:39:43] pg_dump: executing SEQUENCE SET notifications_id_seq
[2017-02-07 08:39:43] pg_dump: processing data for table "public.oauth2_user_infos"
[2017-02-07 08:39:43] pg_dump: dumping contents of table "public.oauth2_user_infos"
[2017-02-07 08:39:43] pg_dump: executing SEQUENCE SET oauth2_user_infos_id_seq
[2017-02-07 08:39:43] pg_dump: processing data for table "public.onceoff_logs"
[2017-02-07 08:39:43] pg_dump: dumping contents of table "public.onceoff_logs"
[2017-02-07 08:39:43] pg_dump: executing SEQUENCE SET onceoff_logs_id_seq
[2017-02-07 08:39:43] pg_dump: processing data for table "public.optimized_images"
[2017-02-07 08:39:43] pg_dump: dumping contents of table "public.optimized_images"
[2017-02-07 08:39:43] pg_dump: executing SEQUENCE SET optimized_images_id_seq
[2017-02-07 08:39:43] pg_dump: processing data for table "public.permalinks"
[2017-02-07 08:39:43] pg_dump: dumping contents of table "public.permalinks"
[2017-02-07 08:39:43] pg_dump: executing SEQUENCE SET permalinks_id_seq
[2017-02-07 08:39:43] pg_dump: processing data for table "public.plugin_store_rows"
[2017-02-07 08:39:43] pg_dump: dumping contents of table "public.plugin_store_rows"
[2017-02-07 08:39:43] pg_dump: executing SEQUENCE SET plugin_store_rows_id_seq
[2017-02-07 08:39:43] pg_dump: processing data for table "public.post_action_types"
[2017-02-07 08:39:43] pg_dump: dumping contents of table "public.post_action_types"
[2017-02-07 08:39:43] pg_dump: executing SEQUENCE SET post_action_types_id_seq
[2017-02-07 08:39:43] pg_dump: processing data for table "public.post_actions"
[2017-02-07 08:39:43] pg_dump: dumping contents of table "public.post_actions"
[2017-02-07 08:39:43] pg_dump: executing SEQUENCE SET post_actions_id_seq
[2017-02-07 08:39:43] pg_dump: processing data for table "public.post_custom_fields"
[2017-02-07 08:39:43] pg_dump: dumping contents of table "public.post_custom_fields"
[2017-02-07 08:39:43] pg_dump: executing SEQUENCE SET post_custom_fields_id_seq
[2017-02-07 08:39:43] pg_dump: processing data for table "public.post_details"
[2017-02-07 08:39:43] pg_dump: dumping contents of table "public.post_details"
[2017-02-07 08:39:43] pg_dump: executing SEQUENCE SET post_details_id_seq
[2017-02-07 08:39:43] pg_dump: processing data for table "public.post_replies"
[2017-02-07 08:39:43] pg_dump: dumping contents of table "public.post_replies"
[2017-02-07 08:39:43] pg_dump: processing data for table "public.post_revisions"
[2017-02-07 08:39:43] pg_dump: dumping contents of table "public.post_revisions"
[2017-02-07 08:39:43] pg_dump: executing SEQUENCE SET post_revisions_id_seq
[2017-02-07 08:39:43] pg_dump: processing data for table "public.post_search_data"
[2017-02-07 08:39:43] pg_dump: dumping contents of table "public.post_search_data"
[2017-02-07 08:39:43] pg_dump: Dumping the contents of table "post_search_data" failed: PQgetResult() failed.
[2017-02-07 08:39:43] pg_dump: Error message from server: ERROR:  missing chunk number 0 for toast value 69852 in pg_toast_17881
[2017-02-07 08:39:43] EXCEPTION: pg_dump failed
[2017-02-07 08:39:43] /var/www/discourse/lib/backup_restore/backuper.rb:172:in `dump_public_schema'
/var/www/discourse/lib/backup_restore/backuper.rb:37:in `run'
/var/www/discourse/lib/backup_restore/backup_restore.rb:164:in `block in start!'
/var/www/discourse/lib/backup_restore/backup_restore.rb:161:in `fork'
/var/www/discourse/lib/backup_restore/backup_restore.rb:161:in `start!'
/var/www/discourse/lib/backup_restore/backup_restore.rb:16:in `backup!'
/var/www/discourse/app/controllers/admin/backups_controller.rb:31:in `create'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_controller/metal/implicit_render.rb:4:in `send_action'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/abstract_controller/base.rb:198:in `process_action'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_controller/metal/rendering.rb:10:in `process_action'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/abstract_controller/callbacks.rb:20:in `block in process_action'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/activesupport-4.2.7.1/lib/active_support/callbacks.rb:117:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/activesupport-4.2.7.1/lib/active_support/callbacks.rb:555:in `block (2 levels) in compile'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/activesupport-4.2.7.1/lib/active_support/callbacks.rb:505:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/activesupport-4.2.7.1/lib/active_support/callbacks.rb:92:in `__run_callbacks__'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/activesupport-4.2.7.1/lib/active_support/callbacks.rb:778:in `_run_process_action_callbacks'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/activesupport-4.2.7.1/lib/active_support/callbacks.rb:81:in `run_callbacks'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/abstract_controller/callbacks.rb:19:in `process_action'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_controller/metal/rescue.rb:29:in `process_action'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_controller/metal/instrumentation.rb:32:in `block in process_action'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/activesupport-4.2.7.1/lib/active_support/notifications.rb:164:in `block in instrument'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/activesupport-4.2.7.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/activesupport-4.2.7.1/lib/active_support/notifications.rb:164:in `instrument'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_controller/metal/instrumentation.rb:30:in `process_action'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_controller/metal/params_wrapper.rb:250:in `process_action'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/railties/controller_runtime.rb:18:in `process_action'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/abstract_controller/base.rb:137:in `process'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionview-4.2.7.1/lib/action_view/rendering.rb:30:in `process'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-mini-profiler-0.10.1/lib/mini_profiler/profiling_methods.rb:76:in `block in profile_method'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_controller/metal.rb:196:in `dispatch'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_controller/metal/rack_delegation.rb:13:in `dispatch'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_controller/metal.rb:237:in `block in action'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_dispatch/routing/route_set.rb:74:in `dispatch'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_dispatch/routing/route_set.rb:43:in `serve'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_dispatch/routing/mapper.rb:49:in `serve'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_dispatch/journey/router.rb:43:in `block in serve'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_dispatch/journey/router.rb:30:in `each'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_dispatch/journey/router.rb:30:in `serve'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_dispatch/routing/route_set.rb:817:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-protection-1.5.3/lib/rack/protection/frame_options.rb:31:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/omniauth-1.3.1/lib/omniauth/strategy.rb:186:in `call!'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/omniauth-1.3.1/lib/omniauth/strategy.rb:164:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/omniauth-1.3.1/lib/omniauth/strategy.rb:186:in `call!'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/omniauth-1.3.1/lib/omniauth/strategy.rb:164:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/omniauth-1.3.1/lib/omniauth/strategy.rb:186:in `call!'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/omniauth-1.3.1/lib/omniauth/strategy.rb:164:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/omniauth-1.3.1/lib/omniauth/strategy.rb:186:in `call!'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/omniauth-1.3.1/lib/omniauth/strategy.rb:164:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/omniauth-1.3.1/lib/omniauth/strategy.rb:186:in `call!'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/omniauth-1.3.1/lib/omniauth/strategy.rb:164:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/omniauth-1.3.1/lib/omniauth/strategy.rb:186:in `call!'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/omniauth-1.3.1/lib/omniauth/strategy.rb:164:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/omniauth-1.3.1/lib/omniauth/builder.rb:63:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-1.6.5/lib/rack/conditionalget.rb:38:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-1.6.5/lib/rack/head.rb:13:in `call'
/var/www/discourse/lib/middleware/anonymous_cache.rb:138:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_dispatch/middleware/params_parser.rb:27:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_dispatch/middleware/flash.rb:260:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-1.6.5/lib/rack/session/abstract/id.rb:225:in `context'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-1.6.5/lib/rack/session/abstract/id.rb:220:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_dispatch/middleware/cookies.rb:560:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/query_cache.rb:36:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/activerecord-4.2.7.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:653:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_dispatch/middleware/callbacks.rb:29:in `block in call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/activesupport-4.2.7.1/lib/active_support/callbacks.rb:88:in `__run_callbacks__'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/activesupport-4.2.7.1/lib/active_support/callbacks.rb:778:in `_run_call_callbacks'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/activesupport-4.2.7.1/lib/active_support/callbacks.rb:81:in `run_callbacks'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_dispatch/middleware/callbacks.rb:27:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_dispatch/middleware/remote_ip.rb:78:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_dispatch/middleware/debug_exceptions.rb:17:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_dispatch/middleware/show_exceptions.rb:30:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/logster-1.2.5/lib/logster/middleware/reporter.rb:31:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/railties-4.2.7.1/lib/rails/rack/logger.rb:38:in `call_app'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/railties-4.2.7.1/lib/rails/rack/logger.rb:22:in `call'
/var/www/discourse/config/initializers/100-quiet_logger.rb:13:in `call_with_quiet_assets'
/var/www/discourse/config/initializers/100-silence_logger.rb:26:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/actionpack-4.2.7.1/lib/action_dispatch/middleware/request_id.rb:21:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-1.6.5/lib/rack/methodoverride.rb:22:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-1.6.5/lib/rack/runtime.rb:18:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-1.6.5/lib/rack/sendfile.rb:113:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-mini-profiler-0.10.1/lib/mini_profiler/profiler.rb:171:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/message_bus-2.0.2/lib/message_bus/rack/middleware.rb:62:in `call'
/var/www/discourse/lib/middleware/request_tracker.rb:73:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/railties-4.2.7.1/lib/rails/engine.rb:518:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/railties-4.2.7.1/lib/rails/application.rb:165:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/railties-4.2.7.1/lib/rails/railtie.rb:194:in `public_send'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/railties-4.2.7.1/lib/rails/railtie.rb:194:in `method_missing'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-1.6.5/lib/rack/urlmap.rb:66:in `block in call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-1.6.5/lib/rack/urlmap.rb:50:in `each'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-1.6.5/lib/rack/urlmap.rb:50:in `call'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/unicorn-5.2.0/lib/unicorn/http_server.rb:562:in `process_client'
/var/www/discourse/lib/scheduler/defer.rb:85:in `process_client'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/unicorn-5.2.0/lib/unicorn/http_server.rb:658:in `worker_loop'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/unicorn-5.2.0/lib/unicorn/http_server.rb:508:in `spawn_missing_workers'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/unicorn-5.2.0/lib/unicorn/http_server.rb:132:in `start'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/unicorn-5.2.0/bin/unicorn:126:in `<top (required)>'
/var/www/discourse/vendor/bundle/ruby/2.3.0/bin/unicorn:23:in `load'
/var/www/discourse/vendor/bundle/ruby/2.3.0/bin/unicorn:23:in `<main>'
[2017-02-07 08:39:43] Notifying 'skriesch' of the end of the backup...
[2017-02-07 08:39:48] Removing old backups...
[2017-02-07 08:39:48] Cleaning stuff up...
[2017-02-07 08:39:48] Removing '.tar' leftovers...
[2017-02-07 08:39:48] Unpausing sidekiq...
[2017-02-07 08:39:48] Disabling readonly mode...
[2017-02-07 08:39:48] Marking backup as finished...
[2017-02-07 08:39:48] Finished!
3

Your database is pretty broken. You should restore from the last good backup.

If you wanna try something else, you can copy the DDL of this table, drop it, create it and then try to `reindex. But this isn’t guaranteed to work, only restoring from a backup is.

3 Likes
4

I created a new dump of the table (without content):
su - postgres -c "pg_dump -s -t post_search_data discourse > /shared/backups/default/post_without_data_live.pg"

DLL is now:
–
– PostgreSQL database dump
–

-- Dumped from database version 9.5.4
-- Dumped by pg_dump version 9.5.4

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: post_search_data; Type: TABLE; Schema: public; Owner: discourse
--

CREATE TABLE post_search_data (
    post_id integer NOT NULL,
    search_data tsvector,
    raw_data text,
    locale character varying
);


ALTER TABLE post_search_data OWNER TO discourse;

--
-- Name: posts_search_pkey; Type: CONSTRAINT; Schema: public; Owner: discourse
--

ALTER TABLE ONLY post_search_data
    ADD CONSTRAINT posts_search_pkey PRIMARY KEY (post_id);


--
-- Name: idx_search_post; Type: INDEX; Schema: public; Owner: discourse
--

CREATE INDEX idx_search_post ON post_search_data USING gin (search_data);


--
-- Name: post_search_data; Type: ACL; Schema: public; Owner: discourse
--

GRANT ALL ON TABLE post_search_data TO discourse;


--
-- PostgreSQL database dump complete
--

I’ll do the change today in the evening (during a time with a low number of customers on the system).

5

I droped the table and executed the table restore.

After reindex I got this output:
root@xxx:/var/www/discourse# rake search:reindex
Reindexing ‘default’

Posts:
rake aborted!
PG::InternalError: ERROR:  missing chunk number 0 for toast value 69779 in pg_toast_16766
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-mini-profiler-0.10.1/lib/patches/db/pg.rb:50:in `exec'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rack-mini-profiler-0.10.1/lib/patches/db/pg.rb:50:in `exec'
/var/www/discourse/lib/freedom_patches/active_record_base.rb:7:in `exec_sql'
/var/www/discourse/lib/tasks/search.rake:9:in `reindex_search'
/var/www/discourse/lib/tasks/search.rake:52:in `block in reindex_search_all_sites'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rails_multisite-1.0.6/lib/rails_multisite/connection_management.rb:126:in `block in each_connection'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rails_multisite-1.0.6/lib/rails_multisite/connection_management.rb:124:in `each'
/var/www/discourse/vendor/bundle/ruby/2.3.0/gems/rails_multisite-1.0.6/lib/rails_multisite/connection_management.rb:124:in `each_connection'
/var/www/discourse/lib/tasks/search.rake:51:in `reindex_search_all_sites'
/var/www/discourse/lib/tasks/search.rake:2:in `block in <top (required)>'
/usr/local/bin/bundle:23:in `load'
/usr/local/bin/bundle:23:in `<main>'
Tasks: TOP => search:reindex
(See full trace by running task with --trace)
6

The backup is giving the same output like in https://meta.discourse.org/t/repairing-a-corrupted-database/16486/2 now.

7

You are going to need to do a full pg dump / restore to recover from this

2 Likes
8

I tried it and get the same error like in the linked issue above:

su - postgres -c "pg_dumpall --database=discourse -f /shared/backups/default/discourse-2017-02-12.sql"
pg_dump: Dumping the contents of table "posts" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR:  missing chunk number 0 for toast value 69836 in pg_toast_16766
pg_dump: The command was: COPY public.posts (id, user_id, topic_id, post_number, raw, cooked, created_at, updated_at, reply_to_post_number, reply_count, quote_count, deleted_at, off_topic_count, like_count, incoming_link_count, bookmark_count, avg_time, score, reads, post_type, vote_count, sort_order, last_editor_id, hidden, hidden_reason_id, notify_moderators_count, spam_count, illegal_count, inappropriate_count, last_version_at, user_deleted, reply_to_user_id, percent_rank, notify_user_count, like_score, deleted_by_id, edit_reason, word_count, version, cook_method, wiki, baked_at, baked_version, hidden_at, self_edits, reply_quoted, via_email, raw_email, public_version, action_code, image_url) TO stdout;
pg_dumpall: pg_dump failed on database "discourse", exiting

What does Zach Alexander mean with:

Update: it was a problem with the cooked column of one particular post. Found it by binary searching through posts (looking for the “missing chunk number” error) in psql (after ssh into container).

To fix the corrupted row, I first copied the contents of raw to cooked, making the row usable again (though it would probably render badly in a browser). Then I went to the Rails console, found the post again, and called p.cooked(p.raw) and p.save. p.cooked is now an HTML version of p.raw, as I presume it should be.

9

Something in your posts table is broken beyond repair, it may be a column in a row, or multiple rows and multiple columns.

What you can try doing is finding which id is broken by selecting it:

psql discourse
% select * from posts where id < 1000 
% select * from posts where id > 1000 and id < 2000

and so on until you isolate the problem row.

Then if you are lucky you can just fix the row by deleting data from it, if you are unlucky you can just delete the row completely

2 Likes
10 
postgres@xxx:~$ for ((i=0; i<13467; i++ )); do psql discourse -c "SELECT * FROM posts LIMIT 1 offset $i" >/dev/null || echo $i; done
ERROR:  missing chunk number 0 for toast value 69836 in pg_toast_16766
1806
ERROR:  unexpected chunk number 1 (expected 0) for toast value 69776 in pg_toast_16766
2725
ERROR:  missing chunk number 0 for toast value 69797 in pg_toast_16766
2772
ERROR:  missing chunk number 0 for toast value 69832 in pg_toast_16766
3032
ERROR:  unexpected chunk number 1 (expected 0) for toast value 69853 in pg_toast_16766
3492
discourse=# select id from posts limit 1 offset 1806;
  id  
------
 5987
(1 row)

discourse=# select id from posts limit 1 offset 2725;
  id  
------
 5978
(1 row)

discourse=# select id from posts limit 1 offset 2772;
  id  
------
 5982
(1 row)

discourse=# select id from posts limit 1 offset 3032;
  id  
------
 5986
(1 row)

discourse=# select id from posts limit 1 offset 3492;
  id  
------
 5990
(1 row)

discourse=# delete from posts where id in (5987, 5978, 5982, 5986, 5990);
DELETE 5
discourse=# VACUUM FULL posts;
VACUUM
2 Likes
12

Is everything working now?

1 Like
13

Yes. Thanks!
Reindex was running and I can create backups again. :slight_smile:

4 Likes