c77_dbh/INSTALL.md

# Installation Guide for c77_dbh PostgreSQL Extension

This guide covers multiple installation methods for the c77_dbh PostgreSQL extension, including standard package installation and manual installation options.

## Table of Contents

- [Prerequisites](#prerequisites)
- [Option 1: Standard Installation](#option-1-standard-installation)
- [Option 2: Manual Installation](#option-2-manual-installation)
- [Post-Installation Setup](#post-installation-setup)
- [Verifying Installation](#verifying-installation)
- [Upgrading](#upgrading)
- [Troubleshooting](#troubleshooting)

## Prerequisites

- PostgreSQL 13 or higher
- PostgreSQL development packages (for building from source)
- Root or `sudo` access to the PostgreSQL server
- Access to `psql` with superuser privileges
- Optional extensions for enhanced functionality:
    - `pgstattuple`: For precise bloat estimates
    - `pg_stat_statements`: For detailed query performance metrics
    - `pg_cron`: For automated statistics collection

## Option 1: Standard Installation

### From Repository

1. Clone the repository:
   ```bash
   git clone https://git.jctr3.com/trogers1884/c77_dbh.git
   cd c77_dbh
   ```

2. Build and install:
   ```bash
   make
   sudo make install
   ```

3. Connect to your PostgreSQL database and create the extension:
   ```sql
   CREATE EXTENSION c77_dbh;
   ```

## Option 2: Manual Installation

If you prefer to install the extension manually or if you don't have build tools available, follow these steps:

### Step 1: Prepare the Extension Files

1. Create the extension directory in PostgreSQL's extension directory:
   ```bash
   # Find PostgreSQL's extension directory
   PG_EXT_DIR=$(pg_config --sharedir)/extension
   sudo mkdir -p $PG_EXT_DIR
   ```

2. Create the control file:
   ```bash
   cat << EOF | sudo tee $PG_EXT_DIR/c77_dbh.control
   comment = 'Database health monitoring tools for vacuum, index, and materialized view performance'
   default_version = '1.0'
   module_pathname = '$libdir/c77_dbh'
   relocatable = true
   EOF
   ```

3. Copy the SQL script:
   ```bash
   sudo cp c77_dbh--1.0.sql $PG_EXT_DIR/
   ```

   If you don't have the repository, create the SQL file manually:
   ```bash
   # Create the SQL file with the content from c77_dbh--1.0.sql
   sudo nano $PG_EXT_DIR/c77_dbh--1.0.sql
   
   # Copy the entire SQL file content into this file and save it
   ```

### Step 2: Create Extension in Database

Connect to your PostgreSQL database and create the extension:

```sql
CREATE EXTENSION c77_dbh;
```

## Post-Installation Setup

After installing the extension, you'll need to set up permissions and perform initial data collection:

1. Set permissions (optional):
   ```sql
   -- For read-only access
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
   
   -- For administrative access
   GRANT ALL ON ALL TABLES IN SCHEMA public TO admin_role;
   ```

2. Initialize statistics:
   ```sql
   -- Update vacuum statistics
   SELECT c77_dbh_update_vacuum_stats();
   
   -- Update index statistics
   SELECT c77_dbh_update_index_stats();
   ```

3. Set up automated updates with pg_cron (if installed):
   ```sql
   -- Ensure pg_cron extension is created
   CREATE EXTENSION IF NOT EXISTS pg_cron;
   
   -- Schedule updates every 6 hours
   SELECT cron.schedule('update_vacuum_stats', '0 */6 * * *', 'SELECT c77_dbh_update_vacuum_stats()');
   SELECT cron.schedule('update_index_stats', '0 */6 * * *', 'SELECT c77_dbh_update_index_stats()');
   ```

## Verifying Installation

To verify the extension was installed correctly:

1. Check if the extension exists:
   ```sql
   SELECT * FROM pg_extension WHERE extname = 'c77_dbh';
   ```

2. Verify tables were created:
   ```sql
   \dt c77_dbh_*
   ```

3. Test a function:
   ```sql
   SELECT c77_dbh_update_vacuum_stats();
   SELECT * FROM c77_dbh_vacuum_stats LIMIT 5;
   ```

## Upgrading

When upgrading the extension to a new version:

1. Create the new version SQL script in the extensions directory.
2. Update the extension in the database:
   ```sql
   ALTER EXTENSION c77_dbh UPDATE TO '1.1';
   ```

## Troubleshooting

### Common Issues

1. **Permission Denied Errors**:
    - Ensure you have superuser privileges when creating the extension.
    - Check file permissions on the extension files.

2. **Extension Not Found**:
    - Verify the extension files are in the correct PostgreSQL extension directory.
    - Check the control file for syntax errors.

3. **Dependency Issues**:
    - If you see errors related to pgstattuple or pg_stat_statements, install these optional extensions.

4. **Limited Precision in Reports**:
    - Install pgstattuple extension for more accurate bloat estimates.

### Debugging

If you encounter issues, check the PostgreSQL logs:

```bash
tail -100 /var/log/postgresql/postgresql.log
```

## Additional Resources

- PostgreSQL Documentation on Extensions: [https://www.postgresql.org/docs/current/extend-extensions.html](https://www.postgresql.org/docs/current/extend-extensions.html)
- pgstattuple Documentation: [https://www.postgresql.org/docs/current/pgstattuple.html](https://www.postgresql.org/docs/current/pgstattuple.html)
- pg_stat_statements Documentation: [https://www.postgresql.org/docs/current/pgstatstatements.html](https://www.postgresql.org/docs/current/pgstatstatements.html)