How to Install PostgreSQL on an Ubuntu VPS Running Nginx

How to Install PostgreSQL on an Ubuntu VPS Running Nginx

Brief description

Having a remote database system with a client library, one may not want to expose the database directly to the internet but instead, use a web server like Nginx to handle client communication with the database.

In this article, step by step instructions on how to configure Nginx to connect to PostgreSQL database directly is covered.

Special note: consult HostAdvice’s  Best PostgreSQL hosting  page to find the leading web hosts in this category, including expert and user reviews.

Overview

Nginx is a very popular web server currently. It boasts the ability to host large and high traffic sites. Besides being cheap, it can also serve multi purposes such as being used as a web server or a reverse proxy. Using PostgreSQL with Nginx as the web server provides an excellent way of protecting the database from online predators as it is not directly exposed to the internet.

Configuring nginx to interact with PostgreSQL

Install PostgreSQL

Get new PostgreSQL packages using apt-get command and Install, include the additional –contrib package that provides us with some additional functionality.

$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib

That is it. We can now go over how it works.

Using PostgreSQL

PostgreSQL uses “roles” to handle authentication and authorisation. PostgreSQL uses ident authentication. It means that if a “role” exists in Postgres, a Linux username with the same name as the role will be able to sign in to that role.

Switching to the Postgres account

During our installation, a default user account called postgres is created. For us to use Postgres, we have to log in that account. To switch over to the postgres account type the following;

$ sudo -i  -u postgres

Access the Postgres prompt by typing;

$  psql

PostgreSQL is now available

Create a database called Mytest with the below command;

postgres=# CREATE DATABASE mytest;

Create a user named “wambui” with a password “mypass” with the below command

CREATE USER wambui WITH PASSWORD 'mypass';

Grant wambui, created above access to the new database mytest with this command

GRANT ALL PRIVILEGES ON DATABASE mytest TO wambui;

Install and configure Nginx.

By default, Apache is the web server. However, we want to configure PostgreSQL with Nginx as the web server. Therefore, we shall install Nginx.

sudo apt-get install nginx

It automatically starts Nginx as the web server. You also start the service manually with this command;

sudo service nginx start

ngx_postgres

For Nginx to communicate with PostgreSQL additional modules are required. To install the modules run the below command assuming the installation path is /opt/nginx \

./configure --prefix=/opt/nginx \

           --add-module=/path/to/ngx_postgres

           --add-module=/path/to/rds-json-nginx-module

           --add-module=/path/to/form-input-nginx-module

           --add-module=/path/to/ngx_devel_kit

           make -j2

           make install

Direct communication to PostgreSQL is through ngx_postgres which is an upstream module. Response is in rds and is compatible with ngx_rds_json and ngx_drizzle.

The sample configuration in this tutorial returns contents of the table employees in rds format.

http {
   upstream database {
       postgres_server  127.0.0.1 dbname=mytest
                        user=wambui password=mypass;
   }
   server {
       location / {
           postgres_pass   database;
           postgres_query  "select * from employees";
       }
   }
}

Conclusion

Now that we have installed the PostgreSQL database, we can now combine two of the internet’s most powerful tools, PostgreSQL and Nginx. A combination of this two technologies enhances the database and the web security.

 

Check out the top 3 Linux hosting services

FastComet
$1.79 /mo
Starting price
Visit FastComet
Rating based on expert review
  • User Friendly
    4.7
  • Support
    5.0
  • Features
    4.8
  • Reliability
    4.5
  • Pricing
    5.0
Kamatera
$4.00 /mo
Starting price
Visit Kamatera
Rating based on expert review
  • User Friendly
    3.5
  • Support
    3.0
  • Features
    3.9
  • Reliability
    4.0
  • Pricing
    4.3
HostArmada
$2.49 /mo
Starting price
Visit HostArmada
Rating based on expert review
  • User Friendly
    4.5
  • Support
    4.5
  • Features
    4.5
  • Reliability
    4.5
  • Pricing
    4.0
  • Click this link and all your queries to best hosting will end.

How to Install PostgreSQL on Windows Servers

This article shows you how to install PostgreSQL on a Windows Server.
2 min read
Max Ostryzhko
Max Ostryzhko
Senior Web Developer, HostAdvice CTO

How to Configure Nginx and Apache on the same Ubuntu VPS or Dedicated Server

Nginx and Apache are great and powerful web servers. However, they both have dra
2 min read
Idan Cohen
Idan Cohen
Marketing Expert

How to Set Up Replication on PostgreSQL on Ubuntu 18.04 VPS or Dedicated Server

High availability and load balancing are important concepts in database manageme
4 min read
Vladimir Rakov
Vladimir Rakov
Hosting Expert

How to Install PostgreSQL Database Server on CentOS 7

PostgreSQL is an object relational database that is open source. PSQL is a core
2 min read
Kennedy Mbuvi
Kennedy Mbuvi
Author
HostAdvice.com provides professional web hosting reviews fully independent of any other entity. Our reviews are unbiased, honest, and apply the same evaluation standards to all those reviewed. While monetary compensation is received from a few of the companies listed on this site, compensation of services and products have no influence on the direction or conclusions of our reviews. Nor does the compensation influence our rankings for certain host companies. This compensation covers account purchasing costs, testing costs and royalties paid to reviewers.
Click to go to the top of the page
Go To Top