58

I'm sure this has been answered but I cannot seem to find an answer.

I installed postgresql using Homebrew (brew install postgresql) which installed to /usr/local/Cellar/postgresql. Afterwords per the instructions I did this:

If this is your first install, create a database with:
    initdb /usr/local/var/postgres

As I understand it, initdb creates a new cluster of databases. So when I did the above is "postgres" the database name and the database itself or just all the necessary bits that all postgresql databases will need?

Where does the actual database end up living? I am new to postgresql and looking for a file or binary that is the database. So, where is the database, and when I create a new one where does it go?

Improve this question

8 Answers 8

Reset to default
52

initdb just sets up the directory structure and such that is needed to create new databases. To create a database, use createdb:

SYNOPSIS
createdb [ option... ] [ dbname ] [ description ]

DESCRIPTION
createdb creates a new PostgreSQL database.

Normally, the database user who executes this command becomes the owner of the new database. However a different owner can be specified via the -O option, if the executing user has appropriate privileges.

createdb is a wrapper around the SQL command CREATE DATABASE [create_database(7)]. There is no effective difference between creating databases via this utility and via other methods for accessing the server.

initdb is sort of like creating a new file system on a hard disk: first you create the file system (initdb), then you create a files and directories (createdb).

The actual database files will be under /usr/local/var/postgres after you create the database. So, just create a database and then see what's new or changed under /usr/local/var/postgres. There isn't a single "dbname.db" file or anything like that, each database is a collection of files with names that are only meaningful to the database server.

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

3 Comments

This is exactly what I wanted to know, and suspected that but I hate now actually knowing. Thank you!
Is there a way to store database on a different harddrive?
@DataGreed: There should be but I don't know off the top of my head. Try asking a question over on dba.stackexchange.com
42

Run the following query:

SHOW DATA_DIRECTORY;
Improve this answer

1 Comment

This works, but you should mention that this is a psql shell command. So you must open the shell from the Terminal (by entering psql and enter the password when prompted) before entering this command.
4

System: MAC OS X 10.9.5 Now (ver. 9.4) this is under the dir called /Library/PostgreSQL If you go there, open the folder named as the ver. of your PG and then go to the folder data you will find your DB. This is really a set of many-many-many different both binary and text files (most of those are named with long numbers like "92891" or so... Note, to open the data folder you have to open its properties (right mouse click and select "Get info") and allow you to read this folder. I wdn't suggest you to open the RW permissions if you aren't familiar with this as any smallest change can affect you whole DB which is would be sad..

Good luck!

Improve this answer

3 Comments

I'm using PostgreSQL 9.4.1 and there is no /Library/PostgreSQL in there.
I am sorry for your instance but it was not a reason to downvote for my answer as I can provide you with proofs. Maybe in your system it is located somewhere else...
If installed with homebrew, as OP indicated, then the location is not /Library...
4

Since there are three different libraries in Mac, developers are usually confused with the three scenarios. However, you can find out your PostgreSQL directory with one of the two methods on the Mac Computer.

1. Directory--Absolute Path

1). Scroll up your mouse to the left top of Mac Screen

2). Click the menu "Go"

3). Select the "Go to Folder..."

4). Input the path

/Users/username/Library/Application Support/Postgres

Please change "username" to your name such as "jack" listed as follows.

/Users/jack/Library/Application Support/Postgres

5). Choose the button "Go" to the directory of Postgres.

You will eventually find out Postgres.

2. Find the directory step by step

1). Scroll up your mouse to the left top of Mac Screen.

2). Click the menu "Go".

3). Select the button "Library"

4). Double click the file holder "Application Support".

5). Find out "Postgres" according to the alphabetic order.

Cheers,

Mike

Improve this answer

Comments

3

In my case (not homebrew) the data location is found in this file /Library/LaunchDaemons/com.edb.launchd.postgresql-9.3.plist

   <array>
            <string>/Users/me/bin/postgres/bin/postmaster</string>
            <string>-D/Users/me/data/postgres</string>
   </array>
Improve this answer

Comments

3

The current behavior of the different installers is documented at https://wiki.postgresql.org/wiki/Installers/Mac_OS_X#Known_Installers. Homebrew does not, as someone commented above, put the data under /Library.

Improve this answer

Comments

1

Using SHOW DATA_DIRECTORY;, the location is

/opt/homebrew/var/postgresql@14
Improve this answer

Comments

1

After installing postgresql@16 by brew:

brew install postgresql@16

it is located on:

/opt/homebrew/Cellar/postgresql@16/16.6

Add /bin into PATH:

~/.zshrc
export PATH="$PATH:/opt/homebrew/Cellar/postgresql@16/16.6/bin"

Apply changes:

source ~/.zshrc

Check psql command available:

% psql --version
psql (PostgreSQL) 16.6 (Homebrew)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.