Running PostgreSQL database in Termux

UPDATE – 06/01/2024 added few useful commands

UPDATE – 10/03/2023 add bytes json data using – dataset and psycopg2-binary python packages

UPDATE – 25/02/2023 After database update and postgresql may not start. It might throw error messages:

Postgres - FATAL: database files are incompatible with server

# workaround - make sure tmp folder exist
# if it does not work then use full path name - /data/data/com.termux/files/usr/var/postgres
pg_ctl -D $PREFIX/var/lib/postgresql -l $PREFIX/tmp/psql.log start

In this article, we will show how to run PostgreSQL database in Termux on Android device.

Create skeleton database

mkdir -p $PREFIX/var/lib/postgresql
initdb $PREFIX/var/lib/postgresql

Starting the database

pg_ctl -D $PREFIX/var/lib/postgresql start

Similarly stop the database using

pg_ctl -D $PREFIX/var/lib/postgresql stop

Create User

$ createuser --superuser --pwprompt yourUserName

Create your database:

$ createdb mydb

Open your database

$ psql mydb
# using sql script
$ psql -u admin mydb -f ch.sql

You will now see the promt

$ psql -U admin mydb
mydb=#
psql (15.2)                                     
Type "help" for help.                                                                           
mydb=# \dt
List of relations                   
Schema |      Name       | Type  |  Owner
--------+-----------------+-------+---------
 public | companies_house | table | admin
 public | companieshouse  | table | admin
 public | data            | table | u0_a445     
(3 rows)

Add a table to the dstabase created above (let’s name that one hn_links) with the following schema.

CREATE TABLE "hn_links" (
	"id" INTEGER NOT NULL,
	"title" VARCHAR NOT NULL,
	"url" VARCHAR NOT NULL,
	"rank" INTEGER NOT NULL
);

Here the complete code with the scraping logic and code for storing result in the database.

import psycopg2
import requests
from bs4 import BeautifulSoup

# Establish database connection
con = psycopg2.connect(host="127.0.0.1",
                       port="5432",
                       user="postgres",
                       password="",
                       database="wpdb")

# Get a database cursor
cur = con.cursor()

r = requests.get('https://news.ycombinator.com')
soup = BeautifulSoup(r.text, 'html.parser')
links = soup.findAll('tr', class_='athing')

for link in links:
	cur.execute("""
		INSERT INTO hn_links (id, title, url, rank)
		VALUES (%s, %s, %s, %s)
		""",
		(
			link['id'],
			link.find_all('td')[2].a.text,
			link.find_all('td')[2].a['href'],
			int(link.find_all('td')[0].span.text.replace('.', ''))
		)
	)

# Commit the data
con.commit();

# Close our database connections
cur.close()
con.close()

Inserting jsonb payload

Inserting bytes json into a table.

• create table with a column type jsonb
• python packages – psycopg2-binary, dataset

"""
pip3 install virtualenv
virtualenv -p python3 .venv
source .venv/bin/activate
pip3 install dataset psycopg2-binary
create table ch
(
   id       serial, 
   payload  jsonb not null default '{}'::jsonb
);

"""
import dataset

def construct_pg_url(postgres_user='utc', postgres_password='utcpassword', postgres_host='localhost', postgres_port='5432', postgres_database='utcdb'):
  PG_URL = "postgresql://" + postgres_user + ":" + postgres_password + '@' + postgres_host + ':' + postgres_port + '/' + postgres_database
  return PG_URL

pgconn = dataset.Database(url=construct_pg_url(),schema='public')

table = pgconn['ch']
rows = [dict(payload='bar')] * 10000
table.insert_many(rows)

Updating password

Run psql command from the command line and use ALTER to change password.

psql databasename
database=# ALTER USER dbuser with password 'newpassword;

Getting data from a database

Store database credential in a file and utility function to retrieve database credentials.

# database credentials - database.ini
[postgresql]
host=127.0.0.1
database=database_name
user=dbuser
password=password

# utility function to get credentials from database.ini file - utils/config.py
#!/usr/bin/python
from configparser import ConfigParser
 
def config(filename='database.ini', section='postgresql_pi'):
    # create a parser
    parser = ConfigParser()
    # read config file
    parser.read(filename)
 
    # get section, default to postgresql
    db = {}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            db[param[0]] = param[1]
    else:
        raise Exception('Section {0} not found in the {1} file'.format(section, filename))
 
    return db

# pytghon script to get data from postgresql database
#!/usr/bin/python3
import psycopg2
from utils.config import config

def get_agent():
    """ query data from the vendors table """
    conn = None
    try:
        params = config('.env/database.ini')
        conn = psycopg2.connect(**params)
        cur = conn.cursor()
        cur.execute("SELECT * FROM agenturl ORDER BY 1 limit 10")
        #print("The number of agents: ", cur.rowcount)
        row = cur.fetchone()

        while row is not None:
            print(row)
            row = cur.fetchone()

        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
            
# run main
if __name__ == '__main__':
    get_agent()