DEV Community: Mehdi Pourfar

Easily use multiple proxies with PAC rules

Mehdi Pourfar — Sun, 27 Sep 2020 16:54:40 +0000

I use proxies to view the contents of the websites that are blocked either for censorship reasons or by sanctions.

But using proxies all the time makes working with websites that are not blocked a little bit slower. There are also some internal websites (like for example.. bank websites) which cannot be opened with any proxy. And also for my own job, I need an ssh tunnel connection to open internal urls.

It can be really tiresome to switch between these proxies manually. After some research, I found a solution for this kind of situations: Setting up a PAC (Proxy auto-config) file.

PAC is a javascript file consisting of a function named FindProxyForUrl with .pac extension

function FindProxyForURL(url, host) {
    if (host === 'a.com') {
        return 'DIRECT';
    } else if {host === 'b.com') {
        return 'SOCKS5 127.0.0.1:1081';
    }
    return 'SOCKS5 127.0.0.1:1080';
};

When you configure your proxy setting to use a PAC file, each time you open an address, your browser checks the url with this function to find out which proxy to use. But writing a function like this and changing it frequently can be complex and error prone.

So I decided to create a command line tool named PacGen to create this file. Here I want to show you how to work with it.

First we install it using pip:

pip install pacgen

Then we define our rules in .yml format in a file at ~/.pacgen.yml. This file consists of four segements:

proxies which defines our proxy servers with a name assigned to each of them.
routes which is a list of predefined routes for some specific hosts.
excludes which is list of the hosts that we want to browse without any proxy.
default_proxy which is the name of the default proxy for the hosts that are neither in routes nor in excludes.

Here is an example of ~/.pacgen.yml file.

proxies:
  ssh_tunnel: socks5://127.0.0.1:1081
  shadowsocks: socks5://127.0.0.1:1080
  httpproxy: http://127.0.0.1:1082
routes:
  172.19.20.10: ssh_tunnel
  youtube.com: shadowsocks
  viemo.com: shadowsocks
  news.com: httpproxy
  analytics.google.com: shadowsocks
default_proxy: shadowsocks
excludes:
  - bank.com
  - google.com

After you defined your rules and saved the file, you should run the command below in your terminal.

updatepac

Then it will create your PAC file in this path: ~/.proxy.pac.

Now that we have created our pac files, it's time to serve it with a webserver. The most straightforward way to do it is using nginx:

sudo apt install nginx
sudo cp ~/.proxy.pac /var/www/html/proxy.pac

Your file will be served at http://localhost/proxy.pac.
Now go to your system proxy setting and choose automatic proxy option and copy the address above into Configuration URL field. That's all.

Backup and replicate Postgresql 12 using pgBackRest in Ubuntu 20.04

Mehdi Pourfar — Sun, 27 Sep 2020 12:40:01 +0000

In this post, I will guide you through the process of setting up a continuous backup system and a replication server using pgBackRest.

For this guide, we need to have 3 servers: pg-master, pg-replica and pg-backup.

On all of these servers, we need to add these lines to /etc/hosts file and put the correct ips there:

{master_server_ip} pg-master
{replica_server_ip} pg-replica
{backup_server_ip} pg-backup

On pg-master:

sudo apt install postgresql-12 pgbackrest

On pg-replica:

sudo apt install postgresql-12 pgbackrest

On pg-backup:

sudo apt install pgbackrest

Since our servers should communicate with each other using ssh connection, we create ssh key pairs on each of them:

On pg-master, pg-replica and pg-backup run these commands:

sudo su postgres
ssh-keygen -t rsa -b 4096 -N ""

And then share all of the public keys inside /var/lib/postgresql/.ssh/authorized_keys file on each server. Then we need to check that all connections can be established without any problem:

On pg-master:

sudo -u postgres ssh postgres@pg-backup
[exit]
sudo -u postgres ssh postgres@pg-replica
[exit]

On pg-replica:

sudo -u postgres ssh postgres@pg-backup
[exit]
sudo -u postgres ssh postgres@pg-master
[exit]

On pg-backup:

sudo -u postgres ssh postgres@pg-master
[exit]
sudo -u postgres ssh postgres@pg-replica
[exit]

If everything works correctly, it's time to configure pgBackRest.

On pg-master, change the content of /etc/pgbackrest.conf with these lines:

[my_cluster]
pg1-path=/var/lib/postgresql/12/main

[global]
repo1-host=pg-backup
repo1-host-user=postgres

[global:archive-push]
compress-level=3

And then, open /etc/postgresql/12/main/postgresql.conf and change these variables:

archive_command = 'pgbackrest --stanza=my_cluster archive-push %p'
archive_mode = on
listen_addresses = '*'
max_wal_senders = 3
wal_level = replica

And then restart postgresql:

sudo systemctl restart postgresql

After that, we will create some test data:

sudo -u postgres psql

psql (12.4 (Ubuntu 12.4-1.pgdg18.04+1))
Type "help" for help.

postgres=# CREATE DATABASE my_db;
CREATE DATABASE
postgres=# \c my_db
You are now connected to database "my_db" as user "postgres".
my_db=# CREATE TABLE nums(val int);
CREATE TABLE
my_db=# INSERT INTO nums VALUES(1);
INSERT 0 1
my_db=# INSERT INTO nums VALUES(2);
INSERT 0 1
my_db=# INSERT INTO nums VALUES(3);
INSERT 0 1

Now we have a database named my_db with a table named nums with three values.

It's time to create our backup on pg-backup server. First edit /etc/pgbackrest.conf on pg-backup as below:

[my_cluster]
pg1-host=pg-master
pg1-host-user=postgres
pg1-path=/var/lib/postgresql/12/main                                                                                                               

[global]                                                                                                                                  
process-max=2
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=1
repo1-host-user=postgres
start-fast=y

Then run this command to create a backup stanza on pg-backup server:

sudo -u postgres pgbackrest --stanza=my_cluster --log-level-console=info stanza-create

Then, run this command on both pg-master and pg-backup to check everything is okay:

sudo -u postgres pgbackrest --stanza=my_cluster --log-level-console=info check

If everything works good, it's time to perform our first backup on pg-backup server:

sudo -u postgres pgbackrest --stanza=my_cluster --log-level-console=info backup

By default, pgBackRest works by copying new archive files to backup server but if we let it be like that, the hard disk will be full very soon. So we need to create new backups based on some routines and pgBackRest will remove old backups for us based on retention policies that we have defined above. To do this, we will use Cronjob.

On pg-backup server:

sudo -u postgres
crontab -e

And add these lines at the end of the file and press enter to create a new line. Then save and exit.

30 06  *   *   0     pgbackrest --type=full --stanza=my_cluster backup
30 06  *   *   1-6   pgbackrest --type=diff --stanza=my_cluster backup

You can read more about full backup, diff backup and retention policies in pgBackRest documentation.

Now that our backup server has been configured correctly, it's time to configure our replication server. Since we want to have a streaming replication, we should first create a replication user on pg-master:

sudo -u postgres psql
CREATE USER replicator PASSWORD '123123' REPLICATION";

On pg-master, open /etc/postgresql/12/main/pg_hba.conf and add this line and change pg_replicate_ip to the ip of pg-replica server:

host    replication     replicator      {pg_replicate_ip}/32       md5

And then restart postgresql

systemctl restart postgresql

Now, on pg-replica open the file /etc/pgbackrest.conf and change the content with these lines:

[my_cluster]
pg1-path=/var/lib/postgresql/12/main
recovery-option=primary_conninfo=host=pg-master port=5432 user=replicator password=123123 application_name=replica1

[global]
repo1-host=pg-backup
repo1-host-user=postgres

Don't worry if you don't know what application_name is. We will talk about it later.
On pg-replica, run these commands to fetch master database data:

systemctl stop postgresql
sudo -u postgres pgbackrest --stanza=my_cluster --delta --type=standby --log-level-console=info restore
systemctl start postgresql

To check that everything works correctly, on pg-replica go to psql and check that my_db database exists:

sudo -u postgres psql -d my_db
SELCET * FROM nums;

You should see these values as result:

 val 
-----
   1
   2
   3
(3 rows)

Now, we add some data on pg-master to check changes will be reflected on replica:

sudo -u postgres psql -d my_db
INSERT INTO nums VALUES (4);

On, pg-replica:

SELECT * FROM nums;

Now you should see these values as result:

 val 
-----
   1
   2
   3
   4
(3 rows)

If you don't see 4 values, look at the pg-replica's log file to find out the problem:

tail /var/log/postgresql/postgresql-12-main.log

As you saw earlier, we have set application_name=replica1 in our connection info, but what is that? By default, Postgresql will use asynchronous replication, meaning that, replication will be done with a lag (usually just a few milliseconds) but if you want to have synchronous replication (meaning that commits will be blocked until replication is done) then you can set this application_name in pg-master's postgresql.conf.
If you want to do this, on pg-master, open the file /ect/postgresql/12/main/postgresql.conf and change this value:

synchronous_standby_names = 'replica1'

And then restart postgresql. Now the replication should be synchronous. To test it, on pg-master go to sql shell and add a new row:

sudo -u postgres psql -d my_db
INSERT INTO nums VALUES(5);

If it doesn't hang, that means everything is okay. Buy if you stop postgresql replica server, command will hang and client will be blocked.

On pg-replica:

systemctl stop postgresql

On pg-master:

INSERT INTO nums VALUES(6);

Now as you see, pg-master's psql client is hanged but as soon as you start pg-replica postgresql server, it will be okay.

systemctl start postgresql

Now it's time to test recovery process. For the test purpose, we truncate nums table and recover the data.
On pg-master, run the command below and copy the time value:

sudo -u postgres psql -Atc  "select current_timestamp"
2020-09-24 14:17:23.105194+03:30

Open psql and truncate nums table:

sudo -u postgres psql -d my_db
TRUNCATE nums;
SELECT * FROM nums;
 val 
-----
(0 rows)

Stop Postgresql servers on pg-master and pg-replica

systemctl stop postgresql

On pg-master run the command below:

sudo -u postgres pgbackrest --stanza=my_cluster --delta --type=time "--target=2020-09-24 14:17:23.105194+03:30" --target-action=promote restore

And then start postgresql server:

systemctl start postgresql

Check the nums table:

sudo -u postgres psql -d my_db

 val 
-----
   1
   2
   3
   4
   5
   6
(6 rows)

As you can see, we have restored our truncated table on pg-master. Now we need to restore it on pg-replica. First, go to pg-backup server and get a new backup:

sudo -u postgres pgbackrest --stanza=my_cluster --log-level-console=info backup

Then, on pg-replica server, restore the data:

sudo -u postgres pgbackrest --stanza=my_cluster --delta --type=standby restore
systemctl start postgresql

Now for testing that everything works correctly add a value to nums table in pg-master and check that it will reflect on pg-replica.

That's all.

Faster CSV export with Django & Postgres

Mehdi Pourfar — Mon, 07 Oct 2019 11:17:19 +0000

As a Django developer, there are many times that you are asked for CSV export of your database tables.

To do this, Python’s CSV module is a simple library to use. But when your dataset is large, it becomes so inefficient that often leads to timeout error. There are two problem with this method:

Python is not very fast.
Generating model objects by Django Orm is very resource consuming.

As an alternative approach, I suggest using Postgres CSV functions. This way, not only we don’t need to create thousands of model objects, but also we rapidly generate CSV thanks to Postgres speed.

But do I need to write complex SQL queries?
What should I do if I currently have a filtered queryset, for example, in Django admin?

Don’t worry. There is no need to write SQL. Here, we write a function that accepts a queryset and a filename and returns a CSV response

from django.db import connection
from django.utils import timezone
from django.http import HttpResponse

def qs_to_csv_response(qs, filename):
    sql, params = qs.query.sql_with_params()
    sql = f"COPY ({sql}) TO STDOUT WITH (FORMAT CSV, HEADER, DELIMITER E'\t')"
    filename = f'{filename}-{timezone.now():%Y-%m-%d_%H-%M-%S}.csv'
    response = HttpResponse(content_type='text/csv')
    response['Content-Disposition'] = f'attachment; filename={filename}'
    with connection.cursor() as cur:
        sql = cur.mogrify(sql, params)
        cur.copy_expert(sql, response)
    return response

Suppose we have these models:

from django.db import models
from django.utils.translation import ugettext_lazy as _

class City(models.Model):
    name = models.CharField(max_length=50)

class Place(models.Model):
    PLACE_TYPE_CHOICES = (
        (1, _('Park')),
        (2, _('Cafe')),
        (3, _('Resturant')),
        (4, _('Cinema')),
    )
    name = models.CharField(max_length=50)
    city = models.ForeignKey(City, on_delete=models.CASCADE)
    place_type = models.PositiveSmallIntegerField(
        choices=PLACE_TYPE_CHOICES
    )

If we want to have a CSV export from Place table containing place_id, place_name, place_type and city_name, we pass this query to the function above:

from django.db.models import F

Place.objects.values(
  'id',
  'name',
  'place_type',
  city_name=F('city__name')
)

There is some caveat here. Postgres doesn’t know anything about our pretty, human readable and translated place types and putting some numbers in place_type columns can be completely useless.

The function below can be used to tell Postgres about this kind of mappings using Case and When:

from django.db.models import Case, When, Value, CharField

def map_choices(field_name, choices):
    return Case(
        *[When(**{field_name: value, 'then': Value(str(representation))})
          for value, representation in choices],
        output_field=CharField()
    )

And then, we use it in the query:

Place.objects.values(
  'id',
  'name',
  verbose_type=map_choices('place_type', Place.PLACE_TYPE_CHOICES),
  city_name=F('city__name')
)

By using Django ORM tools like F, Func, ExpressionWrapper, RawSQL … you can easily write more complex queries in a performant way.