PostgreSQL Quick Reference

Creating new user.

CREATE USER davide WITH PASSWORD 'jw8s0F4';  

further details here

Change user password

ALTER USER admin WITH PASSWORD 'admin';  

futher details here

check the list of users:

select u.usename,  
       (select string_agg(d.datname, ',' order by d.datname) 
        from pg_database d 
        where has_database_privilege(u.usename, d.datname, 'CONNECT')) as allowed_databases
from pg_user u  
order by u.usename  
Alter table and add column
ALTER TABLE public.nseequitydata 
ADD COLUMN splitfactor bigint NOT NULL DEFAULT 1;

Login to postgreSQL

sudo su - postgres
[sudo] password for conquistador: 
postgres@inspiron-3542:~$ psql
postgres=#

Important commands

Activity Command
Show all users postgres=# \du
Show all databases postgres=# \l
Connect to database postgres=# \connect equityindia
Show tables equityindia=# \dt

Taking back up of PostgreSQL db and loading back up file

I am working with huge data set and its growing everyday. Fear of loosing data which you have gathered after so much efforts can give you sleepless nights. One of the way to tack care of this risk is by taking backup frequently, whenever you need reload the data, you can simply reload it.

Creating backup file
pg_dump dbname > outfile  
Loading from back up file
psql dbname < infile  

please note that dbname needs to be created prior to loading this backup file and it will not only create database in the same format but also it will load data.

Dealing with huge datasets

If your are dealing with huge databses you can takke dump in zip file

pg_dump dbname | gzip > filename.gz  

and load it as below

gunzip -c filename.gz | psql dbname  

or

cat filename.gz | gunzip | psql dbname  

alternatively you can split files if you are not comfortable with zip or have limitation of file size.

pg_dump dbname | split -b 1m - filename  

load with

cat filename* | psql dbname  

How to install pgAdmin on Ubuntu 16.04

pgadmin3 can be installed using following command

sudo apt-get install pgadmin3

You can invoke pgadmin3 GUI from main menu as well as by typing following command.

$ pgadmin3

From file menu, please use ‘add server’ and enter data as below:

If you facing authentication issue, please use following commands to reset the password.

$sudo -u postgres psql postgres
psql (9.6.3)  
Type "help" for help.

postgres=# password postgres  
Enter new password:  
Enter it again: