How to copy content between different CDN buckets and update the 'files' reference

I have multiple strapi environments for the same site

  • local
  • staging
  • production

I can copy code up and synchronise databases

I have a CDN bucket (using Digital Ocean spaces via strapi-provider-upload-do )

I can copy CDN files between buckets

My problem is that the database ‘files’ table contains references to the url of the files uploaded - including the bucket

So when I load the data from production to staging I still get images linked via the production site

I had hoped that only the relative path would be stored and the URL recreated from the strapi-provider-upload-do config

Is there a way of doing this other than a search-and-replace on the database dump ?

It seems like running this SQL on the files tables does what I need

NB I am using postgresql

update files set url=REPLACE(url, 

update files set formats=REPLACE(formats::text,