How do I find PostgreSQL's data directory?

  • I forgot how I started PostgreSQL the last time (it was months ago) and I don't remember where the data directory is located. The postgres command seems to require the location of the data directory.

    I'm on MacOsX if that helps.

    /usr/local/postgres doesn't exist on my Mac.

    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`

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

    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.

  • You can also query for it

    select setting from pg_settings where name = 'data_directory';
    

    Returned 0 rows for pgsql v.10

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

    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.

    Note for Ubuntu/Debain: pg_config is not installed with the `postgresql-` apt package, but requires also `postgresql-server-dev-`

    @PSCL True, but OP was under OS X.

    Understood. Just a note for the future reference of Debian users (such as myself) stumbling upon this question based on its generic title.

    Mine does not have `datadir`. Using the version here: https://anaconda.org/anaconda/postgresql

    @Vaz Seems like brew still doesn't symlink the data dir. Is it a legit workaround to manually symlink them together?

    @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).

  • 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
    

    Still works as of 2020

  • 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
    
  • Open postgresql.conf, go to the line:

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

    is your answer.

    where do you find `postgresql.conf`

    @user5359531 You can do `find / -iname "postgresql.conf" 2>/dev/null` to find out

    My `postgresql.conf` has no `data_directory` defined. It's only referenced in a comment: `#data_directory = 'ConfigDir' # use data in another directory`

  • 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 https://www.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?

  • 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

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

  • Generalizing from the 2 top answers:

    To show the data directory directly at the command-line:

    psql -U postgres -tA -c "SHOW data_directory;"
    

    To also see all other defined paths to files and directories:

    psql -U postgres -c "SELECT name, setting FROM pg_settings WHERE setting LIKE '/%';"
    

    Sample output:

              name           |                 setting                  
    -------------------------+------------------------------------------
     config_file             | /etc/postgresql/9.6/main/postgresql.conf
     data_directory          | /mnt/pg_ssd_500/9.6/main
     external_pid_file       | /var/run/postgresql/9.6-main.pid
     hba_file                | /etc/postgresql/9.6/main/pg_hba.conf
     ident_file              | /etc/postgresql/9.6/main/pg_ident.conf
     ssl_cert_file           | /etc/ssl/pg_server.cer
     ssl_key_file            | /etc/ssl/private/pg_server.key
     stats_temp_directory    | /var/run/postgresql/9.6-main.pg_stat_tmp
     unix_socket_directories | /var/run/postgresql
    (9 rows)
    

    (On Windows, replace LIKE '/%' with LIKE '%/%' because the path starts with a drive letter)

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM