PostgerSQL Straight Installation and Usage for Users Coming From Mysql

"PostgerSQL straight installation and usage for users coming from Mysql"

PostgreSQL (pronounced ‘post-gres-Q-L’) is a free, open-source object-relational database management system (object-RDBMS), similar to MySQL. It is commonly used as a backend for web and mobile applications. This article will help you to install PostgreSQL database server on your Ubuntu machine and get you through some pretty straight commands without dragging your here and there.

Install PostgreSQL

Ubuntu’s default repositories contain Postgres packages, so we can install them without a hassle using the apt packaging system. First, ensuring the list of available packages is up to date before installing anything new. Postgresql installation is pretty straight forward in most of the popular linux distribution. apt and yum repository both are so easy to use. Because i am doing it on ubuntu, So i’m talking about apt.

1
2
$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib

Like other RDBMS, PostgreSQL comes with a default admin/root user called – “postgres”. This means the default or admin user of PostgreSQL called postgres when installed. It has no default password.

Connect to Postgresql

After installing PostgreSQL database server, by default it creates a user ‘postgres’ with role ‘postgres’. It also creates a system account with same name ‘postgres’. So to connect to postgres server, login to your system as user postgres and connect database as following:

1
$ sudo -i -u postgres

It will allow you to login and redirect you to your shell prompt for the postgres user. Now you can access the PostgreSQL prompt with the command:

1
$ psql

Now you are logged in to PostgreSQL database server. We can also change the password for postgres role by typing:

1
postgres=# \password postgres

To disconnect from PostgreSQL database command prompt just type below command and press enter. It will return you back to Ubuntu command prompt.

1
postgres=# \q

How to create password protected new user and database?

First of all create new user at OS level. My user is “sarha”. Type the following command to create a Linux user called sarha on ubuntu:

1
2
$ adduser sarha
$ passwd sarha

You need to login postreg(default user) which is root user of the databaes. Type following command to create a new user with password. And then create new database.

1
2
3
$ pgsql
postgres=# CREATE USER sarha WITH PASSWORD 'mypass';
postgres=# CREATE DATABASE sale;

Now grant required privileges to “sarha” user for “sale” database

1
2
postgres=# GRANT ALL PRIVILEGES ON DATABASE sale to sarha;
postgres=# \q 

To login as sarha user you need to type the following commands:

1
2
$  su - sarha
$ psql -d sale -U sarha

To check login info use following command from database command prompt:

1
sale=> \conninfo

And you are done. Now you can use your MySQL skills to create, update, delete, select queires.

Comments