Quickbook has grown to get its cluster

First phase of our quickbook is over, it has grown to get its cluster. We see that this application is growing enormously and has enormous potential for us to comfortably grow our business. We operate in different industries and our company works in the Automobile, HR and Real Estate sector. This requires enormous flexibility from our accounting CRM and similar systems. So much flexibility that we can do nothing more than just switch to our own system a little at a time, which we develop a little at a time and expand every quarter and every year. If we see opportunities in the management of our company, we try to solve them with a personal approach and then when a solution is found, we automate it with technology.


But for everything to run reasonably without delays, we now have to move from a standard database to a cluster in the cloud in Germany / Finland so that we have the flexibility of a cluster and horizontal scaling. Important that it is within the EU for GDPR and data protection.
Install Citus and Initialize the Cluster

For that we will try interesting candidate Citus. That is something powerful, but it is not too far from our Dearly loved Postres.


Citus postgres install and config on cloud cluster.

Buy a small cloud server with ubuntu but see in which location you are going to choose your servers. Be sure that the entire cluster is in 1 datacenter, which will improve performance a lot.


In Germany or Finland there are now interesting offers with automated backup. (Firewalls, Backups, Snapshots, load balancers etc.. )


Login via SSH and install Citus.. You will config firewall in Next step when Citus is installed and you could test it from Web server and from Development computers from Office. So, you could switch configuration and test something when it needed.

curl https://install.citusdata.com/community/deb.sh | sudo bash

sudo apt-get -y install postgresql-15-citus-11.1

Initialize the Cluster

# this user has access to sockets in /var/run/postgresql

sudo su - postgres

# include path to postgres binaries

export PATH=$PATH:/usr/lib/postgresql/15/bin

Modify my PATH so that the changes are available in every Terminal session!!!

So, you could add in in your profile. ( postgres user ) 

vim ~/.profile 

mkdir citus

initdb -D citus 

Citus is a Postgres extension. To tell Postgres to use this extension you’ll need to add it to a configuration variable called shared_preload_libraries

echo "shared_preload_libraries = 'citus'" >> citus/postgresql.conf

Start an instance of PostgreSQL

pg_ctl -D citus -o "-p 9700" -l citus_logfile start

U can also use this commands

pg_ctl -D citus -o "-p 9700" -l citus_logfile reload

pg_ctl -D citus -o "-p 9700" -l citus_logfile stop

pg_ctl -D citus -o "-p 9700" -l citus_logfile start

psql -p 9700 -c "CREATE EXTENSION citus;"

To verify that the installation has succeeded. 

psql -p 9700 -c "select citus_version();"

vim citus/postgresql.conf

listen_addresses = ‘*’ 

To connect from external host. 

You should see details. 

If you need multi node installation please see documentation.

https://docs.citusdata.com/en/stable/installation/multi_node.html 

Multi-tenant Applications

Data from different tenants is stored in a central database, and each tenant has an isolated view of their own data.

ps aux |grep postgres

To see if postgres is running properly. 

sudo su - postgres

psql -p 9700

psql (15.1 (Ubuntu 15.1-1.pgdg22.04+1))

Type "help" for help.

postgres=#

Now you can create database and connect from django. 

CREATE USER quickbook WITH PASSWORD 'pwd';

CREATE DATABASE quickbook WITH OWNER quickbook;

NOTICE:  Citus partially supports CREATE DATABASE for distributed databases

DETAIL:  Citus does not propagate CREATE DATABASE command to workers

HINT:  You can manually create a database and its extensions on workers.

CREATE DATABASE

Test connection 

psql -h localhost -d yourname -U yourname

Or external IP

psql -h 65.108.145.25 -Uquickbook -d quickbook -p 9700

Try to connect from Django to created database. 

If it not so much data you can use dumpdata command 

python3 manage.py dumpdata --exclude auth.permission --exclude contenttypes > data/dump_no_content_types_01.02.2023.json


If not then you have to use postgres backup systeem.. We will use this for next step.

https://www.webdeveloper.today/2021/04/backup-postgres-sql-format.html 

Of if you want to copy only some objects you can use this workflow

https://www.webdeveloper.today/2022/11/how-to-copy-django-objects-from-one-db.html

If you need more connections install then pooler. At this time the application is not used so much to use pooler.

There are a few options when it comes to your connection pooler, including PgBouncer and PgPool. At Citus we leverage PgBouncer.

https://www.youtube.com/watch?v=x_XpPbfomso 

https://www.youtube.com/watch?v=a0SDogoPzss 

https://www.citusdata.com/blog/2017/05/10/scaling-connections-in-postgres/ 

Installing PgBouncer on Ubuntu

On the web server where your django app is installed

 apt install pgbouncer -y

[databases]

* = host=POSTGRESQL_IP port=9700

On the postgres / Citus host  config central PgBouncer

vim citus/pg_hba.conf

host all all PGBOUNCER_IP/NETMASK trust

pg_ctl -D citus -o "-p 9700" -l citus_logfile reload

On the server where web and pgbouncer installed 

psql -h 127.0.0.1 -Uquickbook -d quickbook -p 6432

Don’t forget to /etc/pgbouncer/userlist.txt add user and password to the userlist file.

Install django connector and postgres client

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

If you need a test database then..

apt install postgresql

pip3 install psycopg2

DATABASES = {

'default': {

     'ENGINE': 'django.db.backends.postgresql_psycopg2',

     'NAME': 'databasename',

     'USER': 'user',

     'PASSWORD': 'pwd',

     'HOST': '127.0.0.1', # localhost pgbouncer

     'PORT': '6432',

     'AUTOCOMMIT': True,

}

}