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 only takes a minute to sign up.
Sign up to join this community
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.
pg_conftoolDebian 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_lsclustersIf 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)
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.
Your question would be better suited for the Unix or Ubuntu site because it looks like a basis Linux issue.
Anyway here goes.
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.
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.
SHOW data_directory;. Otherwise, how did you install PostgreSQL?/var/lib/postgresql?