Top Menu

Jump to content
Home
    Modules
      • Projects
      • Activity
      • Work packages
      • Gantt charts
      • Calendars
      • Team planners
      • Boards
      • News
    • Getting started
    • Introduction video
      Welcome to OpenProject Community
      Get a quick overview of project management and team collaboration with OpenProject. You can restart this video from the help menu.
    • Help and support
    • User guides
    • Videos
    • Shortcuts
    • Community forum
    • Enterprise support
    • Additional resources
    • Data privacy and security policy
    • Digital accessibility (DE)
    • OpenProject website
    • Security alerts / Newsletter
    • OpenProject blog
    • Release notes
    • Report a bug
    • Development roadmap
    • Add and edit translations
    • API documentation
  • Sign in
      Forgot your password?

      or sign in with your existing account

      Google

Side Menu

  • Overview
  • Activity
    Activity
  • Roadmap
  • Work packages
    Work packages
  • Gantt charts
    Gantt charts
  • Calendars
    Calendars
  • Team planners
    Team planners
  • Boards
    Boards
  • News
  • Forums
  • Feature tour
    Feature tour

Content

You are here:
  1. Forums
  2. General discussion

[Help] PostgreSQL restore database on server migration problem

Added by Da Silva over 5 years ago

Hi guys.

I have a problem in restoring a PostgreSQL backup file to a new server
I have followed the instructions here https://www.openproject.org/operations/backup/migrating-packaged-installation-environment/

The old and new server are running the same configurations on ubuntu 16.04 and the latest stable 8 version of OpenProject 

https://dl.packager.io/srv/opf/openproject-ce/stable/8/installer/ubuntu/16.04.repo

I have done a sudo openproject run backup on the old server, and have loaded the files in the new server successfully (see attached images)

I then performed 

cd /var/db/openproject/backup
pg_restore -h -u -W postgresql-dump-20190413161921.pgdump

Which then returned -- PostgreSQL database dump complete

...


-- Name: webhooks_projects fk_rails_d7ea5de5b8; Type: FK CONSTRAINT; Schema: public; Owner: openproject
--

ALTER TABLE ONLY public.webhooks_projects
    ADD CONSTRAINT fk_rails_d7ea5de5b8 FOREIGN KEY (webhooks_webhook_id) REFERENCES public.webhooks_webhooks(id);


--
-- Name: webhooks_projects fk_rails_e978b5e3d7; Type: FK CONSTRAINT; Schema: public; Owner: openproject
--

ALTER TABLE ONLY public.webhooks_projects
    ADD CONSTRAINT fk_rails_e978b5e3d7 FOREIGN KEY (project_id) REFERENCES public.projects(id);


--
-- PostgreSQL database dump complete
--

webuser@dvlab:/var/db/openproject/backup$

All looks well, but when I access the OpenProject site, none of the saved projects and users or saved data from the old server is available, all I see is a new OpenProject site with the demo projects as from a new installation - how can I recover the data from the backup and see my old completed and ongoing projects in the new installation?


Replies (6)

RE: [Help] PostgreSQL restore database on server migration problem - Added by Raeniel Jan Cornelio about 5 years ago

Can any of the devs have an update on this one? I have the same problem as this guy. When I try to restore the dump file made by openproject it will not restore the data in the old server.

RE: [Help] PostgreSQL restore database on server migration problem - Added by Oliver Günther about 5 years ago

Hi Raeniel,

you likely have not restored the database dump into your new database. Then you ran openproject configure which treated the empty database as a new installation and seeded the default users.

First, print the DATABASE_URL that OpenProject is using to connect to the database:

openproject config:get DATABASE_URL

This will look something like the following

postgres://USER:PASSWORD@HOST/DATABASE_NAME

Then ensure the openproject service is stopped so noone is accessing the database:

systemctl stop openproject

You will then need to perform the following steps as the postgres user (as root, run sudo su postgres)

  1. Remove the database that only contains demo data with dropdb DATABASE_NAME . Warning! This will remove the entire database DATABASE_NAME . Please double check the name of the database to remove is correct.
  2. Create a new database owned to the user USER with: createdb -O USER DATABASE_NAME
  3. Restore the dump into that database with psql postgres://USER:PASSWORD@HOST/DATABASE_NAME < /path/to/the/dumpfile

Afterwards, restart your server and your database should be running the correct version. If you have switched versions from the dumpfile, you will need to run openproject configure so that new database migrations and data changes can run.

Best,

Oliver

RE: [Help] PostgreSQL restore database on server migration problem - Added by Raeniel Jan Cornelio about 5 years ago

So the one on the official documentation for OpenProject Backup and Restore lacks sufficient instructons? I am using postgres 10. Do I not use pg_restore? The process I used for restoration is from the dumpfile that was generated by "openproject backup"

I followed the process of backup and restoration but I can't seem to restore the custom pgdump that was generated to a new server with the same version of openproject. It will show a file that it is dumping back but it will not reflect on the database itself.

RE: [Help] PostgreSQL restore database on server migration problem - Added by Oliver Günther about 5 years ago

You only use pg_restore if your dump is in the pg_dump internal format. If you followed the linked guide, this is the case and pg_restore is indeed correct

If you migrate from a different major postgres version, then you will want to use pg_dump -F p (format plain) to output SQL only, this can then be restored by the means above. If using pg_dump and pg_restore, you will get an error during restore and not the output that you linked.

I will extend the guide appropriately.

Best,

Oliver

RE: [Help] PostgreSQL restore database on server migration problem - Added by Raeniel Jan Cornelio about 5 years ago

I'm actually using the "openproject backup" command for backups and not the pg_dump. For some reason the dump file does not reflect the new changes made to the database of the old one on the new server when I follow the restore on the guide for packaged installations. 

This is the guide I am following by the way: https://www.openproject.org/operations/backup/backup-guide-packaged-installation/

RE: [Help] PostgreSQL restore database on server migration problem - Added by Max Harper almost 5 years ago

Hello Oliver,

I am having very similar problems and I hope you can help me too :-)  I have been trying all sorts of ways to restore my postgresql database to a mirror open project server but can't figure out how to do it.  Let me explain.

I am running Ubuntu 18.04.3 LTS on a Virtual Private Server (VPS) using OpenProject 10.1.0 stable and Postgresql 10.10-0.  I am pleased to say it runs perfectly from packaged installation and I have numerous projects in the database (which I migrated from mySQL originally).

In case of problems I have a created a mirror of the above machine as a local virtual machine using Virtual Box (VB).  It is running the same version of Ubuntu, Openproject and Postgresql from packaged installation.  All running perfectly but default database only.

When I make the backup on the VPS using the following commands [from https://www.openproject.org/operations/backup/backup-guide-packaged-installation/]

'sudo openproject run backup'

I get the backup files in the backup files in the expected /var/db/openproject/backup/ directory.  

I then ftp the files from the VPS to the VB and attempt to restore on the VB but nothing seems to work.  I have tried various ways using psql, pg_restore and even creating my own manual backup with pg_dump.  The best I've got so far is an apparently successful restore but then I can't access the project databases through open project - they all seem to be missing.  

I have been thinking that it would be helpful if you could tell me the exact command open project is using to create the .pgdump backup file.  I might have a better chance to restore it that way.   I could not find anywhere the format of a .pgdump file.  Is it a .bak file created by pgdump for instance or something else?  

Also do you have any suggestion of what command I should be using to restore the .pgdump database onto my VB machine in this case?  I had been hoping it would be listed on your backup guidance page but there is nothing there to say, just general advice at the moment.  

I fully understand if you're working on lots of other things but I hope you can help with this as being unable to successfully backup open project is pretty critical.  

Many thanks, Max

  • (1 - 6/6)
Loading...