Databases@Powered by Passion

Month: April 2024

  • Secure PostgreSQL: Database, Patroni, Etcd and Pgbackrest

    Secure PostgreSQL: Database, Patroni, Etcd and Pgbackrest

    —

    by

    Osman DİNÇ
    ,

    This guide provides a comprehensive approach to secure the PostgreSQL environment, including the database, Patroni, Etcd, and Pgbackrest. It covers key aspects such as authentication, encryption, auditing, parameter hardening, patch management, network security, and backup strategies along with securing Patroni REST APIs and Etcd’s SSL configuration. Additionally, it emphasizes the importance of securing PGBackrest endpoints with TLS encryption to protect sensitive data during backup processes, ensuring comprehensive security for the entire PostgreSQL database environment.

  • “spatial_vector_acceleration” parameter  improves Oracle spatial vector query performance

    “spatial_vector_acceleration” parameter  improves Oracle spatial vector query performance

    —

    by

    Osman DİNÇ
    , ,

    Oracle Database 12c and above introduce significant speed improvements for spatial operations. The SPATIAL_VECTOR_ACCELERATION parameter, set to TRUE, enhances index performance, geometry engine efficiency, and CPU and memory usage. Oracle Spatial and Graph is included in the Oracle Database license at no extra cost. Rebuilding spatial indexes with INDEXTYPE=MDSYS.SPATIAL_INDEX_V2 can simplify index management and improve performance for specific queries.

  • How to Upgrade PostgreSQL, PostGIS and Patroni in Offline Environment

    How to Upgrade PostgreSQL, PostGIS and Patroni in Offline Environment

    —

    by

    Osman DİNÇ
    ,

    This blog post outlines the process of upgrading a PostgreSQL environment with no internet, covering major and minor upgrades, as well as updates to various extensions. It details the specific steps for upgrading each component, including Patroni, pgbackrest, PostGIS, pg_cron, and Oracle_FDW. The post emphasizes the importance of careful planning and thorough testing for major upgrades, along with considerations for downtime and data replication. Additionally, it provides guidance on memory resource management and configuration adjustments during the upgrade process.