Unable to restore my backup


(Steven Arnott) #1

Hi, Attempting to migrate to a new instance of discourse that is using an external postgres DB.

`[2015-06-12 17:52:30] Switching schemas... try reloading the site in 5 minutes, if successful, then reboot and restore is complete.
    [2015-06-12 17:52:31] Migrating the database...
    [2015-06-12 17:52:31] EXCEPTION: Error dumping database
    [2015-06-12 17:52:31] /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.10/lib/active_record/tasks/postgresql_database_tasks.rb:55:in `structure_dump'
    /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.10/lib/active_record/tasks/database_tasks.rb:150:in `structure_dump'
    /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.10/lib/active_record/railties/databases.rake:270:in `block (3 levels) in <top (required)>'
    /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rake-10.4.2/lib/rake/task.rb:240:in `call'
    /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rake-10.4.2/lib/rake/task.rb:240:in `block in execute'
    /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rake-10.4.2/lib/rake/task.rb:235:in `each'
    /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rake-10.4.2/lib/rake/task.rb:235:in `execute'
    /var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rake-10.4.2/lib/rake/task.rb:179:in `block in invoke_with_call_chain'

(Jeff Atwood) #2

What versions of Postgres?


(Régis Hanol) #3

Can you paste all the logs? We’re missing some information here.


(Steven Arnott) #4

OK here is the fuller picture:

I have a ‘stand alone’ instance of discourse, which is running on an unsupported (hackathon) host. That plan is to migrate the instance to supported hosts. We have decide create non-docker instance of the Redis and Postgres servers. I upgraded the current instance to the latest and attempted to export and import the data.

The postgres instance is 9.4:

I re-ran the restore of the backup today and got this message:

    [2015-06-15 12:58:48] EXCEPTION: PG::InsufficientPrivilege: ERROR:  permission denied for schema restore
CONTEXT:  SQL statement "ALTER TABLE restore.api_keys SET SCHEMA public;"
PL/pgSQL function inline_code_block line 11 at EXECUTE statement
: BEGIN;
      DO $$DECLARE row record;
      BEGIN
        -- create <destination> schema if it does not exists already
        -- NOTE: DROP & CREATE SCHEMA is easier, but we don't want to drop the public schema
        -- ortherwise extensions (like hstore & pg_trgm) won't work anymore...
        CREATE SCHEMA IF NOT EXISTS backup;
        -- move all <source> tables to <destination> schema
        FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
        LOOP
          EXECUTE 'DROP TABLE IF EXISTS backup.' || quote_ident(row.tablename) || ' CASCADE;';
          EXECUTE 'ALTER TABLE public.' || quote_ident(row.tablename) || ' SET SCHEMA backup;';
        END LOOP;
        -- move all <source> views to <destination> schema
        FOR row IN SELECT viewname FROM pg_views WHERE schemaname = 'public'
        LOOP
          EXECUTE 'DROP VIEW IF EXISTS backup.' || quote_ident(row.viewname) || ' CASCADE;';
          EXECUTE 'ALTER VIEW public.' || quote_ident(row.viewname) || ' SET SCHEMA backup;';
        END LOOP;
      END$$;

      DO $$DECLARE row record;
      BEGIN
        -- create <destination> schema if it does not exists already
        -- NOTE: DROP & CREATE SCHEMA is easier, but we don't want to drop the public schema
        -- ortherwise extensions (like hstore & pg_trgm) won't work anymore...
        CREATE SCHEMA IF NOT EXISTS public;
        -- move all <source> tables to <destination> schema
        FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'restore'
        LOOP
          EXECUTE 'DROP TABLE IF EXISTS public.' || quote_ident(row.tablename) || ' CASCADE;';
          EXECUTE 'ALTER TABLE restore.' || quote_ident(row.tablename) || ' SET SCHEMA public;';
        END LOOP;
        -- move all <source> views to <destination> schema
        FOR row IN SELECT viewname FROM pg_views WHERE schemaname = 'restore'
        LOOP
          EXECUTE 'DROP VIEW IF EXISTS public.' || quote_ident(row.viewname) || ' CASCADE;';
          EXECUTE 'ALTER VIEW restore.' || quote_ident(row.viewname) || ' SET SCHEMA public;';
        END LOOP;
      END$$;

COMMIT;

Looks like a permissions issue…


(Kane York) #5

The discourse user should own the database and the schemas.


(Steven Arnott) #6

Turned out that the restore schema was owned by postgres… so the discourse user could remove/restore/etc