2

I am new to Ubuntu and PostgreSQL. I am trying to find a "data directory" for PostgreSQL on my Ubuntu. When I Googled, it appears that it should be /var/lib/postgresql/9.3/main. But on my Ubuntu, I do not see that folder. I only see "bin" and "lib" folders. Can anyone guide where I could locate a data directory for PostgreSQL?

I was informed that the variable is normally called datadir.

4
  • 1
    If you can log into the DB, just execute SHOW data_directory;. Otherwise, how did you install PostgreSQL?
    – dezso
    Apr 28, 2017 at 16:48
  • Are you using version 9.3? Is there anything under /var/lib/postgresql ? Apr 28, 2017 at 18:23
  • Yes. I am using version 9.3. Only folder that I see is a folder called "9.3".
    – Java
    Apr 28, 2017 at 18:56
  • Where do I go to execute SHOW data_directory; ? I tried this at SQL statement and got following error message SQL error: ERROR: syntax error at or near ")" LINE 1: SELECT COUNT() AS total FROM (SHOW data_directory) AS sub ^ In statement: SELECT COUNT() AS total FROM (SHOW data_directory) AS sub
    – Java
    Apr 28, 2017 at 19:27

3 Answers 3

2

pg_conftool

Debian and Ubuntu provide their own utilities. To get this information, use pg_conftool. Here is what it looks like,

pg_conftool show -s data_directory;
/var/lib/postgresql/9.5/main

For reference,

Usage:
  /usr/bin/pg_conftool [options] [<version> <cluster name>] [<configfile>] <command>

Options:
  -b --boolean  Format output as boolean
  -s --short    Print only value
  -v --verbose  Verbose output
  --help        This help

Commands:
  show <parameter>|all
  set <parameter> <value>
  remove <parameter>
  edit

pg_lsclusters

If you're running multiple versions of PostgreSQL on the same machine, you may find pg_lsclusters useful. It will show you all of the versions running.

pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log

You can parse that pretty easy, here we use awk to get out the Ver and Data directory,

pg_lsclusters | awk '{print $1,$6}'
Ver Data
9.5 /var/lib/postgresql/9.5/main

If you have JSON.pm installed you can use pg_lsclusters -j to output JSON,

Error: Please install JSON.pm for JSON output (Debian: libjson-perl)
2
  • Thanks. Didn't know that. However, on a multi-instance PostgreSQL server it only displays one entry (possibly the main/default entry). I tried it on our multi-instance PostgreSQL server. As can be seen in my answer, we have multiple instances running on one server.
    – John K. N.
    Nov 14, 2017 at 7:54
  • @hot2use see the update Nov 14, 2017 at 8:21
0

As you have told us that you are new to Ubuntu AND PostgreSQL, I can imagine that things are hard to find (I've been there myself ...).

Use the command line (terminal). Have a look in the postgresql.conf file. In order to get to the correct directory, use cd. In all likelihood, you'll need

cd /etc/postgresql/9.3/main

Below, you can see the top part of an unaltered .conf file . This particular postgres server is installed on a machine running Debian (Ubuntu is "based on" Debian). Run cat postgresql.conf | more (example: see below). You only need to look at the top half of the file (use enter/return to advance one line. Type q for getting back to the command line). Notice the line reading "data_directory ..." - this should give you the correct path.

postgres@debian:/etc/postgresql/9.3/main$ cat postgresql.conf | more
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# several lines not displayed !
#

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

data_directory = '/var/lib/postgresql/9.3/main'     # use data in another directory
                    # (change requires restart)
hba_file = '/etc/postgresql/9.3/main/pg_hba.conf'   # host-based authentication file
                    # (change requires restart)
ident_file = '/etc/postgresql/9.3/main/pg_ident.conf'   # ident configuration file
                    # (change requires restart)

When executing ls /var/lib/postgresql/9.3/main (again using a terminal emulator and the command line), I see the following:

ls /var/lib/postgresql/9.3/main
base    pg_clog       pg_notify  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION  postmaster.opts
global  pg_multixact  pg_serial  pg_stat       pg_subtrans  pg_twophase  pg_xlog     postmaster.pid

If you cannot find these (or similar) file names in this path, then it could be that things went pear shaped* after the installation. Or it could be another problem altogether, maybe related to the machine you are using. As you have not provided any details about that, it is hard to give any advice regarding this.

* see Urban Dictionary.
2
  • Thanks. I do not see any file info. after I tried with the command line you provided. Only thing I see is: # ------------------------------- # PostgreSQL configuration file # -------------------------------- # This file consists of lines of the form: # name = value
    – Java
    May 1, 2017 at 15:30
  • I am writing this very cautiously: Considering that you have only started off with Linux and PostgreSQL, it maybe an idea to start from scratch again. Sometimes this is quicker - particularly if you are using virtual machines - than trying to fix a system that you are not familiar with. HOWEVER, if you have stored (important) data on this machine already, you MUST create one (or more) reliable backups! A successful PostgreSQL installation on Linux should more or less just work "out of the box".
    – stefan
    May 2, 2017 at 17:29
0

Your question would be better suited for the Unix or Ubuntu site because it looks like a basis Linux issue.

Anyway here goes.

Solution

After you have logged in to your Ubuntu server, you should be on a shell prompt similar to the following:

Last login: Mon Sep 18 14:42:02 2017 from 161.78.198.153
$ 

The PostgreSQL service will be normally running under the postgres login, but lets go ahead and verify that. First we switch to the postgres account:

$ su - postgres
Password:

[postgres@hostname:~] > 

Now let's go and search for the postgresql.conf file:

[postgres@hostname:/etc/postgresql/main1] > locate postgresql.conf /etc/postgresql/9.5/main/postgresql.conf /etc/postgresql/9.5/main1/postgresql.conf /etc/postgresql/9.5/main2/postgresql.conf /etc/postgresql/9.5/main3/postgresql.conf /pgsql/data/postgres2/postgresql.conf /pgsql/data/postgres3/postgresql.conf /usr/lib/tmpfiles.d/postgresql.conf /usr/share/postgresql/9.5/postgresql.conf.sample [postgres@hostname:/etc/postgresql/main1] >

Ok. We have found multiple files. Let's go and look at one:

[postgres@hostname:/etc/postgresql/main1] > more /etc/postgresql/9.5/main/postgresql.conf

This gives us:

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
# "#" anywhere on a line.  The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload".  Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on".  Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units:  kB = kilobytes        Time units:  ms  = milliseconds
#                MB = megabytes                     s   = seconds
#                GB = gigabytes                     min = minutes
#                TB = terabytes                     h   = hours
#                                                   d   = days


#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

data_directory = '/var/lib/postgresql/9.5/main'         # use data in another directory
                                        # (change requires restart)
hba_file = '/etc/postgresql/9.5/main/pg_hba.conf'       # host-based authentication file
                                        # (change requires restart)
ident_file = '/etc/postgresql/9.5/main/pg_ident.conf'   # ident configuration file
--More--(9%)

So you know have a configuration setting pointing you in the right direction.

Caution

Now the PostgreSQL instance could have manually been started with a different configuration file. Let's double-check with the ps -efx command (beautiful isn't it?):

[postgres@hostname:/etc/postgresql/main1] > ps -efx
  PID TTY      STAT   TIME COMMAND
 4507 pts/0    S      0:00 -su TERM=xterm PATH=/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games LANG=en_US.UTF-8 LANGUAGE=en_US:en MAIL
 4972 pts/0    R+     0:00  \_ ps -efx pgarchiv=/pgsql-backup/archive/postgres1/ SHELL=/bin/bash TERM=xterm PGHOSTADDR=10.57.198.21 USER=postgr
 1672 ?        S      0:08 /usr/lib/postgresql/9.5/bin/postgres -D /pgsql/data/postgres2 -c config_file=/etc/postgresql/9.5/main2/postgresql.co
 1739 ?        Ss     0:00  \_ postgres: checkpointer process
 1744 ?        Ss     0:02  \_ postgres: writer process
 1745 ?        Ss     0:02  \_ postgres: wal writer process
 1746 ?        Ss     0:04  \_ postgres: autovacuum launcher process
 1747 ?        Ss     0:00  \_ postgres: archiver process   last was 000000010000000200000012
 1748 ?        Ss     0:05  \_ postgres: stats collector process
 1671 ?        S      0:11 /usr/lib/postgresql/9.5/bin/postgres -D /pgsql/data/postgres1 -c config_file=/etc/postgresql/9.5/main1/postgresql.co
 1751 ?        Ss     0:00  \_ postgres: checkpointer process
 1752 ?        Ss     0:02  \_ postgres: writer process
 1753 ?        Ss     0:02  \_ postgres: wal writer process
 1754 ?        Ss     0:06  \_ postgres: autovacuum launcher process
 1755 ?        Ss     0:00  \_ postgres: archiver process   last was 000000010000000200000033
 1756 ?        Ss     0:07  \_ postgres: stats collector process
 1664 ?        S      0:08 /usr/lib/postgresql/9.5/bin/postgres -D /pgsql/data/postgres3 -c config_file=/etc/postgresql/9.5/main3/postgresql.co
 1740 ?        Ss     0:00  \_ postgres: checkpointer process
 1741 ?        Ss     0:02  \_ postgres: writer process
 1742 ?        Ss     0:02  \_ postgres: wal writer process
 1743 ?        Ss     0:04  \_ postgres: autovacuum launcher process
 1749 ?        Ss     0:00  \_ postgres: archiver process   last was 000000010000000200000012
 1750 ?        Ss     0:05  \_ postgres: stats collector process
[postgres@hostname:/etc/postgresql/main1] >

You will find a line containing:

/usr/lib/postgresql/9.5/bin/postgres -D /pgsql/data/postgres2 -c config_file=/etc/postgresql/9.5/main2/postgresql.conf

This is how you can check which PostgreSQL instances are using which *.conf files.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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