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

Installation
1

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 `<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>'
[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?

2

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

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

4

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.

5

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?

1 Like
6

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.

7

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

8

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.

9

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.

1 Like
10

It worked! Thanks @zogstrip, @riking!