Where does PostgreSQL store the database?

Where are the files for a PostgreSQL database stored?

Solution 1:

To see where the data directory is, use this query.

show data_directory;

To see all the run-time parameters, use

show all;

You can create tablespaces to store database objects in other parts of the filesystem. To see tablespaces, which might not be in that data directory, use this query.

SELECT *, pg_tablespace_location(oid) FROM pg_tablespace;

Solution 2:

On Windows7 all the databases are referred by a number in the file named pg_database under C:\Program Files (x86)\PostgreSQL\8.2\data\global. Then you should search for the folder name by that number under C:\Program Files (x86)\PostgreSQL\8.2\data\base. That is the content of the database.

Solution 3:

As suggested in "PostgreSQL database default location on Linux", under Linux you can find out using the following command:

ps aux | grep postgres | grep -- -D

Solution 4:

Open pgAdmin and go to Properties for specific database. Find OID and then open directory

<POSTGRESQL_DIRECTORY>/data/base/<OID>

There should be your DB files.