Standalone PostgreSQL Installation¶
This is not a required step; however, some people like to put PostgreSQL on a separate server box. PostgreSQL 15 is assumed for these instructions.
If you do install PostgreSQL on a different machine from DjangoPBX, please make sure to remove the postgresql.service entries from both of these files on your DjangoPBX machine:
There is no need to use the full DjangoPBX installer for this task, use the pgsql-server-standalone-install.sh instead.
mkdir -p /usr/src/djangopbx-install
cd /usr/src/djangopbx-install
wget https://codeberg.org/DjangoPBX/djangopbx-install.sh/raw/branch/main/pgsql-server-standalone-install.sh
No changes to the configuration section are necessary; however, if you have already generated a password for the database user, you can add that to the configuration section:
nano pgsql-server-standalone-install.sh
chmod +x pgsql-server-standalone-install.sh
Run the installer:
./pgsql-server-standalone-install.sh
By default, the installer will set up /etc/nftables.conf to allow all private (RFC1918) IP address ranges. If you prefer a more strict firewall then edit /etc/nftables.conf to add to the IPv4_white_list all the private IP addresses of the machines that are likely to access the database. This list will normally include your DjangoPBX machine and any DjangoPBX replicas, and also all of your FreeSWITCH machines.
Some like to put the actual database onto a separate disk to make upgrading and volume level backups easier. If you wish to do this then you will need to do something like the following:
Assume /dev/sdb is a newly added disk device.
fdisk /dev/sdb
type n
choose p
accept all the defaults.
type wq
Now you have a partition /dev/sdb1
Lets put an ext4 filing system on it
mkfs.ext4 /dev/sdb1
Now stop the postgresql service:
systemctl stop postgresql
Move the files in /var/lib/postgresql to the new disk…
mount /dev/sdb1 /mnt
mv /var/lib/postgresql/* /mnt
umount /dev/sdb1
mount /dev/sdb1 /var/lib/postgresql
touch /var/lib/postgresql/.psql_history
chown postgres:postgres /var/lib/postgresql/.psql_history
touch /var/lib/postgresql/.bash_history
chown postgres:postgres /var/lib/postgresql/.bash_history
Start up the postgresql service:
systemctl start postgresql
Now add an entry to /etc/fstab so the partition will be automatically mounted on startup.
Find out the partition ID of /dev/sdb1 using the blkid command; make a note of it.
Now using your Block UUID add a line to the /etc/fstab file as follows:
# PostgreSQL storage
UUID=4bf50954-654d-4725-8e80-abaa6fa01c51 /var/lib/postgresql ext4 noatime,rw 0 0
Make the following changes in /etc/postgresql/15/main/postgresql.conf:
set listen_addresses to both 'localhost' and '<your private LAN IP address>'
listen_addresses = 'localhost,172.20.20.104'
You can find PostgreSQL tuning information at https://pgtune.leopard.in.ua/ Below are some optimisations, based on having a machine with SSD storage, 12 CPU cores and 12Gb of memory, allowing PostgreSQL to use 8Gb of memory and 8 of the CPU cores.
# DB Version: 15
max_connections = 100
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB (or default -1 sets on shared buffers)
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 2621kB
huge_pages = off
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
Edit /etc/postgresql/15/main/pg_hba.conf Add:
# IPv4 djangopbxprivate LAN connections
host all all 10.10.10.10/32 scram-sha-256
# IPv4 freeswitch private LAN connections
host all all 10.10.10.11/32 scram-sha-256
Additional Configuration for Replication¶
On both Master and slave…
Add additional settings to /etc/postgresql/15/main/postgresql.conf
wal_level = logical
wal_log_hints = on
leave max_wal_senders = 10 (The default)
hot_standby = on
On Master… Create a replication user:
su - postgres
psql
CREATE USER djangorep WITH REPLICATION ENCRYPTED PASSWORD '3671b15b8409d650e7c8';
\quit
exit
Edit /etc/postgresql/15/main/pg_hba.conf Add:
host replication djangorep 10.10.10.13/32 scram-sha-256
Reload the configuration:
su - postgres
psql -c "select pg_reload_conf();"
\quit
exit
On Slave… First check connectivity with the master:
telnet 10.10.10.10 5432
Remove any existing data:
rm -rf /var/lib/postgresql/15/main
Create backup of master on slave (you will be prompted for the password):
su - postgres
pg_basebackup -h 10.10.10.12 -D /var/lib/postgresql/15/main -U djangorep -P -c fast --slot=<random string, e.g. use replication host name> -C -v -R -X stream
exit
Restart the postgresql service:
systemctl start postgresql
That should be the Standalone PostgreSQL server (and replica, if required) installation completed.