Repairing Postgresql After Upgrade to Osx Mavericks

Repairing postgresql after upgrade to OSX Mavericks

if you installed it via homebrew, try this (from brew info postgresql)

launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Which will reload it. Postgress.app my default will not find your databases (you would need to point it to the PGDATA directory, and you might run into version conflicts (if you where running 9.2 and postgress.app is 9.3, a dump /restore would be in order.

pulling from comments.

ok so it looks like you have 9.2.3 installed, what I would do is this.

postgres -D /usr/local/var/postgres 

Then back them all up. pg_dumpall > ~/mydatabases.dump

kill postgres

a brew reinstall postgresql , but warning this will bring you from 9.2.3 to 9.3.1. Then reimport all your databases psql < mydatabaes.dump. Making sure to follow the directions on the unload/load for the launchctl stuff, and at that point you should be good. You could also look at using postgress.app and importing your databases into that app, but you would need to backup/dump your database anyway.

Postgres Configuration messed up after OSX Mavericks

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.

Rails - OS Mavericks and postgresql

You should change the owner of the postgres data directory:

chown davidburton /user/local/var/postgres
# or
sudo chmod ugo=rwX /user/local/var/postgres # You might want to change the permissions as your needs

(You might need to use sudo)

You didn't post the error when you try to start rails/postgres, maybe this question can help you: ERROR: Failed to build gem native extension on Mavericks

How can I start PostgreSQL server on Mac OS X?

The Homebrew package manager includes launchctl plists to start automatically. For more information, run brew info postgres.

Start manually

pg_ctl -D /usr/local/var/postgres start

Stop manually

pg_ctl -D /usr/local/var/postgres stop

Start automatically

"To have launchd start postgresql now and restart at login:"

brew services start postgresql

---

What is the result of pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start?

What is the result of pg_ctl -D /usr/local/var/postgres status?

Are there any error messages in the server.log?

Make sure tcp localhost connections are enabled in pg_hba.conf:

# IPv4 local connections:
host    all    all    127.0.0.1/32    trust

Check the listen_addresses and port in postgresql.conf:

egrep 'listen|port' /usr/local/var/postgres/postgresql.conf

#listen_addresses = 'localhost'        # What IP address(es) to listen on;
#port = 5432                # (change requires restart)

---

Cleaning up

PostgreSQL was most likely installed via Homebrew, Fink, MacPorts or the EnterpriseDB installer.

Check the output of the following commands to determine which package manager it was installed with:

brew && brew list|grep postgres
fink && fink list|grep postgres
port && port installed|grep postgres

---

Related Topics