Where does PostgreSQL store the database

Question

Where are the files for a PostgreSQL database stored

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Open pgAdmin and go to Properties for specific database  Find OID and then open directory   lt POSTGRESQL DIRECTORY gt  data base  lt OID gt    There should be your DB files

User · Answer

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

User · Answer

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    lt database name gt   SELECT relname  relfilenode FROM pg class WHERE relname    lt table name gt      mine is in datname 16393 and relfilenode 41603

User · Answer

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    lt database name gt     Find the table file name by running  Gives an integer  This is the file name    SELECT relname  relfilenode FROM pg class WHERE relname    lt table name gt      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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Under my Linux installation  it s here   var lib postgresql 8 x   You can change it with initdb -D  c  mydb

User · Answer

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

User · Answer

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

[postgresql] Where does PostgreSQL store the database?

Examples related to postgresql