This note contains basics how to install PostgreSQL and intialize very basic server.

# install PostgreSQL and follow the instructions
brew install PostgreSQL
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

# connecting to maintenance database as the default superuser
psql postgres

CREATE ROLE woozer
WITH PASSWORD 'woozer'
     LOGIN
     NOSUPERUSER
     INHERIT
     CREATEDB
     NOCREATEROLE
     NOREPLICATION;

CREATE DATABASE woozerdb
WITH OWNER = woozer
     ENCODING = 'UTF8'
     LC_COLLATE = 'en_US.UTF-8'
     LC_CTYPE = 'en_US.UTF-8'
     CONNECTION LIMIT = -1;

GRANT ALL ON DATABASE woozerdb TO woozer;

The new database will have:

• 2 catalogs, information_schema and pg_catalog
• 1 extension, plpsql
• 1 schema, public
• All of these are owned by the default superuser, which is fine as your new user owns the database and can add stuff in, it just can't alter these 4 entities.
• Don't alter ownership of catalogs, the default are fine.

psql woozerdb

/c woozerdb -- change database
ALTER SCHEMA public OWNER TO woozer;
CREATE EXTENSION "uuid-ossp";
\q -- Disconnects the client

psql --username=woozer woozerdb

-- Test if the extension works.
SELECT uuid_generate_v4();

Time Zone

You will want to keep your timezone as UTC at all times.

-- If this shows something else than UTC, consider changing db server configs.
SHOW timezone;

vim /usr/local/var/postgres/postgresql.conf
# find and change following values
log_timezone = 'UTC'
timezone = 'UTC'
# restart db server
launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
# now SHOW timezone; should show UTC
# you can change timezone for a specific connection with SET timezone = 'UTC';

Sources

• Improving the Command-Line Postgres Experience
• PostgreSQL on the Command Line
• Postgres Guide
• PostgreSQL documentation