Repairing a corrupted database


(Zach Alexander) #1

Per my flurry of threads yesterday, my postgres database has gotten corrupted, possibly due to two containers running at the same time (or some other reason).

It’s working enough at the moment, but I can’t complete a backup, nor restore from the most recent successful backups.

I’m going to do what I can myself with the help of Google and Stack Overflow, but any help ya’ll can give along the way will be greatly appreciated.

The proximate problem I’m having right now is when I try to backup, I get

ERROR:  missing chunk number 0 for toast value 108751 in pg_toast_63466

Full log:

[2014-06-12 11:47:18] 'zach' has started the backup!
[2014-06-12 11:47:18] Marking backup as running...
[2014-06-12 11:47:18] Making sure '/var/www/discourse/tmp/backups/default/2014-06-12-184718' exists...
[2014-06-12 11:47:18] Making sure '/var/www/discourse/public/backups/default' exists...
[2014-06-12 11:47:18] Writing metadata to '/var/www/discourse/tmp/backups/default/2014-06-12-184718/meta.json'...
[2014-06-12 11:47:19] Enabling readonly mode...
[2014-06-12 11:47:19] Pausing sidekiq...
[2014-06-12 11:47:19] Waiting for sidekiq to finish running jobs...
[2014-06-12 11:47:19] Dumping the public schema of the database...
...
[2014-06-12 11:47:24] pg_dump: processing data for table "posts"
[2014-06-12 11:47:24] pg_dump: dumping contents of table posts
[2014-06-12 11:47:24] pg_dump: Dumping the contents of table "posts" failed: PQgetResult() failed.
[2014-06-12 11:47:24] pg_dump: Error message from server: ERROR:  missing chunk number 0 for toast value 108751 in pg_toast_63466
[2014-06-12 11:47:24] 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) TO stdout;
[2014-06-12 11:47:24] EXCEPTION: pg_dump failed
[2014-06-12 11:47:24] /var/www/discourse/lib/export/exporter.rb:167:in `dump_public_schema'
/var/www/discourse/lib/export/exporter.rb:33:in `run'
/var/www/discourse/lib/backup_restore.rb:160:in `block in start!'
/var/www/discourse/lib/backup_restore.rb:157:in `fork'
/var/www/discourse/lib/backup_restore.rb:157:in `start!'
/var/www/discourse/lib/backup_restore.rb:14:in `backup!'
/var/www/discourse/app/controllers/admin/backups_controller.rb:26:in `create'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_controller/metal/implicit_render.rb:4:in `send_action'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/abstract_controller/base.rb:189:in `process_action'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_controller/metal/rendering.rb:10:in `process_action'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/abstract_controller/callbacks.rb:20:in `block in process_action'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:113:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:113:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `block in halting'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `block in halting'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `block in halting'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:149:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:149:in `block in halting_and_conditional'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `block in halting'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `block in halting'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `block in halting'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `block in halting'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `block in halting'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `block in halting'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `block in halting'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `block in halting'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `block in halting'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:229:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:229:in `block in halting'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:166:in `block in halting'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:86:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:86:in `run_callbacks'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/abstract_controller/callbacks.rb:19:in `process_action'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_controller/metal/rescue.rb:29:in `process_action'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_controller/metal/instrumentation.rb:31:in `block in process_action'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/notifications.rb:159:in `block in instrument'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/notifications.rb:159:in `instrument'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_controller/metal/instrumentation.rb:30:in `process_action'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_controller/metal/params_wrapper.rb:250:in `process_action'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.1/lib/active_record/railties/controller_runtime.rb:18:in `process_action'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/abstract_controller/base.rb:136:in `process'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionview-4.1.1/lib/action_view/rendering.rb:30:in `process'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-mini-profiler-0.9.1/lib/mini_profiler/profiling_methods.rb:108:in `block in profile_method'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_controller/metal.rb:195:in `dispatch'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_controller/metal/rack_delegation.rb:13:in `dispatch'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_controller/metal.rb:231:in `block in action'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/routing/route_set.rb:80:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/routing/route_set.rb:80:in `dispatch'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/routing/route_set.rb:48:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/routing/mapper.rb:45:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/journey/router.rb:71:in `block in call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/journey/router.rb:59:in `each'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/journey/router.rb:59:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/routing/route_set.rb:676:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-protection-1.5.2/lib/rack/protection/frame_options.rb:31:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.1/lib/omniauth/strategy.rb:186:in `call!'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.1/lib/omniauth/strategy.rb:164:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.1/lib/omniauth/strategy.rb:186:in `call!'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.1/lib/omniauth/strategy.rb:164:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.1/lib/omniauth/strategy.rb:186:in `call!'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.1/lib/omniauth/strategy.rb:164:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.1/lib/omniauth/strategy.rb:186:in `call!'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.1/lib/omniauth/strategy.rb:164:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.1/lib/omniauth/strategy.rb:186:in `call!'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.1/lib/omniauth/strategy.rb:164:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.1/lib/omniauth/strategy.rb:186:in `call!'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.1/lib/omniauth/strategy.rb:164:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.1/lib/omniauth/builder.rb:59:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.2/lib/rack/etag.rb:23:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.2/lib/rack/conditionalget.rb:35:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.2/lib/rack/head.rb:11:in `call'
/var/www/discourse/lib/middleware/anonymous_cache.rb:119:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/middleware/params_parser.rb:27:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/middleware/flash.rb:254:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.2/lib/rack/session/abstract/id.rb:225:in `context'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.2/lib/rack/session/abstract/id.rb:220:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/middleware/cookies.rb:560:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/message_bus-0.9.4/lib/message_bus/rack/middleware.rb:55:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.1/lib/active_record/query_cache.rb:36:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:621:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/middleware/callbacks.rb:29:in `block in call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.1/lib/active_support/callbacks.rb:82:in `run_callbacks'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/middleware/callbacks.rb:27:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/middleware/remote_ip.rb:76:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/middleware/debug_exceptions.rb:17:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/middleware/show_exceptions.rb:30:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/logster-0.0.10/lib/logster/middleware/reporter.rb:23:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/railties-4.1.1/lib/rails/rack/logger.rb:38:in `call_app'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/railties-4.1.1/lib/rails/rack/logger.rb:22:in `call'
/var/www/discourse/config/initializers/quiet_logger.rb:10:in `call_with_quiet_assets'
/var/www/discourse/config/initializers/silence_logger.rb:26:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.1/lib/action_dispatch/middleware/request_id.rb:21:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.2/lib/rack/methodoverride.rb:21:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.2/lib/rack/runtime.rb:17:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.2/lib/rack/sendfile.rb:112:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-mini-profiler-0.9.1/lib/mini_profiler/profiler.rb:300:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/railties-4.1.1/lib/rails/engine.rb:514:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/railties-4.1.1/lib/rails/application.rb:144:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/railties-4.1.1/lib/rails/railtie.rb:194:in `public_send'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/railties-4.1.1/lib/rails/railtie.rb:194:in `method_missing'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.2/lib/rack/builder.rb:138:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.2/lib/rack/urlmap.rb:65:in `block in call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.2/lib/rack/urlmap.rb:50:in `each'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.2/lib/rack/urlmap.rb:50:in `call'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/unicorn-4.8.2/lib/unicorn/http_server.rb:572:in `process_client'
/var/www/discourse/lib/middleware/unicorn_oobgc.rb:95:in `process_client'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/unicorn-4.8.2/lib/unicorn/http_server.rb:666:in `worker_loop'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/unicorn-4.8.2/lib/unicorn/http_server.rb:521:in `spawn_missing_workers'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/unicorn-4.8.2/lib/unicorn/http_server.rb:140:in `start'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/unicorn-4.8.2/bin/unicorn:126:in `<top (required)>'
/var/www/discourse/vendor/bundle/ruby/2.0.0/bin/unicorn:23:in `load'
/var/www/discourse/vendor/bundle/ruby/2.0.0/bin/unicorn:23:in `<main>'
[2014-06-12 11:47:24] Notifying 'zach' of the end of the backup...
[2014-06-12 11:47:25] Cleaning stuff up...
[2014-06-12 11:47:25] Removing tmp '/var/www/discourse/tmp/backups/default/2014-06-12-184718' directory...
[2014-06-12 11:47:25] Unpausing sidekiq...
[2014-06-12 11:47:25] Disabling readonly mode...
[2014-06-12 11:47:25] Marking backup as finished...
[2014-06-12 11:47:25] Finished!

(Zach Alexander) #2

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.

Creating a backup in the admin panel works now. :slight_smile: About to see it I can restore from it…


Repairing damaged post_search_data (unable to create database dumps and execute search reindex)
(Jeff Atwood) #3

Just to follow up on this, we were likely part of the problem as our default Docker container setup did not do a clean Postgres shutdown until the last iteration of it a week or two ago.