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
- Click this link and all your queries to best hosting will end.