Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

8 Answers 8

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.

share|improve this answer
    
didn't know that, I usually use the query, so +1 –  DrColossos Feb 21 '11 at 6:51
3  
if it's not running, try running a find for postgresql.conf, which is usually in the data directory, or will have information about the data directory. –  xzilla Dec 12 '11 at 20:15

You can also query for it

select setting from pg_settings where name = 'data_directory';
share|improve this answer
    
Good, this works without being super-user. –  Rauni Jul 22 at 11:01

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
share|improve this answer

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
share|improve this answer
    
This is specific to Debian/Ubuntu. –  Peter Eisentraut Feb 20 '11 at 15:30
2  
What's wrong with that? It solves the problem for at least some of the users. –  Adam Matan Feb 21 '11 at 11:55
    
+1: me for example :-) –  Stephane Rolland Mar 19 '13 at 15:59

pg_config shows a lot of information including the data directory:

CONFIGURE = '--disable-debug' '--prefix=/usr/local/Cellar/postgresql/9.3.2' 
            '--datadir=/usr/local/Cellar/postgresql/9.3.2/share/postgresql' 
            '--docdir=/usr/local/Cellar/postgresql/9.3.2/share/doc/postgresql' 
            '--enable-thread-safety' '--with-bonjour' '--with-gssapi' '--with-krb5' 
            '--with-ldap' '--with-openssl' '--with-pam' '--with-libxml' '--with-libxslt'
            '--with-ossp-uuid' '--with-python' '--with-perl' '--with-tcl' 'CC=clang' 
            'CFLAGS=-I/usr/local/Cellar/ossp-uuid/1.6.2/include' 
            'LDFLAGS=-L/usr/local/Cellar/ossp-uuid/1.6.2/lib' 'LIBS=-luuid'

This might be a brew-specific thing, though; without brew I don't know what it shows.

share|improve this answer

On OS X 10.8 and 10.9 (not 10.10) with server app installed, in a terminal window, type

sudo serveradmin settings postgres

On OS X 10.9 Mavericks, this is the output I get from that, which includes the data directory.

postgres:log_connections = "on"
postgres:unix_socket_directory = "/private/var/pgsql_socket"
postgres:listen_addresses = "127.0.0.1,::1"
postgres:unix_socket_group = "_postgres"
postgres:log_statement = "ddl"
postgres:log_line_prefix = "%t "
postgres:unix_socket_permissions = "0770"
postgres:log_lock_waits = "on"
postgres:logging_collector = "on"
postgres:log_filename = "PostgreSQL.log"
postgres:dataDir = "/Library/Server/PostgreSQL/Data"
postgres:log_directory = "/Library/Logs/PostgreSQL"

I just tried this in Yosemite (OS X 10.10) and postgres is no longer listed as a service under serveradmin (although postgres is installed).

sudo serveradmin list

does not list postgres

share|improve this answer

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
share|improve this answer

Open postgresql.conf, go to the line:

data_directory='/var/lib/postgresql/main/'

is your answer.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.