III. Install PostgreSQL

I choose PostgreSQL as database engine for OpenSSO because it is the popular database. It is Open Source, Free, and many enterprises and startups use it.

1. Setup Repository

We should setup repository for PostgreSQL because the default PostgreSQL version from ubuntu is very old. We should update it, so we can use the latest PostgreSQL version.

a. Install Dependency

apt-get install gnupg -y

b. Create the file repository configuration:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

c. Import the repository signing key:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

d. Update the package lists:

apt update

Incase of error

output

Skipping acquire of configured file 'main/binary-i386/Packages' as repository 'http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease' doesn't support architecture 'i386'

You have to edit the sources.list

nano /etc/apt/sources.list.d/pgdg.list

Change to this

Opened file: /etc/apt/sources.list.d/pgdg.list

deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main

Then save it by press ctrl+x then y and enter.

---

2. Install PosgreSQL

In this guide, I'll use postgresql version 15.

apt install postgresql-15 -y

If you want the latest version

apt install postgresql -y

---

3. Configure PostgreSQL

a. Set Password

As default, PostgreSQL didn't set a password. So we have to set it now.

Switch to account postgres

sudo su - postgres

Set password for user database postgres

psql -c "ALTER USER postgres WITH PASSWORD 'newpassword'"

b. Allow Remote Connection

Edit the postgresql.conf

nano /etc/postgresql/15/main/postgresql.conf 

note

I use postgresql version 15, so the path is /etc/postgresql/15/

Search listen_addresses and add the following line.

Opened file: /etc/postgresql/15/main/postgresql.conf

#listen_addresses = 'localhost'
listen_addresses = '*'

Then save it by press ctrl+x then press y and enter.

Now edit the pg_hba.conf

nano /etc/postgresql/15/main/pg_hba.conf 

Opened file: /etc/postgresql/15/main/pg_hba.conf

# TYPE  DATABASE    USER    ADDRESS     METHOD
host    all         all     0.0.0.0/0   md5
host    all         all     ::0/0       md5

c. Check Version

psql -c "SELECT version();"

d. Restart PostgreSQL

Back to user root

su

If you can't switch user using above command, try with logout command

exit

Then as root user, now you can run this command

systemctl restart postgresql

e. Open Port PostgreSQL

ufw allow 5432/tcp

Congratulations

Now database PostgreSQL already run in your ubuntu server.

---

4. Completely Uninstall

If you has been failed to configure the PostgreSQL or just in case want to completely uninstall PostgreSQL.

a. Uninstall PostgreSQL

apt remove postgresql --purge

b. Remove Dependent Packages

Run the following command to list dependent packages.

dpkg -l | grep postgres

Remove the packages listed in output using the following command.

apt-get --purge remove package1 package2

c. Verify Uninstallation

You can verify that PostgreSQL is removed by running the following command.

whereis postgres
whereis postgresql