Basic Operations
By Prashant Raghava
How to Connect to PostgreSQL
#sudo -i -u postgres
#sudo -u postgres psql
If you are connecting on a non default port then connect as follows:
#psql -p <port> -d <database name> -U <user name>
How to Create User and Database
postgres# CREATE ROLE <Name> LOGIN PASSWORD ‘<password>’;
postgres# CREATE DATABASE <DB name> OWNER <user name> ENCODING ‘UTF8’;
postgres# grant all privileges on database <DB name> to <User Name>;
postgres# alter user <user name> with superuser;
Example:
postgres#create user talend_user with password ‘talend@123’;
postgres#alter user talend_user with superuser;
postgres#create database talend_db OWNER talend_user;
Command Commands:
postgres#\q —Exit
postgres#\z to show all tables
postgres#\l —display db
postgres#\du —display users / role
How to Drop Database
postgres=# DROP DATABASE <DB Name>;
How to change DB password
If db port is changed then
$sudo su postgres
bash-4.2$ psql -p 1525
postgres=# ALTER USER “user_name” WITH PASSWORD ‘new_password’;
If DB port is default
$sudo -u user_name psql db_name
ALTER USER “user_name” WITH PASSWORD ‘new_password’;