PostgreSQL database default location on Linux

Question

What is the default directory where PostgreSQL will keep all databases on Linux?

Answer 1

The "directory where postgresql will keep all databases" (and configuration) is called "data directory" and corresponds to what PostgreSQL calls (a little confusingly) a "database cluster", which is not related to distributed computing, it just means a group of databases and related objects managed by a PostgreSQL server.

The location of the data directory depends on the distribution. If you install from source, the default is /usr/local/pgsql/data:

In file system terms, a database cluster will be a single directory under which all data will be stored. We call this the data directory or data area. It is completely up to you where you choose to store your data. There is no default, although locations such as /usr/local/pgsql/data or /var/lib/pgsql/data are popular. (ref)

Besides, an instance of a running PostgreSQL server is associated to one cluster; the location of its data directory can be passed to the server daemon ("postmaster" or "postgres") in the -D command line option, or by the PGDATA environment variable (usually in the scope of the running user, typically postgres). You can usually see the running server with something like this:

[root@server1 ~]# ps auxw |  grep postgres | grep -- -D
postgres  1535  0.0  0.1  39768  1584 ?        S    May17   0:23 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data

Note that it is possible, though not very frequent, to run two instances of the same PostgreSQL server (same binaries, different processes) that serve different "clusters" (data directories). Of course, each instance would listen on its own TCP/IP port.

Answer 2

/var/lib/postgresql/[version]/data/

At least in Gentoo Linux and Ubuntu 14.04 by default.

You can find postgresql.conf and look at param data_directory. If it is commented then database directory is the same as this config file directory.

Answer 3

Connect to a database and execute the command:

SHOW data_directory;

More information:

https://www.postgresql.org/docs/current/sql-show.html https://www.postgresql.org/docs/current/runtime-config-file-locations.html

Answer 4

Default in Debian 8.1 and PostgreSQL 9.4 after the installation with the package manager apt-get

ps auxw |  grep postgres | grep -- -D
postgres 17340  0.0  0.5 226700 21756 ?        S    09:50   0:00 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.conf

so apparently /var/lib/postgresql/9.4/main.

Answer 5

On Centos 6.5/PostgreSQL 9.3:

Change the value of "PGDATA=/var/lib/pgsql/data" to whatever location you want in the initial script file /etc/init.d/postgresql.

Remember to chmod 700 and chown postgres:postgres to the new location and you're the boss.

Answer 6

Below query will help to find postgres configuration file.

postgres=# SHOW config_file;
             config_file
-------------------------------------
 /var/lib/pgsql/data/postgresql.conf
(1 row)

[root@node1 usr]# cd /var/lib/pgsql/data/
[root@node1 data]# ls -lrth
total 48K
-rw------- 1 postgres postgres    4 Nov 25 13:58 PG_VERSION
drwx------ 2 postgres postgres    6 Nov 25 13:58 pg_twophase
drwx------ 2 postgres postgres    6 Nov 25 13:58 pg_tblspc
drwx------ 2 postgres postgres    6 Nov 25 13:58 pg_snapshots
drwx------ 2 postgres postgres    6 Nov 25 13:58 pg_serial
drwx------ 4 postgres postgres   36 Nov 25 13:58 pg_multixact
-rw------- 1 postgres postgres  20K Nov 25 13:58 postgresql.conf
-rw------- 1 postgres postgres 1.6K Nov 25 13:58 pg_ident.conf
-rw------- 1 postgres postgres 4.2K Nov 25 13:58 pg_hba.conf
drwx------ 3 postgres postgres   60 Nov 25 13:58 pg_xlog
drwx------ 2 postgres postgres   18 Nov 25 13:58 pg_subtrans
drwx------ 2 postgres postgres   18 Nov 25 13:58 pg_clog
drwx------ 5 postgres postgres   41 Nov 25 13:58 base
-rw------- 1 postgres postgres   92 Nov 25 14:00 postmaster.pid
drwx------ 2 postgres postgres   18 Nov 25 14:00 pg_notify
-rw------- 1 postgres postgres   57 Nov 25 14:00 postmaster.opts
drwx------ 2 postgres postgres   32 Nov 25 14:00 pg_log
drwx------ 2 postgres postgres 4.0K Nov 25 14:00 global
drwx------ 2 postgres postgres   25 Nov 25 14:20 pg_stat_tmp

Answer 7

The command pg_lsclusters (at least under Linux / Ubuntu) can be used to list the existing clusters and with it also the data directory:

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

Answer 8

I think best method is to query pg_setting view:

 select s.name, s.setting, s.short_desc from pg_settings s where s.name='data_directory';

Output:

      name      |        setting         |            short_desc
----------------+------------------------+-----------------------------------
 data_directory | /var/lib/pgsql/10/data | Sets the server's data directory.
(1 row)