Where does PostgreSQL store the database?

Question

Where are the files for a PostgreSQL database stored?

Habergeon

postgresql.org/docs/9.5/static/storage-file-layout.html – Doradorado

sudo -u postgres psql -c "show data_directory;" will show the current storage locations on a standard PostgreSQL installation. – Eighteenmo

psql -U postgres -c "show data_directory;" worked for me – Jaal

Answer 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;

Baecher

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. – Johnsten

It says "must be superuser to examine data directory" :( – Fontes

If you're not a DBA, you don't really need to know anyway. – Oud

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. – Alfons

To run a query, use PGAdmin III and use the "run a query" icon in the menu bar. – Amaranthine

And to get it directly at the command-line: psql -U postgres -tA -c "SHOW data_directory;" – Raisin

Answer 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.

Aphoristic

@Aphoristic I have postgresql 11 and windows 10. As you mentioned, I tried to see the pg_database inside global folder but I could not see any. I could see was bunch of _vms ,_fsm, config_exec_params, pg_control, pg_filenode.map, pg_internal.init. For windows 10 which file named should I search. Is it different? – Marinetti

Answer 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

Drynurse

simple and no need to authenticate to a db! – Rollet

Answer 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.

Tamanaha

SELECT oid from pg_database where datname = '<dbname>' – Publishing

Answer 5

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

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

Tenuis

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. – Drynurse

(up to F34 it's still in the same directory, btw) – Cirrocumulus

Answer 6

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

SELECT name, setting FROM pg_settings WHERE category = 'File Locations';

Gebhart

You are a life saver! I've been searching for this for days. – Straightaway

Answer 7

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

Snowstorm

Answer 8

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.

Tamayo

If you use homebrew, an easier way to locate this data is simply brew info postgres – Symbol

You are right about the reason I am looking for the database folder. But the thing is I cant find any of the above files with locate <filename> – Metrics

Found them...Had to use sudo locate <filename> – Metrics

Answer 9

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

CREATE TABLESPACE dbspace LOCATION '/data/dbs';
CREATE TABLE something (......) TABLESPACE dbspace;
CREATE TABLE otherthing (......) TABLESPACE dbspace;

Quadrinomial

Answer 10

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

Collimator

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 – Verify

/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. – Publishing

@Publishing You can browse using Finder from the Go menu. Go > Go to Folder..., or ⇧⌘G – Darlenadarlene

@JasonS Yes, you could use that trick to open the not-mac-like folder in Finder. – Publishing

FYI, this Answer is still correct as of Postgres 9.4 using the installer provided by EnterpriseDB. – Curvet

In my case it is in: /Users/bob/Library/Application Support/Postgres/var-9.5 – Florettaflorette

Answer 11

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

C:\PostgreSQL\9.2\data\base\

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>;
SELECT relname, relfilenode FROM pg_class WHERE relname = <table_name>; 

mine is in datname 16393 and relfilenode 41603

Welcy

Should have the most votes^^ – Underlay

Answer 12

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.

Counterglow

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." – Senior

This depends. You could have configured it to a different folder on install. – Reaves

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? – Olvera

Answer 13

I'm running postgres (9.5) in a docker container (on CentOS, as it happens), and as Skippy le Grand Gourou mentions in a comment above, the files are located in /var/lib/postgresql/data/.

$ docker exec -it my-postgres-db-container bash
root@c7d61efe2a5d:/# cd /var/lib/postgresql/data/
root@c7d61efe2a5d:/var/lib/postgresql/data# ls -lh
total 56K
drwx------. 7 postgres postgres   71 Apr  5  2018 base
drwx------. 2 postgres postgres 4.0K Nov  2 02:42 global
drwx------. 2 postgres postgres   18 Dec 27  2017 pg_clog
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_commit_ts
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_dynshmem
-rw-------. 1 postgres postgres 4.4K Dec 27  2017 pg_hba.conf
-rw-------. 1 postgres postgres 1.6K Dec 27  2017 pg_ident.conf
drwx------. 4 postgres postgres   39 Dec 27  2017 pg_logical
drwx------. 4 postgres postgres   36 Dec 27  2017 pg_multixact
drwx------. 2 postgres postgres   18 Nov  2 02:42 pg_notify
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_replslot
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_serial
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_snapshots
drwx------. 2 postgres postgres    6 Sep 16 21:15 pg_stat
drwx------. 2 postgres postgres   63 Nov  8 02:41 pg_stat_tmp
drwx------. 2 postgres postgres   18 Oct 24  2018 pg_subtrans
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_tblspc
drwx------. 2 postgres postgres    6 Dec 27  2017 pg_twophase
-rw-------. 1 postgres postgres    4 Dec 27  2017 PG_VERSION
drwx------. 3 postgres postgres   92 Dec 20  2018 pg_xlog
-rw-------. 1 postgres postgres   88 Dec 27  2017 postgresql.auto.conf
-rw-------. 1 postgres postgres  21K Dec 27  2017 postgresql.conf
-rw-------. 1 postgres postgres   37 Nov  2 02:42 postmaster.opts
-rw-------. 1 postgres postgres   85 Nov  2 02:42 postmaster.pid

Painstaking

Answer 14

A single Terminal command: pg_lsclusters, (using Ubuntu)

What you need is under Data directory:

Ver Cluster Port Status Owner    Data directory               Log file
10  main    5432 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log
11  main    5433 online postgres /var/lib/postgresql/11/main  /var/log/postgresql/postgresql-11-main.log

Connor