What is the default directory where PostgreSQL will keep all databases on Linux?
This question is related to
linux
postgresql
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)
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
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
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
.
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
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.
/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.
Source: Stackoverflow.com