Sometimes, you start working on a PostgreSQL instance but forget about the data directory, here we will discuss different methods to know the data directory location
The database parameter related to the data file directory is "data_directory"
Method 1: pg_settings
If you to know the value of any parameter in PostgreSQL, you can query the table pg_settings, now let's see what the columns present in pg_settings using "\d pg_settings" command
postgres=# \d pg_settings;
View "pg_catalog.pg_settings"
Column | Type | Collation | Nullable | Default
-----------------+---------+-----------+----------+---------
name | text | | |
setting | text | | |
unit | text | | |
category | text | | |
short_desc | text | | |
extra_desc | text | | |
context | text | | |
vartype | text | | |
source | text | | |
min_val | text | | |
max_val | text | | |
enumvals | text[] | | |
boot_val | text | | |
reset_val | text | | |
sourcefile | text | | |
sourceline | integer | | |
pending_restart | boolean | | |
From these all columns, there are few important , i.e. name, setting, pending_restart, etc
to know data_directory location using pg_settings use below command
SELECT setting FROM pg_settings WHERE name = 'data_directory';
postgres=# SELECT setting FROM pg_settings WHERE name = 'data_directory';
setting
-------------------------------------
C:/Program Files/PostgreSQL/10/data
(1 row)
Method 2 : using show command
most of parameters values can be retrieved using show command, one limitation is, you should know parameter name or else simply use pg_settings view with like operator
show data_directory command will give you location of data directory
postgres=# show "data_directory";
data_directory
-------------------------------------
C:/Program Files/PostgreSQL/10/data
(1 row)
[Also Read- Physical location of datafiles in postgres]
Method 3 : configuration file
check the configuration file and find data_directory parameter value from it
Method 4 : using status of postgres services
using ps -ef|grep postgres on unix/linux system
