Configure PostgreSQL in ubuntu and connect with DataGrip
PostgreSQL is the open source relation database system. PostgreSQL has some advance features of database like
- User-defined types
- Table inheritance
- Sophisticated locking mechanism
- Foreign key referential integrity
- Views, rules,subquery
- Nested transactions (savepoints)
- Multi-version concurrency control (MVCC)
- Asynchronous replication
Here i will explain how to configure PostgreSQL on Ubuntu Server and connect with your local machine.
First of all you need a ubuntu server and connect your server using SSH. When you are connected with SSH use the following commands to install PostgreSQL on ubuntu.
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib
PostgreSQL is installed on your ubuntu sever. Currently your database user is postgres that has all database related privilege. Now you need to create a new user with password so that you can connect database using your app or any database connection tool like DataGrip.
To create a user in postgres you first need to create a user on your server with same name. So lets create a user in ubuntu server:
sudo adduser sheetal
I have created a user named sheetal.
Now i will create a new user and database named as sheetal. So first you need to switch your user to postgres.
sudo -i -u postgres
Now your current active user is postgres.
createuser sheetal -P
This will ask for password to be set for user. Please remember this password, this will require to connect your database.
Now create a database:
You have a user, password and a database. Now if you server port 5432 is now publicaly open then open it your server firwall and security setting. So that you can connect database publicaly.
Exit from postgres user now:
Now you need to update postgres configutaion to allow users to connect database from anywhere.
sudo vim /etc/postgresql/9.5/main/pg_hba.conf
host all all 0.0.0.0/0 md5
sudo vim /etc/postgresql/9.5/main/postgresql.conf
Uncomment the listen_addresses setting and set *:
Now restart your postgres service.
All is done on server side. Now open DataGrip on your local system and connect.
Now you are live with your database. Enjoy the successful connection.