Installation

Environment: macOS Sierra 10.12.4.

Install this database with ternimal:

  1. Install with brew

    1
    brew install postgresql

  2. After brew installation, initialization of DB

    1
    initdb /usr/local/var/postgres

  3. Start server

    1
    pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Database and user creation

There is no users after installation, use command:

  1. Create your first database

    1
    createdb

  2. Login PostgreSQL console

    1
    psql

    Using \l could list all the information by name, owner, encoding, collate, Ctype and access privileges. \q for quit.

    After database creation, users are also needed to be created.

  3. Create postgres user

    1
    CREATE USER postgres WITH PASSWORD 'password';

  4. Delete default database

    1
    DROP DATABASE postgres;

  5. Create a database under user postgres

    1
    CREATE DATABASE postgres OWNER postgres;

  6. Grant all privileges for user postgres

    1
    GRANT ALL PRIVILEGES ON DATABASE postgres to postgres;

  7. Change attribute of postgres

    1
    ALTER ROLE postgres CREATEDB;

Now you can use postgres to login, and magege databases under this user.

Login console as a user

1
psql -U [user] -d [database] -h [host] -p [port]

Login by psql, in fact, it’s under default setting. 

1
2
3
4
user: current mac user
database: database with the same name of user
host: localhost
port: as default port 5432

Login user postgres 

1
psql -U postgres -d postgres

Common console commands

command meaning
\h help, e.g. \h select
\? list commands
\l list databases
\c [database_name] connect a database
\d list all the tables of current database
\d [database_name] list all the tables of a database
\u list all the users
\e open editor
\conninfo print information of current DB and connection
\password [user] change user psw
\q quit

Reference

[1] Mac安装PostgreSQL
[2] PostgreSQL 9.0.23 Documentation