Sunday, April 02, 2023

PostgreSQL installation & connecting it to to Oracle SQL Developer/HeidiSQL

Install PostgreSQL DB, in my example here, installing it under Ubuntu by referring to below URL.

$ sudo apt update

$ sudo apt install postgresql postgresql-contrib

Now enable remote access to your PostgreSQL by editing 2 files under "/etc/postgresql/12/main/".

Note that "12" is the version number. Change it according to the installed version number.

Edit "postgresql.conf" file and add...

listen_addresses = '*'

Edit "pg_hba.conf" file and add...

host all all 0.0.0.0/0 md5

Now start your PostgreSQL DB using "systemsql" command.

$ sudo systemctl start postgresql

Before you can connect to the PostgreSQL , you need a user. Create PostgreSQL user "puser" with below command line.

$ sudo -u postgres createuser --interactive

And you also need a DB to start with. Create PostgreSQL DB named "pdbase" with below command line.

$ sudo -u postgres createdb pdbase

Create Linux user matching with the PostgreSQL user "puser".

$ sudo adduser puser

Switch user to "puser" and run "plsql" to login to the PostgreSQL DB.

$sudo -u puser psql -d pdbase

You should now get the "plsql" prompt as below indicating that you have successfully login to the your PostgreSQL DB.

pdbase=#

Use "\l" to list databases or use "\dt" to list tables, these is just to make sure the DB is running fine.

pdbase=# \l

pdbase=# \dt

Now alter password for "puser", This password will be used to connect through remote connection such as Oracle SQL Developer or HeidlSQL.

pdbase=# ALTER USER puser WITH PASSWORD 'mypass';

Please refer above URL to add JDBC driver to your SQL developer.

Fill up necessary info such as user/pass/host/port/db and hit Test/Connect.

Same goes to HeidiSQL, fill up necessary info such as user/pass/host/port/db and hit Open