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="4 months"

device_ids=$(psql -h $HOST -U $USER -d $DB -t -A -c "SELECT id FROM tc_devices;")

for device_id in $device_ids; do
  while true; do
    
    sql="SELECT id FROM tc_positions WHERE deviceid = $device_id AND fixtime < NOW() - INTERVAL '$INTERVAL' 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:80}..."

    delete_result=$(psql -h $HOST -U $USER -d $DB -c "$sql")

    echo "$(date) $delete_result"
  done
done

export PGOPTIONS='-c idle_in_transaction_session_timeout=0'

pg_repack -h $HOST -U $USER -d $DB --table=tc_positions -k -e