Postgresql setup

pg_hba.conf

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

psql.md

PSQL -- PostgreSQL interactive terminal

sudo -u postgres psql -> postgres=#

CREATE DATABASE test_database;

CREATE USER test_user WITH password 'qwerty';

GRANT ALL privileges ON DATABASE test_database TO test_user;

ALTER USER test_user CREATEDB;

DROP ROLE test_user;

psql -h localhost test_database test_user

PSQL Commands

\?, \h - available psql commands

\d users, \du, \dg - List of roles - description of the 'users' table

\d, \dt - all tables

\list - all DBs

Create, delete & alter users

• rails new myapp --database=postgresql
• sudo -u postgres psql
• create role alabama_song with createdb login password '1';
• sudo gvim /etc/postgresql/X.Y/main/pg_hba.conf - view config file
• sudo service postgresql restart

View indexes in DB

log in with psql sudo -u postgres psql

connect to DB \connect wiztr-dev

list indexes \di

list relations \d - tables, sequences, ...