Where is data_directory for homebrew? or for postgresql or how do I solve error, ""global/pg_filenode.map": No such file or directory"?

I'm having difficulty with postgresql after installing via homebrew. I'm on Mac OS X 10.9.5 (just updated today and did restart). Note I didn't deliberately change anything from a default postgresql installation via homebrew PROBLEM - I can't uninstall postgresql - I can't log in to view tables / start the "command line" of postgres - createdb returns an error about pg_filenode.map "no such file or directory" - initdb returns error wondering where data_directory is (I don't know) ANY OF THESE WOULD BE SUCCESS - Log in to psql (so I can create a database or table like I can do in mysql, browse existing tables) - Uninstall postgres altogether (so I can re-try with postgresql.app) I've spent almost 10 hours troubleshooting this and come up short. I've looked at loads of answers already (including SO questions 24132418 and 14510237) though I can't include all links due to not having reputation points yet Any pointers would be really appreciated! Below seems lengthy because I have pasted all the logs, please let me know if I have missed some important info though Thanks, -Mark DETAILS I have been working my way through an introduction to Heroku, and they said installing postgresql was a requirment, so I did that using homebrew ``` brew install postgresql ``` I didn't check it, I proceeded building my app, but the next day I run the webapp locally and get error, ``` Request URL: http://localhost:5000/admin/ Django Version: 1.6.5 Exception Type: OperationalError Exception Value: FATAL: could not open relation mapping file "global/pg_filenode.map": No such file or directory Exception Location: /Users/macuser/Dropbox/code/heroku/awe01/lib/python2.7/site-packages/psycopg2/__init__.py in connect, line 164 ``` So I want to investigate. Now I've never used postgresql before, so I want to "get to the command line" for postgresql Please note, I'm a complete newbie and have never used postgresql before. In mysql I know this would be ``` mysql -u root -p ``` (and then enter password) I'm unable to do the equivalent in postgresql ``` moriartymacbookair13:~ macuser$ postgres postgres does not know where to find the server configuration file. You must specify the --config-file or -D invocation option or set the PGDATA environment variable. moriartymacbookair13:~ macuser$ postgres -V postgres (PostgreSQL) 9.3.5 ``` The more detailed heroku docs recommend installing postgresql using postgres.app I hadn't done this, I used homebrew, so I tried uninstalling postgresql ``` moriartymacbookair13:~ macuser$ brew uninstall postgres Uninstalling /usr/local/Cellar/postgresql/9.3.5_1... Error: Permission denied - /usr/local/bin/clusterdb ``` That didn't work, so back to where I was. To figure out what path to specify, I ran ``` brew info postgresql ``` If you scroll down to the bottom of this, you can see it advises me of the path ``` moriartymacbookair13:~ macuser$ brew info postgres postgresql: stable 9.3.5 (bottled), devel 9.4beta2 http://www.postgresql.org/ Conflicts with: postgres-xc /usr/local/Cellar/postgresql/9.3.5_1 (2927 files, 38M) * Poured from bottle From: https://github.com/Homebrew/homebrew/blob/master/Library/Formula/postgresql.rb ==> Dependencies Required: openssl ✔, readline ✔ Recommended: ossp-uuid ✔ ==> Options --32-bit Build 32-bit only --enable-dtrace Build with DTrace support --no-perl Build without Perl support --no-tcl Build without Tcl support --with-python Build with python support --without-ossp-uuid Build without ossp-uuid support --devel install development version 9.4beta2 ==> 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/issue/2510 To migrate existing data from a previous major version (pre-9.3) of PostgreSQL, see: http://www.postgresql.org/docs/9.3/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 wiki. To reload postgresql after an upgrade: launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist 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 ``` Note: I have ignored the comment at the top: ``` Conflicts with: postgres-xc /usr/local/Cellar/postgresql/9.3.5_1 (2927 files, 38M) * ``` Anyway there we have it, toward the end of that large output it confirms the path for me is /usr/local/var/postgres, so I tried -D ``` moriartymacbookair13:~ macuser$ postgres -D /usr/local/postgres postgres cannot access the server configuration file "/usr/local/postgres/postgresql.conf": No such file or directory ``` So this time I specified the config file: ``` moriartymacbookair13:~ macuser$ postgres --config-file=/usr/local/var/postgres/postgresql.conf postgres does not know where to find the database system data. This can be specified as "data_directory" in "/usr/local/var/postgres/postgresql.conf", or by the -D invocation option, or by the PGDATA environment variable. ``` (Out of desperation, I even tried deleting it and going again) ``` moriartymacbookair13:~ macuser$ initdb /usr/local/var/postgres The files belonging to this database system will be owned by user "macuser". This user must also own the server process. The database cluster will be initialized with locale "en_GB.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. initdb: directory "/usr/local/var/postgres" exists but is not empty If you want to create a new database system, either remove or empty the directory "/usr/local/var/postgres" or run initdb with an argument other than "/usr/local/var/postgres". moriartymacbookair13:~ macuser$ rm -rf /usr/local/var/postgres moriartymacbookair13:~ macuser$ initdb /usr/local/var/postgres -E utf8 The files belonging to this database system will be owned by user "macuser". This user must also own the server process. The database cluster will be initialized with locale "en_GB.UTF-8". 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 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 moriartymacbookair13:~ macuser$ postgres -D /usr/local/postgres postgres cannot access the server configuration file "/usr/local/postgres/postgresql.conf": No such file or directory moriartymacbookair13:~ macuser$ postgres --config-file=/usr/local/var/postgres/postgresql.conf postgres does not know where to find the database system data. This can be specified as "data_directory" in "/usr/local/var/postgres/postgresql.conf", or by the -D invocation option, or by the PGDATA environment variable. ``` So I took a look at that file: In that in the conf file itself, the data variable is not defined ``` moriartymacbookair13:~ macuser$ ls /usr/local/var/postgres/ PG_VERSION pg_ident.conf pg_stat/ pg_xlog/ base/ pg_multixact/ pg_stat_tmp/ postgresql.conf global/ pg_notify/ pg_subtrans/ pg_clog/ pg_serial/ pg_tblspc/ pg_hba.conf pg_snapshots/ pg_twophase/ moriartymacbookair13:~ macuser$ vim /usr/local/var/postgres/postgresql.conf ``` shows that the value is commented out (see line 41... almost the whole file is commented out) ``` 35 # FILE LOCATIONS 36 #----------------------------------------------------------------------------- - 37 38 # The default values of these variables are driven from the -D command-line 39 # option or PGDATA environment variable, represented here as ConfigDir. 40 41 #data_directory = 'ConfigDir' # use data in another directory 42 # (change requires restart) 43 #hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file 44 # (change requires restart) 45 #ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file 46 # (change requires restart) 47 48 # If external_pid_file is not explicitly set, no extra PID file is written. 49 #external_pid_file = '' # write an extra PID file 50 # (change requires restart) ``` HELP: DOES ANYONE KNOW WHAT I SHOULD SPECIFY THAT 'ConfigDir' AS for where data_directory is? In case helpful here are contents of the Cellar folder ``` moriartymacbookair13:~ macuser$ ls /usr/local/Cellar/postgresql/9.3.5_1/ COPYRIGHT homebrew.mxcl.postgresql.plist HISTORY include/ INSTALL_RECEIPT.json lib/ README share/ bin/ ``` (I don't understand why I have some postgres files in Cellar and some in usr/local/var) I tried some other things too: ``` moriartymacbookair13:~ macuser$ sudo -u postgres pg_ctl -D /usr/local/var/postgres -w start sudo: unknown user: postgres ``` That's based on other SO questions ``` moriartymacbookair13:~ macuser$ ls -ld /usr/local/var/postgres/ drwx------ 19 macuser admin 646B 20 Sep 19:46 /usr/local/var/postgres// moriartymacbookair13:~ macuser$ chown -R postgres /usr/local/var/postgres chown: postgres: illegal user name moriartymacbookair13:~ macuser$ brew uninstall postgresql Uninstalling /usr/local/Cellar/postgresql/9.3.5_1... Error: Permission denied - /usr/local/bin/clusterdb moriartymacbookair13:~ macuser$ brew uninstall postgres Uninstalling /usr/local/Cellar/postgresql/9.3.5_1... Error: Permission denied - /usr/local/bin/clusterdb moriartymacbookair13:~ macuser$ ``` As I say, I installed Postgresql only to try out heroku, so here is the error Chrome gives me from the code when I run `foreman start` locally. (Note "the django app" is just a demo app `https://devcenter.heroku.com/articles/getting-started-with-python#prepare-the-app`) It works for `localhost:5000` home, but when I add `/admin` (the page that would interact with a database), I get the following error: ``` OperationalError at /admin/ FATAL: could not open relation mapping file "global/pg_filenode.map": No such file or directory Request Method: GET Request URL: http://localhost:5000/admin/ Django Version: 1.6.5 Exception Location: /Users/macuser/Dropbox/code/heroku/awe01/lib/python2.7/site-packages/psycopg2/__init__.py in connect, line 164 Python Executable: /Users/macuser/Dropbox/code/heroku/awe01/bin/python ``` Full error traceback log here: `http://dpaste.com/3CREGVQ` Note this answer `https://stackoverflow.com/a/5053003/870121` recommends createdb not just initdb And the above error is the same as if I run createdb: ``` moriartymacbookair13:~ macuser$ createdb /usr/local/var/postgres/ createdb: could not connect to database template1: FATAL: could not open relation mapping file "global/pg_filenode.map": No such file or directory ``` Steps advised here Postgres is failing with 'could not open relation mapping file "global/pg_filenode.map" ' are ``` Remove and re-add the launch agent Kill the processes for <postgresql version number> Initialize the db initdb /usr/local/var/postgres Restart my computer ``` If this is relevant someone please tell me how to "Remove and re-add the launch agent"? Is is this? (based on the output of the brew info postgresql from earlier) ``` moriartymacbookair13:~ macuser$ launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist moriartymacbookair13:~ macuser$ killall postgresql No matching processes belonging to you were found moriartymacbookair13:~ macuser$ killall postgres No matching processes belonging to you were found moriartymacbookair13:~ macuser$ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist ``` FINALLY In case it inspires anyone, ``` moriartymacbookair13:~ macuser$ 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: The /usr/local directory is not writable. Even if this directory was writable when you installed Homebrew, other software may change permissions on this directory. Some versions of the "InstantOn" component of Airfoil are known to do this. You should probably change the ownership and permissions of /usr/local back to your user account. Warning: Unbrewed dylibs were found in /usr/local/lib. If you didn't put them there on purpose they could cause problems when building Homebrew formulae, and may need to be deleted. Unexpected dylibs: /usr/local/lib/libguide.dylib /usr/local/lib/libKLF_OGL.dylib Warning: Some directories in your path end in a slash. Directories in your path should not end in a slash. This can break other doctor checks. The following directories should be edited: $/Users/macuser/Dropbox/code/aws/ElasticBeanstalkCommandLineTools/AWS-ElasticBeanstalk-CLI-2.6.3/AWSDevTools.sh/macosx/python2.7/ /Users/macuser/Dropbox/code/aws/ElasticBeanstalkCommandLineTools/AWS-ElasticBeanstalk-CLI-2.6.3/AWSDevTools/eb/macosx/python2.7/ Warning: /usr/bin occurs before /usr/local/bin This means that system-provided programs will be used instead of those provided by Homebrew. The following tools exist at both paths: git git-cvsserver git-receive-pack git-shell git-upload-archive git-upload-pack Consider setting your PATH so that /usr/local/bin occurs before /usr/bin. Here is a one-liner: echo export PATH='/usr/local/bin:$PATH' >> ~/.bash_profile moriartymacbookair13:~ macuser$ ``` Earlier today I had this error createdb: could not connect to database postgres: 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"? as per `http://www.postgresql.org/docs/9.3/interactive/tutorial-createdb.html` but that error seems to have gone away now Any advice on un or re-installing postgresql, resolving the `unknown user: postgres` error, setting location of `#data_directory = 'ConfigDir'` or any other tips... I would really appreciate it. It's been a full day lost to this now so I would appreciate anything at all.