Migrate db from old Discourse to new without updating


(Sedrickcz) #1

Hi,

I have instance of Discourse without db backup (0.9.8.3) and I need backup my db before updating to new version and I have to be sure, that I have fully functional db backup that works on the newest version.

I tried this: sudo -iu postgres pg_dump --no-owner --clean discourse | gzip -c > /shared/today-backup.tar.gz and upload it to new version, but it doesn’t restore it :frowning:

Any advice how to do it? Thank you.


(Renis) #2

I have more recent backup and tried to restore (in different ways even manually and from pgadmin) but no luck!


(Jens Maier) #3

This tells me that you are trying to use the Discourse backup feature to import the data?
That won’t work. Discourse expects the backup-file to be a tar archive that contains, apart from the database dump, certain metadata and uploaded files.

I don’t think that there is a recommended method for porting old Discourse instances, but I’d try this:

  1. Dump the database and files:
    pg_dump -cf backup.sql discourse
    tar czf uploads.tar.gz public/uploads

  2. On the new Discourse host, copy these two files into your shared folder, typically /var/docker/shared/standalone

  3. Enter the container:
    sudo ./launcher enter app
    su - discourse

  4. Manually restore the data:
    rm -rf /var/www/discourse/public/uploads
    tar -C /var/www/discourse/ xzf /shared/uploads.tar.gz
    psql -d discourse -c 'drop schema public cascade'
    psql -d discourse -c 'create schema public'
    psql -f /shared/backup.sql -d discourse

  5. Migrate the restored database:
    RAILS_ENV=production bundle exec rake db:migrate

  6. Exit from the container and restart it:
    exit
    ./launcher restart app

Please be aware that the fourth step is destructive. If this or the fifth step fails, the new Discourse instance will be broken and its database must be initialized from scratch (i.e. stop the container, delete /var/docker/shared/standalone and re-bootstrap). Double check everything before you run the rm -rf and drop schema public commands and be absolutely certain that you are running these inside the container of the new Discourse instance and that this instance does not contain any data that you would like to preserve; because this data will be gone.


(Sedrickcz) #4

Hey, you are awesome. Thank you. Unfortunately this doesn’t work. It fails in step 5 with this error:

== 20140306223522 MoveTopicRevisionsToPostRevisions: migrating ================
-- execute("\n    INSERT INTO post_revisions(user_id, post_id, modifications, number, created_at, updated_at)\n    SELECT tr.user_id, p.id, tr.modifications, tr.number, tr.created_at, tr.updated_at\n    FROM topic_revisions tr\n    JOIN topics t ON t.id = tr.topic_id\n    JOIN posts p ON p.topic_id = t.id AND p.post_number = 1\n\n")
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

PG::Error: ERROR:  relation "topics" does not exist
LINE 5:     JOIN topics t ON t.id = tr.topic_id
                 ^
: 
    INSERT INTO post_revisions(user_id, post_id, modifications, number, created_at, updated_at)
    SELECT tr.user_id, p.id, tr.modifications, tr.number, tr.created_at, tr.updated_at
    FROM topic_revisions tr
    JOIN topics t ON t.id = tr.topic_id
    JOIN posts p ON p.topic_id = t.id AND p.post_number = 1

/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-mini-profiler-0.9.1/lib/patches/sql_patches.rb:160:in `exec'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/rack-mini-profiler-0.9.1/lib/patches/sql_patches.rb:160:in `async_exec'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:128:in `block in execute'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract_adapter.rb:373:in `block in log'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activesupport-4.1.4/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract_adapter.rb:367:in `log'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/connection_adapters/postgresql/database_statements.rb:127:in `execute'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:649:in `block in method_missing'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:621:in `block in say_with_time'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:621:in `say_with_time'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:641:in `method_missing'
/var/www/discourse/db/migrate/20140306223522_move_topic_revisions_to_post_revisions.rb:3:in `up'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:598:in `exec_migration'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:579:in `block (2 levels) in migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:578:in `block in migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/connection_pool.rb:294:in `with_connection'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:577:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:752:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:992:in `block in execute_migration_in_transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:1038:in `block in ddl_transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/database_statements.rb:201:in `block in transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/database_statements.rb:209:in `within_new_transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/connection_adapters/abstract/database_statements.rb:201:in `transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/transactions.rb:208:in `transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:1038:in `ddl_transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:991:in `execute_migration_in_transaction'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:953:in `block in migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:949:in `each'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:949:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:807:in `up'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/migration.rb:785:in `migrate'
/var/www/discourse/vendor/bundle/ruby/2.0.0/gems/activerecord-4.1.4/lib/active_record/railties/databases.rake:34:in `block (2 levels) in <top (required)>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

Here is the complete Terminal Output for importing db in step 4 and step 5


Going on an user profile or private message pages return a server error (code 500)
(Jens Maier) #5

Ok, I see what’s wrong. I adapted these commands from my dev environment where the psql commands are run by a Postgres super user… :sweat_smile:


  1. Dump the database and files:
    pg_dump -f backup.sql discourse
    tar czf uploads.tar.gz public/uploads

  2. On the new Discourse host, copy these two files into your shared folder, typically /var/docker/shared/standalone

  3. Enter the container:
    sudo ./launcher enter app

  4. Manually restore the database:
    su - postgres
    psql -d discourse -c 'drop schema public cascade'
    psql -d discourse -c 'create schema public'
    psql -f /shared/backup.sql -d discourse
    exit

  5. Manually restore the uploads:
    su - discourse
    rm -rf /var/www/discourse/public/uploads
    tar -C /var/www/discourse/ xzf /shared/uploads.tar.gz

  6. Migrate the restored database:
    RAILS_ENV=production bundle exec rake db:migrate
    exit (from the previous su session)

  7. Exit from the container and restart it:
    exit
    ./launcher restart app


The pg_dump and psql commands could probably be fine-tuned to avoid becoming the Postgres superuser as a workaround. Then again, this whole procedure is a hack, sooo… oh well. :grin:


(Sam Saffron) #6

I like this, can you perhaps may a canonical howto here cc @techAPJ


Advanced, manual method of manually creating and restoring Discourse backups
(Kane York) #7

@elberet It’s actually easier to do this:

  1. Take down the old site

    bluepill stop && bluepill quit && killall bluepill && killall -9 bluepill
    
  2. Update the code for the old site

    mv config/database.yml{,.old}
    git pull
    # resolve conflicts by making a backup copy and trying again
    
  3. Restore the database configuration

    cp config/discourse_quickstart.conf config/discourse.conf
    # Copy values from config/database.yml.old into config/discourse.conf
    
  4. Run a migration and a backup

    RAILS_ENV=production bundle exec rake db:migrate
     # this might need bundle, don't remember
    RAILS_ENV=production script/discourse backup
    

Then download that backup, then do a normal restore on the new site. (Upload in admin panel, allow_restore true, refresh, restore, ./launcher restart app).


(Jens Maier) #8

My goal was to avoid any modifications to the existing data and Discourse files. I chose this procedure because it retrieves the data from the old instance while leaving it up and running until the new instance is ready and tested.


(Sedrickcz) #9

This is it :slight_smile: Wow, thank you very much for your time.


(Renis) #10

Thank you for this migrate tutorial!
I have a problem,
on RAILS_ENV=production, I get an error: "no schema has been selected to create in: CREATE TABLE… "

what step i’m missing? Thanks!


(Jens Maier) #11

You most likely forgot to re-create the public schema after dropping it. Run psql -d discourse -c 'create schema public' as the user postgres inside the container.


(Renis) #12

I did!

Schema public already exists!

:expressionless:


(Jens Maier) #13

Can you start over at 4. and post the entire output to a Github gist or something?


(Renis) #14

Here is the error from the terminal:

Dropbox - Error - production_error.log

Dropbox - Error production.log

psql -d discourse -c 'drop schema public cascade’
msg: DROP SCHEMA

psql -d discourse -c 'create schema public’
msg: CREATE SCHEMA

psql -f /shared/backup.sql -d discourse
msg: various sql messages! No errors!


(Jens Maier) #15

Are you sure that the database names match? It looks like the database you have configured for production Discourse is empty even after importing the backup.


(Renis) #16

Thanks to @elberet I was able to migrate too! :smiley: :smiley:


(Vinil Menon) #17

Thanks @elberet for these steps! Much appreciated.

Instead of creating a new topic, I am posting this query on this thread - do let me know if I should post a new query?

I used these steps to import an old intranet instance running on IP address (10.x.y.z) to an internet instance on (discussion.example.com). The intranet instance was running on the older bare-metal installation on Ubuntu 14.04. When the steps migrated to docker in 2014, I was unable to keep updating the site. So the DB on the intranet version was quite old (before the updates via admin site were introduced).

I setup a new docker based instance and wanted to migrate all the posts/uploads.
These steps helped get the posts into the new instance, but it also updated the base_url to the hostname of the old instance.

I’ve updated the new url in all the recommended places (app.yml and run a ./launcher rebuild app), and any grep for the old URL fails both on the host as well as inside the container. Yet when I check base_url it shows the old intranet IP address (10.x.y.z) instead of the new internet hostname (discussion.example.com)

RAILS_ENV=production bundle exec rails c
irb(main):001:0> Discourse.base_url
=> "http://10.x.y.z"

How do I update the base_url?