PostgreSQL : How to get data directory location for PostgreSQL instance

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