I’m trying to restore a backup from non-docker discourse installation to a docker based one. The backup process completed properly. But I cannot restore it after several tries of backup and restoring. Here’s the restore log: [2015-05-28 21:04:09] 'admin' has started the restore! [2015-05-28 21:04:09] Marking restore as running... [2015-05-28 21:04:09] Making sure /var/www/discourse/tmp/restores/default/2015-05-28-210409 exists... [2015-05-28 21:04:09] Copying archive to tmp directory... [2015-05-28 21:04:09] Unzipping archive... [2015-05-28 21:04:10] Extracting metadata file... [2015-05-28 21:04:11] Validating metadata... [2015-05-28 21:04:11] Current version: 20150525151759 [2015-05-28 21:04:11] Restored version: 20150525151759 [2015-05-28 21:04:11] Extracting dump file... [2015-05-28 21:04:11] Restoring dump file... (can be quite long) [2015-05-28 21:04:11] SET [2015-05-28 21:04:11] SET [2015-05-28 21:04:11] SET [2015-05-28 21:04:11] SET [2015-05-28 21:04:11] SET [2015-05-28 21:04:11] SET [2015-05-28 21:04:11] DROP SCHEMA [2015-05-28 21:04:11] CREATE SCHEMA [2015-05-28 21:04:11] SET [2015-05-28 21:04:11] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:23: ERROR: must be superuser to create an operator family [2015-05-28 21:04:11] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:31: ERROR: current transaction is aborted, commands ignored until end of transaction block ... [2015-05-28 21:04:11] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:4064: invalid command \. [2015-05-28 21:04:11] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:4073: ERROR: current transaction is aborted, commands ignored until end of transaction block [2015-05-28 21:04:11] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:4938: invalid command \. [2015-05-28 21:04:11] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:4947: ERROR: syntax error at or near "72" [2015-05-28 21:04:11] LINE 1: 72 2015-02-22 3 12 [2015-05-28 21:04:11] ^ [2015-05-28 21:04:11] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:4956: ERROR: current transaction is aborted, commands ignored until end of transaction block [2015-05-28 21:04:11] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:4962: invalid command \. [2015-05-28 21:04:11] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:4971: ERROR: syntax error at or near "1" [2015-05-28 21:04:11] LINE 1: 1 Getting Started [2015-05-28 21:04:11] ^ [2015-05-28 21:04:11] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:4980: ERROR: current transaction is aborted, commands ignored until end of transaction block [2015-05-28 21:04:11] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:4984: invalid command \. [2015-05-28 21:04:11] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:4993: ERROR: syntax error at or near "1" [2015-05-28 21:04:11] LINE 1: 1 Gold 2014-03-22 09:10:35.793711 2014-03-22 09:10:35.793711 [2015-05-28 21:04:11] ^ [2015-05-28 21:04:11] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:5002: ERROR: current transaction is aborted, commands ignored until end of transaction block [2015-05-28 21:04:11] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:5003: invalid command \N ... [2015-05-28 21:04:16] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:9550: invalid command \. [2015-05-28 21:04:16] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:9559: ERROR: syntax error at or near "98480" [2015-05-28 21:04:16] LINE 1: 98480 1 120 0 0 3 2 0 [2015-05-28 21:04:16] ^ [2015-05-28 21:04:16] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:9568: ERROR: current transaction is aborted, commands ignored until end of transaction block [2015-05-28 21:04:16] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:10626: invalid command \. [2015-05-28 21:04:16] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:10635: ERROR: syntax error at or near "2" [2015-05-28 21:04:16] LINE 1: 2 -1 topic_1 1 [2015-05-28 21:04:16] ^ [2015-05-28 21:04:16] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:10644: ERROR: current transaction is aborted, commands ignored until end of transaction block [2015-05-28 21:04:16] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:10653: invalid command \ ... [2015-05-28 21:04:16] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:11203: ERROR: syntax error at or near "5" [2015-05-28 21:04:16] LINE 1: 5 4 topic_1 {"reply":"> You have reached the reply limit for... [2015-05-28 21:04:16] ^ [2015-05-28 21:04:16] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:11203: invalid command \ ... [2015-05-28 21:04:16] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:11422: ERROR: syntax error at or near ")" [2015-05-28 21:04:16] LINE 1: ) [2015-05-28 21:04:16] ^ [2015-05-28 21:04:16] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:11422: invalid command \ ... [2015-05-28 21:04:16] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:11500: ERROR: syntax error at or near "912" [2015-05-28 21:04:16] LINE 1: 912 695 new_topic {"reply":"I am facing a problem to write b... [2015-05-28 21:04:16] ^ [2015-05-28 21:04:16] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:11509: ERROR: current transaction is aborted, commands ignored until end of transaction block [2015-05-28 21:04:16] psql:/var/www/discourse/tmp/restores/default/2015-05-28-210409/dump.sql:11510: invalid command \N ... [2015-05-28 21:04:16] EXCEPTION: psql failed [2015-05-28 21:04:16] /var/www/discourse/lib/backup_restore/restorer.rb:217:in `restore_dump' /var/www/discourse/lib/backup_restore/restorer.rb:38:in `run' /var/www/discourse/lib/backup_restore/backup_restore.rb:161:in `block in start!' /var/www/discourse/lib/backup_restore/backup_restore.rb:158:in `fork' /var/www/discourse/lib/backup_restore/backup_restore.rb:158:in `start!' /var/www/discourse/lib/backup_restore/backup_restore.rb:17:in `restore!' /var/www/discourse/app/controllers/admin/backups_controller.rb:74:in `restore' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_controller/metal/implicit_render.rb:4:in `send_action' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/abstract_controller/base.rb:189:in `process_action' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_controller/metal/rendering.rb:10:in `process_action' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/abstract_controller/callbacks.rb:20:in `block in process_action' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.10/lib/active_support/callbacks.rb:113:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.10/lib/active_support/callbacks.rb:113:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.10/lib/active_support/callbacks.rb:552:in `block (2 levels) in compile' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.10/lib/active_support/callbacks.rb:502:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.10/lib/active_support/callbacks.rb:502:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.10/lib/active_support/callbacks.rb:86:in `run_callbacks' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/abstract_controller/callbacks.rb:19:in `process_action' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_controller/metal/rescue.rb:29:in `process_action' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_controller/metal/instrumentation.rb:32:in `block in process_action' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.10/lib/active_support/notifications.rb:159:in `block in instrument' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.10/lib/active_support/notifications/instrumenter.rb:20:in `instrument' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.10/lib/active_support/notifications.rb:159:in `instrument' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_controller/metal/instrumentation.rb:30:in `process_action' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_controller/metal/params_wrapper.rb:250:in `process_action' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.10/lib/active_record/railties/controller_runtime.rb:18:in `process_action' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/abstract_controller/base.rb:136:in `process' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionview-4.1.10/lib/action_view/rendering.rb:30:in `process' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-mini-profiler-0.9.3/lib/mini_profiler/profiling_methods.rb:106:in `block in profile_method' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_controller/metal.rb:196:in `dispatch' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_controller/metal/rack_delegation.rb:13:in `dispatch' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_controller/metal.rb:232:in `block in action' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_dispatch/routing/route_set.rb:82:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_dispatch/routing/route_set.rb:82:in `dispatch' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_dispatch/routing/route_set.rb:50:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_dispatch/routing/mapper.rb:45:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_dispatch/journey/router.rb:73:in `block in call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_dispatch/journey/router.rb:59:in `each' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_dispatch/journey/router.rb:59:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_dispatch/routing/route_set.rb:692:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-protection-1.5.3/lib/rack/protection/frame_options.rb:31:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.2/lib/omniauth/strategy.rb:186:in `call!' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.2/lib/omniauth/strategy.rb:164:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.2/lib/omniauth/strategy.rb:186:in `call!' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.2/lib/omniauth/strategy.rb:164:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.2/lib/omniauth/strategy.rb:186:in `call!' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.2/lib/omniauth/strategy.rb:164:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.2/lib/omniauth/strategy.rb:186:in `call!' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.2/lib/omniauth/strategy.rb:164:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.2/lib/omniauth/strategy.rb:186:in `call!' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.2/lib/omniauth/strategy.rb:164:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/omniauth-1.2.2/lib/omniauth/builder.rb:59:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.3/lib/rack/conditionalget.rb:35:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.3/lib/rack/head.rb:11:in `call' /var/www/discourse/lib/middleware/anonymous_cache.rb:123:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_dispatch/middleware/params_parser.rb:27:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_dispatch/middleware/flash.rb:254:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/message_bus-1.0.12/lib/message_bus/rack/middleware.rb:67:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.3/lib/rack/session/abstract/id.rb:225:in `context' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.3/lib/rack/session/abstract/id.rb:220:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_dispatch/middleware/cookies.rb:562:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.10/lib/active_record/query_cache.rb:36:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.10/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.10/lib/action_dispatch/middleware/callbacks.rb:29:in `block in call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.10/lib/active_support/callbacks.rb:82:in `run_callbacks' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_dispatch/middleware/callbacks.rb:27:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_dispatch/middleware/remote_ip.rb:76:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_dispatch/middleware/debug_exceptions.rb:17:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/actionpack-4.1.10/lib/action_dispatch/middleware/show_exceptions.rb:30:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/logster-0.8.1/lib/logster/middleware/reporter.rb:31:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/railties-4.1.10/lib/rails/rack/logger.rb:38:in `call_app' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/railties-4.1.10/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.10/lib/action_dispatch/middleware/request_id.rb:21:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.3/lib/rack/methodoverride.rb:21:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.3/lib/rack/runtime.rb:17:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.3/lib/rack/sendfile.rb:112:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-mini-profiler-0.9.3/lib/mini_profiler/profiler.rb:276:in `call' /var/www/discourse/lib/middleware/request_tracker.rb:70:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/railties-4.1.10/lib/rails/engine.rb:514:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/railties-4.1.10/lib/rails/application.rb:144:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/railties-4.1.10/lib/rails/railtie.rb:194:in `public_send' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/railties-4.1.10/lib/rails/railtie.rb:194:in `method_missing' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.3/lib/rack/builder.rb:138:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.3/lib/rack/urlmap.rb:65:in `block in call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.3/lib/rack/urlmap.rb:50:in `each' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-1.5.3/lib/rack/urlmap.rb:50:in `call' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/unicorn-4.8.3/lib/unicorn/http_server.rb:576:in `process_client' /var/www/discourse/lib/scheduler/defer.rb:85: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.3/lib/unicorn/http_server.rb:670:in `worker_loop' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/unicorn-4.8.3/lib/unicorn/http_server.rb:525:in `spawn_missing_workers' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/unicorn-4.8.3/lib/unicorn/http_server.rb:140:in `start' /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/unicorn-4.8.3/bin/unicorn:126:in ` ' /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 ` ' [2015-05-28 21:04:16] Trying to rollback... [2015-05-28 21:04:16] There was no need to rollback [2015-05-28 21:04:16] Notifying 'admin' of the end of the restore... [2015-05-28 21:04:17] Cleaning stuff up... [2015-05-28 21:04:17] Removing tmp '/var/www/discourse/tmp/restores/default/2015-05-28-210409' directory... [2015-05-28 21:04:17] Unpausing sidekiq... [2015-05-28 21:04:17] Marking restore as finished... [2015-05-28 21:04:17] Finished! Both installations are same version. The source non-docker installation uses postgresql 9.3. I’ve never used postgresql before. Any hint how to solve this?

dump.sql:23 contains: CREATE OPERATOR FAMILY btree_hstore_ops USING btree; dump.sql:4063, 4064 lines contain: COPY api_keys (id, key, user_id, created_by_id, created_at, updated_at, allowed_ips, hidden) FROM stdin; \.

Try deleting the CREATE OPERATOR FAMILY line and doing it again.

But there must be an explanation what’s happening here, right? I don’t want to tinker with the backup unless it is absolutely necessary.

hstore is some extension for Postgresql. Presumable you have that on the non-Docker version, the backup script picks up the existance and tries to restore it on the Docker version?

[画像] calmh: hstore is some extension for Postgresql. Presumable you have that on the non-Docker version, the backup script picks up the existance and tries to restore it on the Docker version? How do I confirm that? And what are the implications of it? It was installed with defaults on Ubuntu, I didn’t change anything. But as I said, I’m not an expert postgres user here. Some help will be appreciated.

How about making the discourse postgres user a superuser? Should I do that? How? Any help @sam, @zogstrip, @codinghorror please?

I would extract the dump.sql file from the backup, comment out the line/s that is/are creating an operator family and overwrite the dump.sql file back in the backup.

Yeah, this. That line isn’t included in a normal backup, which means that there was some difference in your PG database versus a Docker-setup one.

Restoring from backup failed with ERROR: must be superuser to create an operator family

Product Support Self-hosting

mugli (Mugli) 2015 年 5 月 29 日午後 8:02 10

It worked! Thanks @zogstrip, @riking!

トピック		返信	表示
Trouble restoring from backup Product Support	10	1446	2018 年 1 月 31 日
Backup restore failing on clean dev docker env: FATAL: Peer authentication failed for user “postgres” Bug pr-welcome , dev-install	14	5017	2026 年 1 月 6 日
Errors on DB restore in Dev Development	2	901	2018 年 10 月 28 日
Error when restore DB backup Self-hosting	25	2263	2018 年 8 月 23 日
Restore failing- possible Data Explorer issue Self-hosting	21	2573	2019 年 12 月 9 日

Restoring from backup failed with ERROR: must be superuser to create an operator family

関連トピック