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.)

1
  • Hi ThomasG77 and thanks for sharing your snippet of code! I used it but, unfortunatley, the layer uri do not persist in the project. After running code, in python console uri and newUri are the same. If I save the project, on the contrary, the newUri is not saved! Have you any ideas? Thanks in advance Fabrizio Mar 11 at 13:08

1 Answer 1

7

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)

2
  • 1
    Search & replace would be my suggestion, too.
    – Erik
    Jul 3, 2020 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, 2020 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.