Postgresql Basics [DRAFT]
Created at 2016-06-06T00:12:25.000Z

Mac OS Specific Tricks

If you did brew install postgres, your postgresql server doesn't accept non-local connection by defaut. So, I changed config files as follows:

/usr/local/var/postgres/postgresql.conf

## before ## 

listen_addresses = 'localhost'


## after ##

listen_addresses = '*'

/usr/local/var/postgres/pg_hba.conf

## before ##

local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust

## after ##

local   all             all                                     trust
host    all             all             all                     trust

Then, restart postgresql with the below command:

$ pg_ctl restart -D /usr/local/var/postgres
  • create special user "postgres"
# create user postgres;
# alter user postgres password 'postgres';

Basic psql commands:

-- login --
$ psql -h localhost -U hiogawa postgres

-- turn off pager --
# \pset pager off

-- show users --
# select * from pg_user;

-- show databases --
# select * from pg_database;
# \l

-- switch databases --
# \connect dbname

-- show tables --
# \dt

-- show help --
# \h select
# \h drop user
-- admin --
$ sudo su - postgres
$ psql

References