How to install postgresql, run the server and connect using cli?

This article describes the installing and starting the postgresql in simple steps. This will also show you how to write the first query, so that after finishing the article you get all set to start using postgresql.

Installing postgresql

Graphical installation in any OS is relatively easier. You can download the appropriate setup from official site and clicking is enough. I am showing you steps to install from terminal.

in ubuntu/linux

It may ask for admin rights and you may have to run these with sudo.

in Mac

As mac doesn’t have apt-get, we can go with other package managers like homebrew and mackports. We are doing stuffs with homebrew here. If you don’t have homebrew already installed, you can run the following commands to install that.

After having homebrew in your mac, you can go ahead to install postgresql by running the following commands.

That’s it. The installation of postgresql is done now.

Running / Starting the database server

Before anyone uses the database, it’s necessary to start the server. As we’ve installed postgres in two different ways in linux and mac, similarly to start the server also we need to run different kind of commands in two different OSs.

Start in ubuntu/linux

That’s it. The installation command using apt-get already created a service named postgresql. You just need to start that and you are good to go.

Start in Mac

In mac it’s a combination of few commands.

It’s time login to database

Now, as your database is up and running, you can now start playing with the actual database queries. Here is how to get into the database.

in ubuntu/linux

The postgres in the line 1 is the user and in line 2 is the database name.

in Mac

postgres is the default user of postgresql database and it doesn’t have a password by default.

Create users and dbs

Once you are inside the database, you can create more databases and users to communicate with the database. Below are the commands.

If you are using ubuntu or linux please edit the file /etc/postgresql/9.X/main/pg_hba.conf to change the authentication from peer to md5. Change the line

and also

Change the password of user postgres

Create a database

Create a user

Assign access

Exit from existing connection

Now you can use the the below command to login to your database again by using the following command.

Once you are connected you can play easily with postgresql. There are few very good tools available, like Valentina Studio which gives you a nice user interface to get connected and do your stuffs with postgresql.

About This Author

Hello! I am Paul Shan, a JavaScript Expert, Full Stack and DevOps Engineer cum Consultant based out of Bengaluru, India.