upgrade ansible automation platform database

[kayiwa]

Description:

This issue outlines the plan to upgrade the Ansible Automation Platform (AAP) database from its current End-of-Life (EOL) Bionic Operating System to PostgreSQL 15. Ubuntu Bionic has reached EOL, meaning it no longer receives security updates, making this upgrade crucial for security and stability.

Current AAP does not support postgreSQL above 13

Benefits:

• sunset lib-postgres3 this is running ubuntu bionic
• Enhanced Security: Upgrading to PostgreSQL 15 ensures that the AAP database benefits from the latest security patches and bug fixes.
• Improved Performance: PostgreSQL 15 offers performance improvements over older versions, potentially leading to faster automation execution and improved responsiveness.
• New Features: Take advantage of new features and enhancements in PostgreSQL 15 to improve database management and efficiency.
• Continued Support: Ensure ongoing compatibility and support from the PostgreSQL community.

Tasks:

1. Assess Current Environment:

   • Document the current AAP version and database configuration (including extensions).
   • Analyze the existing database size and usage patterns.
   • Identify any potential challenges or dependencies.

2. Plan the Upgrade:

   • upgrade Ansible Automation Platform to current
   • Choose a suitable PostgreSQL 15 installation method (e.g., from repository, source).
   • Determine the appropriate PostgreSQL 15 server sizing and configuration.
   • Define a backup and restore strategy for the existing database.
   • Establish a rollback plan in case of issues during the upgrade.

3. Prepare the PostgreSQL 15 Server:

   • Install and configure the PostgreSQL 15 server on a separate instance or VM.
   • Create a dedicated database and user for AAP.
   • Configure necessary access controls and security settings.
   • Install any required extensions used by AAP.

4. Migrate the Database:

   • Option 1: pg_upgrade
     • Back up the existing AAP database.
     • Use pg_upgrade to migrate the data directory to the PostgreSQL 15 cluster.
     • Verify the new cluster and analyze for performance.
   • Option 2: Logical Backup and Restore
     • Back up the existing AAP database using pg_dumpall.
     • Restore the backup into the new PostgreSQL 15 database using psql.

5. Update AAP Configuration:

   • Update the AAP configuration to point to the new PostgreSQL 15 database.
   • Verify database connections and test basic AAP functionality.

6. Testing and Validation:

   • Thoroughly test all AAP functionalities after the database migration.
   • Verify data integrity and consistency.
   • Monitor system performance and stability.

7. Documentation and Communication:

   • Update relevant documentation to reflect the new database configuration.
   • Communicate the upgrade plan and timeline to stakeholders.
   • Provide post-upgrade support and guidance.

Timeline:

• [Start Date] - [End Date]: Complete the assessment and planning phase.
• [Start Date] - [End Date]: Prepare the PostgreSQL 15 server.
• [Start Date] - [End Date]: Perform the database migration and AAP configuration update.
• [Start Date] - [End Date]: Complete testing and validation.
• [Start Date] - [End Date]: Finalize documentation and communication.

Resources:

• Ansible Automation Platform documentation: [Link to relevant AAP documentation]
• PostgreSQL documentation: [Link to PostgreSQL 15 documentation]
• pg_upgrade guide: [Link to pg_upgrade documentation]