traccar positions cleanup

delete-positions.sh

#!/bin/bash

DB="traccar"
USER="neondb_owner"
HOST="ep-late-cherry-a460jx6n.us-east-1.aws.neon.tech"
INTERVAL="2 weeks"

max_id=$(psql -h $HOST -U $USER -d $DB -t -A -c "SELECT max(id) FROM tc_devices;")
cutoff=$(psql -h $HOST -U $USER -d $DB -t -A -c "SELECT NOW() - INTERVAL '$INTERVAL';")

echo "Cutoff time = $cutoff"

for (( device_id=$max_id; device_id>=1; device_id-- )); do
  while true; do    
    sql="SELECT id
         FROM tc_positions
         WHERE deviceid = $device_id
           AND fixtime < '$cutoff'
           AND id NOT IN (SELECT positionId FROM tc_devices WHERE positionid IS NOT NULL and id = $device_id)
         LIMIT 10000;"
    echo "$(date) $sql"      
    ids=$(psql -h $HOST -U $USER -d $DB -t -A -c "$sql")
    if [ -z "$ids" ]; then
      break
    fi
    id_list=$(echo "$ids" | paste -sd "," -)
    sql="delete from tc_positions WHERE deviceid=$device_id and  id IN ($id_list);"
    echo "$(date) ${sql:0:100}..."
    echo "$(date) $(psql -h $HOST -U $USER -d $DB -c "$sql")"
  done
done