165

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

Improve this question
2

9 Answers 9

Reset to default
169

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 ("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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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...
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.
@MacGyver Thanks for pointing that out. Could you provide the link to that comment of yours?
@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".
98 
/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.

Improve this answer

7 Comments

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.
@MagnusHagander it is an answer, y do not you make this comment an answer. It worked well for me.
in Gnome I see the same here /var/lib/8.1/postgresql
For me its '/var/lib/postgresql/9.3/main'
for Centos 7, postgres 9.3 path /var/lib/pgsql/9.3
|
61

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

Improve this answer

Comments

20

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.

Improve this answer

Comments

15

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.

Improve this answer

Comments

6

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
Improve this answer

Comments

4

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
Improve this answer

Comments

3

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)
Improve this answer

Comments

0

@Leos answer works for me. I am using RHEL 8.10 and Postgres 17.2. To improve a little on the solution, I improved the answer, and had a cut and two revs applied. So, my final command was as below.

ps auxw |  grep postgres | grep -- -D | rev | cut -d' ' -f 1 | rev

And it gives an output as given below.

/var/lib/pgsql/17/data/

Hope it helps. Thanks

Improve this answer

4 Comments

I think you mean "improve" instead of "improvise". Also please explain the advantage of your improvement (and make sure that it is not only an advantage for your use case or for your taste), otherwise it seems like your post should be a comment or an edit on the answer you based it on, which by the way you would better reference via the "Share" button beneath it than by user name, which already seems to have changed since you posted this. And now there are two answers which could be taken to be by a "Leo".
Hello, Yunnosch. Thanks for your suggestion. Spelling correction suggestion is taken. I did not understand the rest of your comment. It will be great if you elaborate.
You show an improvement. Please explain the advantage of that improvement. Also please confirm that it is an improvement for the problem described at the top of this page. If it is only an improvement for your possibly different use case it probably does not count as a contribution to the answers to the question here. An alternative way of putting it is: Please make sure that you mean this to be an answer according to How to Answer and edit to make that more obvious.
Generally, if you want to refer to an existing post please use the link offered by the "Share" beneath. Referring to a post by author name could turn out unclear, if the user name gets changed, which is possible. For example, read the names of all authors of existing answers. You will find no Leo, but at least two who could be meant be "Leo".

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.