PostgreSQL knowledge base

PostgreSQL knowledge base.txt

Install PostgreSQL:
Install PostgreSQL 9.6 via homebrew.


Before installation:
Uninstall PostgreSQL if you have installed it by homebrew with command: brew uninstall postgres.
Delete the database cluster, usually locates on /usr/local/var/postgres, or /usr/local/pgsql/data. 
The command is: rm -rf /usr/local/var/postgres, or rm -rf /usr/local/pgsql/data.


Installation with homebrew:
Install postgres: brew install postgres.
Start server: pg_ctl -D /usr/local/var/postgres start


Architecture Fundamentals:
A server process. The server program is called postgres.
A user's client (frontend) application.


Creating a Database:
createdb mydatabase.


Dropping a Database:
dropdb mydatabase.


Accessing a Database:
psql mydatabase.

Get the help of psql while in the psql: \h.
Quit the psql application: \q.

Start PostgreSQL server:
pg_ctl -D /usr/local/var/postgres start

Check the connection status of a PostgreSQL server:
qg_isready

Check whether the server is up and running:
pg_ctl status

configuration on shell
export PGDATA="/usr/local/var/postgres"
export PGHOST=localhost
alias start_pg="pg_ctl -l $PGDATA/server.log start"
alias stop_pg="pg_ctl stop -m fast"
alias show_pg_status="pg_ctl status"
alias restart_pg="pg_ctl reload"

Below is the output information during installation:
==> Caveats
If builds of PostgreSQL 9 are failing and you have version 8.x installed,
you may need to remove the previous version first. See:
  https://github.com/Homebrew/homebrew/issues/2510

To migrate existing data from a previous major version (pre-9.0) of PostgreSQL, see:
  https://www.postgresql.org/docs/9.6/static/upgrading.html

To migrate existing data from a previous minor version (9.0-9.5) of PostgreSQL, see:
  https://www.postgresql.org/docs/9.6/static/pgupgrade.html

  You will need your previous PostgreSQL installation from brew to perform `pg_upgrade`.
  Do not run `brew cleanup postgresql` until you have performed the migration.

To have launchd start postgresql now and restart at login:
  brew services start postgresql
Or, if you don't want/need a background service you can just run:
  pg_ctl -D /usr/local/var/postgres start