marcomalva

#!/bin/bash
#
# launch datascience-notebook jupypter notebook as podman container with read/write to current folder (~/work folder inside container)
#
# once launched open notebook in web-browser under: http://localhost:10000
# copy token from podman stdout
#
# based on official web site, see https://jupyter-docker-stacks.readthedocs.io/en/latest/using/running.html#using-the-podman-cli
# see also: [Running rootless Podman as a non-root user | Enable Sysadmin](https://www.redhat.com/sysadmin/rootless-podman-makes-sense)
#

marcomalva

select pid as process_id, 
       usename as user_name, 
       datname as database_name, 
       client_addr as client_address, 
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity
where datname like '%'

marcomalva

Analysis of Bioacoustic Recordings

Just a collection of links related to the "analysis of bioacoustic recordings".

It is a wide field and requires knowledge in signal processing, statsitical analysis, possibly machine learning (e.g. random forest).

There are some stand-alone application like "Raven Sound Analysis" and OpenSoundscape or extensions to standard statistical software R. Many librarys and tools are written in Python. Just have a look at the link below to see how many differnt application, tools,

marcomalva

-- list database locks and blocked apps/pids, source: <https://wiki.postgresql.org/wiki/Lock_Monitoring>
SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process,
         blocked_activity.application_name AS blocked_application,
         blocking_activity.application_name AS blocking_application
   FROM  pg_catalog.pg_locks         blocked_locks

marcomalva

-- list databse locks hold by SQL statements for current DB instance, source: <https://wiki.postgresql.org/wiki/Lock_Monitoring>
SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.query,
         a.query_start,
         age(now(), a.query_start) AS "age",

marcomalva

-- Returns table with JSON field name, field type, min, and max occurances
-- Change "your_source_table" to the table with the JSON/JSONB column(s)
--
-- CTE to get "list" of JSON object entities to analyze (here: location or items)
-- Assumes that column raw_order is of type JSONB
-- Modify with complex JSON field name in the CTE to match the one you want to analyze
--
WITH tp AS (
   SELECT s.raw_order->'location' AS raw_order                       -- location is an object field
   -- SELECT jsonb_array_elements(s.raw_order->'items') AS raw_order -- use this if location field is a array instead of an object

marcomalva

SELECT * 
FROM stl_load_errors s
-- WHERE s.filename LIKE '%'
ORDER BY s.starttime DESC
LIMIT 5;

marcomalva

#!/bin/bash
#
# extract schema and then records for the tables listed in tables.lst, skipping entries starting with a #
# env var db_host, db_port, db_user and db_name control which database server/instance/role to use
#
[[ -z "${db_host}" ]] && echo "must set env var db_host. exit" && exit
[[ -z "${db_user}" ]] && echo "must set env var db_user. exit" && exit
[[ -z "${db_name}" ]] && echo "must set env var db_name. exit" && exit

# extract schema only of tables listed in tables.lst file

marcomalva

-- Refresh All Materialized Views In Proper Order
--
-- original: [Refresh All Materialized Views - PostgreSQL wiki](https://wiki.postgresql.org/wiki/Refresh_All_Materialized_Views)
-- modified: removed ownername and pg_authid due to error "permission denied for table pg_authid" on the Google cloud PostgreSQL 11.14
--
-- It is best to put these views into a specific "operations" realated schema, like:
--
--    CREATE SCHMEMA IF NOT EXISTS ops;
--    SET search_path = ops, pg_catalog;
--

marcomalva

# see also: 
# * to export public key: <https://mirrors.tripadvisor.com/centos-vault/4.5/docs/html/rhel-sbs-en-4/s1-gnupg-export.html>
# * to extract private key to another machine: <https://makandracards.com/makandra-orga/37763-gpg-extract-private-key-and-import-on-different-machine>
# * to check key expiration date: <https://stackoverflow.com/questions/48914338/how-to-get-expiration-date-from-a-gpg-key>
# * to encrypt/decrypt: <https://www.gnupg.org/gph/en/manual/x110.html>
#

# list public keys
gpg --list-keys