DEV Community 👩‍💻👨‍💻

Gaëtan Masson
Gaëtan Masson

Posted on • Updated on

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.

Alt 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

Alt Postgres.app

  • 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`
Enter fullscreen mode Exit fullscreen mode

(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! :)

Top comments (5)

Collapse
rhymes profile image
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

Collapse
gaetanm profile image
Gaëtan Masson Author

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.

Collapse
rhymes profile image
rhymes

Ah ok! :-)

Collapse
brohemian6 profile image
Jeremy

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

Collapse
gaetanm profile image
Gaëtan Masson Author

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. 🙂

🌚 Life is too short to browse without dark mode