120

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
  • When you use Homebrew to install Postgres, then data is /usr/local/var/postgres – andrerpena Dec 29 '18 at 12:49

12 Answers 12

149

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.

  • 7
    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
33

You can also query for it

select setting from pg_settings where name = 'data_directory';
18

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.

  • 1
    I'm using brew too. As of now, the datadir listed in pg_config --configure is not the same as the one brew info postgresql recommends (which is $(brew --prefix)/var/postgres and is not a symlink to the one listed). It seems the formula is a bit lax on using config flags properly. – Vaz Aug 12 '15 at 21:52
  • Note for Ubuntu/Debain: pg_config is not installed with the postgresql-<ver> apt package, but requires also postgresql-server-dev-<ver> – pscl Apr 20 '18 at 20:36
  • @PSCL True, but OP was under OS X. – Dave Newton Apr 20 '18 at 20:38
  • Understood. Just a note for the future reference of Debian users (such as myself) stumbling upon this question based on its generic title. – pscl May 1 '18 at 23:21
  • 1
    @Tom I don't think the formula really intended to use the datadir passed to configure... it expects to be passed the datadir as a commandline arg, you can see what gets passed in when it's run automatically as a service in $(brew --prefix postgresql)/homebrew.mxcl.postgresql.plist, look for the path following the -D (it's /usr/local/var/postgres on mine). – Vaz Nov 25 '18 at 9:55
15

On Ubuntu\Debian, try the pg_lsclusters command.

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
9

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
2

Open postgresql.conf, go to the line:

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

is your answer.

  • where do you find postgresql.conf – user5359531 Nov 13 '18 at 20:02
  • @user5359531 You can do find / -iname "postgresql.conf" 2>/dev/null to find out – Tom Nov 21 '18 at 16:14
  • My postgresql.conf has no data_directory defined. It's only referenced in a comment: #data_directory = 'ConfigDir' # use data in another directory – SimpleJ Mar 14 at 16:56
2

Modern versions of the PostgreSQL desktop client for OSX have a convenient "Server Settings" dialog that will allow you not just to list the data directory but open it directly.

Very useful if you experience a random machine crash that leaves behind a lock file.

enter image description here

  • Where is this dialog? I cant find anything like this on a direct install (not brew) of 9.5 via postgresql.org/download/macosx. It does come with pgadmin III, but this does not seem to have this dialog. I cant find any desktop clients? – John Little Oct 8 '18 at 17:51
1

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

1

On Windows Server 2012, the command pg_config did not reveal the data directory for some crazy reason. But I found it by looking at the file C:\Program Files\PostgreSQL\9.4\pg_env.bat, which contained this line:

@SET PGDATA=E:\POSTGRESQL

That was the answer I needed.

0

How about using the standard utility pg_config such as pg_config --bindir One could also get the the configure parameters used to compile PostgreSQL and parse them with pg_config --configure

  • --bindir shows the location of the executables, not the data directory. --configure is OK, even though one has to parse the output a bit. – dezso Oct 28 '17 at 21:49
  • Yes that is true and unfortunately pg_config has no parameter that would reveal the datadir. When I wrote this, I thought that at least it gives something close, assuming the datadir is not in a special location. datadir would be relative then to the bindir such as ../data – Jacques Oct 29 '17 at 22:32
0

As pointed out, one could also use ps to capture the data location such as:

ps -e |egrep postgres|egrep -E '[[:blank:]]+\-D'| perl -pe 's/^.*[[:blank:]]+-D[[:blank:]]+(\S+).*?$/$1/'.

This would work most of the time. It would return something like /usr/local/pgsql/data or wherever else it was specified in the parameters of PostgreSQL at launch.

0

Based on the answer by @Matthew Mark Miller, on macOS 10.14 using Postgres 11 it is located at:

~/Library/Application Support/Postgres

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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