PostgreSQL Cheatsheet

written by Sudheer Satyanarayana on 2016-07-25

Install PostgreSQL server

yum install postgresql-server

New server setup

postgresql-setup initdb

Upgrading from an older version

postgresql-setup --upgrade

Allow password based login

Edit /var/lib/pgsql/data/pg_hba.conf

  host    all     all     127.0.0.1/32    md5

Create user

 CREATE USER username login PASSWORD 'password';

Create database

 CREATE DATABASE databasename WITH OWNER = username;

Generate CREATE TABLE statement for a particular table

pg_dump -U username -h 127.0.0.1 dbname -t tablename --schema-only