How do I find PostgreSQL's data directory?

Question

I forgot how I last time started postgres (it was months ago) and I don't remember where the data directory is located at. The postgres command seems to require to location. I'm on MacOsX if that helps.

/usr/local/postgres did not exists.

Using the answers provided below, I found that it was here

/usr/local/var/postgres

Answer 1

If you can connect to the database with superuser access, then

SHOW data_directory;

is the shortest way.

If the server is not running and you forgot where the data directory was, then you really have to guess yourself. An operating system specific convention or the shell history might provide hints.

Answer 2

You can also query for it

select setting from pg_settings where name = 'data_directory';

Answer 3

If you can't log in (eg, forgot your password) but the service is running, as MacOS X is a posix varient, you can always check ps to see if it's being passed in as an argument :

On a linux box with two postgres instances running:

-bash-3.2$ ps ax | grep postgres | grep -v postgres:
 4698 ?        S      9:59 /opt/PostgreSQL/8.4/bin/postgres -D /opt/PostgreSQL/8.4/data
 6115 ?        S      8:16 /opt/PostgreSQL/8.4/bin/postgres -D /opt/PostgreSQL/8.4/data_sums
30694 pts/9    S+     0:00 grep postgres

Answer 4

On Ubuntu\Debian, try pg_lscluster.

On my machine:

pg_lsclusters 
Version Cluster   Port Status Owner    Data directory             Log file
8.4     main      5432 online postgres /home/adam/db/main/data    /home/adam/db/main/log

Answer 5

I don't know about osx, but for at least for ubuntu it could be as easy as /etc/init.d/postgres start to get it started. (Which was your objective, right?) Even if it is not auto-started, the control scripts tends to be present.

If you still need to find the directory, you could try a file search for some file found in the data directory - e.g. locate pg_hba.conf (assuming locate is available on osx, something will be)

It may turn up some false positives, such as wherever it is copied from when you create a new one, man pages, etc, but I think the list should be short enough that you'll have no problem finding it.

One of my postgres clusters has this data directory:

base
global
pg_clog
pg_hba.conf
pg_ident.conf
pg_log
pg_multixact
pg_notify
pg_stat_tmp
pg_subtrans
pg_tblspc
pg_twophase
PG_VERSION
pg_xlog
postgresql.conf
postmaster.opts
stderr
stdout