PostgreSQl is most widely used SQL database. This article details about the installation and basic commands to get started with PostgreSQL database. It also discusses about dumping the database for safe keeping and restoring it.
Before starting anything make sure that you have Homebrew installed on your system. If not installed already, enter the below command on your terminal else proceed.
$ /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
Now install postgresql by below command
$ brew install postgresql
You will get screen like once it gets successfully installed.
Start PostgreSQL Server
Top start PostgreSQL service just enter the below commnad
$ brew services start postgresql
Alternatively to stop the service
$ brew services stop postgresql
Interactive Session PSQL
To perform any postgres operation you need to enter the interactive session of psql.
Enter using below command
$ psql postgres
To exit from interactive session just enter
To see all the databases that are already present you need to use
\list or shortcut
\l in interactive session.
When you install postgres by default 3 databases
template1 will already be present.
To create new database just use below command in interactive session, here we are creating
testdb database for example, you can give your own custom name.
CREATE DATABASE testdb;
and it will create
testdb database, you can check by listing it using
Alternatively, if you want you can Drop database using
DROP DATABASE testdb;
Create User for Database
You need to create a user to handle this database, the user should have all the privileges on this database.
Here we are going to create
testuser with password
password, you can create your own custom user with own password. Then set some settings changes.
CREATE USER testuser WITH PASSWORD 'password';
ALTER ROLE testuser SET client_encoding TO 'utf8';
ALTER ROLE testuser SET default_transaction_isolation TO 'read committed';
ALTER ROLE testuser SET timezone TO 'Asia/Kolkata';
GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;
After all these you will have
testdb created with all the privileges to
testuser. Just check it using listing by
By default you are connected to
postgres database, You can switch to a new database using
\connect or shortcut
Once you have selected the database, you can view all its tables using
\dt command. For now there is no table so it will not show any.
Now we will create a
person table with
email and id set as PRIMARY KEY.
CREATE TABLE person (id INTEGER PRIMARY KEY, name VARCHAR, email VARCHAR);
Once created it will show in tabular form as below
Alternatively, if you want you can drop table using
DROP TABLE person;
Fetching Records from Table
We can see all the records inside a table using
SELECT * FROM person;
As for now there is no records so it will show 0 rows.
Inserting Data in Table
You can insert record into
person table using
INSERT INTO person(id, name, email) VALUES(1, 'astik anand', 'firstname.lastname@example.org');
Now seeing the record it will show 1 row. You can insert as many record as you want.
Many times for safety reasons you need to take the backup of database and keep the file safe.
You can do it using
pg_dump dbname > outputfile command.
Just exit from interactive session and then enter below command.
$ pg_dump testdb > backup
and you will see that one backup file is created, you can keep it safely somewhere and can restore data from it in future.
To restore data you must have a backup file created by
Just create a new database(for example:- newtestdb) in which you want to restore the backup file and then use
psql dbname < backupfile.
$ psql newtestdb < backup
Like this post
Share this post on
About the author
- Career over Relationships. Family above everything else.
- Passionate Geek with love for Computer Science, Artificial Intelligence.
- Avid interest in Economics, Sports and Adventures.
- Curious to know outside the boundary limits, coz what I know is very little and what I don’t know is huge.
Join the discussion