Basic postgresql commands

PostgreSQL (AKA postgres) is the one of the well-known relational database. It is pretty capable of doing everything such as storing JSON object, DB replication and so on. Beside that it is also open source software. In this post I demonstrate some basic commands of  postgresql for beginners.

The first thing you need to know is installation procedure of Postgres which is fairly easy in Ubuntu. You can install postgres by following the tutorial below link.

https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-12-04

Now here is the list of the some of the most useful Postgres commands,

Postgres version : psql –version

“;” : end of each sql command should add “;”

Enter to database : psql [dbname]

Quit from database : \q

Create database : createdb [mydb];

Drop database : dropdb [mydb];

Stop Postgres service :  service postgresql stop Or /etc/init.d/postgresql stop

Start Postgres service : service postgresql start Or /etc/init.d/postgresql start

Restart Postgres service : service postgresql restart Or /etc/init.d/postgresql restart

Reload Postgres service : service postgresql reload Or /etc/init.d/postgresql reload

Add a normal user to db : createuser [kasra]

Add a super user  to db : createuser -s [kasra]

Getting list of available databases : psql -l

Backup database : pg_dump -O dbName > dumpFileName.pg_dump

Populate database : psql -f dumpFileName dbName

Send your idea and information to kasra@madadipouya.com

Leave a Reply