search

dimitri / pgcopydb

Copy a Postgres database to a target Postgres server (pg_dump | pg_restore on steroids)
Other
1.19k stars 78 forks source link

Problem: Couldn't exclude a specific extension #740

Open arajkumar opened 7 months ago

arajkumar commented 7 months ago

As far as the extension migration is concerned, pgcopydb supports either all or nothing approach via --skip-extensions CLI flag.

While it might work for most use cases, it does not work in a few instances where the source includes extensions that are not directly consumed by the application but are provided only for management purposes by some cloud providers. e.g. https://github.com/aiven/aiven-extras

When attempt migration from the source which has extensions like above, pgcopydb would fail because the same extension won't be available in the target as it is not really needed.

11:09:04.338 267470 ERROR pg_restore: error: could not execute query: ERROR: could not open extension control file "/usr/share/postgresql/16/extension/aiven_extras.control": No such file or directory 11:09:04.338 267470 ERROR Command was: CREATE EXTENSION IF NOT EXISTS aiven_extras WITH SCHEMA aiven_extras; 11:09:04.340 267470 ERROR Failed to run pg_restore: exit code 1

It is not possible to drop those extension on source due to restrictions. I think the only option would be discarding them during migration using filters.

My proposal is to introduce a new filtering section, [exclude-extension] and take list of extension to be excluded during migration.

filter.ini

[exclude-extension]
aiven_extras
dimitri commented 7 months ago

Hi @arajkumar ; that's a good idea for a needed feature. Do you want to work on it?

arajkumar commented 7 months ago

@dimitri , Absolutely! I already have a WIP patch, will create a PR soon. Thanks for reviewing this issue.

theothornhill commented 4 months ago

@arajkumar Did you get any further with this?

arajkumar commented 4 months ago

@theothornhill We have implemented on our fork which is yet to be upstreamed. The implementation also has to adopt the new sqlite catalog changes, hopefully will do some overhaul next week.

theothornhill commented 4 months ago

Good news! I'm available to test, if needed :)

jarvis-telus commented 2 weeks ago

Hey @arajkumar!

Would your PR be pushed anytime soon? I have an exact use case for this feature 🥹 I'd be happy to contribute incase you need an extra pair of helping hands or typing fingers? 🤔

arajkumar commented 2 weeks ago

Hello @jarvis-telus, Sorry, I don't have time to work on this atm.

I can share the work which I did on our fork, feel free to take it forward.

https://github.com/arajkumar/pgcopydb/commit/d3297962bf199e5959f94c04015fa35835718516