Posgresql on VPS

Intro

In this article, we are going to set up a postgresqlDB on Ubuntu instance and connect it remotely using DBeaver.

Sign in via SSH

Let's create a new user first:

  >> ssh username@IPv4

Update and Install postgresql

In this step, we will be installing PostgreSqlDB along with the postgresql-contrib module that provides additional functionality.:

>> sudo apt-get Update
>> sudo apt-get install postgresql postgresql-contrib

And we will need to ensure PostgreSQL is running with systemctl

>> sudo systemctl start postgresql.service

And we will also need to register it with systemctl to launch PostgreSQL upon system boot-up

>> sudo systemctl enable postgresql.service

Let's check PostgreSQL status:

>> sudo systemctl status postgresql.service

And by now, PostgreSQL status should be active.

Configuration

Let's start by creating a new user:

>> su - postgres
>> createuser --interactive --pwprompt

and fill in the inputs accordingly to your perference.

After we've successfully created a user, we will also need to allow remote access:

verify your postgresql version:

>> psql --version

Use your version number to locate .conf path:

>> nano /etc/postgresql/12/main/postgresql.conf

and look for listen_addresses and set its value to * as the following:

Save and exit the file.

Next, we need to modify pg_hba.conf to also allow connections from everyone:

>> cd /etc/postgresql/12/main/
>> ls -al
>> sudo nano pg_hba.conf

and look for # IPv4 local connections: and change the port access from 127.0.0.1/32 to 0.0.0.0/0:

and we will also need to allow port 5432 through firewall:

>> sudo ufw allow 5432/tcp

Finally, we need to restart postgresql service to apply the changes we have made:

>> sudo systemctl restart postgresql

Connect to Postgresql via DBeaver

Use the new username and password that we've created above:

Change user Password

sudo su - postgres psql wcsadmin2y10vpSFBMMJIWpW44WjyY1yetFFWIDpR0lv6Qg0fFyPGA7ERUjDWmS ALTER USER wcsadmin WITH PASSWORD '2y10vpSFBMMJIWpW44WjyY1yetFFWIDpR0lv6Qg0fFyPGA7ERUjDWmS';

Ref => How to Install and Use PostgreSQL on Ubuntu 20.04.

Ref => Setting up a remote Postgres database server on Ubuntu 18.04

Go Back.