Getting started with PostgreSQL on MAC

Getting started with PostgreSQL on MAC

Dec. 31, 2017
7 minutes read time
1 likes
0 comments
829 times viewed


header image

Description

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.

Content

Installation

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.

postgresql_install_kodefork.png

Start PostgreSQL Server

Top start PostgreSQL service just enter the below commnad

$ brew services start postgresql

start_postgres_kodefork.png

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

enter_psql_kodefork.png

To exit from interactive session just enter

\q

List Databases

To see all the databases that are already present you need to use \list or shortcut \l in interactive session.

\l

list_databases_psql_kodefork.png

When you install postgres by default 3 databases postgres, template0 and template1 will already be present.

Create Database

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;

create_psql_db_kodefork.png

and it will create testdb database, you can check by listing it using \l command.

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;

set_psql_db_user_kodefork.png

After all these you will have testdb created with all the privileges to testuser. Just check it using listing by \l.

final_database_list_psql_kodefork.png

Switch Database

By default you are connected to postgres database, You can switch to a new database using \connect or shortcut \c command

\c testdb

connect_psql_database_kodefork.png

Listing Tables

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.

\dt

tables_psql_db_kodefork.png

Create Table

Now we will create a person table with id, name, 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

tables_psql_kodefork.png

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.

record_psql_table_kodefork.png

Inserting Data in Table

You can insert record into person table using

INSERT INTO person(id, name, email) VALUES(1, 'astik anand', 'astikanand@gmail.com');

Now seeing the record it will show 1 row. You can insert as many record as you want.

insert_record_psql_table_kodefork.png

Backup Database

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

dump_psql_kodefork.png

and you will see that one backup file is created, you can keep it safely somewhere and can restore data from it in future.

Restore Database

To restore data you must have a backup file created by pg_dump.
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


We are done



Like this post

1   Like


Share this post on

Google Plus LinkedIn


About the author




Join the discussion

Nothing to preview

Post Comment



Comments