Started a new project, where I need to be loading some patient data from a database dump. Below a short summary how to install PostgreSQL and deal with it.

Installing and creating databases on OSX

Easiest to install and launch with brew:

brew doctor && brew update
brew install postgresql
brew services start postgresql

💡 Likewise, use stop and restart to change service status.

⚠️ Installing postgresql comes with lots of helpers: initdb creates a database cluster, which is a collection of databases managed by one server, that is, your postgresql installation. Then there’s the self-explanatory createdb, and finally psql, which gives you lots of backslashed shorthands for manipulating your databases.

initdb checks the environment variable PGDATA to know where to create the directories that will store all the data of the databases. On OSX, it is /usr/local/var/postgres by default; and by removing this directory and running the following, you can set up a new cluster:

mv /usr/local/var/postgres /usr/local/var/postgres.bkp
export PGDATA=/usr/local/var/postgres
initdb --locale en_US.UTF-8
createdb benzaiten

💡 You can change the locale for the individual databases, but this will set the default template1 from which all databases are created to en_US.UTF-8.

To create more superusers, use psql and then CREATE ROLE test_role LOGIN SUPERUSER.

psql

psql -l lists available databases, psql -d database [-U user] connects to a database, and allows the running of SQL queries as well as some shorthand commands:

• \dt to list tables and \du for all users
• \i file to run SQL commands from a file
• \q to quit

🧹 rake db:schema:load test (to create schema.rb in test environment and database)

pgAdmin

To connect to pgAdmin, select add server and enter the default values: