This post was originally published as a Gist which I since forgotabout and accidentally rediscovered recently when the need arose toresolve a similar accidental upgrade. I felt like it deserved tolive somewhere where there is a chance I might update it.

Update

$ brew services start postgresql@10 Successfully started `postgresql@10` (label: homebrew.mxcl.postgresql@10) however, no postgresql server is running, but the plist file was copied to /Library/LaunchAgents $ pwd && ls homebrew. /Users/ckhall/Library/LaunchAgents homebrew.mxcl.postgresql@10.plist What you expected to happen. I'm on a brew install postgresql@9.6 for legacy reasons, and actually needed to: # First, close all connections, e.g. Application servers and psql connections, then brew services stop postgres killall postgres brew services start postgres. As of writing (1.6.1), doing brew install postgresql@9.6 seems to work, but if you attempt brew info postgresql still points to 10.3 (current stable). Doing brew info postgresql@9.6 looks like it works, but one cannot brew switch to it. Still not sure yet how one resolves that. – Scott Corscadden Apr 23 '18 at.

In Homebrew 1.5 and above, there’s a new process to upgrade yourPostgres database across major versions using the brew postgresql-upgrade-databasecommand which was recently added by Mike McQuaid.

This is a fantastic one-step improvement over the lengthy guide you’llsee below, but since this new command isn’t foolproof (it will fail ifyou haven’t killed all processes connected to your database) it seemslike a good idea to still understand the way it works behind the scenes.

I’ve learned a cool new trick from Homebrew’s upgrade command however and youcan find it in the last section of this updated post.

Cache Cleaner - Clean up useless application cache files -System Booster: Optimize memory to maw which speed up your phone or Tablet -Game Booster - You can manage your games, accelerate loading speed of games. It is a powerful Boost speed performance and free up memory space -CPU Cooler - Minimize CPU temperature and enhance performance. Memory management since Gingerbread (2.3) has been handled very well by Android itself. Also, there is an old adage - Empty RAM is wasted RAM. If you mean app storage space, delete some apps. If you are rooted, you can use something like app2sd to move apps to your SDcard. Not with apps, but with 'System Memory'. The device only has 8 GB of storage, and about 48%+ of that (3.3 GB) is used by System Memory. Well, apps fill 4 GB, but that is why I have removed about 20% of the system apps (like Google, Hangouts, and many more). The internal memory, as opposed to the internal storage, is the short term memory that is used by your Android device. It is more commonly known as the RAM or Random Access Memory. This is the temporary memory used when you open applications on your device. These make use of your internal memory.

Accidental Homebrew Upgrades

This guide assumes that you recently ran brew upgrade postgresql anddiscovered to your dismay that you accidentally bumped from one majorversion to another: say 9.3.x to 9.4.x. Yes, that is a major versionbump in PG land.

Some of the common signs of this issue are the following statements inyour logs:

No need to panic. Your data should be fine. While you’ve upgraded the Postgreslibrary, your existing databases have not been removed. They simply need to beupgraded to run with the new library.

Important Caveat for Non-Incremental Upgrades

The process I describe below will work for incrementalversion updates: for example 9.5 to 9.6. But it will probably not workif you jump versions: for example 9.5 to 10.0.

In order to upgrade properly you will first need to downgrade thePostgres version in Homebrew to the version directly after the one ofyour data directory.

How do you figure out what version your data directory uses? Ask it.

There we go. And what is your currently activated Postgres version inHomebrew?

Good. If you find yourself one step further than you should be, sayon 10.0 like I was. You can do the following to step back down to9.6:

Couldn’t you use this to go back to Postgres 9.5? Yes, you could. Butit’s never a bad idea to stay up to date with newer versions, so whynot use the opportunity to be less worried about upgrades?

Now, check the active version of Postgres with:

Checking Versions

You can check all the versions of postgres that are installed inHomebrew with:

In this case you can see that I don’t have Postgres 9.6 installed in theusual postgres formula, since I had to specifically install that versionin order to be able to upgrade from 9.5 withbrew install postgresql@9.6.

So there are 5 different versions of PostgreSQL installed on my machinein different subfolders. We can find out where those are with:

As you can see I have my old versions installed in/usr/local/Cellar/postgresql/. And what about this new postgres@9.6?

That one’s in a standalone directory:

One final check to make sure we have the correct versions of thePostgres commands running:

This means that the pg_upgrade binary we’ll be using is also the newone. But let’s not assume:

Good good.

Mise en place

The directory where your actual database data is stored is differentfrom the one where your PostgreSQL binaries are installed. Homebrewinstalls the data directory in /usr/local/var/postgres/ and doesn’ttouch that data folder when you upgrade from one version to the next.This is a good thing because if Homebrew tried to install a brand newdatabase (with initdb) it could squash all your existing data.

First I recommend moving your existing data to a directory with adifferent name:

Now that the old data directory has been “moved”, you can safely createa brand new clean database:

Your output will be slightly different. For one, the user won’t beolivierlacan but whatever your system user is. You can find that outeasily with whoami.

Interesting to note, but if you use PostgreSQL with Rails. This meansthere will be no password on your development database, so you can skipthe password field in database.yml or leave it *completely empty.

Shutting Things Down

Strata foto 3d cx 2 for mac. First we have to make sure both database servers are not running when wedo the upgrade:

If you get the following message it’s possible that you have PG inlaunchctl which may prevent you from stopping the server:

In that case let’s remove PG from launchctl for now, you can add it backlater by following the instructions given on brew info postgres:

Then try stopping the server again:

This would be good news:

If you’re using a recent version of Homebrew (as you should), then it’slikely you’re using Homebrew Services instead of launchctl to managepersistent processes like postgres. So use this instead:

While Homebrew Services should do the job of stopping postgres, it’s notguaranteed that you don’t have a server process connected to postgresand locking it’s pid (process ID) file. It’s always a good idea tocheck for that file which may exist at/usr/local/var/postgres/postmaster.pid. If it does, remove it withrm /usr/local/var/postgres/postmaster.pid and you’ll save yourselfsome surprises later on.

Upgrading

I’ve based this article on the fairly detailed pg_upgrade guide in thePostgreSQL documentation. If you have specific needs (very largedata directory for example), you should consult it too. This sectionis also not specific to a macOS Homebrew install of Postgres since ituses the same commands you should be able to use on other operatingsystems like Linux.

Assuming you’re dealing with the same version numbers I’m dealing with(you probably aren’t, so change them when running this on your machine),this is what the pg_upgrade command should look like when you run it:

Lowercase flags (-b and -d) are for old binary and datadirectories respectively. Their uppercase counterparts are for their newequivalents.

I really dislike short form flags because they make self-descriptive discoveryvery difficult so here is the alternative with long-form flags:

How nice is this?

You should see the following output immediately if the upgrade processis starting:

You’re done!

Restarting

You manually shut down PG during this upgrade so now it won’t be runningunless you follow the (old) brew info postgres instructions:

With Homebrew Services, the operation is even simpler:

Once PG is running you could run the optimization script recommended bypg_upgrade. It was created in whichever directory you ran pg_upgradein, and you can run it with:

Troubleshooting

You may encounter the following error:

This means you have at least one PG server running. So go back to thebeginning of this section and make real sure you shut down all yourservers.

You can also check this pg_upgrade_server.log file to be certain ofthe issue. It should be located in/usr/local/var/log/pg_upgrade_server.log but you can always use thefind command to locate it:

Postgresql Brew Types

When using brew postgresql-upgrade-database, this log should containthe reason the upgrade process failed as well as the actual commandused, which will be very useful for you to restart the upgrade processmanually.

I’ve found that running brew postgresql-upgrade-database again afteran initial failed attempt usually results in an error message sayingthat the data was already upgraded when it’s not actually true or atleast the process hasn’t fully completed.

You can see how this script works by checking its source code. Itseems like it’s comparing the Homebrew-installed version to theversion of the currently active Postgres database inside of the /var/postgresdata directory. It references the PostgreSQL documentation upgrade articleas well. The only notable difference with the instructions I’ve listed aboveis the use of the -j flag, which defines how many threads or processes thepg_upgrade command will use. Homebrew sets that number toHardware::CPU.cores.to_s which would be 2, 4, 6 or more CPU cores your machinemay have to handle CPU-intensive tasks like these.

Homebrew Postgres

However I’d recommend not doing this since you may have operations alreadyusing some CPU on your machine. My best tip is to use one less core than yourtotal, which I learned from this excellent Thoughbot blog post on parallelRubyGem install configuration for Bundler:

Brew Postgres

Google chrome energy impact mac. So here’s the final command in its full glory for all of you who skippedstraight to the bottom of this post:

Postgresql Brew Free

Safe upgrades!