How to setup PostgreSQL on Ubuntu Server

This article will help readers to set up PostgreSQL on Ubuntu Server and be able to connect remotely.

Instructions

Before start reading this article I assume you are familiar with basic Linux commands and putty is installed on your pc.

• Open putty and connect with your remote server using your credentials.
• After successful login you will be shown something similar to this:

Ubuntu Login Screen

It’s including your OS version (here Ubuntu 21.04) and system information. If you see something like to updates (yellow marked line in the image) you have to run a command to update these packages.
Run sudo apt dist-upgrade -y and hit enter. If face any error message on the screen run sudo apt-get update and then try again.

• After a successful update process system may require a reboot. Use sudo reboot to reboot the server. [Note: You will lose your control after the reboot command, so connect again]
• Now it’s time to install PostgreSQL. use the following commands to install Postgres.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql

• This will install Postgres along with its associated dependencies. When the process is complete, switch the user to postgres be able to execute Postgres commands with Postgres default user by running:

su - postgres

• The server user will be switched from root to postgres. You can access the Postgres shell by running:

psql

• You will be shown something similar to this:

root@vultr:/# su - postgres
postgres@vultr:~$ psql
psql (13.4 (Ubuntu 13.4-1.pgdg21.04+1))
Type "help" for help.

• Now you have to change the default password for postgres user. To change the password run the following commands

\password postgres

• You will be prompted to type a new password. Repeat this for the owner and postgres user, giving each a strong, unique password.
  [Note: here postgres is the default superuser. You can also create a new user if required]
• Now exit the Postgres shell by executing:

\q

• Next, we need to assign postgres user to a database. To do this, run the following command:

createdb -O postgres LmsDb

• To check your latest created DB run:

#assume you are still logged on postgres shell
psql
\l

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges
— — — — — -+ — — — — — + — — — — — + — — — — — — -+ — — — — — — -+ — — — — — — — — — — — -
LmsDb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres

Allow remote access

• In this step, we will look at how to configure Postgres to accept external connections. To begin, open the configuration file with your preferred editor:

nano /etc/postgresql/13/main/postgresql.conf

Here 13 is the PostgreSQL version.

• Look for this line in the file: #listen_addresses = 'localhost'
• Uncomment, and change the value to '*', this will allow Postgres connections from anyone. listen_addresses = '*'
• Save and exit the file. Next, modify pg_hba.conf to also allow connections from everyone. Open the file with your preferred editor:

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

• Modify this section:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

To this

# IPv4 local connections:
host    all             all             0.0.0.0/0            md5

• This file stores the client authentication, each record specifies an IP address range, database name, username, and authentication method. In our case, we are granting all database users access to all databases with any IP address range, thus, letting any IP address connect. Save and exit the file. Next, allow port 5432 through the firewall by executing:

sudo ufw allow 5432/tcp

• Finally, restart Postgres to apply all the changes you have made to its configuration by running:

sudo systemctl restart postgresql

One more step left. We have to enable Firewall for remote login. To check your Firewall status run:

sudo ufw status

• If Status: inactive then you have to enable it by running:

sudo ufw enable

• Then allow some ports:

#allow ssh port
sudo ufw allow 22
#allow postgres port
sudo ufw allow 5432

• Finally allow ssh

sudo ufw allow ssh

• To see the allowed app and port, run:

sudo ufw status verbose

• The output will be something like this:

Status: active
Logging: on (low)
Default: deny (incoming), allow (outgoing), disabled (routed)
New profiles: skip
To                         Action      From
--                         ------      ----
5432/tcp                   ALLOW IN    Anywhere
22                         ALLOW IN    Anywhere
5432/tcp (v6)              ALLOW IN    Anywhere (v6)
22 (v6)                    ALLOW IN    Anywhere (v6)

Now try to connect your remote PostgreSQL server from anywhere.