Different Linux distros have their preferred place of where stuff goes and of course the default location on windows is completely different from that too. So there isn't really one default location where you can find PostgreSQL data cluster. Of course user's can pick their
locations as well. So what is a casual DBA supposed to do?
The pg_settings table
PostgreSQL has a convenient system
table view called pg_settings that stores a lot of information. It stores the location of the data cluster, the pg_hbafile and other conf files.
In additon to that you can interogate it to find out information you will find in the postgresql.conf file. Why sift thru that postgresql.conf file (assuming you can already query your postgresql server) when you can find the answers you are looking
for with an SQL query?
Below are some queries that list some of the things we find useful
Get listing of all File Locations
As Joe noted: this query only works if you are connected as a super user, though the queries after do not require super user access.
This will tell you where your data cluster is and all your conf files (postgresql.conf, pg_hba.conf, pg_ident.conf, external_pid if you have one).
SELECT name, setting
WHERE category = 'File Locations';
If you are taking advantage of PostgreSQL support for tablespaces, the data file location will not be enough to tell you where all your stuff is. Another handy table
to interrogate for this is the pg_tablespace table. If the location of a table space is not in the init cluster location, the spclocation will be filled in with the file path to it.
SELECT spcname, spclocation
Listing of memory and buffer settings
This gives you information about all the different memory settings -- work_mem, maintenance_work_mem, shared_buffers, temp_buffers, wal_buffers and whether
you have constraint exclusion enabled.
Greg Smith suggested this query which gives a prettier more understandable look to setting (makes it show in kb/MB) and also included effective cache size -
SELECT name, setting, unit, category
WHERE name like '%mem%' or name LIKE 'constraint%' or name like '%buffer%'
ORDER BY name;
SELECT name, setting, current_setting(name),unit, category
WHERE name like '%mem%' or name LIKE 'constraint%' or name like '%buffer%' or name like 'effective%'
ORDER BY name;
Listing of all categories of settings
Menu of all categories of settings
SELECT DISTINCT category
ORDER BY category;