Skip to content
This repository has been archived by the owner. It is now read-only.

Unable to install Postgres after hitting incompatible database files, even after Postgres is removed #35240

Closed
aprescott opened this Issue Dec 24, 2014 · 39 comments

Comments

Projects
None yet
@aprescott
Copy link

aprescott commented Dec 24, 2014

This seems to be similar to #34885.

At some point in the last few days, I upgraded my installed Homebrew packages, and then today I encountered this error when trying to start Postgres:

$ postgres -D /usr/local/var/postgres
LOG:  skipping missing configuration file "/usr/local/var/postgres/postgresql.auto.conf"
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.3, which is not compatible with this version 9.4.0.

So I figured I would just reinstall Postgres using brew. The same error kept showing, though. I decided to manually remove Postgres from /usr/local and then try the install again.

The shell commands I ran are below, but note that, like, #34885, even starting with a clean slate, I end up with /usr/local/var/postgres owned by root, not my user. The directory is in fact empty, and no errors were given during installation, as you can see here.

I've searched for the database incompatibility error and the error about the missing .conf file, but nothing came up, except for #34885, which was closed due to user error from manually creating /usr/local/var/postgres. However, in my case, even after rming that directory, it comes back as owned by root.

It seems I'm unable to install Postgres.

$ brew uninstall postgres
Uninstalling /usr/local/Cellar/postgresql/9.4.0...
$ brew uninstall postgresql
Error: No such keg: /usr/local/Cellar/postgresql
$ brew remove postgres
Error: No such keg: /usr/local/Cellar/postgresql
$ brew remove postgresql
Error: No such keg: /usr/local/Cellar/postgresql
$ rm /Library/Caches/Homebrew/postgresql-9.*
$ ps aux | grep postgres
adamprescott    18364   0.0  0.0  2432772    472 s000  R+   10:47AM   0:00.00 grep postgres
$ ps aux | grep psql
adamprescott    18366   0.0  0.0  2441988    668 s000  S+   10:47AM   0:00.00 grep psql
$ rm -rf /usr/local/var/postgres/*
$ rmdir /usr/local/var/postgres
$ find /usr/local -iname "*postgres*"
/usr/local/Library/Aliases/postgres
/usr/local/Library/Formula/check_postgres.rb
/usr/local/Library/Formula/postgres-xc.rb
/usr/local/Library/Formula/postgresql.rb
$ brew upgrade
$ brew install postgres
==> Downloading https://downloads.sf.net/project/machomebrew/Bottles/postgresql-9.4.0.yosemite.bottle.tar.gz
######################################################################## 100.0%
==> Pouring postgresql-9.4.0.yosemite.bottle.tar.gz
==> Caveats
If builds of PostgreSQL 9 are failing and you have version 8.x installed,
you may need to remove the previous version first. See:
  https://github.com/Homebrew/homebrew/issues/2510

To migrate existing data from a previous major version (pre-9.4) of PostgreSQL, see:
  http://www.postgresql.org/docs/9.4/static/upgrading.html

When installing the postgres gem, including ARCHFLAGS is recommended:
  ARCHFLAGS="-arch x86_64" gem install pg

To install gems without sudo, see the Homebrew documentation:
https://github.com/Homebrew/homebrew/blob/master/share/doc/homebrew/Gems,-Eggs-and-Perl-Modules.md

To have launchd start postgresql at login:
    ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
Then to load postgresql now:
    launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
Or, if you don't want/need launchctl, you can just run:
    postgres -D /usr/local/var/postgres
==> Summary
�  /usr/local/Cellar/postgresql/9.4.0: 2990 files, 40M
$ ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
/Users/adamprescott/Library/LaunchAgents/homebrew.mxcl.postgresql.plist -> /usr/local/opt/postgresql/homebrew.mxcl.postgresql.plist
$ brew info postgres
# ...
$ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
$ postgres -D /usr/local/var/postgres
postgres cannot access the server configuration file "/usr/local/var/postgres/postgresql.conf": Permission denied
$ ls -la /usr/local/var
total 0
drwxr-xr-x  10 adamprescott  admin   340B Dec 24 10:52 .
drwxrwxr-x  22 root          admin   748B Dec 19 10:58 ..
drwxr-xr-x   3 adamprescott  admin   102B Oct 16 20:41 cache
drwxr-xr-x   3 adamprescott  admin   102B Oct 16 20:41 db
drwxr-xr-x   4 adamprescott  admin   136B Oct 16 20:41 log
drwxr-xr-x   5 adamprescott  admin   170B Oct 16 21:03 mongodb
drwxr-xr-x  25 adamprescott  admin   850B Dec 24 10:11 mysql
drwxr--r--   2 root          admin    68B Dec 24 10:52 postgres
drwxr-xr-x   2 adamprescott  admin    68B Dec 19 10:59 run
-rw-r--r--   1 adamprescott  admin     0B Mar 20  2014 stdout
$ sudo ls -la /usr/local/var/postgres
Password:
total 0
drwxr--r--   2 root          admin   68 Dec 24 10:52 .
drwxr-xr-x  10 adamprescott  admin  340 Dec 24 10:52 ..
$ brew doctor
Please note that these warnings are just used to help the Homebrew maintainers
with debugging if you file an issue. If everything you use Homebrew for is
working fine: please don't worry and just ignore them. Thanks!

Warning: A newer Command Line Tools release is available.
Update them from Software Update in the App Store.
@aprescott

This comment has been minimized.

Copy link
Author

aprescott commented Dec 24, 2014

rm -rf /usr/local/var/postgres && initdb /usr/local/var/postgres -E utf8 does in fact solve this problem, so I'm a little curious why the other issue was closed. Shouldn't brew install postgres handle this for me? Am I missing something?

@MikeMcQuaid

This comment has been minimized.

Copy link
Member

MikeMcQuaid commented Dec 24, 2014

The caveats say:

To migrate existing data from a previous major version (pre-9.4) of PostgreSQL, see:
http://www.postgresql.org/docs/9.4/static/upgrading.html

Did that not help?

@aprescott

This comment has been minimized.

Copy link
Author

aprescott commented Dec 24, 2014

@MikeMcQuaid I missed that when upgrading. But the part I don't understand here is I removed postgres and ran (among other things in my shell paste)

$ rm -rf /usr/local/var/postgres/*
$ rmdir /usr/local/var/postgres

which should have given me a completely blank slate as far as the postgres package goes, right? But then my brew install postgres leaves me with a root-owned /usr/local/var/postgres, and in no runnable state. The initdb step was necessary even when starting from an empty state.

@MikeMcQuaid

This comment has been minimized.

Copy link
Member

MikeMcQuaid commented Dec 24, 2014

If #{var}/postgres doesn't exist we should run it for you on reinstall.

@aprescott

This comment has been minimized.

Copy link
Author

aprescott commented Dec 24, 2014

It is created on install, but owned by root and empty. Isn't that a bug?

@jacknagel

This comment has been minimized.

Copy link
Contributor

jacknagel commented Dec 24, 2014

Who owns /usr/local/var?

@aprescott

This comment has been minimized.

Copy link
Author

aprescott commented Dec 25, 2014

@jacknagel you can see this in my paste in my opening issue comment with the rest of the info:

drwxr-xr-x  10 adamprescott  admin   340B Dec 24 10:52 .
@mrThe

This comment has been minimized.

Copy link

mrThe commented Dec 27, 2014

I just found this issue, and i fixed it by this manual: http://stackoverflow.com/questions/24379373/how-to-upgrade-the-postgre-from-9-3-to-9-4-without-lossing-data

tl;dr: just migrate your database from 9.3 to 9.4.

@aprescott

This comment has been minimized.

Copy link
Author

aprescott commented Dec 27, 2014

@mrThe, again, the point of this issue is that my installation is broken when starting with no database. There is no database to upgrade because I removed it.

@aprescott aprescott changed the title Unable to start or reinstall Postgres after hitting incompatible database files Unable to install Postgres after hitting incompatible database files, even after Postgres is removed Dec 27, 2014

@aprescott

This comment has been minimized.

Copy link
Author

aprescott commented Dec 27, 2014

I have renamed this issue title to hopefully clarify that this is beyond not catching the upgrade warning (which to be honest should be clearer in error messaging, but alas).

@deoxxa

This comment has been minimized.

Copy link
Contributor

deoxxa commented Feb 9, 2015

Same deal here, not sure what the deal is. Somehow, this root-owned postgres directory is being created upon install, stopping the createdb command from working.

➜  var git:(master) ls -l
total 0
drwxr-xr-x   9 conrad  admin  306  9 Feb 14:41 cayley
drwxr-xr-x   3 conrad  admin  102 26 Oct 17:30 db
drwxr-xr-x   3 conrad  admin  102 26 Oct 17:39 elasticsearch
drwxr-xr-x   3 conrad  admin  102  8 Nov 07:07 lib
drwxr-xr-x   6 conrad  admin  204 28 Nov 21:17 log
drwxr-xr-x  10 conrad  admin  340 26 Oct 17:55 mongodb
drwxr-xr-x  13 conrad  admin  442  9 Feb 14:41 mysql
drwxr-xr-x   2 conrad  admin   68 10 Dec 09:24 run
➜  var git:(master) brew install postgres
==> Downloading https://downloads.sf.net/project/machomebrew/Bottles/postgresql-9.4.1.yosemite.bottle.1.tar.gz
Already downloaded: /Library/Caches/Homebrew/postgresql-9.4.1.yosemite.bottle.1.tar.gz
==> Pouring postgresql-9.4.1.yosemite.bottle.1.tar.gz
==> Caveats
If builds of PostgreSQL 9 are failing and you have version 8.x installed,
you may need to remove the previous version first. See:
  https://github.com/Homebrew/homebrew/issues/2510

To migrate existing data from a previous major version (pre-9.4) of PostgreSQL, see:
  http://www.postgresql.org/docs/9.4/static/upgrading.html

To have launchd start postgresql at login:
    ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
Then to load postgresql now:
    launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
Or, if you don't want/need launchctl, you can just run:
    postgres -D /usr/local/var/postgres
==> Summary
🍺  /usr/local/Cellar/postgresql/9.4.1: 2996 files, 40M
➜  var git:(master) ls -l
total 0
drwxr-xr-x   9 conrad  admin  306  9 Feb 14:41 cayley
drwxr-xr-x   3 conrad  admin  102 26 Oct 17:30 db
drwxr-xr-x   3 conrad  admin  102 26 Oct 17:39 elasticsearch
drwxr-xr-x   3 conrad  admin  102  8 Nov 07:07 lib
drwxr-xr-x   6 conrad  admin  204 28 Nov 21:17 log
drwxr-xr-x  10 conrad  admin  340 26 Oct 17:55 mongodb
drwxr-xr-x  13 conrad  admin  442  9 Feb 14:41 mysql
drwxr--r--   2 root    admin   68 10 Feb 08:53 postgres
drwxr-xr-x   2 conrad  admin   68 10 Dec 09:24 run
➜  var git:(master) postgres -D /usr/local/var/postgres
postgres cannot access the server configuration file "/usr/local/var/postgres/postgresql.conf": Permission denied
@commandtab

This comment has been minimized.

Copy link
Contributor

commandtab commented Feb 22, 2015

I just ran into this issue. I had nothing critical in my local postgres, so rather than figuring out how to upgrade from 9.3.x to 9.4.x, I just blew away all the installed versions and data by running brew uninstall --force postgresql and rm -rf /usr/local/var/postgres. And, as others have noted, performing a re-install succeeds, but the data directory is never populated, so the install won't actually start or initdb.

Here's a gist of the brew install postgresql -v after performing the above removals: https://gist.github.com/commandtab/92049df70fe8cd7b349f

I'm on OS X 10.10.2.

@mermop

This comment has been minimized.

Copy link

mermop commented Feb 26, 2015

Just ran into the same issue - homebrew installed root-owned postgres directory.
Eventually got it working with
initdb /usr/local/var/postgres9.4 -E utf8
rm -rf postgres
mv postgres9.4 postgres

@eoinkelly

This comment has been minimized.

Copy link
Contributor

eoinkelly commented Feb 26, 2015

As @mermop mentioned, it seems /usr/local/var/postgres is being created with root as owner which presumably is causing initdb to fail. Until a fix lands this may help

# WARNING: the process below will destroy any existing postgres databases you may have

# run install
brew install postgresql --force

# stop the running postgres server (if any)
pg_ctl -D /usr/local/var/postgres stop

# destroy your postgres data directory  (you will have to supply root password to do this)              
sudo rm -rf /usr/local/var/postgres

# recreate a postgres data dir
initdb /usr/local/var/postgres -E utf8

# start postgres server manually
postgres -D /usr/local/var/postgres
@dmitrygusev

This comment has been minimized.

Copy link

dmitrygusev commented Mar 2, 2015

Following official migration guide helped:

brew switch postgres 9.3.5    # presuming you already installed 9.4.1
pg_dumpall > outputfile
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
mv /usr/local/var/postgres /usr/local/var/postgres.old
brew switch postgres 9.4.1
initdb -D /usr/local/var/postgres
psql -d postgres -f outputfile

That's all. Check if import went well, then delete backups:

rm outputfile
rm -Rf /usr/local/var/postgres.old
@aprescott

This comment has been minimized.

Copy link
Author

aprescott commented Mar 2, 2015

@dmitrygusev while that's useful, the issue here is more that on a fresh install of postgres, it shouldn't be necessary to chown directories or manually call initdb. I've had to fix this for several coworkers over the last few weeks, and twice for myself.

@silvenon

This comment has been minimized.

Copy link

silvenon commented Mar 7, 2015

Yes, I had to chown on a fresh install as well. This is a legit issue 👍

@mwildehahn

This comment has been minimized.

Copy link

mwildehahn commented Mar 21, 2015

i ran into this issue as well. following @dmitrygusev instructions fixed the issue, although i did have to change owner and permissions on the directory created by initdb

@mattrobbins

This comment has been minimized.

Copy link

mattrobbins commented Mar 31, 2015

I had a similar issue. Every time I tried the reinstall and then ran the initdb or createdb commands I would get a 'Permission Denied' error. The postgres directory was owned by root and not by my user. I am glad I found this post and by by following @dmitrygusev instructions fixed the issue. I had removed all previously installed postgres version and data as I had nothing critical so I didn't switch to the previous version or do the pg_dumpall command as stated in the instructions. I executed the commands from there. I did have to change the owner on the 'postgres' directory before I could rename it. After that things worked. Can this not be fixed with the install?

@cacheflow

This comment has been minimized.

Copy link

cacheflow commented Apr 8, 2015

Thanks! This just fixed a problem I was having.

@bonkydog

This comment has been minimized.

Copy link

bonkydog commented May 7, 2015

It seems as if the root-owned /usr/local/var/postgres directory is created by launchctl when we load ~/Library/LaunchAgents/homebrew.mxcl.postgresql92.plist. Deleting this directory while the plist is loaded doesn't work -- it gets re-created in a few seconds, probably by the KeepAlive facility.

Removing the StandardErrorPath from the plist seems to stop this from happening.

@redbar0n

This comment has been minimized.

Copy link

redbar0n commented Jul 19, 2015

+1 I just tried a normal brew upgrade, and my DB started crashing with this error with no specific error message. Took some time to find this thread, after having tried several other ways to fix it, and inspecting server.log.

@dmitrygusev's solution worked for me, with the addition that I had to run
psql before pg_dumpall, to avoid this error:

› pg_dumpall > outputfile
pg_dumpall: could not connect to database "template1": could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
@aprescott

This comment has been minimized.

Copy link
Author

aprescott commented Jul 19, 2015

I'd like to repeat again — for the sake of clarity since people keep mentioning upgrades — that this issue is mainly about a broken fresh install, where directories are incorrectly owned by root and the correct setup isn't performed, etc.

I've no doubt the upgrade information is useful, I just don't want this issue I opened to be mistakenly closed because it has deviated into "read the upgrade notes" or something else.

@patrickdet

This comment has been minimized.

Copy link

patrickdet commented Jul 20, 2015

i have the same issue as @aprescott. the /usr/local/var/postgres/ directory is owned by root and it's empty because the install script was not able to install it

@danielmeyer

This comment has been minimized.

Copy link

danielmeyer commented Jul 21, 2015

Some time ago I had installed 9.3 and after a recent upgrade the datafiles were no longer compatible.
I opted to completely remove the 9.3 package via "brew uninstall" and "brew uninstall --force".

I had no problem installing the 9.4 package, however the database initialization step would fail.

Worked through the suggestions above, but would end up with an issue on database initialization due to root owning /usr/local/var/postgres or because it already existed.

The following steps made it possible to successfully run the initdb process and start the database.

Un-registering the plist.

$ launchctl list | grep -i sql
PID Status  Label
-   2   homebrew.mxcl.postgresql
$ launchctl remove homebrew.mxcl.postgresql
$ launchctl list | grep -i sql

Remove the symlink from the LaunchAgents directory, and remove the referenced plist file.

$ cd /Users/xxxxx/Library/LaunchAgents
$ rm -rf homebrew.mxcl.postgresql.plist
$ rm -rf /usr/local/opt/postgresql/homebrew.mxcl.postgresql.plist

Remove existing postgres data directory:

$ rm -rf /usr/local/var/postgres

Initialize the database:

$ initdb /usr/local/var/postgres
The files belonging to this database system will be owned by user "xxxxxx".
This user must also own the server process.

The database cluster will be initialized with locale "en_CA.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

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 dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /usr/local/var/postgres/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... 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:

    postgres -D /usr/local/var/postgres
or
    pg_ctl -D /usr/local/var/postgres -l logfile start

Confirm that the postgres server will start:

$ postgres -D /usr/local/var/postgres
LOG:  database system was shut down at 2015-07-21 11:53:35 EDT
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
@silvenon

This comment has been minimized.

Copy link

silvenon commented Jul 21, 2015

@danielmeyer please use code tags.

@wwalser

This comment has been minimized.

Copy link

wwalser commented Aug 5, 2015

I had either the same or a very similar issue with a root owned /usr/local/var/posgres. I solved the problem by blowing away the bad postgres directory, followed by initdb and finally chown. Not ideal but as the old data wasn't needed but it didn't actually cause much trouble.

Along the way to fixing the problem I did find that brew uninstall tends to leave posgres servers running which caused subsequent installs to refuse to pg_ctl start because of PID locks or port collisions. I'm not sure if that should fall on brew to handle though.

To be clear, there are a few factors in my case the obfuscated this particular bug but after running a brew installed a root owned /usr/local/var/postgres directory would exist. The issue seems legit.

@MikeMcQuaid

This comment has been minimized.

Copy link
Member

MikeMcQuaid commented Aug 5, 2015

I had either the same or a very similar issue with a root owned /usr/local/var/posgres. I solved the problem by blowing away my old data, followed by initdb and finally chown. Not ideal but as the old data wasn't needed but it didn't actually cause much trouble.

I suspect this is an issue that predates our creation of var/postgres which meant the daemon did on startup.

@colleenmcguckin

This comment has been minimized.

Copy link

colleenmcguckin commented Jan 14, 2016

I also had the /usr/local/var getting set to root permissions even with a fresh homebrew install. I went through the steps @danielmeyer provided to get it working. Thank you @danielmeyer!!

@esbanarango

This comment has been minimized.

Copy link

esbanarango commented Jan 28, 2016

@theofanislekkas

This comment has been minimized.

Copy link

theofanislekkas commented Feb 11, 2016

@eugenesvk

This comment has been minimized.

Copy link

eugenesvk commented Feb 13, 2016

I had the same issue, where usr/local/var/postgres was created with root permissions when I upgraded to a newer version, but forgot to change database path from default to my custom Dropbox location (so there was no usr/local/var/postgres folder after the upgrade). Rather strange brew would ever do something root-like :(
usr/local/var is owned by user with 700 permissions.

@MikeMcQuaid

This comment has been minimized.

Copy link
Member

MikeMcQuaid commented Feb 13, 2016

Are any of you running the postgresql launch agent as root?

@eugenesvk

This comment has been minimized.

Copy link

eugenesvk commented Feb 13, 2016

@MikeMcQuaid no, never sudoed it or anything, was using the default commands to start the service after update.

@MikeMcQuaid

This comment has been minimized.

Copy link
Member

MikeMcQuaid commented Feb 14, 2016

I've pushed something in 19de04b that'll hopefully help. Unless there's any reproducible failure cases for this I'll consider it closed.

@deoxxa

This comment has been minimized.

Copy link
Contributor

deoxxa commented Feb 14, 2016

@MikeMcQuaid I count three separate logs in this thread of commands that reproduce this problem, along with their output. What other information do you want?

@MikeMcQuaid

This comment has been minimized.

Copy link
Member

MikeMcQuaid commented Feb 15, 2016

@deoxxa Step-by-step reproduction instructions (with as minimal input data as possible) for the current version of the PostgreSQL formula in core (i.e. 9.5.1 at 19de04b or later). Ideally? A pull request.

springmeyer added a commit to mapnik/python-mapnik that referenced this issue Mar 29, 2016

springmeyer added a commit to mapnik/mapnik that referenced this issue Mar 30, 2016

@joewhaley

This comment has been minimized.

Copy link

joewhaley commented Jul 10, 2016

Ran into this bug. /usr/local/var/postgres would reappear every few seconds owned by root, even after uninstalling postgres. It was a real mystery until I discovered the postgres LaunchAgent was still around, even after uninstall. Running launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist did fix the problem and allow me to (re)install postgres. Shouldn't uninstalling postgres remove the LaunchAgent?

@MikeMcQuaid

This comment has been minimized.

Copy link
Member

MikeMcQuaid commented Jul 10, 2016

@joewhaley Technically it does just launchctl doesn't pick it up automatically. As you need to run launchctl or brew services manually (Homebrew does not do it for you) on install you need to also run it manually on uninstall.

@Homebrew Homebrew locked and limited conversation to collaborators Jul 11, 2016

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
You can’t perform that action at this time.