PostgreSQL install and usage examples

postgresql-usage-examples

* install
# yum install postgresql-server postgresql-contrib
# postgresql-setup initdb

* allow passowrd authentication & remote access
# vi /var/lib/pgsql/data/pg_hba.conf
host    all             all             127.0.0.1/32            ident
host    all             all             ::1/128                 ident
->
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

* allow remote access
# vi /var/lib/pgsql/data/postgresql.conf
listen_addresses='localhost' 
-> 
listen_addresses='*'

* enable and start service 
systemctl start postgresql
systemctl enable postgresql

* usage
su - postgres
psql
CREATE USER dbuser WITH PASSWORD 'password';
CREATE DATABASE exampledb OWNER dbuser;
GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;
\q

psql exampledb 
psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432 
\password postgres

postgres shell usages
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

\l             list databases
\c db_name     connect to db_name
\d             list current database tables
\d table_name  describe tables 
\du            list all users
\conninfo      list current database and connection information