3

I have a QGIS project with around 100 layers referring to a postgres database.

Since we're migrating to a new server, the database will be transfered too, but will be given a new IP. The connection between all layers in my QGIS project will be lost, due to a slight alteration in the source connection.

Is there a way to change the datasource of all layers, instead of the need to refer to this new db, layer by layer?

(Extra info: the way how the postgres database is structured isn't changing, the IP is the only thing being changed.)

6

I know at least 2 ways to do it

  • Do a search and replace. For this intent, close the project. If it's a qgz file , rename it to qgs.gz and open the archive to find a qgs file. Edit it in place by researching the old ip and do a search and replace. Then, rename your qgs.gz to qgz and open in QGIS

  • PyQGIS

your_host = 'your_new_ip'
layers = QgsProject.instance().mapLayers().values()

for layer in layers:
    if layer.dataProvider().name() == 'postgres':
        provider = layer.dataProvider()
        uri = provider.uri()
        newUri = QgsDataSourceUri()
        newUri.setConnection(your_host, uri.port(), uri.database(), uri.username(), uri.password())
        newUri.setDataSource (uri.schema(), uri.table(), uri.geometryColumn())
        provider.setDataSourceUri(newUri.uri())
        provider.reloadData()

On the long term, I would use a pg_service.conf file to declare the database infos outside your QGIS project. See https://docs.qgis.org/2.14/en/docs/user_manual/working_with_vector/supported_data.html#service-connection-file (old doc but always working)

| improve this answer | |
  • 1
    Search & replace would be my suggestion, too. – Erik Jul 3 at 11:11
  • Grr, that I didn't think of using find/replace myself! Thanks anyway :-) Your suggestion for the long term seems interesting, but it would store db passwords in an unencrypted way? – Koen Ver Jul 6 at 7:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.