Considering a very good job, career leap up, but would require moving away from my son, has anyone been in this situation?

I’m divorced, I live in a small town without a lot of opportunity. I got offered a (seemingly) dream job in a field I love doing what I’ve always wanted to. It’s a significant step up in my career but would require moving away from my 9 year old boy, and I just don’t think I can do that. I work away from him two weeks at a time now and it kills me. I’m all about short term sacrifice for long term gain, but I absolutely hate being away from him daily. So do I take the job, significantly grow my career, but likely lose time with my son? Or do I stay put and invest in his life but sacrifice my career?

Options are 1. Get his mom to move to the same area (I would assist with that) but this is very unlikely. 2. Commute back and forth as often as possible but this job is 100% in person. 3. Have him half the year

If anyone has been a child with a long distance father, or in this situation I would love to hear how you navigated this and how it turned out.

TLDR: offered a great job but would require moving away from my son. Any advice is appreciated.

UPDATE: I turned it down. There will be other opportunities. Pursuing remote PM work. Thanks all.