PostGreSQL Setup

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

Method 2: Using PostgreSQL.app

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

Ubuntu Setup

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)

# 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)

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 = '*'

# 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