Skip to main content

How to Install PostgreSQL on debian stretch in oder to use it with your Django application and allow remote connection


In this guide, we'll demonstrate how to install and configure PostgreSQL to use with your Django applications. We will install the necessary software, create database credentials for our application, and then start and configure a Django project to use this backend. We will start by  install the database software and the associated libraries required to interact with them. This guide assumes that you allready have a working Django project.

Step 1 – Prerequsities

apt-get install python3-pip python3-dev libpq-dev postgresql postgresql-contrib

Step 2 – Connect to PostgreSQL


After installing the PostgreSQL database server by default, it creates a user ‘postgres’ with role ‘postgres’. It also creates a system account with the same name ‘postgres’. So to connect to postgres server, login to your system as user postgres and connect database.

su - postgres
You should now be in a shell session for the postgres user. Log into a Postgres session by typing: 

psql

Step 3 – Configure PostgreSQL to allow remote connection

By default PostgreSQL is configured to be bound to “localhost”.
netstat -nlt
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:10000           0.0.0.0:*               LISTEN
tcp        0      0 192.168.1.246:53        0.0.0.0:*               LISTEN
tcp        0      0 192.168.0.245:53        0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:53            0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:21              0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:953           0.0.0.0:*               LISTEN

As we can see above port5432 is bound to 127.0.0.1.It means any attempt to connect to the postgresql server from outside the machine will be refused. In order to fix this issue we need to change the default settings on  postgresql.conf


Step 4 – Configuring postgresql.conf

nano /etc/postgresql/9.6/main/postgresql.conf
on line 59 
# listen_addresses = 'localhost' change it to 
listen_addresses = '*'
restart postgresql server
systemctl restart postgresql

netstat -nlt
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:10000           0.0.0.0:*               LISTEN
tcp        0      0 192.168.1.246:53        0.0.0.0:*               LISTEN
tcp        0      0 192.168.0.245:53        0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:53            0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:21              0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:953           0.0.0.0:*               LISTEN

Step 5 – Configuring pg_hba.conf

try to connect to remote postgresql server using “psql”.
psql -h 192.168.0.245 -U postgres
psql: FATAL:  no pg_hba.conf entry for host "192.168.0.245", user "postgres", database "postgres", SSL on
FATAL:  no pg_hba.conf entry for host "192.168.0.245", user "postgres", database "postgres", SSL off
In order to fix it, open pg_hba.conf and add following entry on line 93.
host    all             all              0.0.0.0/0              trust
systemctl restart postgresql      # Restart postgresql server.

psql -h 192.168.0.245 -U postgres
psql (9.6.13)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# ALTER USER postgres PASSWORD 'posttest';
ALTER ROLE    # After login, you can ALTER postgres user: (setting password)
 change pg_hba.conf file again from trust to md5
host    all             all              0.0.0.0/0              md5 
systemctl restart postgresql  # Now restart the server again
 
psql -h 192.168.0.245 -U postgres
Password for user postgres:
psql (9.6.13)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 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
(3 rows)

postgres=# \q
<./code>

We are able to connect to postgresql server remotely.With the installation and basic configuration out of the way, we can move on to create our database and database user that we will use to connect with Django.


Step 6 – Create a Database and Database User


 CREATE DATABASE mydjango;
CREATE DATABASE
postgres=# CREATE USER mydjangouser WITH PASSWORD 'password';
CREATE ROLE
postgres=# ALTER ROLE mydjangouser SET client_encoding TO 'utf8';
ALTER ROLE
postgres=# ALTER ROLE mydjangouser SET default_transaction_isolation TO 'read committed';
ALTER ROLE
postgres=# ALTER ROLE mydjangouser SET timezone TO 'Africa/Dar_es_Salaam';
ALTER ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE mydjango TO mydjangouser;
GRANT
postgres=# \q

Step 7 – Configure the Django Database Settings

Before we install applications within the virtual environment, we need to activate it. You can do so by typing:

source venv/bin/activate

We will install the psycopg2 package that will allow us to use the database we configured:

pip3 install psycopg2

We need to configure the Django project to use the database we created.
Open the main Django project settings file

 nano news_art/settings.py

Replace all the databases entry for sqlite3 with this code

DATABASES = {
     'default': {
         'ENGINE': 'django.db.backends.postgresql_psycopg2',
         'NAME': 'mydjango',
         'USER': 'mydjangouser',
         'PASSWORD': 'password',
         'HOST': '192.168.0.245',
         'PORT': '5432',
      }
}

Step 8 –Migrate the Database and Test your Project

Now that the Django settings are configured, we can migrate our data structures to our database and test out the server.

python3 manage.py makemigrations
No changes detected
python3 manage.py migrate
 Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying sessions.0001_initial... OK

you can test that your database is performing correctly by starting up the Django development server:

 python3 manage.py runserver 192.168.0.245:8000

In your web browser, visit your server's domain name or IP address followed by :8000 to reach default Django root page: In my case http://192.168.0.245:8000/




 

Labels:

Popular posts from this blog

How To Install the Anaconda Python Distribution on Debian 9 And Running a public notebook server

Anaconda Distribution is an open-source package manager, environment manager and distribution of Python and R programming languages. With a collection of 1,000+ open source packages with free community support. Designed for data science and machine learning workflows, you can use it whether you are on Windows, macOS or Linux. The Anaconda distribution ships with the conda command-line package management utility. You can learn more about Anaconda and conda by reading the official Anaconda Documentation . Jupyter is a browser-based interpreter that allows you to interactively work with Python and R. Anaconda provides Jupyter as well. You can think of Jupyter as a digital notebook that gives you an ability to execute commands, take notes and draw charts.It’s primarily used by Data Scientists. But I find that very useful tool if you are learning Python or R. It’s basically the same as working on a shell but much better. The Jupyter notebook web application is based on a
Read more

How to create REST API using Django REST Framework

This post begins with already working project and app's, I found that there some few requirement's that my project needed to handle and the best option for those requirement's was to use the Django's  Rest Framework. The way that I will tackle this task is more specific to the needs of the project rather than a one to one how to..., that being said you can still follow along, the approach that I'm going to use is easy to follow since I'll be providing a lot of information a log the way for better understanding of the why and how.....this code is available on Github , enough with the alerts and on with the show. Note:  If you would want to mimic the exactly settings then you will need to enable user authentication on your project you can follow this link for details .  Start with the DRF (Django Rest Framework) installation pip3 install djangorestframework For our app to use DRF, we'll have to add rest_framework into our settings.py.   nan
Read more

django react app setting up the backend

On the previous article I demonstrated how we can use the generic views along with ModelSerializer classes to rapidly develop our REST APIs. Knowledge that you will need  in your career as full stack / backend developer, however think of this article as an extension to the previous one, equipped with what we already know about REST API we will step our game up and discuss about ViewSet, ModelViewset we will dig deep into the concepts of Routers which allow us to manage our api routes in a simple and sophisticated manner as well as helping to speed up building APIs even further. There for on part II of this article i'll work you through on how React application can consume this RESTful API. There for at the end of the day we will have a full stack web app, in short we strat our development at the backend then later on we move at the frontend... so are you excited and ready to take the challange? lets do this then..... you can get source code for the bakend on github Preparat
Read more