Introduction I’ve been interested in setting up some home monitoring/automation since getting my first Raspberry Pi a couple of years ago. After using the device for various development projects I finally decided on my first project, try to make a basic home temperature/humidity monitor that cold measure both indoor and outdoors. The idea is to …
Category archives: Analytics
Bitcoin RPC Commands over SSH Tunnel
If you’re running a Bitcoin full node and want to run RPC commands against the Bitcoin client from a remote machine the easiest and safest way to do this is using Port Forwarding over an SSH connection. What is Port Forwarding used for? Secure access to a port that is otherwise not listening on a …
Getting Database and Table Sizes in Postgres
Total Database Size This SQL simply gets the total size of the database in a human readable format. SELECT pg_size_pretty(pg_database_size(‘postgres’)) as db_size List all Tables This lists all the tables in the database public schema. SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = ‘public’ Search Schema for Column Name I often need to search all the …
Continue reading “Getting Database and Table Sizes in Postgres”
Looking Up Offset Rows in PostgreSQL
A common task when either reporting or doing data analysis is to extract data from a database table or view and then lookup corresponding values from either previous or next rows. A good example of this was a recent KPI report I made for E-commerce where the KPI depended not only on the total daily …