CODEWITHSUNDEEP
macospostgresqlhomebrew
coisnepe
coisnepe

Reputation: 532

Postgres databases and roles disappeared

Yesterday evening I closed my macbook's lid and left work. Came back this morning, turned my computer on, and upon trying to log into psql got a warning that the postgres role didn't exist... Upon further inspection, it seems that all but one of my databases are gone (and the default template0/1, postgres and my user's), as well as every roles but mine (<user>). \du+ in the psql console confirms my user has superuser rights. I still tried $ psql -d database_that_disappeared, to no avail. Tried switching to the other postgres versions I have installed locally (9.5.3 --> 9.6.2 --> 9.5.3), with no luck.

I obviously haven't run any brew update or upgrade, nor has OSX automatically updated anything, as I've turned automatic updates off. I have tried both shutting down and rebooting, to no avail either.

Edit: /usr/local/var/postgres/base shows 26 folders + pgsql_tmp, which makes me feel that the data itself isn't gone?

Upvotes: 1

Views: 3991

Answers (1)

Felipe N Moura
Felipe N Moura

Reputation: 1627

Just to let you know. I was facing the very same problem. Realised I had a process called "Google" that was listening to the port 5432 (found out using the command:
lsof -i -n -P | grep 5432

Besides that, my user (in the OS) disappeared as well!

Well, I found out the problem was that I did have two instances of PostgreSQL installed. When my mac restarted for an update, it started automatically the wrong one! That looks to have happened due to updating the PGAdmin3 to 4. The 4th installs a new version of the PostgreSQL with it and, instead of replacing the previous version (as I expected) it kept both side by side.

Again, this is what I THINK has happened, because I realised I'm suddenly using a previous version of PGAdmin, with the newest version of Postgres (it even triggers a warning message saying there is a version mismatch).

I hope this has helped someone :) And if you figure out WHY/HOW that happened, let me know.

Upvotes: 1

Related Questions