Skip to main content

Setting up a remote Postgres database server on Ubuntu 18.04

Introduction

Postgres is a powerful relational database management system, it can handle large workloads from a single machine to that of a data center. It is highly scalable and widely popular. In this article, we will be learning how to set up a remote Postgres database server to use for your projects. This article setup will allow Postgres connection from any IP address and will not cover specific/authorized IP connection.

Prerequisites

  • Familiarity with the command line interface
  • An Ubuntu server, you can quickly provision one from DigitialOcean or any cloud provider
  • A lot of patience
  • Postgres installed on a local machine

Installing Postgres

In this step, you will be installing Postgres on your server. The first thing to do is SSH into your server by running:
ssh server_user@server_ip
Note:
server_user is your server user you would like to log in with
server_ip is the IP address of your server
Then input your relevant user password or SSH key password if any. Next, update your server packages and dependencies by running:
sudo apt-get update
When that is done, install Postgres by running:
sudo apt-get install postgresql postgresql-contrib
This will install Postgres along with its associated dependencies. When the process is complete, switch the user to postgres to 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:
postgres@logrocket:~$ psql
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help
postgres=#

Create user

In this step, you will be creating a new user that will be used to access your Postgres database remotely. To create a new user, exit the Postgres shell by executing:
\q
While still being logged in as postgres run the following command to create a new user:
createuser --interactive --pwprompt
A prompt will be shown to you asking you to input your desired user role, name, password, and if you want the user to be a superuser. Here is an example:
Enter name of role to add: cleopatra
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y
I named my user role cleopatra and I made my user a superuser. A superuser is a user that has all the privileges available on a Postgres instance. Next, we will be assigning cleopatra to a database. To do this, run the following command:
createdb -O cleopatra egypt
This command above will create a new database named egyptand assign cleopatra to be the database user.

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/10/main/postgresql.conf
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/10/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 5432through 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

Connect to Postgres remotely

In this step, you will be connecting to your server from an external machine. Connect to the remote Postgres database by running:
psql -h {server_ip} -d egypt -U cleopatra
Where {server_ip} is your server IP address, you will get a prompt to type your user password, if the credentials match you’ll be logged into the Postgres shell for cleopatra and database egypt.
login cli
Create a new table and name it pharaohs by executing the following in the Postgres shell:
create table pharaohs(name text);
create database
Next, add a record to the pharaohs table, you will be adding Tutankhamun as a string to the table by running the following in the Postgres shell:
insert into pharaohs (name) values ('Tutankhamun');
add record
Next, we will be accessing our database using a GUI (Graphical User Interface) tool like tablePlus which enables you to visualize data away from the command line interface to see if we can find the records we created. Open TablePlus and click on Create a newconnection.
Open TablePlus
Select Postgres from the dropdown
dropdown of database options
Input credentials
Note:
Postgres default port is 5432
Ignore the SSL regions (we won’t cover this topic in this post)
Input credentialsIf your credentials are correct, you will be shown a GUI panel to view your database records in which you will find the table created and the record we added to it.
View records
As shown in the image, we can see the pharaohs table we created earlier and the record we added to it. Our remote database is ready!

Conclusion

We have seen how to configure a Postgres database server for remote access. With this knowledge, you can set up a database server for your next project. In production, there are some security measures you will have to keep in mind. For example, only allowing the specified IP address and not allowing root access to your server, 

Comments

Post a Comment

Popular posts from this blog

4 Ways to Communicate Across Browser Tabs in Realtime

1. Local Storage Events You might have already used LocalStorage, which is accessible across Tabs within the same application origin. But do you know that it also supports events? You can use this feature to communicate across Browser Tabs, where other Tabs will receive the event once the storage is updated. For example, let’s say in one Tab, we execute the following JavaScript code. window.localStorage.setItem("loggedIn", "true"); The other Tabs which listen to the event will receive it, as shown below. window.addEventListener('storage', (event) => { if (event.storageArea != localStorage) return; if (event.key === 'loggedIn') { // Do something with event.newValue } }); 2. Broadcast Channel API The Broadcast Channel API allows communication between Tabs, Windows, Frames, Iframes, and  Web Workers . One Tab can create and post to a channel as follows. const channel = new BroadcastChannel('app-data'); channel.postMessage(data); And oth...
Post a Comment
Read more

Certbot SSL configuration in ubuntu

  Introduction Let’s Encrypt is a Certificate Authority (CA) that provides an easy way to obtain and install free  TLS/SSL certificates , thereby enabling encrypted HTTPS on web servers. It simplifies the process by providing a software client, Certbot, that attempts to automate most (if not all) of the required steps. Currently, the entire process of obtaining and installing a certificate is fully automated on both Apache and Nginx. In this tutorial, you will use Certbot to obtain a free SSL certificate for Apache on Ubuntu 18.04 and set up your certificate to renew automatically. This tutorial will use a separate Apache virtual host file instead of the default configuration file.  We recommend  creating new Apache virtual host files for each domain because it helps to avoid common mistakes and maintains the default files as a fallback configuration. Prerequisites To follow this tutorial, you will need: One Ubuntu 18.04 server set up by following this  initial ...
Post a Comment
Read more

Working with Node.js streams

  Introduction Streams are one of the major features that most Node.js applications rely on, especially when handling HTTP requests, reading/writing files, and making socket communications. Streams are very predictable since we can always expect data, error, and end events when using streams. This article will teach Node developers how to use streams to efficiently handle large amounts of data. This is a typical real-world challenge faced by Node developers when they have to deal with a large data source, and it may not be feasible to process this data all at once. This article will cover the following topics: Types of streams When to adopt Node.js streams Batching Composing streams in Node.js Transforming data with transform streams Piping streams Error handling Node.js streams Types of streams The following are four main types of streams in Node.js: Readable streams: The readable stream is responsible for reading data from a source file Writable streams: The writable stream is re...
Post a Comment
Read more