gplv2

TL;DR

Create a backup:

sudo -Hiu postgres pg_dumpall > mybackup.sql

Install Postgres 11, and then:

gplv2

# This is the car code for the remote controlled Hot Rod
# (c) Anton's Mindstorms & Ste7an

# Full tutorial here:
# https://antonsmindstorms.com/2021/06/19/how-to-remote-control-lego-spike-prime-and-robot-inventor-with-python/

# Building instructions here:
# https://antonsmindstorms.com/product/remote-controlled-hot-rod-with-51515/

# Use with the the remote control script here:

gplv2

Prerequisite
One instance of Alfresco on mysql, one postgresl of exactly the same version (schema version).

Step 1. Dump database
mysqldump --port 3306 -u alfresco --password=alfloftux -h 127.0.0.1 --databases alfresco  --skip-comments --skip-extended-insert --no-create-db --hex-blob --default-character-set=utf8 --skip-triggers --compact --no-create-info --skip-quote-names > mydump.sql

Step 2. Dump local Postgres schema
You can install a clean version of Alfresco to use as for schema dump
(need to insert command for dump here)

gplv2

process.on
(
	'uncaughtException',
	function (err)
	{
		var stack = err.stack;
		var timeout = 1;
		
		// print note to logger
		logger.log("SERVER CRASHED!");

gplv2

------------
-- Locks  --
------------
    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    

gplv2

-- Create a group
CREATE ROLE readaccess;

-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

gplv2

import csv
import glob
import os

# gpx layout from https://www.gps-data-team.com/convert/
gpx_header = '<?xml version="1.0" encoding="ISO-8859-2" standalone="no" ?><gpx:gpx creator="csv2DaimlerGPX" version="1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:gpx="http://www.topografix.com/GPX/1/1" xsi:schemaLocation="http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd" xmlns:gpxd="http://www.daimler.com/DaimlerGPXExtensions/V2.4">'
gpx_entry = '<gpx:wpt lat="{lat}" lon="{lon}"><gpx:name>{name}</gpx:name><gpx:extensions><gpxd:WptExtension><gpxd:WptIconId IconId="16"></gpxd:WptIconId><gpxd:POICategory Cat="Speed Cameras"></gpxd:POICategory><gpxd:Activity Active="true" Level="warning" Unit="second" Value="15"></gpxd:Activity><gpxd:Presentation ShowOnMap="true"></gpxd:Presentation><gpxd:Address ISO="" Country="" State="" City="" CityCenter="" Street="" Street2="" HouseNo="" ZIP=""></gpxd:Address><gpxd:Phone Default=""></gpxd:Phone></gpxd:WptExtension></gpx:extensions></gpx:wp

gplv2

# haproxy postgresql master check
#
# haproxy listen: 5432
# pg, instance #1 listen: 5432 (master node)
# pg, instance #2 listen: 5432 (replica node)
# external failover, promoting replica to master in case of failure
# passwordless auth for user web
# template1 database is accessible by user web
#
# haproxy will pass connection to postgresql master node:

gplv2

gplv2

-- performance tools
-- https://www.vividcortex.com/resources/network-analyzer-for-postgresql

-- show running queries (pre 9.2)
SELECT procpid, age(query_start, clock_timestamp()), usename, current_query 
FROM pg_stat_activity 
WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

-- show running queries (post 9.2)