Add postgres version tests to github actions #9
This file contains hidden or 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: Test PostgreSQL Versions | |
| on: | |
| push: | |
| branches: [ master, main ] | |
| pull_request: | |
| branches: [ master, main ] | |
| jobs: | |
| test: | |
| runs-on: ubuntu-latest | |
| strategy: | |
| matrix: | |
| postgres-version: ['13', '14', '15', '16', '17'] | |
| fail-fast: false | |
| services: | |
| postgres: | |
| image: postgres:${{ matrix.postgres-version }} | |
| env: | |
| POSTGRES_PASSWORD: postgres | |
| POSTGRES_DB: test | |
| options: >- | |
| --health-cmd pg_isready | |
| --health-interval 10s | |
| --health-timeout 5s | |
| --health-retries 5 | |
| ports: | |
| - 5432:5432 | |
| steps: | |
| - name: Checkout code | |
| uses: actions/checkout@v4 | |
| - name: Install PostgreSQL client | |
| run: | | |
| # Install PostgreSQL client from Ubuntu repositories (works with all PostgreSQL versions) | |
| sudo apt-get update | |
| sudo apt-get install -y postgresql-client-common postgresql-client-14 | |
| # Verify installation | |
| psql --version | |
| - name: Configure PostgreSQL for pg_stat_statements | |
| run: | | |
| # Wait for PostgreSQL to be ready | |
| until pg_isready -h localhost -p 5432 -U postgres; do | |
| echo "Waiting for postgres..." | |
| sleep 2 | |
| done | |
| # Get container ID | |
| CONTAINER_ID=$(docker ps --filter "expose=5432" --format "{{.ID}}") | |
| echo "PostgreSQL container: $CONTAINER_ID" | |
| # Stop PostgreSQL to modify configuration | |
| docker exec $CONTAINER_ID pg_ctl -D /var/lib/postgresql/data -m fast stop || true | |
| # Configure shared_preload_libraries | |
| docker exec $CONTAINER_ID bash -c "echo \"shared_preload_libraries = 'pg_stat_statements'\" >> /var/lib/postgresql/data/postgresql.conf" | |
| # Configure trust authentication for easier testing | |
| docker exec $CONTAINER_ID bash -c "echo 'host all all all trust' > /var/lib/postgresql/data/pg_hba.conf" | |
| docker exec $CONTAINER_ID bash -c "echo 'local all all trust' >> /var/lib/postgresql/data/pg_hba.conf" | |
| # Restart PostgreSQL | |
| docker exec $CONTAINER_ID pg_ctl -D /var/lib/postgresql/data -l /var/lib/postgresql/data/logfile start | |
| # Wait for PostgreSQL to be ready after restart | |
| sleep 5 | |
| until pg_isready -h localhost -p 5432 -U postgres; do | |
| echo "Waiting for postgres to restart..." | |
| sleep 2 | |
| done | |
| echo "PostgreSQL ${{ matrix.postgres-version }} configured successfully" | |
| - name: Prepare test database | |
| run: | | |
| # Check PostgreSQL version | |
| PGPASSWORD=postgres psql -h localhost -U postgres -d test -c 'SELECT version();' | |
| # Create extensions | |
| PGPASSWORD=postgres psql -h localhost -U postgres -d test -c 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;' | |
| PGPASSWORD=postgres psql -h localhost -U postgres -d test -c 'CREATE EXTENSION IF NOT EXISTS pgstattuple;' | |
| # Verify extensions | |
| PGPASSWORD=postgres psql -h localhost -U postgres -d test -c 'SELECT extname FROM pg_extension ORDER BY extname;' | |
| # Create test tables (exactly as in CircleCI) | |
| PGPASSWORD=postgres psql -h localhost -U postgres -d test -c "CREATE TABLE align1 AS SELECT 1::int4, 2::int8, 3::int4 AS more FROM generate_series(1, 100000) _(i);" | |
| PGPASSWORD=postgres psql -h localhost -U postgres -d test -c "CREATE TABLE align2 AS SELECT 1::int4, 3::int4 AS more, 2::int8 FROM generate_series(1, 100000) _(i);" | |
| - name: Test wide mode | |
| run: | | |
| echo "\set postgres_dba_wide true" > ~/.psqlrc | |
| echo "\set postgres_dba_interactive_mode false" >> ~/.psqlrc | |
| echo "Testing all SQL files in wide mode..." | |
| for f in sql/*; do | |
| echo " Testing $f..." | |
| if ! PGPASSWORD=postgres psql -h localhost -U postgres -d test --no-psqlrc -f warmup.psql -f "$f" > /dev/null 2>&1; then | |
| echo "❌ FAILED: $f in wide mode" | |
| echo "Error output:" | |
| PGPASSWORD=postgres psql -h localhost -U postgres -d test --no-psqlrc -f warmup.psql -f "$f" | |
| exit 1 | |
| fi | |
| done | |
| echo "✅ All tests passed in wide mode" | |
| - name: Test normal mode | |
| run: | | |
| echo "\set postgres_dba_wide false" > ~/.psqlrc | |
| echo "\set postgres_dba_interactive_mode false" >> ~/.psqlrc | |
| echo "Testing all SQL files in normal mode..." | |
| for f in sql/*; do | |
| echo " Testing $f..." | |
| if ! PGPASSWORD=postgres psql -h localhost -U postgres -d test --no-psqlrc -f warmup.psql -f "$f" > /dev/null 2>&1; then | |
| echo "❌ FAILED: $f in normal mode" | |
| echo "Error output:" | |
| PGPASSWORD=postgres psql -h localhost -U postgres -d test --no-psqlrc -f warmup.psql -f "$f" | |
| exit 1 | |
| fi | |
| done | |
| echo "✅ All tests passed in normal mode" | |
| - name: Run regression tests | |
| run: | | |
| echo "\set postgres_dba_wide false" > ~/.psqlrc | |
| echo "\set postgres_dba_interactive_mode false" >> ~/.psqlrc | |
| echo "Running regression tests..." | |
| echo " Testing 0_node.sql..." | |
| diff -b test/regression/0_node.out <(PGPASSWORD=postgres psql -h localhost -U postgres -d test --no-psqlrc -f warmup.psql -f sql/0_node.sql | grep Role) | |
| echo " Testing p1_alignment_padding.sql..." | |
| diff -b test/regression/p1_alignment_padding.out <(PGPASSWORD=postgres psql -h localhost -U postgres -d test --no-psqlrc -f warmup.psql -f sql/p1_alignment_padding.sql | grep align) | |
| echo " Testing a1_activity.sql..." | |
| diff -b test/regression/a1_activity.out <(PGPASSWORD=postgres psql -h localhost -U postgres -d test --no-psqlrc -f warmup.psql -f sql/a1_activity.sql | grep User) | |
| echo "✅ All regression tests passed" | |
| test-beta: | |
| runs-on: ubuntu-latest | |
| continue-on-error: true # Allow beta tests to fail without breaking CI | |
| strategy: | |
| matrix: | |
| postgres-version: ['18-beta', '19-beta'] | |
| fail-fast: false | |
| services: | |
| postgres: | |
| image: postgres:${{ matrix.postgres-version }} | |
| env: | |
| POSTGRES_PASSWORD: postgres | |
| POSTGRES_DB: test | |
| options: >- | |
| --health-cmd pg_isready | |
| --health-interval 10s | |
| --health-timeout 5s | |
| --health-retries 5 | |
| ports: | |
| - 5432:5432 | |
| steps: | |
| - name: Checkout code | |
| uses: actions/checkout@v4 | |
| - name: Install PostgreSQL client | |
| run: | | |
| # Install PostgreSQL client from Ubuntu repositories (works with all PostgreSQL versions) | |
| sudo apt-get update | |
| sudo apt-get install -y postgresql-client-common postgresql-client-14 | |
| # Verify installation | |
| psql --version | |
| - name: Configure PostgreSQL for pg_stat_statements | |
| run: | | |
| # Wait for PostgreSQL to be ready | |
| until pg_isready -h localhost -p 5432 -U postgres; do | |
| echo "Waiting for postgres..." | |
| sleep 2 | |
| done | |
| # Get container ID | |
| CONTAINER_ID=$(docker ps --filter "expose=5432" --format "{{.ID}}") | |
| echo "PostgreSQL container: $CONTAINER_ID" | |
| # Stop PostgreSQL to modify configuration | |
| docker exec $CONTAINER_ID pg_ctl -D /var/lib/postgresql/data -m fast stop || true | |
| # Configure shared_preload_libraries | |
| docker exec $CONTAINER_ID bash -c "echo \"shared_preload_libraries = 'pg_stat_statements'\" >> /var/lib/postgresql/data/postgresql.conf" | |
| # Configure trust authentication | |
| docker exec $CONTAINER_ID bash -c "echo 'host all all all trust' > /var/lib/postgresql/data/pg_hba.conf" | |
| docker exec $CONTAINER_ID bash -c "echo 'local all all trust' >> /var/lib/postgresql/data/pg_hba.conf" | |
| # Restart PostgreSQL | |
| docker exec $CONTAINER_ID pg_ctl -D /var/lib/postgresql/data -l /var/lib/postgresql/data/logfile start | |
| # Wait for PostgreSQL to be ready after restart | |
| sleep 5 | |
| until pg_isready -h localhost -p 5432 -U postgres; do | |
| echo "Waiting for postgres to restart..." | |
| sleep 2 | |
| done | |
| echo "PostgreSQL ${{ matrix.postgres-version }} configured successfully" | |
| - name: Prepare test database | |
| run: | | |
| # Check PostgreSQL version | |
| PGPASSWORD=postgres psql -h localhost -U postgres -d test -c 'SELECT version();' | |
| # Create extensions | |
| PGPASSWORD=postgres psql -h localhost -U postgres -d test -c 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements;' | |
| PGPASSWORD=postgres psql -h localhost -U postgres -d test -c 'CREATE EXTENSION IF NOT EXISTS pgstattuple;' | |
| # Create test tables | |
| PGPASSWORD=postgres psql -h localhost -U postgres -d test -c "CREATE TABLE align1 AS SELECT 1::int4, 2::int8, 3::int4 AS more FROM generate_series(1, 100000) _(i);" | |
| PGPASSWORD=postgres psql -h localhost -U postgres -d test -c "CREATE TABLE align2 AS SELECT 1::int4, 3::int4 AS more, 2::int8 FROM generate_series(1, 100000) _(i);" | |
| - name: Test wide mode (beta) | |
| run: | | |
| echo "\set postgres_dba_wide true" > ~/.psqlrc | |
| echo "\set postgres_dba_interactive_mode false" >> ~/.psqlrc | |
| echo "Testing all SQL files in wide mode (PostgreSQL ${{ matrix.postgres-version }})..." | |
| for f in sql/*; do | |
| echo " Testing $f..." | |
| PGPASSWORD=postgres psql -h localhost -U postgres -d test --no-psqlrc -f warmup.psql -f "$f" > /dev/null 2>&1 || echo " ⚠️ Warning: $f failed in wide mode (beta)" | |
| done | |
| echo "✅ Wide mode tests completed (beta)" | |
| - name: Test normal mode (beta) | |
| run: | | |
| echo "\set postgres_dba_wide false" > ~/.psqlrc | |
| echo "\set postgres_dba_interactive_mode false" >> ~/.psqlrc | |
| echo "Testing all SQL files in normal mode (PostgreSQL ${{ matrix.postgres-version }})..." | |
| for f in sql/*; do | |
| echo " Testing $f..." | |
| PGPASSWORD=postgres psql -h localhost -U postgres -d test --no-psqlrc -f warmup.psql -f "$f" > /dev/null 2>&1 || echo " ⚠️ Warning: $f failed in normal mode (beta)" | |
| done | |
| echo "✅ Normal mode tests completed (beta)" | |