For some reason my postgres setup had a havoc following an upgrade of my Homebrew installation that I, involuntarily, happened to start. One of the consequences was that my postgres server could not start, and I could not access the GeoImagine Framework database. I tried several suggestions that I found in various fora online. Until I discovered that the postgres version I had been using was missing and had been replaced by the latest version. The database as such was intact. But I also needed the postgres version associated with the database, and that was gone. I could restore the postgres installation from my backups (Mac OSX TimeMachine), but I could not manage to restore the connection to the actual database. Thus I gave up.
Trying to solve the problem I discovered the page Migrating Homebrew Postgres to a New Version by Olivier Lacan. It seems to me to be the best source for solving connection problems related to Homebrew updated. It, however, assumes that your original installation Homebrew installation of postgres is intact.
In the end I could not solve the problem. The Homebrew update had deleted my active postgreSQL installation (under Cellar etc). Without it, it is not possible to restore or migrate your database. I tried restoring the postgreSQL setup uing a backup (Mac OSX TimeMachone) but secondary connection problems and incomplete installation were reported errors. I found a number of suggested solutions on stackoverflow. Trying some of them, I got no one to work properly.
Alternative to homebrew
Getting Back to an Old PostgreSQL Specific Version
Introduction
This post is only relevant if you installed postgres using Homebrew on Mac OSX. This is how I usually install postgres on Mac OSX, and it outlined in the post Install postgreSQL and postGIS. This particular solution includes two different installations that need to communicate: the directory where your actual database data is stored, and the Homebrew postgres installation directory. The first thing you have to do for fixing your postgres access is thus to check your installation.
Check your installation
In my case I accidentally upgraded my complete Homewbrew postgres installation, while also erasing all older version. Homebrew postgres installations are found under the path /usr/local/Cellar/postgresql/. The postgres actual data, however, are found under another path, /usr/local/var/postgres/.
Check installed and active (Homebrew) postgres versions
You can check which Homebrew postgres versions you have installed from the Terminal by listing (ls) the content of the Homewbrew postgres installation directory:
$ ls /usr/local/Cellar/postgresql
Each version of postgres resides in sub-folders and will be listed. For example:
10.1 11.5_1
You can also ask Homebrew for which versions are installed:
$ brew list --versions | grep postgres
The returned list can exceed the number of actual folders, for example:
postgresql 11.5_1 10.1
postgresql@10 10.10_1
This means that your system is setup with multiple versions under one of your instalaltions.
Your system can contain multiple versions even if there is only a single directory under /usr/local/Cellar/postgresql/.
postgres command line tool psql to get the active version:
$ psql --version
The prompt should return something like:
psql (PostgreSQL) 11.5
Stop your postgres services
If you need to change your postgres version, or do other maintaenance work on postgres involving Homebrew, you should stop the brew service running postgres:
$ brew services stop postgres
HERE
Before swithiching you ahve to unlink. I had made the mistake of istanlling a sub (@) version replicated the version of my old (deleted) postgres stuff (10.10). Thus I had 2 version of 10.10 one stand alone (postgresql 10.10.1) and one under a heiger postgres version (posgresql@10 10.10.1). I had linknked to the slave (@) version, and needed to unlnik.
$ brew unlink postgresql@10
You can now do a “dry-run” to test what effect –overwrite would have:
<>brew link –overwrite –dry-run postgresql<>
In my case, having previously linked to version @10.10 1, I get the message
<>Warning: Already linked: /usr/local/Cellar/postgresql/10.1<> <>To relink: brew unlink postgresql && brew link postgresql<>
Uninstall postgres
I needed to ununstall my slove (@) version of postgres as it caused confusion (whether for me of Homebrew is unclear). First list you Homebrew installations:
$ brew list
Identify the keg (?) you want unistall (e.g. postgresql@10), and then run:
$ brew uninstall postgresql@10
If you rerun $ brew list, postgresql@10 should no longer be listed. List the postgres versions available with brew:
$ brew list --versions | grep postgres
The version you removed should not occur in the returend list.
Finally???
Trying to unlink, or link, instead runoff
brew unlink postgresql && brew link postgresql
But then I got another error, the readline version was to high /for 11-5 I assume), and had to be downgraded.
<>brew switch readline 7.0.1<>
Check the postgres database
Remember that your actual postgres databse resides under the directory /usr/local/var/postgres/. The version is given in the file PG_VERSION. To see the version from the content of PG_VERSION you can use the command line tool pico:
$ pico /usr/local/var/postgres/PG_VERSION
You might have to use “super user do” (sudo) to view the content:
$ sudo pico /usr/local/var/postgres/PG_VERSION
You can also get the version of the actual protgres database by typing:
$ cat /usr/local/var/postgres/PG_VERSION
The result should be a single number (integer or float)
If the version of your actual postgres database (as stated in PG_VERSION) and your active postgres Homebrew installation are the same, you are fine. If not you need to harmonize.
Upgrade the database or downgrade postgres
if your actual postgres database and your active postgres (app or server) installation do not correspond, you have two options: downgrade you postgres installation or upgrade your postgres database.
Backup your existing database
I am not an expert on postgres databases, and I feel that from here on it is like skating on thin ice. Thus I start by backing up my actual postgres database.
$ cp -R /usr/local/var/postgres/ /usr/local/var/postgres.10.0.backup/
If you feel more comfortable, you can instead move the exising database:
$ mv /usr/local/var/postgres/ /usr/local/var/postgres.10.0.backup/
Downgrade postgres (app or server) installation
In general, downgrading is not recommended, but sometimes it might be necessary. Downgrading your postgresql installation under Homebrew can be done by installing a parallel (downgraded) version.
$ brew install postgresql@10
$ brew services start postgresql@10
$ brew link postgresql@10 –force
If your existing (higher) postgres version is active, the –force will not take effect and an Error will be reported. Two alternatives are suggested, either to unlink postgresql or –overwrite existing links.
I tried $ brew unlink postgresql folloed by $ brew link postgresql@10 –force
did not work
I then tried another thing, namely “brew postgresql-upgrade-database”
It seems the postgres server was running and I had to stop it
Stop the db $ pg_ctl -D /usr/local/var/postgres stop -m fast $ pg_ctl -D /usr/local/var/postgresql@10 stop -m fast $ pg_ctl -D /usr/local/var/postgres.10.0.backup stop -m fast
repeat the commands until you get the response:
pg_ctl: PID file “/usr/local/var/postgresql@10/postmaster.pid” does not exist
Is server running?
If the server does not stop (or restarts) you have to check your launchagent. You can open the directory on your active postgres and check if the .pid file is recreated directly after stopping. Fopr me that happened.
but with the latest version of Homebrew, you have stop like this $ brew services stop postgresql
In between the trials, the script (“brew postgresql-upgrade-database”) always managed to move the db into a subfolder (‘postgres.old’) inside the folder ‘postgres’. So for each trial I had to move the folder content up one level. And then I also had to reset the permit for the ‘postgres’ folders
$ chmod 0700 /usr/local/var/postgres
But the same Error appeared at evey try:
Error: Failure while executing; /usr/local/Cellar/postgresql/11.5_1/bin/initdb --lc-collate en_US.UTF-8 --lc-ctype en_US.UTF-8 /usr/local/var/postgres
exited with 1./span>
Several pages suggests that this relats to the collation (lc-collate) and ctype (lc-ctype), as they seem to have been given as constants in earlier versions of brew-postgresql-upgrade-database.rb, but later versions seems to read the correct colaltion and Ctype from the existing db.
Further trial on shutting down $ launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist But my plist is somewhere resampled
OK, understand, I forced version 10 and then a new launchagent was CreateErsHeadersMonthlyOkaLinyantiNgami $ ls ~/Library/LaunchAgents
The postgres launvhagent will something like
homebrew.mxcl.postgresql*.plist</file>
Stop the launchagent from restarting postgres in 2 steps: $ launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql*.plist launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql@10.plist rm ~/Library/LaunchAgents/homebrew.mxcl.postgresql@10.plist
Renewed energy. Uninstall postgresql $ brew uninstall postgresql brew uninstall postgresql@10 but because I have postgis installed (a dependecny) I have to run the command
$ brew uninstall –ignore-dependencies postgresql
brew install postgresql@9.6 brew services start postgresql@9.6 brew link postgresql@9.6 –force
brew services start postgresql@10.1
TRIAL 5 or so
Brew Uninstall postgreSQL Install a higher subversion (but not main??) compared to your PG_VERSION
(e.g. brew install psogresql@10)
Remove old symlinks $ brew unlink postgresql
overwrite links withthe version you wnat to have brew link –overwrite postgresql@10
Force link $ brew link postgresql@10 –force
If you try out your version $ psql –version
The response should the version you want to migrate to (remember it should be a higher version than the data but within the same main version).
So Ih ave 10.10 and 10 and will now try the next step.
Check the pg_upgrade version that is active
$ pg_upgrade –version
the response should be the same version that you –forced linked above (10.10 for me)
Create a new db
As you have lost the contact to your existing database, you need to create a new one. Move you old db to a backup.
$ mv /usr/local/var/postgres/ /usr/local/var/postgres.10.0.backup2/
Then create a new db
$ initdb /usr/local/var/postgres/
he files belonging to this database system will be owned by user "thomasgumbricht".
This user must also own the server process.
The database cluster will be initialized with locales
COLLATE: C
CTYPE: UTF-8
MESSAGES: C
MONETARY: C
NUMERIC: C
TIME: C
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
creating directory /usr/local/var/postgres ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Europe/Stockholm
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /usr/local/var/postgres/ -l logfile start
Shut down all postgres servers
pg_ctl -D /usr/local/var/postgres stop -m fast pg_ctl -D /usr/local/var/postgresql@10 stop -m fast pg_ctl -D /usr/local/var/postgres.10.0.backup stop -m fast
The chances are that the server restarts immediately. You have to first stop and then delete the plist file. K, understand, I forced version 10 and then a new launcagent was CreateErsHeadersMonthlyOkaLinyantiNgami $ ls ~/Library/LaunchAgents
The postgres launvhagent will something like
homebrew.mxcl.postgresql*.plist</file>
Stop the launchagent from restarting postgres in 2 steps: $ launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql@10.plist launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql@10.plist rm ~/Library/LaunchAgents/homebrew.mxcl.postgresql@10.plist
Now try stopping the postgres servere from running again. Check that the .pid file does not reappear.
Then also try (to be sure)
$ brew services stop postgresql
It should repond with an errors
Error: Service postgresql
is not started.
if the pid is still around force remove it
rm /usr/local/var/postgres/postmaster.pid extracted
Upgrading
pg_upgrade -b /usr/local/Cellar/postgresql/9.5.4_1/bin/ -B /usr/local/Cellar/postgresql@10/10.10_1 -d /usr/local/var/postgres.9.5.backup/ -D /usr/local/var/postgres
pg_upgrade -b /usr/local/Cellar/postgresql/9.5.4_1/bin/ -B /usr/local/Cellar/postgresql@9.6/9.6.6/bin/ -d /usr/local/var/postgres.9.5.backup/ -D /usr/local/var/postgres
pg_upgrade -b /usr/local/Cellar/postgresql/10.1/bin/ -B /usr/local/Cellar/postgresql@10/10.10_1/bin/ -d /usr/local/var/postgres.10.0.backup/ -D /usr/local/var/postgres
$ pg_upgrade
-d /usr/local/var/postgres.10.0.backup
-D /usr/local/var/postgres
-b /usr/local/Cellar/postgresql/10.1/bin/
-B /usr/local/Cellar/postgresql@10/10.10_1/bin/
-v
Resources
https://olivierlacan.com/posts/migrating-homebrew-postgres-to-a-new-version/
https://stackoverflow.com/questions/13410686/postgres-could-not-connect-to-server
https://apple.stackexchange.com/questions/304024/how-do-you-install-an-older-version-of-postgres-9-6-using-homebrew