Our manual recipe to do a PostgreSQL version upgrade (example from 9.6 to 10.1) looks like this at the moment:
- stop DB (via systemctl)
- start DB on a different port
- pg_dumpall using the different port
- mv old data directory to a different location
- install new RPM, uninstall old RPM
- start DB on a different port
- pg_restore the DB
- stop DB running on a different port, start DB on default port.
We use a different port during dump/restore to make it atomic. We want to prevent connections during this phase.
Maybe this procedure is too complicated, I don't know.
Up to now we do this by hand, but automating it would be nice.
We use Salt for configuration management. But this is a state based tool. Above recipe looks more like a procedural thing.
How to automate this using Salt?
If there is a better solution (without Salt) ... nice. Please tell me.