PostgreSQL database default location on Linux

Question

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

Another thread where the same questions was discussed. http://stackoverflow.com/questions/1137060/where-does-postgresql-store-the-database — Vishal, Jun 14 '13 at 08:58
Possible duplicate of [Where does PostgreSQL store the database?](https://stackoverflow.com/questions/1137060/where-does-postgresql-store-the-database) — George Hilliard, Feb 09 '19 at 20:21

score 160 · Accepted Answer · edited Jun 23 '16 at 16:21

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.

I can see the current data directory, but I see many other folders and files. Does psql "save" the database in many files or is there only one inside this data directory that is the DB? because I was thinking in something like the DB file on mysql or sql server for example... — CamiloVA, Mar 26 '20 at 04:33
This works up to CentOS 7, but not CentOS 8. Not sure about other distributions. There is another answer with the new command for CentOS 8. See my comment. — JustBeingHelpful, Nov 06 '20 at 06:39
@MacGyver Thanks for pointing that out. Could you provide the link to that comment of yours? — leonbloy, Nov 06 '20 at 12:11
@leonbloy /var/lib/pgsql/data/ is the path for CentOS 8. But you provided the way to obtain it by running the "ps auxw | grep postres | grep -- -D". — JustBeingHelpful, Nov 06 '20 at 17:13

score 95 · Answer 2 · edited Apr 23 '14 at 11:26

95

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

edited Apr 23 '14 at 11:26

Abdul

3
2

answered Jun 09 '10 at 09:24

silent

3,843
23
29

24

It will be depending on the distribution, they all do it slightly differently. If you can connect to the system, using "SHOW data_directory" is probably the easiest way. – Magnus Hagander Jun 09 '10 at 12:03
@MagnusHagander it is an answer, y do not you make this comment an answer. It worked well for me. – Vishal Jun 14 '13 at 08:57
in Gnome I see the same here `/var/lib/8.1/postgresql` – Junior Mayhé Apr 30 '14 at 11:47
1

For me its '/var/lib/postgresql/9.3/main' – radtek Feb 26 '15 at 20:27
9

for Centos 7, postgres 9.3 path `/var/lib/pgsql/9.3` – ranjeet Dec 17 '15 at 13:02
@MagnusHagander what do you mean `if you can connect to the system`? What's the system? – Agent Zebra Feb 28 '19 at 05:56
I found this Answer to be true in Docker Desktop 4.22. – Basil Bourque Aug 21 '23 at 23:51

score 54 · Answer 3 · edited Jan 04 '19 at 00:30

54

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

edited Jan 04 '19 at 00:30

Michel Milezzi

10,087
3
21
36

answered Nov 25 '17 at 23:02

Roby Sottini

2,117
6
48
88

Léo Léopold Hertz 준영 · Answer 4 · 2015-07-21T05:51:24.880

19

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.

edited Jul 21 '15 at 05:51

answered Jul 14 '15 at 07:38

Léo Léopold Hertz 준영

134,464
179
445
697

score 13 · Answer 5 · edited Jun 23 '16 at 16:22

13

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.

edited Jun 23 '16 at 16:22

the Tin Man

158,662
42
215
303

answered May 28 '14 at 08:28

Dum

131
1
2

score 5 · Answer 6 · answered Aug 19 '19 at 01:50

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

score 4 · Answer 7 · answered Nov 25 '19 at 14:41

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

score 3 · Answer 8 · answered Jan 11 '19 at 13:49

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)

PostgreSQL database default location on Linux

8 Answers8

Linked