Scheduled Weekly Synapse Reset #13
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: Scheduled Weekly Synapse Reset | |
| on: | |
| schedule: | |
| - cron: '0 0 * * 0' # Runs every week at midnight UTC | |
| jobs: | |
| cleanup-synapse-db: | |
| runs-on: ubuntu-latest | |
| steps: | |
| - name: 'Checkout GitHub Action' | |
| uses: actions/checkout@v4.1.7 | |
| - name: Install Kubectl | |
| uses: azure/setup-kubectl@v4.0.0 | |
| with: | |
| version: 'v1.29.6' # Ensure this matches the version used in your cluster | |
| - name: Set up Kubeconfig for Hetzner k3s | |
| run: | | |
| mkdir -p $HOME/.kube # Ensure the .kube directory exists | |
| echo "${{ secrets.KUBECONFIG_SECRET_HETZNER_TEST }}" > $HOME/.kube/config | |
| chmod 600 $HOME/.kube/config | |
| - name: Install PostgreSQL client | |
| run: sudo apt-get update && sudo apt-get install -y postgresql-client | |
| - name: Port-forward to PostgreSQL service and reset database | |
| env: | |
| POSTGRES_USER: ${{ secrets.POSTGRES_USER }} | |
| POSTGRES_PASSWORD: ${{ secrets.POSTGRES_PASSWORD }} | |
| POSTGRES_DB: ${{ secrets.POSTGRES_DB }} | |
| run: | | |
| # Port-forward to the PostgreSQL service in the background | |
| kubectl port-forward svc/postgres 5432:5432 & | |
| PORT_FORWARD_PID=$! | |
| sleep 5 # Give port-forwarding some time to start | |
| # Drop all connections to the target database | |
| echo "Dropping all connections to the database $POSTGRES_DB" | |
| PGPASSWORD=$POSTGRES_PASSWORD psql -h 127.0.0.1 -p 5432 -U $POSTGRES_USER -d postgres -c \ | |
| "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '$POSTGRES_DB' AND pid <> pg_backend_pid();" | |
| # Wait a bit to ensure connections are fully terminated | |
| echo "Waiting for connections to close..." | |
| sleep 10 # Increase wait time to give PostgreSQL time to release all connections | |
| # Retry mechanism to ensure the database is dropped | |
| DROP_ATTEMPTS=5 | |
| for i in $(seq 1 $DROP_ATTEMPTS); do | |
| echo "Attempt $i to drop the database $POSTGRES_DB" | |
| # Check if there are still any connections to the database | |
| ACTIVE_CONNECTIONS=$(PGPASSWORD=$POSTGRES_PASSWORD psql -h 127.0.0.1 -p 5432 -U $POSTGRES_USER -d postgres -t -c \ | |
| "SELECT COUNT(*) FROM pg_stat_activity WHERE datname = '$POSTGRES_DB';") | |
| if [[ "$ACTIVE_CONNECTIONS" -eq 0 ]]; then | |
| # Drop the database only if there are no active connections | |
| DROP_RESULT=$(PGPASSWORD=$POSTGRES_PASSWORD psql -h 127.0.0.1 -p 5432 -U $POSTGRES_USER -d postgres -c \ | |
| "DROP DATABASE IF EXISTS $POSTGRES_DB;") | |
| if [[ $DROP_RESULT == *"DROP DATABASE"* ]]; then | |
| echo "Database $POSTGRES_DB dropped successfully." | |
| break | |
| else | |
| echo "Failed to drop the database. Retrying in 5 seconds..." | |
| sleep 5 | |
| fi | |
| else | |
| echo "There are still $ACTIVE_CONNECTIONS active connections. Retrying in 5 seconds..." | |
| sleep 5 | |
| fi | |
| done | |
| # Recreate the target database | |
| echo "Creating the database $POSTGRES_DB" | |
| PGPASSWORD=$POSTGRES_PASSWORD psql -h 127.0.0.1 -p 5432 -U $POSTGRES_USER -d postgres -c "CREATE DATABASE $POSTGRES_DB;" | |
| # Kill the port-forward process | |
| kill $PORT_FORWARD_PID | |
| restart-synapse-server: | |
| needs: cleanup-synapse-db | |
| runs-on: ubuntu-latest | |
| steps: | |
| - name: Install Kubectl | |
| uses: azure/setup-kubectl@v4.0.0 | |
| with: | |
| version: 'v1.29.6' # Ensure this matches the version used in your cluster | |
| - name: Set up Kubeconfig for Hetzner k3s | |
| run: | | |
| mkdir -p $HOME/.kube # Ensure the .kube directory exists | |
| echo "${{ secrets.KUBECONFIG_SECRET_HETZNER_TEST }}" > $HOME/.kube/config | |
| chmod 600 $HOME/.kube/config | |
| - name: Restart deployment | |
| run: | | |
| # Restart the deployment | |
| kubectl rollout restart deployment/synapse-deployment | |
| # Wait for the deployment to complete successfully | |
| kubectl rollout status deployment/synapse-deployment --timeout=600s | |
| restart-matrix-adapter: | |
| needs: restart-synapse-server | |
| runs-on: ubuntu-latest | |
| steps: | |
| - name: Install Kubectl | |
| uses: azure/setup-kubectl@v4.0.0 | |
| with: | |
| version: 'v1.29.6' # Ensure this matches the version used in your cluster | |
| - name: Set up Kubeconfig for Hetzner k3s | |
| run: | | |
| mkdir -p $HOME/.kube # Ensure the .kube directory exists | |
| echo "${{ secrets.KUBECONFIG_SECRET_HETZNER_TEST }}" > $HOME/.kube/config | |
| chmod 600 $HOME/.kube/config | |
| - name: Restart deployment | |
| run: | | |
| # Restart the deployment | |
| kubectl rollout restart deployment/alkemio-matrix-adapter-deployment | |
| # Wait for the deployment to complete successfully | |
| kubectl rollout status deployment/alkemio-matrix-adapter-deployment --timeout=600s |