Where are the PostgreSQL database files in the cluster directory?
On OSX, I recently installed PostgreSQL via Homebrew:
brew install postgresql
Then I created a new database cluster:
initdb /usr/local/var/postgres
I confirm that the 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 am creating a new database:
createdb mynewdb
I can see that he exists.
$ psql
<user>=# \l
mynewdb | <user> | UTF8 | en_US.UTF-8 | en_US.UTF-8
But I don't see any obvious changes in the cluster directory (for example, just checking the contents ls -lt
).
Where is the database written / saved in the cluster directory (or subdirectories)?
source to share
as @ahorsewithnoname says they are in the base directory (on some other platfotms data / database) the numeric name are OID
databases and the directory will have files with the name of the OID
table or index they belong to.
The databse value can be determined using this query. oid is like a hidden unique id column.
select oid,* from pg_catalog.pg_database;
tables and indexes can be found like this:
select * from pg_catalog.pg_class;
column relifilenode for base filename (thanks @jjames)
source to share