Getting Back to an Old PostgreSQL Specific Version

TL;DR

Exemple about getting back to 9.6.6 from 9.6.7

brew services stop postgresql 
brew services stop postgresql@9.6

brew uninstall postgresql 
brew uninstall postgresql@9.6

rm -rf /usr/local/var/postgres .psql_history .psqlrc .psql.local .pgpass .psqlrc.local

Select the Postgres.app release having the desired version here. Install it, "Initialize" the server then run:

sudo mkdir -p /etc/paths.d && echo /Applications/Postgres.app/Contents/Versions/9.6/bin | sudo tee /etc/paths.d/postgresapp

Reboot the shell.

Details

The PostgreSQL version of the project I'm working on at work is 9.6.6. The problem is that my machine uses a later version. It results in conflicts with the db/structure file each time I run a db:migrate.

Like most dev running on macOS, I usually use Homebrew. It allows to easily switch to another installed version of a formula by running brew switch formula@version. As you guess I tried, but the problem is that Homebrew only handles major versions of each release and I need a specific minor version.

Because I also need to switch between old and recent Rails projects, I often have to change the running PostgreSQL version. My current solution is to use the Postgres.app.

If you already have PostgreSQL installed on your system via Homebrew but want to use a specific version (like a previous one) with Postgres.app, just follow these steps:

1. Remove all PostgreSQL version installed

• Check running version brew services list then stop them brew services stop postgresql.

• Delete all installed formulas with brew uninstall postgresql and brew uninstall postgresql@version per installed version.

• Then delete the related files rm -rf /usr/local/var postgres .psql_history .psqlrc .psql.local .pgpass .psqlrc.local.

2. Install Postgres.app

• Download the release having the desired version.

• Install it and create a new server:

• Initialize it.

3. Make PostgreSQL related tools usable with the terminal

This last step allows you to use tools like pg_dump etc. Just run:

sudo mkdir -p /etc/paths.d &&
echo /Applications/Postgres.app/Contents/Versions/9.6/bin | sudo tee /etc/paths.d/postgresapp`
```

(Don't forget to replace 9.6 with your version).

Relaunch your shell. Welcome back to the past!

If you have better solution to switch between specific versions of PostgreSQL without external app like I did, don't hesitate to share it! :)

Comments

[rhymes]

Have you tried to investigate what's the difference between 9.6.6 and 9.6.7 ? It's weird that two minor versions are incompatible like that.

It results in conflicts with the db/structure file each time I run a db:migrate

Can you elaborate?

Maybe we can sort this out :-) I would understand it for major versions but this is a patch of the same version :D

[Gaëtan Masson]

It's not really breaking changes. It changes the file like adding "public" to each table name, updating the header like "Dumped by pg_dump x.x.x" etc.

[rhymes]

Ah ok! :-)

[Jeremy]

These are the worst possible instructions for downgrading postgresql.
rm -rf /usr/local/var/postgres will delete ALL of your local databases.

[Gaëtan Masson]

It's about local development environment and usually you don't care about these data. But it's true I should warn people.

Feel free to share the good instructions. 🙂