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 | /usr/bin | /var/pgsql | /System/Library/LaunchDaemons/org.postgresql.postgres.plist | |
EnterpriseDB Mac OS X 1-click Installer | 9.2.x | /Library/PostgreSQL/9.2 | /Library/PostgreSQL/9.2/data | /Library/LaunchDaemons/com.edb.launchd.postgresql-9.2.plist | C |
EnterpriseDB Mac OS X 1-click Installer | 9.1.x | /Library/PostgreSQL/9.1 | /Library/PostgreSQL/9.1/data | C | |
EnterpriseDB Mac OS X 1-click Installer | 9.0.x | /Library/PostgreSQL/9.0 | /Library/PostgreSQL/9.0/data | C | |
EnterpriseDB Mac OS X 1-click Installer | 8.4.x | /Library/PostgreSQL/8.4 | /Library/PostgreSQL/8.4/data | C | |
Postgres.app | 9.0.x | /Applications/Postgres.app/Contents/MacOS/bin | ~/Library/Application Support/Postgres/var | C | |
Postgres.app | 9.1.x | /Applications/Postgres.app/Contents/MacOS/bin | ~/Library/Application Support/Postgres/var | C | |
Postgres.app | 9.2.x | /Applications/Postgres.app/Contents/MacOS/bin | ~/Library/Application Support/Postgres/var | C | |
homebrew (aka 'brew install postgres') | 9.2 (as of Sept 2012) | /usr/local/bin (binary named postgres ) | /usr/local/var/postgres | /Library/LaunchAgents/homebrew.mxcl.postgresql.plist (original: /usr/local/Cellar/postgresql/9.1.4/homebrew.mxcl.postgresql.plist ) | UTF-8 (recommended in docs, but not done automatically) |
macports | 9.0 | /opt/local/lib/postgresql90/bin | /opt/local/var/db/postgresql90/defaultdb (suggested) | /Library/LaunchDaemons/org.macports.postgresql92-server.plist | No initdb done automatically |
macports | 9.2 | /opt/local/lib/postgresql92/bin | /opt/local/var/db/postgresql92/defaultdb (suggested) | /Library/LaunchDaemons/org.macports.postgresql92-server.plist | No initdb done automatically |
ps -auxwww | grep -i postgres
to find the Postgres process that's running - the path to your data directory is often right there in the process description.pg_dump
and pg_dumpall
l
from the psql
command-line very helpful in troubleshooting restore problems later.pg_upgrade
or pg_dump
. Some versions require version-specific upgrade steps to be taken as well, which pg_upgrade
or pg_dump
will not execute for you.pg_upgrade
or use command-line psql
you 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 PATH
settings.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.pg_upgrade
, you must be able to stop the running Postgres instance. On MacOS X, that probably means using launchctl
and pointing it at the plist that controls your Postgres daemon.LC_COLLATE
is 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_COLLATE
values. 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_upgrade
will 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.