🐘 PostgreSQL - Initialize Server
Updated at 2015-11-18 23:11
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.
/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();
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';