Database disk layout

In order to fully interpret the information returned by utilities such as pg_buffer_cache, and therefore use that information to adjust memory sizing, it's helpful to know a bit about how databases are stored on disk. On Unix-like systems, typically, the $PGDATA environment variable on your system will point to your database cluster and, underneath the base/ directory, actually contains the database tables.

If you're not sure where your database is located on disk, but if you can connect to the database, the location of this and other important files on the server is available from the pg_settings view. This particular detail is in the data_directory setting; here's an example of locating it, and a query showing the locations ...

Get PostgreSQL 10 High Performance now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.