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; \.

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?

[imagen] 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.

It worked! Thanks @zogstrip, @riking!

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

Product Support Self-hosting

riking (Kane York) 28 Mayo, 2015 23:20 3

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

Tema		Respuestas	Vistas
Trouble restoring from backup Product Support	10	1446	31 Enero 2018
Backup restore failing on clean dev docker env: FATAL: Peer authentication failed for user “postgres” Bug pr-welcome , dev-install	14	5017	6 Enero 2026
Errors on DB restore in Dev Development	2	901	28 Octubre 2018
Error when restore DB backup Self-hosting	25	2263	23 Agosto 2018
Restore failing- possible Data Explorer issue Self-hosting	21	2573	9 Diciembre 2019

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

Temas relacionados