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.
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.
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:
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:
Now you are logged in to PostgreSQL database server. We can also change the password for postgres role by typing:
To disconnect from PostgreSQL database command prompt just type below command and press enter. It will return you back to Ubuntu command prompt.
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:
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
Now grant required privileges to “sarha” user for “sale” database
To login as sarha user you need to type the following commands:
To check login info use following command from database command prompt:
And you are done. Now you can use your MySQL skills to create, update, delete, select queires.