1

After I installed Mavericks my postgres configuration seems completely messed up. I installed the dev tools for Maverick from xcode and I've tried putting host:localhost in the db yml but still if I try to run rails s:

could not connect to server: No such file or directory (PGError)
Is the server running locally and accepting
connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

If I try to start postgres manually with:

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

I get: server starting

but pg_ctl -D /usr/local/var/postgres status returns pg_ctl: no server running ??

I tried to reinstall pg gem and reload postgresql but to no avail. brew info postgres returns:

postgresql: stable 9.3.1
http://www.postgresql.org/
Conflicts with: postgres-xc
/usr/local/Cellar/postgresql/9.3.1 (2919 files, 39M) *

Since I did brew reinstall postgress I know get this:

The data directory was initialized by PostgreSQL version 9.2, 
which is not compatible with this version 9.3.1.

I have a postgres query tool that doesn't seem to have any trouble connecting so I know the data is still there.

I would really appreciate if someone can help me figure this out, thanks.

Jaqx
  • 826
  • 3
  • 14
  • 39
  • 1
    The error mentioning the Unix domain socket cannot happen when trying to connect to localhost (through the host entry in `database.yml`), which means that your change to this file is ignored. You should try and fix that before anything else. Especially if your query tool can still connect, since that means postgres is actually running. – Daniel Vérité Nov 02 '13 at 12:18

2 Answers2

8

The error The data directory was initialized by PostgreSQL version 9.2, which is not compatible with this version 9.3.1. is caused by the incompatible data directory between 9.2 and 9.3.

if you don't need the old data in your 9.2 db, it's very easy to solve this problem:

rm -rf /usr/local/var/postgres

where /usr/local/var/postgres is the default data directory path. you may need to change the path according to your setting.

After deleting the old data directory, init a new one with 9.3: 

initdb /usr/local/var/postgres -E utf8

Then you are good to go!

====

if you need to migrate the old data from 9.2 to 9.3:

a) rename your old data directory:

mv /usr/local/var/postgres /usr/local/var/postgres9.2

b) init a new 9.3 db (create a new data directory): 

initdb /usr/local/var/postgres -E utf8

c) migrate:

pg_upgrade \
-b /usr/local/Cellar/postgresql/9.2.4/bin/ \
-B /usr/local/Cellar/postgresql/9.3.1/bin/ \
-d /usr/local/var/postgres9.2 \
-D /usr/local/var/postgres \
-v

-b is your old binary while -B is your new binary. you can get them through brew info postgres.

-d is the renamed old data directory while -D is the new data directory you just created at step b.

then you would get the following message: 

Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    delete_old_cluster.sh

d) rock with your postgres 9.3!

Brian
  • 30,156
  • 15
  • 86
  • 87
  • The first solution of deleting and doing initdb did not work for me. After the db has been initialized I try psql and get the following errorpsql: FATAL: database "databasename" does not exist – frediy Dec 07 '13 at 09:49
  • Needed to do 'createdb' in terminal. Working. – frediy Dec 07 '13 at 09:57
  • of course you need `createdb`. `initdb` is different from `createdb` :) – Brian Dec 08 '13 at 14:24
3

The most conservative way to upgrade PostgreSQL entails needing to:

  1. Compile a version of PostgreSQL using 9.2.X sources (e.g. ./configure --prefix=/tmp/myPg-9.2 && make && make install)
  2. Start the database using the 9.2 binaries (e.g. /tmp/myPg-9.2/bin/postgres -D /usr/local/var/postgres, this will keep PostgreSQL in the foreground in this terminal)
  3. Dump the database using pg_dumpall
  4. Shut down the 9.2 database.
  5. Move /usr/local/var/postgres to someplace safe (e.g. /usr/local/var/postgres-9.2)
  6. initdb a new database using the new 9.3 binaries.
  7. Load the dump from pg_dumpall.

Make sure you hold on to a copy of your old 9.2 data directory until you've successfully recovered. Once you've determined that you've fully recovered from this situation, you can blow away your temp 9.2 installation in /tmp/myPg-9.2 and the old 9.2 data directory /usr/local/var/postgres-9.2. I'd make a backup of /usr/local/var/postgres-9.2 and would sit on it for a few months "just in case" (e.g. tar cjpf /usr/local/var/postgres-9.2-2013-10-31.tar.bz2 /usr/local/var/postgres-9.2).

Per comment, adding a few extra steps:

  1. Compile a version of PostgreSQL:
    1. cd /tmp
    2. Download the latest .bz2 tarball of PostgreSQL's 9.2 source from http://www.postgresql.org/ftp/source/ (currently 9.2.5 as of 2013-10-31).
    3. tar xjpf postgresql-9.2.5.tar.bz2
    4. cd postgresql-9.2.5
    5. ./configure --prefix=/tmp/myPg-9.2 - Don't run this as root
    6. make - Also don't run this as root
    7. make install - Frequently you would do this as root, but you don't need to this time because you're installing in to /tmp where you have permissions to install. If your prefix was /usr/local you would have to run this command as root.
Sean
  • 9,888
  • 4
  • 40
  • 43
  • sorry I'm a novice in this area. I am supposed to type `./configure --prefix=/tmp/myPg-9.2 && make && make install` in my apps root? the directory doesn't exist. – Jaqx Nov 01 '13 at 05:46
  • Expanded the steps for compiling a version of PostgreSQL. Wrote that from memory so there might be a typo in there. – Sean Nov 01 '13 at 06:01
  • when I tried to start the db I got `LOG: could not bind IPv6 socket: Address already in use HINT: Is another postmaster already running on port 5432?` – Jaqx Nov 01 '13 at 06:49
  • There should only ever be one copy of `postgres` running in a given `PGDATA` directory at a time. In your case, you need to shut down all other instances of PostgreSQL pointing to `/usr/local/var/postgres` and then run the above command(s) by hand from Terminal. – Sean Nov 01 '13 at 07:55
  • how do I find and shut down those instances? `pg_ctl -D /usr/local/var/postgres stop -s -m fast` ? – Jaqx Nov 01 '13 at 08:05
  • on `pg_ctl -D /usr/local/var/postgres status` I get no server running – Jaqx Nov 01 '13 at 08:10
  • That's one way to do it. The most important thing is to _NEVER_ `kill -9` any PostgreSQL process, ever. Try `pgrep postgres` to see if it returns any PIDs. If so, then try gracefully killing the process using one of [PostgreSQL's accepted shutdown signals](www.postgresql.org/docs/current/static/server-shutdown.html). Maybe this SO question will be of use to you: http://stackoverflow.com/questions/6950395/postgresql-server-wouldnt-shutdown-on-lion-mac-os-10-7 – Sean Nov 01 '13 at 08:10
  • this was the output `246 251 252 253 254 255` – Jaqx Nov 01 '13 at 08:11
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/40357/discussion-between-sean-and-jaqx) – Sean Nov 01 '13 at 08:13
  • if you could grant me some insight tmr into why rails s still doesn't work that would be great. I updated my results in the chat – Jaqx Nov 01 '13 at 09:14
  • After your great guidance all I had to do was add host: localhost in my database yml file which seems to have the same effect as writing export PGHOST=localhost in your bash profile – Jaqx Nov 07 '13 at 07:27
  • I get this error PGError: duplicate key value violates unique constraint "microposts_pkey" Id= 2 exists. How can I get the DB to insert into a row that doesn't already exist? – Jaqx Dec 07 '13 at 02:20