PostgreSQL Setup

This setup gives you a functioning local PostgreSQL server that you can use for data warehousing and queries. Remember to use strong passwords for production environments and consider firewall rules if exposing the database to remote connections.

macOS

Using Homebrew (Recommended)

• Install Homebrew (if not already installed):
  

  /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
      

• Install PostgreSQL
  

  brew install postgresql@17
      

• Initialize the database
  

  initdb --locale=C -E UTF-8 $(brew --prefix)/var/postgres
      

• Start PostgreSQL service
  

  brew services start postgresql@17
      

• Verify installation
  

  psql postgres
      

Method 2: Using PostgreSQL.app

• Download PostgreSQL.app from https://postgresapp.com
• Move to your Applications folder and open it
• Click “Initialize” to create a new server
• Add to your PATH with:

echo 'export PATH="/Applications/Postgres.app/Contents/Versions/latest/bin:$PATH"' >> ~/.zshrc

Ubuntu Setup

• Update package lists

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl status postgresql
sudo systemctl start postgresql
sudo systemctl enable postgresql

Post-Installation Configuration (Both Systems)

• Creating a Database and User:

# Switch to the postgres user:
# On macOS
psql -U postgres
# On Ubuntu
sudo -u postgres psql

- Create a new database and user

# (psql interface)
CREATE DATABASE mydb;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

Configure Remote Access (Optional)

• Edit postgresql.conf to listen on all interfaces:

On macOS with Homebrew (vim or another plaintext editor)

vim $(brew --prefix)/var/postgres/postgresql.conf

On Ubuntu

sudo vim /etc/postgresql/14/main/postgresql.conf
# Find the line with listen_addresses and change it to
listen_addresses = '*'

• Edit pg_hba.conf to allow remote connections

# On macOS with Homebrew
vim $(brew --prefix)/var/postgres/pg_hba.conf
# On Ubuntu
sudo vim /etc/postgresql/14/main/pg_hba.conf
# Add the following line:
host    all             all             0.0.0.0/0               md5

Restart PostgreSQL:

# On macOS with Homebrew
brew services restart postgresql
# On Ubuntu
sudo systemctl restart postgresql

Connecting from Julia

using LibPQ
# Connect to the database
conn = LibPQ.Connection("host=localhost dbname=mydb user=myuser password=mypassword")

Execute a query

result = execute(conn, "SELECT * FROM mytable")

Process results

for row in result
  println(row)
end
close(conn)

Be sure always to close the connection with close(conn), otherwise performance will degrade.

Common Troubleshooting

• Connection refused errors: Check if PostgreSQL is running with

  ps aux | grep postgres

• Authentication failed: Ensure your pg_hba.conf is properly configured for the authentication method
• Permission denied: Check user privileges with

  \l

  in psql
• Port conflicts: If port 5432 is already in use, change PostgreSQL's port in postgresql.conf