0

Our manual recipe to do a PostgreSQL version upgrade (example from 9.6 to 10.1) looks like this at the moment:

  1. stop DB (via systemctl)
  2. start DB on a different port
  3. pg_dumpall using the different port
  4. mv old data directory to a different location
  5. install new RPM, uninstall old RPM
  6. start DB on a different port
  7. pg_restore the DB
  8. 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.

guettli
  • 3,113
  • 14
  • 59
  • 110

2 Answers2

1

according to the PostgreSQL website there's now a utility called pg_upgrade designed specifically to help during upgrades.

You can find more info there. They include a detailed usage example.

Regards.

Pierre-Alain TORET
  • 1,244
  • 7
  • 14
0

Looks good. And yes, this is a simple procedural sequential approach with no need for object oriented stuff.

Put all your manual commands into a shell-script.

#/bin/sh

Must be the first line. Make sure to use the full path for all commands.

Use parameters for non interactive mode, if interactive is default for the command.

Nils
  • 7,657
  • 3
  • 31
  • 71
  • I stopped writing shell scripts some time ago. What happens if pgdumpall failes? AFAIK the shell would write an error message to stderr and continue with the next line of the script. Yes, I am a sissy ... I don't want loose data... – guettli Nov 27 '17 at 13:31
  • @guettli you should check pgdumpall exit code. – Alexander Tolkachev Nov 30 '17 at 17:21
  • Use set -e in the script and it will stop execution when a command fails to execute. Additionally, you should be handling errors from commands. Salt is just stopping the workflow right? Same concept. – Andrew Domaszek Jan 03 '18 at 16:59