Installers/Mac OS X
Why create this document?
There are a number of Mac OS X installers, each with its own idea of where software and data should reside. Below is a guide to help developers and users figure out where their data is, and which installer they might have used.
If you are using one of these installers, or maybe a maintainer: Feel free to fill in missing information! Thanks.
What version of PostgreSQL server are you running?
You can find this out by running the SQL command: "
select version();" on your running Postgres server. This is an important first step in troubleshooting an upgrade.
|Installer||Version||Binary location||Data directory location||Startup script location||Default LC_COLLATE setting|
|Built-in PostgreSQL on Mac OS X Lion||9.0.x||
|EnterpriseDB Mac OS X 1-click Installer||9.2.x||
|EnterpriseDB Mac OS X 1-click Installer||9.1.x||
|EnterpriseDB Mac OS X 1-click Installer||9.0.x||
|EnterpriseDB Mac OS X 1-click Installer||8.4.x||
|homebrew (aka 'brew install postgres')||9.2 (as of Sept 2012)||
||UTF-8 (recommended in docs, but not done automatically)|
||No initdb done automatically|
||No initdb done automatically|
If you want to save your data before you upgrade...
Do not rely on your installer to take care of your data for you. Definitely do the following:
- Figure out where your data directory is. See Data Directory Locations below, or use
ps -auxwww | grep -i postgresto find the Postgres process that's running - the path to your data directory is often right there in the process description.
- Make a copy of this data directory and store it somewhere safe, and/or
- Dump the data out of your running postgres instance with
See the PostgreSQL documentation on backups for more information.
You may also find saving the output of
\l from the
psql command-line very helpful in troubleshooting restore problems later.
Why do we have these columns in the table?
Here's why the columns included in the table below matter:
- Version: Major versions of PostgreSQL must be upgraded with either
pg_dump. Some versions require version-specific upgrade steps to be taken as well, which
pg_dumpwill not execute for you.
- Binary location: In order to start Postgres from the command-line (aka Terminal), use
pg_upgradeor use command-line
psqlyou will need to know the path to your Postgres binaries. This is especially important because the paths used by each of the installers are wildly different, and not included in your UNIX environment
- Data directory dilocation: To use
pg_upgrade, you must know where your data is located. This is also important if you are backing up your data - some people do not back up a directory like
/usr/local, assuming that only re-installable information is in that directory. If you are using
homebrew, this is a bad assumption.
- Startup script location: To run
pg_upgrade, you must be able to stop the running Postgres instance. On MacOS X, that probably means using
launchctland pointing it at the plist that controls your Postgres daemon.
- Default LC_COLLATE setting:
LC_COLLATEis set by
initdb, which is how you create a new Postgres cluster in your data directory. Most installers take care of this for you, but different installers use different
LC_COLLATEvalues. This is not entirely the installers' fault, as opinions about what should be the default value changed within the Postgres project itself. However, because different installers use different values,
pg_upgradewill fail. This sucks for the user. If you specified either a collation or an encoding (typically with
initdb -E UTF8) then this value may not match what's in the column.
- Macports, v9.0: http://bsnyderblog.blogspot.com/2011/09/installing-postgresql-90-on-mac-os-x.html
- Macports, v9.2: https://coderwall.com/p/xezzaa
- Homebrew (source of install script): https://github.com/mxcl/homebrew/blob/master/Library/Formula/postgresql.rb
- Postgres.app docs on installation directories: http://postgresapp.com/documentation#toc_20
- Built-in Postgres vs macports: http://www.jonathandean.com/2011/08/postgresql-8-4-on-mac-os-x-10-7-lion/
- More about built-in postgres on Lion: http://apple.stackexchange.com/questions/19209/postgresql-in-os-x-lion
- Apple's Postgres tree: http://opensource.apple.com/source/PostgreSQL/PostgreSQL-23/