0

I am trying to learn a bit of PostgreSQL, and I am currently trying to understand the catalog. I think that if it stores all the metadata of the DB, should it also store things like the file locations?

I mean, I can (not involving the catalog) do:

SHOW data_directory;

or

SHOW config_file;

etc etc. I think it would be quite interesting to retrieve the same information via a catalog query, but I am not finding information about it. At most, I could do:

SELECT pg_settings.name, pg_settings.sourcefile FROM pg_settings WHERE pg_settings.sourcefile LIKE '%.conf%';

and kind of see where the files should be stored. I wondered if there is a more direct way to get that information.

Maybe is not possible?

  • SELECT current_setting('data_directory'); – dezso Apr 12 '15 at 11:12
  • Thanks! It looks a bit silly that I was trying to do my long pg_settings.name query, and it was just that easy... – telex-wap Apr 12 '15 at 11:16
2

The config file and data directory location cannot be stored in the system catalogs, because the database cannot read the system catalogs until it knows where they are.

It's a bit like putting the map to your buried treasure into your treasure chest.

(Note that pg_settings is not technically a system catalog. It is a view that shows GUCs as configured by postgresql.conf / ALTER SYSTEM ... SET, by ALTER USER ... SET, by ALTER DATABASE ... SET, etc.)

  • Great example! It was helpful to help me finishing understanding – telex-wap Apr 12 '15 at 16:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.