Postgres Errors

Daily Standup

March 14, 2019

The other day I was doing something completely unrelated with homebrew and it erroneously updated postgres. Today I have all kinds of problems:

2019-03-14 17:32:01.898 CDT [7476] FATAL:  database files are incompatible with server
2019-03-14 17:32:01.898 CDT [7476] DETAIL:  The data directory was initialized by PostgreSQL version 10, which is not compatible with this version 11.2.

This message ultimately helped me solve the issue, but how did I get there?

Finding Postgres Issues

The first clue was obvious, I could not connect to the database any longer from my app:

Unable to connect to the database: { SequelizeConnectionRefusedError: connect ECONNREFUSED 127.0.0.1:5432
    at connection.connect.err

I don’t remember exactly what made me look at homebrew first, but I checked to see what services were running, in case Postgres had stopped:

$ brew services list
Name       Status  User   Plist
postgresql started user /Users/user/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

The important thing to note here was that the started status was in green. I tried restarting the service at the suggestion of some StackOverflow article:

$ brew services restart postgresql
Stopping `postgresql`... (might take a while)
==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)
==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)
$ brew services list
Name       Status  User   Plist
postgresql started user /Users/user/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

My app still would not run. Interestingly, now the started status was in yellow.

A bit more StackOverflowing indicated that this means the postgres service had started but without homebrew being able to get a status (meaning maybe it started starting, but didn’t complete it successfully).

So I found a way to find out what the status is while restarting the service again:

$ brew services restart -vvv postgresql
Stopping `postgresql`... (might take a while)
==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)
==> Generated plist for postgresql:
   <?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
   <plist version="1.0">
   <dict>
     <key>KeepAlive</key>
     <true/>
     <key>Label</key>
     <string>homebrew.mxcl.postgresql</string>
     <key>ProgramArguments</key>
     <array>
       <string>/usr/local/opt/postgresql/bin/postgres</string>
       <string>-D</string>
       <string>/usr/local/var/postgres</string>
     </array>
     <key>RunAtLoad</key>
     <true/>
     <key>WorkingDirectory</key>
     <string>/usr/local</string>
     <key>StandardOutPath</key>
     <string>/usr/local/var/log/postgres.log</string>
     <key>StandardErrorPath</key>
     <string>/usr/local/var/log/postgres.log</string>
   </dict>
   </plist>
   

/bin/launchctl enable gui/501/homebrew.mxcl.postgresql
/bin/launchctl bootstrap gui/501 /Users/user/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)

The key here is the StandardErrorPath file location, which you can open to find out what the actual error is:

tail -n 10 /usr/local/var/log/postgres.log

This is where I found the very helpful error I mentioned before:

2019-03-14 17:32:01.898 CDT [7476] FATAL:  database files are incompatible with server
2019-03-14 17:32:01.898 CDT [7476] DETAIL:  The data directory was initialized by PostgreSQL version 10, which is not compatible with this version 11.2.

THE FIX!

Aaaaaaaages ago I wrote about upgrading Postgres, and needing to run that handy `brew postgresql-upgrade-database` command every time the database is upgraded. Homebrew even prompts you to do this when it upgrades Postgres. And I would have even sworn I did it!

I didn’t.

So I ran it again, but there was an error (because maybe I did already run it?). So I deleted the old .old file and tried upgrading again:

Warning!! There was a chance doing this would delete all the data, but I did it anyway. It didn’t delete it, but I recommend looking into the contents of the postgres.old file more closely before playing games with deleting it in future.

$ brew postgresql-upgrade-database
Error: /usr/local/var/postgres.old already exists!
Remove it if you want to upgrade data automatically.
$ rm -rf /usr/local/var/postgres.old
$ brew postgresql-upgrade-database

SUCCESS!!! It updated the data from the old version to the new version, and the started status turned green again.

And most importantly, my app works again 💃💃💃

Other Stuff

I read an interesting article today about generating PDF documents from your site in a Node app. Several options were well-documented, it’s a good read!

Up Next

Working on the user section in the app, it is nearly at MVP status! Well at least the backend function. I can’t even think about UI right now!

Tags icon Stacked price tag looking things Tags: