Where is my data and other stuff   

Below are some queries that list some of the things we find useful

Get listing of all File Locations

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;

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;