ruby on rails 4 - How do I start enterpiseDB PostgreSQL on Mac OSX 10.6.8?

Question

Mac OSX 10.6.8

The postgres website says postgres 9.2+ is compatible with Mac OSX 10.6+, so I downloaded the 9.2.4 version of the installer for Mac OSX here:

http://www.enterprisedb.com/products-services-training/pgdownload

I accepted all the defaults, so postgres was installed in the directory:

/Library/PostgreSQL/9.2

(If you are installing postgres for rails development, in order to install the pg gem you need to add to your PATH: http://excid3.com/blog/installing-postgresql-and-pg-gem-on-mac-osx/#.UfkImuB6gy5)

Now, I am trying to figure out how to use postgres. I found a stackoverflow thread that said you can start postgres with this command:

~$ /Library/PostgreSQL/9.2/bin/pg_ctl start -D /Library/PostgreSQL/9.2/data -l postgres.log

But that produces this error:

pg_ctl: could not open PID file "/Library/PostgreSQL/9.2/data/postmaster.pid": Permission denied

If I try the same command with sudo:

~$ sudo /Library/PostgreSQL/9.2/bin/pg_ctl start -D /Library/PostgreSQL/9.2/data -l postgres.log

I get this error:

pg_ctl: cannot be run as root Please log in (using, e.g., "su") as the (unprivileged) user that will own the server process.

What the heck does that mean??

Another SO thread says that in response to that error, you should do this:

$ sudo -u postgres bash

which produces this output:

Password:
shell-init: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
bash: /Users/7stud/.bashrc: Permission denied
bash-3.2$ 

What's going on with the error there?

Answer 1

List of errors encountered and solved:

1. shell-init: error retrieving current directory: getcwd: cannot access parent directories: Permission denied

2. pg_config error

3. Library not loaded: libpq.5.dylib

4. fe_sendauth: no password supplied

5. pg_ctl: no database directory specified and environment variable PGDATA unset

6. pg_ctl: server does not shut down

7. (PG::Error)

A) Pre-installation:

When you click on the .dmg file to begin installation, a dialog window opens, and there is a README file alongside the installer. Read that. I changed the shared memory as prescribed:

$ sudo vi /etc/sysctl.conf

On a MacBook Pro with 2GB of RAM, the author's sysctl.conf contains:

kern.sysv.shmmax=1610612736
kern.sysv.shmall=393216
kern.sysv.shmmin=1
kern.sysv.shmmni=32
kern.sysv.shmseg=8
kern.maxprocperuid=512
kern.maxproc=2048

I scrolled down the file to the k's and changed each of the listed lines to the specified values. Then you need to reboot your computer.

B) Installing and using PostgreSQL:

Then click on the .dmg file again, then click on the installer and install postgres. I accepted all the defaults.

As far as I can tell, the postgres installer only gives permissions for the directory /Library/PostgreSQL/9.2 to a user named 'postgres'. During the postgres installation, the installer asked for a password for the database superuser, and if you wondered what the database superuser's name was, it's 'postgres'.

When you issue the sudo command, you are temporarily changing to the user 'root'. However, the root user does not have access to the dir /Library/PostgreSQL/9.2. So you have to use the su command (switch user) to change to the postgres user. But in order to issue the su command, you need to be the root user. Got that?

As a result, whenever you issue postgres commands you need to do this first:

$ cd /Library/PostgreSQL/9.2
$ sudo su postgres
Password: <normal sudo password>

Errors like this:

~$ sudo su postgres
shell-init: error retrieving current directory: getcwd: cannot access parent directories: Permission denied

are caused by the fact that you are trying to switch to the user postgres in a directory where the postgres user does not have permissions, which is, as far as I can tell, every directory except /Library/PostgreSQL/9.2

After you correctly switch to the postgres user:

$ cd /Library/PostgreSQL/9.2
$ sudo su postgres
Password: <normal sudo password>

you get a funny looking prompt. In my case, it looks like this:

bash-3.2$ 

If you issue the ls command:

bash-3.2$ ls
bin             installer           scripts
data                lib             share
doc             pgAdmin3.app            stackbuilder.app
include             pg_env.sh           uninstall-postgresql.app

you can surmise that you are in the /Library/PostgreSQL/9.2 directory.

The enterpriseDB installer automatically starts the postgres server for you when it is done with the installation. If you need to start the server, e.g. you turned your computer off, see "you can stop or start the server from the command line" in step 5 below.

C) Connecting to the server using pgAdmin3:
I spent hours trying to figure out how to start the server and just generally use postgres with no luck. Then I happened to see something called pgAdmin3 in the dir /Library/PostgreSQL/9.2. I clicked on that, and a window with several panes opened up.

In the left pane of the pgAdmin3 window, it says:

Server Groups
--Servers(1)
----PostgresSQL 9.2 (localhost:5432)

I clicked on 'PostgreSQL 9.2 (localhost:5432)' to highlight it, and then I right clicked to bring up a menu, and I chose Connect. When prompted, I entered the database superuser password.

D) Setting up rails to use PostgreSQL:
Then I followed the directions in this tutorial for getting rails setup:

https://pragtob.wordpress.com/2012/09/12/setting-up-postgresql-for-ruby-on-rails-on-linux/#comment-1142

as follows:

1) The first step is creating a new user.
If you use the same name as your mac user name, then you don't have to add a username (or a password) to the database.yml file in your rails app:

$ cd /Library/PostgreSQL/9.2
$ sudo su postgres
Password: <normal sudo password>
bash-3.2$ createuser --interactive 7stud
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
Password:<database superuser password>
bash-3.2$

That creates a new user with no password.

Next, in pgAdmin3 if you click on:

---Login Roles(1)

to highlight it, and then click on the icon "Refresh the selected object" at the top of the window, you will see the new user displayed.

The postgres docs for createuser are here:

http://www.postgresql.org/docs/9.2/interactive/app-createuser.html

There is also a dropuser command:

bash-3.2$ dropuser 7stud

2) Then add the 'pg' gem to your Gemfile, and comment out the sqlite gems.

3) Then try to install the pg gem with Bundler:

rails_projects/sample_app/$ bundle install --without production

When I tried that, I got a 'pg_config' error. To fix that error, I followed the advice here:

http://excid3.com/blog/installing-postgresql-and-pg-gem-on-mac-osx/#.UftQUOB6gy6

and added /Library/PostgreSQL/9.2 to my PATH (I do all PATH manipulations in .bashrc.). Don't forget to 'source' .bashrc or quit Terminal and restart Terminal for the new PATH to take effect.

Then I was able to install the pg gem without error:

rails_projects/sample_app/$ bundle install --without production
rails_projects/sample_app/$ bundle update
rails_projects/sample_app/$ bundle install

(I don't really understand why you have to do all three of those commands, but that is what railstutorial does.)

4) Next change your config/database.yml file.
For each of the sections: test, development, and production change the 'adapter' and 'database' lines, and add an 'encoding' line, e.g.:

development:
  adapter: postgresql
  database: ArbitaryDatabaseName (e.g. sampleapp_dev)
  encoding: utf8

  pool: 5
  timeout: 5000

Make sure all three database lines specify a different name.

5) Then try to create the databases:

rails_projects/sample_app/$ bundle exec rake db:create:all

When I tried that, I got the error:

dlopen(/Users/7stud/.rvm/gems/ruby-2.0.0-p247@railstutorial_rails_4_0/gems/pg-0.15.1/lib/pg_ext.bundle, 9): Library not loaded: libpq.5.dylib
Library not loaded: libpq.5.dylib
  Referenced from: /Users/7stud/.rvm/gems/ruby-2.0.0-p247@railstutorial_rails_4_0/gems/pg-0.15.1/lib/pg_ext.bundle

The library libpq.5.dylib is located in

/Library/PostgreSQL/9.2/lib

Using the suggestions here:

https://github.com/PostgresApp/PostgresApp/issues/109

I solved that error by adding the following to my .bashrc file(or you can put it in .bash_profile):

export DYLD_FALLBACK_LIBRARY_PATH="/Library/PostgreSQL/9.2/lib:$DYLD_LIBRARY_PATH"

(Remember to 'source' .bashrc or exit Terminal and start a new Terminal window.)

Then I tried to create the databases again:

rails_projects/sample_app/$ bundle exec rake db:create:all
fe_sendauth: no password supplied …

To solve that blasted error, you need to change the pg_hba.conf file, which is in the directory

/Library/PostgreSQL/9.2/data 

You can't go into that directory using Finder--you have to use the postgres user to go into that directory:

$ cd /Library/PostgreSQL/9.2
$ sudo su postgres
Password: <normal sudo password>
bash-3.2$ cd data
bash-3.2$ ls
PG_VERSION  pg_hba.conf pg_notify   pg_subtrans postgresql.conf
base        pg_ident.conf   pg_serial   pg_tblspc   postmaster.opts
global      pg_log      pg_snapshots    pg_twophase postmaster.pid
pg_clog     pg_multixact    pg_stat_tmp pg_xlog

bash-3.2$ mvim pg_hba.conf  
(or instead of mvim open with another text editor, e.g. vim)

In the .conf file you need to change 'md5' to 'trust':

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust  #md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust  #md5
# IPv6 local connections:
host    all             all             ::1/128                 trust  #md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                md5
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

The access METHOD 'md5' means the database is expecting an encrypted password for the specified database users. But when you created the new user, you created it without a password. The access METHOD 'trust' means the database does not expect a password.

Then I tried to create the databases again:

rails_projects/sample_app/$ bundle exec rake db:create:all
fe_sendauth: no password supplied …

In an attempt to get the server to read the changes I made in the .conf file, I disconnected from the server in pgAdmin3:

Server Groups
--Servers(1)
----PostgresSQL 9.2 (localhost:5432)

Click on PostgresSQL 9.2 (localhost:5432) to highlight it, 
right click, then select Disconnect,
then right click and select Connect.

But apparently that wasn't working. Disconnecting and reconnecting to the server does not cause the server to reload the .conf file. To get the server to reload the .conf file, in pgAdmin3 you can right click on PostgresSQL 9.2 (localhost:5432) and select:

Reload configuration

Or you can stop and then start the server from the command line:
(Note the following commands assume you have already switched to the postgres user, see "Installing and using PostgreSQL" above.)

bash-3.2$ cd /Library/PostgreSQL/9.2/
bash-3.2$ pg_ctl stop  (First disconnect from the server in pgAdmin3)

However, that produced the