We have stored our QGIS projects in a PostgreSQL database.
Now we have to move the server that hosts the database that many projects access. Is there a way to change the connection parameters within each project afterwards?
For projects stored outside of the database, the XML can be easily changed in a text editor, but unfortunately the projects stored in the database are in binary format.
Perhaps the overlay function for binary string representation (bytea) data makes you smile. It is the equivalent of what you do with 'replace' in a text editor.
For further reading: https://www.postgresql.org/docs/12/functions-binarystring.html
My first attempt was to encode the binary field as base64 and then edit it by using replace() or overlay(). The problem with this is that a project saved as qgz is zipped. This means that even if you encode it, you won't get the XML to change text passages. As a result you only damage the project file and the project can't be opened anymore.
My workaround now is a python script that changes the source URI of all postgres layers in the project at once and then writes the project back to the database. I didn't find a way to edit the projects directly on database level, but it would at least require the .qgz files to be unzipped.
One way to avoid this problem would be to store the projects as .qgs in the database, so the project files would be available as plain text and thus editable.
