Where are PostgreSQL database files in the cluster directory?

Question

On OSX, I recently installed PostgreSQL via Homebrew:

brew install postgresql

I then created a new database cluster:

initdb /usr/local/var/postgres

I confirm that postgresql server is running with the expected database cluster:

$ ps auxwww | grep postgres    
0:00.03 /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres

I create a new database:

createdb mynewdb

I see that it exists.

$ psql
<user>=# \l 
mynewdb | <user> | UTF8 | en_US.UTF-8 | en_US.UTF-8

But, I see no obvious changes to the cluster directory (e.g., just by checking contents ls -lt).

Where is the database written / stored in the cluster directory (or sub-directories)?

There should be a new directory in /usr/local/var/postgres/base. More details in the manual: postgresql.org/docs/current/static/storage-file-layout.html — user330315
– user330315, Commented Jan 10, 2015 at 23:18
Got it, thanks. Saw that directory, but wasn't obvious that it contained the database(s) because the sub-directories (databases) in it have integer names (e.g., 12292, 1, 16384, etc) rather than the names I assigned (correctly listed by \l in psql). Will check the docs further; I'm sure there's a reason for this. — lmart999
– lmart999, Commented Jan 11, 2015 at 0:04
If it used the names, it would cause problems for databases named in character sets supported by PostgreSQL but not supported by the file system. Also, it would cause problems around transactionality (although more for tables than for databases) — jjanes
– jjanes, Commented Jan 12, 2015 at 20:41

Jasen · Accepted Answer · 2015-01-13 10:22:31Z

1

as @ahorsewithnoname says they are in the base directory, (on some other platfotms data/base) the numeric name is the OID of the database and in the directory will be files named for the OID of the table or index they relate to.

databse oids can be determined with this query. oid is like a hidden unique id column.

select oid,* from pg_catalog.pg_database;

table and index oids can be found like this,

select * from pg_catalog.pg_class;

column relifilenode for the base of the filename (thanks @jjames)

edited Jan 13, 2015 at 10:22

answered Jan 11, 2015 at 0:44

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jjanes Over a year ago

For pg_class, it is really the relfilenode column you want to look at. They are often the same, but when they are not it is the relfilenode which is correct.

Collectives™ on Stack Overflow

Where are PostgreSQL database files in the cluster directory?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related