Installation for version 3.18

Материал из WiKi - UserSide

en | ru

ATTENTION: These instructions are valid for ERP USERSIDE versions 3.18 (3.19)

Description

For the sake of simplicity, the commands for the Linux Debian 12 (bookworm) operating system will be considered. For experienced system administrators, it should not be difficult to use similar commands for another operating system. If you do not feel confident in administering operating systems, we recommend you to use Debian or Ubuntu Server LTS. Because of their simplicity and because our technical support is much more familiar with Debian-like distributions. The likelihood of being able to tell you something not related to ERP USERSIDE, but related to operating system administration, will be much higher if you have Debian or a similar distribution based on it.

On the other hand, if you are an experienced system administrator and have skills and experience with Docker, you may find it more convenient to use a ready-made environment based on Docker images, which we have prepared specially, including everything you need to run ERP USERSIDE. In this case, you will not have to configure anything, except to perform simple Docker-bundle settings https://github.com/userside/userside-docker.

Requirements

ERP USERSIDE requires various system applications and services, such as PHP interpreter with a set of extensions, WEB server, database management system and others. This section will list such requirements depending on the version of ERP USERSIDE.

Also note that your operating system must be configured with the correct time zone and locale. The correct display of information and correct sorting depends on this.

The list of required PHP extensions contains all extensions that are not part of the PHP core. Some of these extensions may come with the core PHP package or as part of the php-common package, while others must be installed additionally.

3.19 (beta)

  • PHP: 8.3
  • PHP extensions: ctype, gd, json, libxml, mbstring, openssl, pdo, pdo_pgsql, posix, simplexml, snmp, sockets, zlib, pcntl
  • Additional PHP extensions: ldap, soap
  • Python: 3.9+ (preferably 3.11)
  • Python modules: pip, venv
  • PostgreSQL: 12+ (preferably 16)
  • Redis: 5+ (preferably 7)
  • RabbitMQ: 3.10+ (preferably 3.13)
  • Supervisor

3.18 (current stable)

  • PHP: 8.1
  • PHP extensions: ctype, gd, json, libxml, mbstring, openssl, pdo, pdo_pgsql, posix, simplexml, snmp, sockets, zlib, pcntl
  • Additional PHP extensions: ldap, soap
  • Python: 3.8+ (preferably 3.11)
  • Python modules: pip, venv
  • PostgreSQL: 10+ (preferably 16)
  • Redis: 5+ (preferably 7)
  • RabbitMQ: 3.8+ (preferably 3.13)
  • Supervisor

Main Agreements

The directory where ERP USERSIDE is installed is /var/www/userside. Inside this directory there should be a subdirectory userside3, which is the root directory for the WEB server. If you want to use a different directory for ERP USERSIDE, remember to make the appropriate corrections to all the examples in these instructions.

Create the directory:

sudo mkdir -p /var/www/userside/userside3
sudo chown -R www-data:www-data /var/www/userside

You will need to allocate a domain name for ERP USERSIDE. The examples below use userside.mycompany.com, but you will need to change it to your own. Write this domain name in /etc/hosts if it is different from the hostname of the installation.

Installing the required components

The installation will be shown for the currently stable version of ERP USERSIDE 3.18. If you are going to install 3.19-beta, you will need to install PHP-8.3 instead of 8.1, and note the difference in nginx configuration. Otherwise, the steps are identical.

First, install the utilities that will be needed later in the installation process. These are required for any ERP USERSIDE. You can copy the lines one by one and paste them into the command line of your operating system.

Copy and paste the following lines in their entirety:

sudo tee /etc/apt/sources.list.d/contrib-non-free.list << EOL
deb http://deb.debian.org/debian/ $(lsb_release -sc) contrib non-free non-free-firmware
EOL

sudo apt update
sudo apt full-upgrade -y
sudo apt install -y gnupg ca-certificates lsb-release debian-archive-keyring debian-keyring libsnmp-dev snmp-mibs-downloader

PostgreSQL

Alternative official repository

We strongly recommend using the latest version of PostgreSQL, as this always has a positive impact on speed. Add the official Postgres repository to the system:

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update

If you have a different operating system, you can find the necessary commands for it at official PostgreSQL website.

Installation

Caution! Before installing PostgreSQL, your operating system must have your local locale and correct time zone set! This can be done later, but it will be much easier if it is done before installing PostgreSQL. Run the following command to make sure the correct locale is present:

locale -a

If your local locale is not among those listed, then install it. To do this, simply edit the /etc/locale.gen file by removing the comments before the line(s) with the desired locale, and then run the command:

sudo locale-gen

If you need to change the default locale, then execute:

sudo dpkg-reconfigure locales

To change the time zone, execute:

sudo timedatectl set-timezone Europe/Kyiv

Now you can move on to the PostgreSQL installation:

sudo apt install -y postgresql-16 postgresql-16-postgis-3

Setting

Create a user and database for ERP USERSIDE. The example below creates a user named userside and a database with the same name. If your locale is not uk_UA, remember to change the command. The PostGis extension needed to work with geographic data is then connected to the database. After doing the first line, you will need to enter the password for the new user twice - make a note of this password, you will need it later to set up the ERP USERSIDE

sudo -u postgres createuser userside -P
sudo -u postgres createdb -e -E "UTF-8" -l "uk_UA.UTF-8" -O userside -T template0 userside
sudo -u postgres psql -d userside -c "CREATE EXTENSION postgis"

If your PostgreSQL is installed on another server, you will need to make corrections to the files postgresql.conf and pg_hba.conf located in the /etc/postgresql/16/main/ directory. In the postgresql.conf file, you must uncomment and change the value of the listen_addresses parameter, specifying there all IP addresses of the server interfaces on which it can accept connections to PostgreSQL. In the file pg_hba.conf you need to add a line at the very end, similar to the previous ones, specifying from which address the ERP USERSIDE user can connect. But if your PostgreSQL is located on the same server as USERSIDE - there is no need to make any adjustments to these files.

Redis

The Debian 12 standard repository includes Redis version 7.0.15. This version is suitable for running ERP USERSIDE.

Installation

sudo apt install -y redis-server

Setting

By default Redis accepts connections without a password, but we strongly recommend setting a password. Since the password is transmitted in the clear (Redis does not provide encryption because it focuses on speed of request processing and extra steps like encryption are not applied), you need to create a really long and complex password, for example, using the openssl utility as follows:

openssl rand --hex 32

The output will be a random set of 32 bytes in 16-character form, which you will use as a password. Next, you will need to substitute this generated string in place of the word MYPASSWORDHERE. Write it down. Then in the settings you will specify this string as the Redis password.

Specify the generated passphrase hash in the Redis configuration file:

sudo sed -i 's@^.*requirepass .*@requirepass MYPASSWORDHERE@g' /etc/redis/redis.conf

where instead of MYPASSWORDHERE specify the hash of the passphrase received earlier.

It is also necessary to disable timeout (on some versions of Redis it is enabled):

sudo sed -i 's@^timeout .*@timeout 0@' /etc/redis/redis.conf

Restart Redis and make sure it works (should get PONG in response):

sudo systemctl restart redis
redis-cli -h 127.0.0.1 -p 6379 -a MYPASSWORDHERE ping

RabbitMQ

The Debian 12 standard repository includes an older version of RabbitMQ 3.10. You can use it - it is supported. However, this version is relatively old and your best bet is to install a newer version from alternative repositories. The official RabbitMQ website has quite detailed information on installing RabbitMQ for each operating system.

Adding alternative repositories

Copy and paste the following block in its entirety into the command line

curl -1sLf https://dl.cloudsmith.io/public/rabbitmq/rabbitmq-erlang/gpg.E495BB49CC4BBE5B.key | sudo gpg --dearmor | sudo tee /usr/share/keyrings/rabbitmq-erlang-archive-keyring.gpg > /dev/null
curl -1sLf https://dl.cloudsmith.io/public/rabbitmq/rabbitmq-server/gpg.9F4587F226208342.key | sudo gpg --dearmor | sudo tee /usr/share/keyrings/rabbitmq-server-archive-keyring.gpg > /dev/null

sudo tee /etc/apt/sources.list.d/rabbitmq.list <<EOF
deb [signed-by=/usr/share/keyrings/rabbitmq-erlang-archive-keyring.gpg] https://dl.cloudsmith.io/public/rabbitmq/rabbitmq-erlang/deb/debian $(lsb_release -cs) main
deb-src [signed-by=/usr/share/keyrings/rabbitmq-erlang-archive-keyring.gpg] https://dl.cloudsmith.io/public/rabbitmq/rabbitmq-erlang/deb/debian $(lsb_release -cs) main
deb [signed-by=/usr/share/keyrings/rabbitmq-server-archive-keyring.gpg] https://dl.cloudsmith.io/public/rabbitmq/rabbitmq-server/deb/debian $(lsb_release -cs) main
deb-src [signed-by=/usr/share/keyrings/rabbitmq-server-archive-keyring.gpg] https://dl.cloudsmith.io/public/rabbitmq/rabbitmq-server/deb/debian $(lsb_release -cs) main
EOF

sudo apt update

Installation

sudo apt install -y erlang-base \
                    erlang-asn1 erlang-crypto erlang-eldap erlang-ftp erlang-inets \
                    erlang-mnesia erlang-os-mon erlang-parsetools erlang-public-key \
                    erlang-runtime-tools erlang-snmp erlang-ssl \
                    erlang-syntax-tools erlang-tftp erlang-tools erlang-xmerl

sudo apt install rabbitmq-server -y --fix-missing

Installing the necessary add-ons

Do the next two rows one at a time!

sudo rabbitmq-plugins enable rabbitmq_management --offline
sudo rabbitmq-plugins enable rabbitmq_web_stomp --offline

Setting

Copy and paste the following block in its entirety:

sudo tee /etc/rabbitmq/rabbitmq.conf << EOF
listeners.tcp.default = 5672

web_stomp.port = 15674
web_stomp.cowboy_opts.max_keepalive = 60
EOF

Now we need to create users. We recommend to use three different users: for server administration (full rights), for userside and modules (full rights but no administrative rights) and web-stomp user for websocket (minimal rights to read certain broker objects). But you can only create two: an admin, which will also be used for userside, and a user for websocket. These must necessarily be at least two different users, since the password for the websocket user is passed to the browser and can be easily read by the user. The following will show an example for the recommended three users.

Create a user for administration RabbitMQ. This example uses the username admin and password password. The user is assigned the administrator tag immediately after creation, giving the user maximum administrator rights, and then set permissions on the vhost / allowing full access to configure, write and read everything within that vhost*.

sudo rabbitmqctl add_user "admin" "password"
sudo rabbitmqctl set_user_tags "admin" "administrator"
sudo rabbitmqctl set_permissions -p "/" "admin" ".*" ".*" ".*"

Create a user on behalf of which ERP USERSIDE and modules will run. Example for the user name userside (there is no need to give admin rights to this user):

sudo rabbitmqctl add_user "userside" "password2"
sudo rabbitmqctl set_user_tags "userside" "monitoring"
sudo rabbitmqctl set_permissions -p "/" "userside" ".*" ".*" ".*"

Create a WebSTOMP user. You will need it to use notifications via WebSocket. Instead of websock-user you can specify a different user name. Instead of websock-password, specify your password, but this password will be passed in the clear to the browser, so don't make it look like other passwords:

sudo rabbitmqctl add_user "websock-user" "websock-password"
sudo rabbitmqctl set_permissions -p "/" "websock-user" "^userside-stomp:id-.*" "" "^userside-stomp:id-.*"

Caution!: You will need to specify the WebSTOMP username and password in the settings in the USERSIDE interface (Menu: Settings - Main - Websocket).

* vhost is a virtual host inside RabbitMQ that allows you to differentiate between different applications using the same server. Like, for example, different databases on the same RDBMS server. The default vhost is named / and almost always this is enough for you. But if you plan, for example, to run multiple copies of ERP USERSIDE on the same server, then for each copy you will need to create a different vhost and, accordingly, users for it. See official guide for details.

Restart the rabbitmq service:

sudo systemctl restart rabbitmq-server

Management

One of the commands above installs a management module in RabbitMQ that provides a convenient WEB interface to monitor, diagnose, and manage the RabbitMQ server. With the help of this module you can monitor the server, monitor the number of messages in the queues and other states. All this can be done using the console utility rabbitmqctl, but using the WEB-interface can be much more clear and convenient.

By default, the WEB-interface of the control is available at http://userside.mycompany.com:15672

You can read more about the management module on the official RabbitMQ website.

PHP

Alternative repository

sudo curl -sSLo /usr/share/keyrings/deb.sury.org-php.gpg https://packages.sury.org/php/apt.gpg
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/deb.sury.org-php.gpg] https://packages.sury.org/php/ $(lsb_release -sc) main" > /etc/apt/sources.list.d/php.list'
sudo apt update

Installation

Execute the following commands:

sudo apt install -y php8.1-{fpm,cli,common,curl,intl,mbstring,opcache,pgsql,readline,xml,zip,snmp,gd}

Note: if you are installing an environment for 3.19, you should use PHP8.3 instead of PHP8.1 here and below.

If you plan to use LDAP, you need to install the php-ldap extension:

sudo apt install -y php8.1-ldap

If you plan to use TurboSMS to send SMS, you will also need the php-soap extension:

sudo apt install -y php8.1-soap

Setting

The following commands make changes to the configuration files. Remember to change them if you have installed PHP8.3.

sudo sed -i "s@^;date.timezone.*@date.timezone = $(cat /etc/timezone)@" /etc/php/8.1/fpm/php.ini
sudo sed -i "s@^;date.timezone.*@date.timezone = $(cat /etc/timezone)@" /etc/php/8.1/cli/php.ini
sudo sed -i "s@;cgi.fix_pathinfo=1@cgi.fix_pathinfo=0@" /etc/php/8.1/fpm/php.ini
sudo sed -i "s@post_max_size = 8M@post_max_size = 100M@" /etc/php/8.1/fpm/php.ini
sudo sed -i "s@upload_max_filesize = 2M@upload_max_filesize = 100M@" /etc/php/8.1/fpm/php.ini
sudo sed -i "s@max_execution_time.*@max_execution_time = 300@" /etc/php/8.1/fpm/php.ini
sudo sed -i "s@^;request_terminate_timeout =.*@request_terminate_timeout = 300@" /etc/php/8.1/fpm/pool.d/www.conf

sudo systemctl restart php8.1-fpm

NGINX

Alternative official repository

The version of NGINX supplied in the Debian repository is fully usable and there is no need to add an alternative repository. However, if you want to always have the latest version, then run the following lines to add the official nginx repository to your operating system:

curl https://nginx.org/keys/nginx_signing.key | gpg --dearmor \
    | sudo tee /usr/share/keyrings/nginx-archive-keyring.gpg >/dev/null
echo "deb [signed-by=/usr/share/keyrings/nginx-archive-keyring.gpg] \
http://nginx.org/packages/debian `lsb_release -cs` nginx" \
    | sudo tee /etc/apt/sources.list.d/nginx.list
echo -e "Package: *\nPin: origin nginx.org\nPin: release o=nginx\nPin-Priority: 900\n" \
    | sudo tee /etc/apt/preferences.d/99nginx
sudo apt update

If you have a different operating system, you can find the necessary commands for it on the official NGINX website: https://docs.nginx.com/nginx/admin-guide/installing-nginx/installing-nginx-open-source/.

Installation

sudo apt install -y nginx

Setting

NGINX should run as the www-data user - the php-fpm www-pool runs as the same user:

sudo sed -i "s@^user.*;@user www-data www-data;@" "/etc/nginx/nginx.conf"
sudo systemctl restart nginx

The following configuration example assumes that ERP USERSIDE will be installed in the default system directory /var/www/userside. If you wish to install ERP USERSIDE in a different directory, please remember to fix it in all subsequent examples. In order not to make a mistake, we recommend to use exactly the /var/www/userside directory.

This directory will contain the web-root, i.e. files accessible via the http protocol.

Now edit the /etc/nginx/conf.d/default.conf file. Or delete it and create a new file with an arbitrary name, for example /etc/nginx/conf.d/userside.conf. In either case, regardless of your choice, the contents of the file should be as follows (instead of userside.mycompany.com, specify your domain name):

server {
    listen       80 default_server;
    server_name  userside.mycompany.com;
    charset      utf-8;
    client_max_body_size 100M;

    access_log  /var/log/nginx/userside-access.log;
    error_log   /var/log/nginx/userside-error.log;

    root   /var/www/userside/userside3;
    index  index.php;

    location = /favicon.ico { access_log off; log_not_found off; }
    location = /robots.txt  { access_log off; log_not_found off; }

    location / {
        try_files $uri $uri/ /index.php$is_args$args;
    }

    location ~* ^.+\.(css|js|ogg|ogv|svg|svgz|eot|otf|woff|mp4|ttf|rss|atom|jpg|jpeg|gif|png|ico|zip|tgz|gz|rar|bz2|doc|xls|exe|ppt|tar|mid|midi|wav|bmp|rtf)$ {
        access_log    off;
        log_not_found off;
        expires       max;
        add_header    Pragma public;
        add_header    Cache-Control "public";
    }

    location ~ \.php$ {
        try_files     $uri =404;
        fastcgi_split_path_info ^(.+\.php)(/.+)$;
        fastcgi_pass  unix:/run/php/php8.1-fpm.sock;  # Для USERSIDE 3.19 использовать php8.3-fpm.sock
        fastcgi_index index.php;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        fastcgi_read_timeout 300;
        include       fastcgi_params;
    }

    location /ws {
        proxy_pass http://127.0.0.1:15674/ws;
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "Upgrade";
        proxy_set_header Host $host;
    }

    location ~ /\.ht { deny  all; }

    error_page 404 /404.php?type=404;
    error_page 403 /404.php?type=403;
    error_page 500 /404.php?type=500;

    location = /404.php {
        root /var/www/userside/userside3/main/error/;
        internal;
    }
}

You may also want to configure SSL and other things. This topic is beyond the scope of this manual.

If you installed RabbitMQ on another server, or changed the value of the WebSTOMP port from 15674 to some other, then specify the IP address of the server with RabbitMQ and the port number in the proxy_pass http://127.0.0.1:15674/ws; line. The protocol must remain http. The path should remain /ws.

Check the configuration and if it is ok reload nginx:

sudo nginx -t && sudo systemctl restart nginx

Python

Debian 12 has Python 3.11 in its default repository - this is sufficient, so no alternative repositories are needed. But if you have a version lower than 3.9 on your host, you should be sure to install additionally a more recent version of Python.

Installing the necessary packages

sudo apt install -y python3-dev python3-pip python3-venv libffi-dev pkg-config

Supervisor

sudo apt install -y supervisor

Downloading and running the installer

See also: Installer Help

1. navigate to the system directory

cd /var/www/userside

2. download the installer script

sudo -u www-data php -r "copy('https://my.userside.eu/install', 'userside_install.phar');" 

3. run the installer

sudo -u www-data php userside_install.phar install

During the process, the installer verifies compliance with technical requirements, requests access parameters, and checks connections to server services.

You can enter any ERP USERSIDE version number available to you for installation. To do this, you can enter the version number from the list (it contains only the latest version of each branch) or enter the entire version number if it is not on the list.

When the installer finishes, a message will be displayed indicating that the installation was successful.

If you did not install as a web server user, be sure to make that user the owner of all userside files recursively after the installation is complete!

sudo chown -R www-data:www-data /var/www/userside
sudo chmod -R u=rwX,g=rwX,o=r /var/www/userside

Set up after installation

Configuration of system services

After installation, copy the sample configuration files of the system services and adjust them if necessary (for example, specify the path to the userside directory if it is different from /var/www/userside). Run the commands:

sudo cp etc/us-core-worker.conf-example /etc/supervisor/conf.d/us-core-worker.conf
sudo cp microservice/poller/etc/usm_poller.conf-example /etc/supervisor/conf.d/usm_poller.conf
sudo cp etc/logrotate-example /etc/logrotate.d/userside
sudo cp microservice/poller/etc/logrotate-example /etc/logrotate.d/usm_poller
sudo cp etc/crontab-example /etc/cron.d/userside

Install the necessary dependencies for usm_poller

cd microservice/poller
sudo -H python3 -m venv venv
sudo -H ./venv/bin/pip install -U pip
sudo -H ./venv/bin/pip install -U -r requirements.txt

Launching the supervisor

The supervisor monitors the health of the services specified in its configuration. To start the supervisor, execute:

sudo systemctl restart supervisor

After starting the supervisor, after a few seconds you can observe the state of all the services it monitors. All services must be in the RUNNING state:

sudo supervisorctl status

Immediately after installation

This completes the installation of ERP USERSIDE. Now perform the following steps:

  • Open the system page http://userside.mydomain.com/ and log in (default username: Admin, password: 1234).
  • Perform Websocket configuration under: Settings - Main - WebSocket. Enable and enter the username and password of the WebStomp user you created earlier in the RabbitMQ section.
  • Perform the basic settings under: Settings - Main.
  • Configure the interaction with billing according to the instructions.
  • Read the instructions on the page: Where to start?.
  • It is advisable to restrict access to the API file. "/var/www/userside/userside3/api.php" from certain IP addresses.

System upgrade

Technically, any end-to-end upgrade from ERP USERSIDE 3.11 to the latest version is possible. However, there are situations when such an upgrade causes problems with data migrations. This is most likely to occur on older systems where data has been accumulated for many years (starting with the upgrade to ERP USERSIDE version 3). This is why we recommend a phased system upgrade. If you have 3.11, first upgrade to the latest 3.12, then 3.13, etc. Note that each version has different system requirements (see above), but most are not limited to the version above. That is, if you have 3.11 and want to upgrade to 3.16, you can install the latest version of PostgreSQL, Redis, RabbitMQ, etc. right away, despite the fact that you will be doing a phased upgrade. The only thing you will have to consider is PHP versions. Different versions of ERP USERSIDE support a different range of PHP versions. Unfortunately, it is impossible to avoid this, because, for example, we did not test ERP USERSIDE 3.14 on version PHP 7.4 because at that time it simply did not exist, so we simply can not guarantee the work on this version. But the range of supported PHP versions is quite wide, so that you do not have to install a new version of PHP for each version of ERP USERSIDE. For example, to upgrade from 3.11 to 3.16 you only need a few steps:

  • upgrade PHP to version 7.1 and with this version you can upgrade to 3.15. Also install all necessary dependencies for 3.16 at once
  • after upgrading to 3.15, upgrade PHP to 7.4 and upgrade to 3.16.

We recommend making a copy of the system to safely perform the upgrade. Refer to HOWTO: USERSIDE cloning. You can always delete the copy and start over. This is easy and safe. But if you wish to upgrade a running system, perform it at times of least stress, with time to spare for disaster recovery if something goes wrong. Always be sure to back up your database and files before upgrading. If an update is interrupted, especially during the data migration phase, there is a risk that it will be very difficult or even impossible to restore the data correctly. In general, the responsibility for having an up-to-date backup lies entirely with you.

You may need to upgrade PostgreSQL before upgrading. Please refer to our PostgreSQL upgrade instructions for this procedure before you start the ERP USERSIDE upgrade process.

Upgrade process

We strongly recommend performing test updates on a copy of the system.

To perform an upgrade, run the installer in install mode and follow the instructions:

cd /var/www/userside
sudo -u www-data php userside_install.phar install

After updating the USERSIDE, you should be sure to update the usm_poller dependencies:

sudo -H microservice/poller/venv/bin/pip install -U -r microservice/poller/requirements.txt

Now you need to restart all the services that are supervised by the supervisor and make sure that all of them start and are in the RUNNING state:

sudo supervisorctl restart all
sudo supervisorctl status

System maintenance

To keep the system up and running and to ensure reliable operation in the future, it is recommended that you follow a few simple steps:

  • periodically back up the database
  • periodically create a backup copy of uploaded files (this can be done simply by archiving the /var/www/userside/var/attachments directory and the .env file)
  • periodically monitor server performance (using htop, atop) and the RabbitMQ broker and make decisions about scaling fpm pools, kernel background processes and microservices

Backup

Set up periodic backups of the database and user files. Preferably at least once a day when the server is least loaded. In PostgreSQL, there are two ways to back up the database: dump and sql-script. Each of them has its own advantages and disadvantages. The first one creates a secure dump of the entire database, and the second one creates a SQL-script, which can be executed to restore both the structure and data. It is recommended that you refer to documentation on how to use the pg_dump command to choose the method or set of parameters that is best for you.

Below are two commands for creating a database backup - choose one of them and add it to your crontab:

# DUMP
sudo -u postgres pg_dump --no-acl -Fc userside > /backup/userside.dump

# SQL-script
sudo -u postgres pg_dump --no-acl -Fp -Z 5 userside > /backup/userside.sql.gz

To create a minimal backup of files you can use the following command (for version 3.15 and later):

sudo tar -czf /backup/userside.tgz .env common/config/settings.json var/attachments

These files together with the database will be enough to restore USERSIDE. But you can back up the entire /var/www/userside directory for more reliability.

Restore from backup

The pg_restore command is used to restore from a dump. Documentation on using pg_restore. The psql utility is used to restore from a SQL script. Documentation on using psql.

DUMP

The dump contains the entire database with all its elements. Therefore, before restoring a database from a dump, it must not exist on the server - delete the database before restoring from a dump. It is also important that the database user (the owner of all items) already exists - create it before restoring if you are restoring the database on a new server where the required user does not exist yet.

Also note that the dump structure may not be compatible between different versions of PostgreSQL. This is not necessarily the case, but it is likely.

To restore from the dump, use the command:

sudo -u postgres pg_restore --clean --if-exists --create --exit-on-error --dbname=postgres /backup/userside.dump

Note that the database name in the command must be postgres because the name of the database to be restored is taken from the dump.

SQL-script

You can restore a database from a SQL script using the command:

gunzip < /backup/userside.sql.gz | sudo -u postgres psql -d userside

The database must already exist before executing this command, but it must be empty. The user (owner) of the database objects must also exist.

After database restoring

After restoring the database by any method, you must clear the ERP USERSIDE cache, as the cache now contains different data from the database. Go to the /var/www/userside directory and run the clear cache command:

cd /var/www/userside
php run cache/flush-all

For version 3.17 and below, instead of the last command:

php userside cache/flush-all

If the versions of the USERSIDE files and the database dump are different, then after restoring the database from the backup, it is necessary to bring the USERSIDE version to the corresponding data. To do this, run the command in the /var/www/userside directory:

sudo -u www-data php userside_install.phar repair

PostgreSQL maintenance

PostgreSQL's architecture is such that operations that change data in tables always create a new copy of that data, and mark the old ones as "dead" - this speeds up these operations considerably, but database files can grow in size over time, which can also significantly affect performance.

By default, Postgres has automatic vacuuming enabled, which runs continuously in parallel with the DBMS itself. Automatic vacuuming deletes "dead" tuples from files so that the free space can be reused by new data. But this vacuumisation mode does not perform "file defragmentation", leaving their size at least the same.

In most cases, auto-vacuuming is sufficient and we do not recommend disabling auto-vacuuming to prevent your files from growing to huge sizes and slowing down your database tens or hundreds of times.

The full vacuum procedure, which can be run manually, performs defragmentation, reducing the size of the database files. But it is performed with full table locking! The full vacuuming procedure (as well as index rebuilding) should preferably be performed at the time of lowest server load when clearly necessary (if the database file size is too large, tens or hundreds of times the normal size). During this procedure, the database tables will be locked, as this procedure creates new data files and transfers only "live" data into them. It is also desirable to perform an index rebuild procedure after this procedure. You can find detailed information on vacuuming and reindexing in the PostgreSQL documentation.

The commands below are aliases to SQL queries of the same name. If you find it easier to execute SQL queries instead of commands, please refer to the documentation at the links above.

sudo -u postgres vacuumdb --full --analyze userside
sudo -u postgres reindexdb userside

If you have run a manual vacuumisation procedure before, you have probably used the -j or --jobs parameter to specify the number of threads. When using full vacuumisation, it is undesirable to use multithreading to avoid interlocking of tables.

RabbitMQ monitoring

Only required for version 3.16 and newer.

The RabbitMQ management system displays graphs with the workload of message queues in a visual form. A consistently high or potentially increasing number of messages in the queues indicates the need for more background processes. For example, if the number of messages in the queue named userside is constantly high (more than 10...20), it is worth adding one more instance of the us-core-worker handler. To do this, edit the supervisor file /etc/supervisor/conf.d/us-core-worker.conf by increasing the numprocs value and then restart the supervisor.