Where is my data and other stuff

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
	FROM pg_settings
	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
			FROM pg_tablespace;
	

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.

SELECT name, setting, unit, category
FROM pg_settings
WHERE name like '%mem%' or name LIKE 'constraint%' or name like '%buffer%'
ORDER BY name;

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, current_setting(name),unit, category 
FROM pg_settings 
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
FROM pg_settings
ORDER BY category;