Dear Survey Solutions,
We’re in the middle of data collection for a longitudinal project. We have an existing server, but we are experiencing issues with the local internet service provider. We’d like to move the server to a cloud host.
We have configured a new survey solutions server on a windows cloud VM. It is working fine.
Can we dump the postgresql database (Survey Solutions) and restore it on the the new VM?
Is there a better way to do this?
We will have to use a new domain name and will have to reset our data collector tablets–that is okay.
@fvandyk , from your description I understand that you:
- will be changing the actual equipment to host the server;
- will be changing the domain name
- will be resetting the tablets
To me this looks like just setting up a new (second) server. Why cluttering it with old data?
Just do a new installation after a round of your survey and start your new round on a new server. Synchronize all tablets, download all data from the old one and dispose of it. What am I missing?
Yes, your understanding is correct.
I think you are correct, it may be best to start fresh here.
Because we have been running for over a year already, I was hoping to minimize the number of form versions. For example, our Enrollment form already has 3 versions. But, you’re correct, it is not a hard task to stack a 4th table onto these existing versions.
And from what you are implying, it may not be simply a question of restoring the postgreSQL database on the new machine? There may be values in the database that would break the program?
From the context of your survey you may still decide to start with an empty server, but let me still answer the question of database restore, which may still be needed for users who for any other technical reason need to restore from a backup made earlier (everyone remembers to do systematic backups for ongoing surveys, right?)
Indeed, creating backup file (using pg_dump) and then restoring using pg_restore would work. Please make sure that there is no running headquarters application writing to either old or destination databases while you’re doing this.
If you setup the postgres in the same way, including the same username (another reminder, it’s much secure to use a dedicated user for each application with limited roles instead of using builtin ‘postgres’ superuser) then the restore will just work.
Database objects like schemas and tables are by default owned by the user who created them, so if for any reason you’re changing the usernames between the old and new databases, restore will fail saying that doesn’t exist. You can avoid this error by using parameter to ignore ownership when restoring.
Thanks for the clarification Zurab.
It’s good to know we have this option and that we could restore a backup if needed. These tips seem important:
- Use the precise postgres username
- Make sure to stop headquarters while creating backup and while restoring.