Setting up a remote Postgres database server on Ubuntu 18.04
Tags: postgres, ubuntu
Published: 2020-05-06 17:03:23

---
title: Setting up a remote Postgres database server on Ubuntu 18.04
published: true
date: 2020-04-10 16:00:22 UTC
tags: postgresql,ubuntu
canonical_url: https://blog.logrocket.com/setting-up-a-remote-postgres-database-server-on-ubuntu-18-04/
cover_image: https://dev-to-uploads.s3.amazonaws.com/i/s54ztlaemtq9tp4f4x58.png
---
**Written by [Michael Okoh](https://blog.logrocket.com/author/michaelokoh/)**✏️

## Introduction

[Postgres](https://www.postgresql.org) 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](https://digitialocean.com) or any cloud provider
- A lot of patience
- Postgres installed on a local machine

[![LogRocket Free Trial Banner](https://i0.wp.com/blog.logrocket.com/wp-content/uploads/2017/03/f760c-1gpjapknnuyhu8esa3z0jga.png?resize=1200%2C280&ssl=1)](https://logrocket.com/signup/)

## 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:

```jsx
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:

```jsx
sudo apt-get update
```

When that is done, install Postgres by running:

```jsx
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:

```jsx
su - postgres
```

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

```jsx
psql
```

You will be shown something similar to this:

```jsx
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:

```jsx
\q
```

While still being logged in as `postgres` run the following command to create a new user:

```jsx
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:

```jsx
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:

```jsx
createdb -O cleopatra egypt
```

This command above will create a new database named `egypt` and 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:

```jsx
nano /etc/postgresql/10/main/postgresql.conf
```

Look for this line in the file:

```jsx
#listen_addresses = 'localhost'
```

Uncomment, and change the value to `'*'`, this will allow Postgres connections from anyone.

```jsx
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:

```jsx
nano /etc/postgresql/10/main/pg_hba.conf
```

Modify this section:

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

To this:

```jsx
# 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:

```jsx
sudo ufw allow 5432/tcp
```

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

```jsx
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:

```jsx
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](https://i1.wp.com/blog.logrocket.com/wp-content/uploads/2020/04/login.png?resize=730%2C534&ssl=1)

Create a new table and name it `pharaohs` by executing the following in the Postgres shell:

```jsx
create table pharaohs(name text);
```

![create database](https://i0.wp.com/blog.logrocket.com/wp-content/uploads/2020/04/createtablepharaohs.png?resize=730%2C512&ssl=1)

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:

```jsx
insert into pharaohs (name) values ('Tutankhamun');
```

![add record](https://i0.wp.com/blog.logrocket.com/wp-content/uploads/2020/04/addrecord.png?resize=730%2C512&ssl=1)

Next, we will be accessing our database using a GUI (Graphical User Interface) tool like [tablePlus](https://tableplus.com) 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 new connection`.

![Open TablePlus](https://i0.wp.com/blog.logrocket.com/wp-content/uploads/2020/04/tableplus.png?resize=730%2C526&ssl=1)

Select Postgres from the dropdown

![dropdown of database options](https://i2.wp.com/blog.logrocket.com/wp-content/uploads/2020/04/dropdown.png?resize=730%2C526&ssl=1)

Input credentials

> Note:  
> Postgres default port is `5432`  
> Ignore the SSL regions (we won’t cover this topic in this post)

![Input credentials](https://i2.wp.com/blog.logrocket.com/wp-content/uploads/2020/04/Screenshot_2020-02-20_at_00.27.59_idup3u.png?resize=730%2C605&ssl=1)If 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](https://i0.wp.com/blog.logrocket.com/wp-content/uploads/2020/04/viewrecords.png?resize=730%2C468&ssl=1)

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, you can learn how to set this up in [this article](https://www.cyberciti.biz/tips/postgres-allow-remote-access-tcp-connection.html).

* * * 
## Plug: [LogRocket](https://logrocket.com/signup/), a DVR for web apps
 
![LogRocket Dashboard Free Trial Banner](https://i2.wp.com/blog.logrocket.com/wp-content/uploads/2017/03/1d0cd-1s_rmyo6nbrasp-xtvbaxfg.png?resize=1200%2C677&ssl=1)
 
[LogRocket](https://logrocket.com/signup/) is a frontend logging tool that lets you replay problems as if they happened in your own browser. Instead of guessing why errors happen, or asking users for screenshots and log dumps, LogRocket lets you replay the session to quickly understand what went wrong. It works perfectly with any app, regardless of framework, and has plugins to log additional context from Redux, Vuex, and @ngrx/store.
 
In addition to logging Redux actions and state, LogRocket records console logs, JavaScript errors, stacktraces, network requests/responses with headers + bodies, browser metadata, and custom logs. It also instruments the DOM to record the HTML and CSS on the page, recreating pixel-perfect videos of even the most complex single-page apps.
 
[Try it for free](https://logrocket.com/signup/).
* * *

The post [Setting up a remote Postgres database server on Ubuntu 18.04](https://blog.logrocket.com/setting-up-a-remote-postgres-database-server-on-ubuntu-18-04/) appeared first on [LogRocket Blog](https://blog.logrocket.com).