Question #1966
closed
Button to import sql dump
Added by Enric Rodellas almost 2 years ago. Updated almost 2 years ago.
Description
I would like to have a button in administration area to import sql dump generated with export SQL
Updated by Alexander Watzinger almost 2 years ago
- Tracker changed from Feature to Question
- Category set to Data import/export
- Status changed from New to In Progress
- Assignee set to Enric Rodellas
I turned this issue into a question because this is outside the scope of OpenAtlas application and more related to security and permission topics (at least on Linux based systems). See details below but thank you for raising this topic.
Permissions
For interacting with the database via the application (in a web browser via the user interface) a "regular" PostgreSQL user is used. In a standard install this PostgreSQL user is called "openatlas". But to drop/create databases, add extensions and so forth (which would be needed for importing the SQL dump) a database super user is needed. On Linux systems this would be the "postgres" user which is kind of special in so far, that this user has no regular username/password login so it wouldn't be possible to e.g. write a script and ask for credentials. This is all related to security and even if we could work around it somehow, it would be very questionable to do so.
Other issues
Even if we would make this work there would be issues like e.g. the user you are logged in with might than not exist anymore. Another issue would be that if anything went wrong, it would leave you with a broken install and because the user interface depends on an installed database you wouldn't be able to fix the problem (or even get error messages) via the user interface.
Question
May I ask which scenario you had in mind where you would need this. E.g. if it is to use an existing database when doing the first install there may be other approaches to solve this.
Updated by Enric Rodellas almost 2 years ago
Alex,
I'm trying to get the worst scenario: computer/server crash, postgres db lost, etc... How we could guarantee that data is save and we could install scenario (server, database, data) from the start.
In the same way that sql backup is provided, I think there should be a sql restore or mechanisms that, from the administration user, restore the state of the database and uploaded files.
I would like absolute confidence that the data (and relationships between them) are not lost and that the database can be restored. I see that in the json and rdfs the semantic coherence is maintained, but I need mechanisms that allow the entire application to be reset.
Maybe there is a documented procedure, but I haven't found it. I work in computing department, and the question, where are my data? It is the first that arises in the event of a catastrophe.
Updated by Alexander Watzinger almost 2 years ago
- Status changed from In Progress to Closed
It is possible (for users with the role admin or manager) to make SQL dumps via the backend anytime: admin -> data -> SQL export. Also, every time the database_upgrade.py script, which is used at an upgrade to a new OpenAtlas version, is called before doing database changes. These backups are located at files/export and because the date of creation is included in the filename it is easy to see when they were made.
Of course it is good practice to store these SQL backups on another machine too, e.g. in case of a hard disk failure, but this is outside the scope of OpenAtlas.
In case these backups are needed, lets say for example an import went wrong, the workflow on a Debian machine would be as follows. The commands are executed as postgres user and it is assumed the database and user are called openatlas.
- Delete the current database
dropdb openatlas
- Create a new, empty database with the owner openaltas
createdb openatlas -O openatlas
- Import the (unpacked) backup into the new database
psql openatlas < path/to/the/unpacked/backup.sql
I'm not sure about adding (and maintaining) this to the documentation. A lot of technologies are involved when working with OpenAtlas and we can't write tutorials for all of them.
In this case we are talking about basic database commands, there is a lot of documentation (e.g. https://www.postgresql.org/docs/) and tutorials in the internet which explains it better and in much more detail.
Also, the workflow differs between operating systems and depend on other factors like e.g. what the database and database user is called, if it was installed "standalone" or via docker, ...
However, I put this into the Redmine wiki for now, see SQL commands