Where are the files for a PostgreSQL database stored?

12 Answers 12


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 * FROM pg_tablespace;
  • show data_directory; command points to exact location of data. Searching specific folder is painful as someone else might have installed it for you and now you do not know the configuration, so following sql helps to save the time. :) Thanks Mike. – Vishal Jun 14 '13 at 8:54
  • 3
    It says "must be superuser to examine data directory" :( – temporary_user_name Aug 19 '13 at 22:45
  • 5
    If you're not a DBA, you don't really need to know anyway. – Mike Sherrill 'Cat Recall' Aug 20 '13 at 10:15
  • 7
    BTW - if anyone is looking for the database location for Postgres.app on a mac like I was, it's in ~/Library/Application Support/Postgres[ver]/var by default. – sstringer Jan 11 '14 at 19:00
  • 1
    To run a query, use PGAdmin III and use the "run a query" icon in the menu bar. – Rutger Hofste Dec 8 '16 at 16:16

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.

  • 3
    Don't answer in an OS specific way unless the question explicitly states the OS. – simonmenke Oct 26 '12 at 9:54
  • 18
    Why not? If you don't mention the OS and just say "this works", anyone trying that on a different OS will be confused. It's relevant. EDIT: Oh, you probably mean "don't give an OS specific answer at all." I guess that makes sense, I don't know. – David Winiecki May 16 '13 at 22:26
  • 29
    @David, The question was closed presumably because it didn't specify which OS. (If it had specified an OS, it would have been closed for being too specific.) Your answer was helpful and informative - just ignore the naysayers and downvoters till they succeed in finally destroying SO. Haters gonna hate, and all that. – SamGoody Jul 9 '13 at 10:58
  • @SamGoody there'd be a bit of a point to what you were saying, if this answer was actually correct. (It's not strictly incorrect, as the folder might be there in Windows, but it certainly isn't a given). Whether it's the case or not can be easily found out by following the answer already given. – Jon Hanna May 13 '14 at 18:17

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


There should be your DB files.

  • 2
    How do I get the OID using the commandline tool? – Nick Retallack Jan 23 '14 at 19:12
  • 11
    SELECT oid from pg_database where datname = '<dbname>' – Charlie Nov 10 '14 at 16:28

Under my Linux installation, it's here: /var/lib/postgresql/8.x/

You can change it with initdb -D "c:/mydb/"

  • 9
    Depends on the distribution — for Fedora 20 it's under /var/lib/pgsql/data. Better to find out using ps auxw|grep postgres|grep -- -D. – Skippy le Grand Gourou Feb 26 '14 at 10:22

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

The location of specific tables/indexes can be adjusted by TABLESPACEs:

CREATE TABLE something (......) TABLESPACE dbspace;
CREATE TABLE otherthing (......) TABLESPACE dbspace;

Everyone already answered but just for the latest updates. If you want to know where all the configuration files reside then run this command in the shell


Postgres stores data in files in its data directory. Follow the steps below to go to a database and its files:

The database corresponding to a postgresql table file is a directory. The location of the entire data directory can be obtained by running SHOW data_directory. in a UNIX like OS (eg: Mac) /Library/PostgreSQL/9.4/data Go inside the base folder in the data directory which has all the database folders: /Library/PostgreSQL/9.4/data/base

Find the database folder name by running (Gives an integer. This is the database folder name):

SELECT oid from pg_database WHERE datname = <database_name>;

Find the table file name by running (Gives an integer. This is the file name):

SELECT relname, relfilenode FROM pg_class WHERE relname = <table_name>; 

This is a binary file. File details such as size and creation date time can be obtained as usual. For more info read this SO thread


On Mac: /Library/PostgreSQL/9.0/data/base

The directory can't be entered, but you can look at the content via: sudo du -hc data

  • 13
    If you installed via homebrew (and why wouldn't you?), it would be in /usr/local/var/postgres which you can discover using @MikeSherrill's show data_directory; tip – Chris Beck Apr 16 '14 at 0:40
  • /Library/PostgreSQL/9.0/data/base seems like a more Mac-like location than /usr/local/var/postgres. You can't browse to the latter in Finder without enabling hidden Finder features. – Charlie Nov 10 '14 at 16:20
  • @Charlie You can browse using Finder from the Go menu. Go > Go to Folder..., or ⇧⌘G – Jason S Mar 10 '15 at 5:45
  • @JasonS Yes, you could use that trick to open the not-mac-like folder in Finder. – Charlie Mar 10 '15 at 19:15
  • 1
    In my case it is in: /Users/bob/Library/Application Support/Postgres/var-9.5 – Bwyss Mar 18 '16 at 16:51

I'd bet you're asking this question because you've tried pg_ctl start and received the following error:

pg_ctl: no database directory specified and environment variable PGDATA unset

In other words, you're looking for the directory to put after -D in your pg_ctl start command.

In this case, the directory you're looking for contains these files.

PG_VERSION      pg_dynshmem     pg_multixact
pg_snapshots    pg_tblspc       postgresql.conf
base            pg_hba.conf     pg_notify   
pg_stat         pg_twophase     postmaster.opts
global          pg_ident.conf   pg_replslot
pg_stat_tmp     pg_xlog         postmaster.pid
pg_clog         pg_logical      pg_serial
pg_subtrans     postgresql.auto.conf    server.log

You can locate it by locating any of the files and directories above using the search provided with your OS.

For example in my case (a HomeBrew install on Mac OS X), these files are located in /usr/local/var/postgres. To start the server I type:

pg_ctl -D /usr/local/var/postgres -w start

... and it works.

  • 1
    If you use homebrew, an easier way to locate this data is simply brew info postgres – Ben Jan 5 '16 at 20:51

On Windows, the PGDATA directory that the PostgresSQL docs describe is at somewhere like C:\Program Files\PostgreSQL\8.1\data. The data for a particular database is under (for example) C:\Program Files\PostgreSQL\8.1\data\base\100929, where I guess 100929 is the database number.

  • 1
    Beware: If you want to do filesystem level backup, don't just back up these directories, because, as docs describe: "...you might be tempted to try to back up or restore only certain individual tables or databases from their respective files or directories. This will not work because the information contained in these files is not usable without the commit log files, pg_clog/*, which contain the commit status of all transactions." – Janis Veinbergs Jun 20 '13 at 13:58
  • This depends. You could have configured it to a different folder on install. – ANeves Nov 7 '14 at 14:21
  • on mine, there is no data folder inside C:\Program Files\PostgreSQL\9.4\ is this something specific to 9.4 or have done something wrong? – LucyViolet Jun 21 '17 at 3:25

picmate's answer is right. on windows the main DB folder location is (at least on my installation)


and not in program files.

his 2 scripts, will give you the exact directory/file(s) you need:

SELECT oid from pg_database WHERE datname = <database_name>;

mine is in datname 16393 and relfilenode 41603

database files in postgresql

protected by Quentin Mar 9 '12 at 7:20


Would you like to answer one of these unanswered questions instead?
