Skip to main content

Azure VM Configuration

This document contains all the steps to setup the Azure VM from scratch.

Installing postgres​

While installing postgres, make sure you are installing the version of your choice (Use Version 14 for ORSANCO Flows, as of 2/2/2024).

info

The installation instructions are derived from the timescaledb website here.

# 1. At the command prompt, as root, add the PostgreSQL third party repository to get the latest PostgreSQL packages:
sudo apt install gnupg postgresql-common apt-transport-https lsb-release wget

# 2. Run the PostgreSQL repository setup script (might require sudo?):
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# 3. Add the TimescaleDB third party repository (might require sudo?):
curl -s https://packagecloud.io/install/repositories/timescale/timescaledb/script.deb.sh | sudo bash

# 4. Update and install packages:
sudo apt update
sudo apt install timescaledb-2-postgresql-14
sudo apt install postgresql-client-14

# 5. Run the timescaledb tuner, accepting all defaults:
sudo timescaledb-tune -y

Configure NGINX​

  1. nano /etc/nginx/sites-available/riverflows

  2. File contents:

    server {
        listen 443 ssl default_server;
        listen [::]:443 ssl default_server;

        ssl_certificate /home/gqc/ssl_cert_01222024/ohioriver_org_chain.crt;
        ssl_certificate_key /home/gqc/ssl_old/ssl_cert_2024/ohioriver_2024.key;

        add_header Access-Control-Allow-Origin * always;
        server_name www.ohioriver.org;

        location = /favicon.ico { access_log off; log_not_found off; }
        location /static/ {
            root /home/gqc/riverflows_django/riverflows/;
        }

        location / {
            if ($scheme = http) {
                return 301 https://$server_name$request_uri;
            }
            include proxy_params;
            proxy_pass http://unix:/run/gunicorn.sock;
        }
    }

  3. sudo service nginx restart

  4. sudo service nginx status

Attach disk to the VM​

Link to official Microsoft Documentation → Link

Detach disk from the VM​

Link to official Microsoft Documentation → Link

Delete disk from the VM​

Link to official Microsoft Documentation → Link

Update data-directory in postgres​

Link to Digital Ocean Tutorial → Link

Restore dump on the VM​

Link to the doc created by Jake → Link

Configure postgres​

listen addresses​

listen_addresses controls which IPs the server will answer on, not which IPs the server will permit connections to authenticate from. It's entirely reasonable and normal to use listen_addresses '*' so the server will accept incoming connections on any ip assigned to an interface on the postgresql server host, while using pg_hba.conf to control access at a finer grained level for which IPs the server will accept logins from for specific databases and users

Reference link: StackOverFlow

add IP addresses​

Add Sudhir, Jake and Deven's ip addresses in the pg_hba.conf file.

Errors​

Creating a new user in postgres​

 sudo su postgres
 psql
 create user gqc with password 'password';
 create database DBNAME;
 alter database DBNAME owner to "gqc"; 
 \q
 exit

Peer authentication failed​

pg_restore: error: connection to database "orsanco" failed: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "postgres"

To fix this, you need to update the pg_hba.conf

From

local   all             postgres                                peer

To

local   all             postgres                                trust

Then restart the server. sudo service postgresql restart

Missing timescaledb extension​

Either add the timescaledb extension in the postgresql.conf in the following fashion.

shared_preload_libraries = 'timescaledb'

Or add the extension manually in the database.

CREATE EXTENSION IF NOT EXISTS timescaledb WITH SCHEMA public;

Stacktrace of the entire error​

gqc@orsanco-flows:~$ pg_restore -d orsanco -1 orsanco-flows-10-plus-2-history-and-daily-02152023.sql
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 2; 3079 156663 EXTENSION timescaledb (no owner)
pg_restore: error: could not execute query: FATAL:  extension "timescaledb" must be preloaded
HINT:  Please preload the timescaledb library via shared_preload_libraries.

This can be done by editing the config file at: /etc/postgresql/13/main/postgresql.conf
and adding 'timescaledb' to the list in the shared_preload_libraries config.
        # Modify postgresql.conf:
        shared_preload_libraries = 'timescaledb'

Another way to do this, if not preloading other libraries, is with the command:
        echo "shared_preload_libraries = 'timescaledb'" >> /etc/postgresql/13/main/postgresql.conf

(Will require a database restart.)

If you REALLY know what you are doing and would like to load the library without preloading, you can disable this check with:
        SET timescaledb.allow_install_without_preload = 'on';
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
Command was: CREATE EXTENSION IF NOT EXISTS timescaledb WITH SCHEMA public;