Tag: PostgreSQL

PostgreSQL Link to heading

This section documents database administration, performance tuning, and schema design for PostgreSQL clusters.

🎛 Core Administration Link to heading

  • Configuration: Tuning postgresql.conf (shared_buffers, work_mem) for hardware resources.
  • Maintenance: Vacuum strategies, index bloat management, and analyzing statistics.
  • Security: Role-based access control (RBAC), pg_hba.conf rules, and TLS encryption.

🛠 Operations & HA Link to heading

  • Replication: Streaming replication setup, logical replication, and failover management (Patroni/Repmgr).
  • Backup/Restore: Point-in-time recovery (PITR) using tools like pgBackRest or Barman.
  • Extensions: Management of extensions like PostGIS or pg_stat_statements.

📚 External Resources Link to heading

  • PostgreSQL - Create Role for a Database
    1 min read

    PostgreSQL - Create Role for a Database Problem I want to create a role that has all permissions on a database (owner of such database) so the application can do whatever it needs within the db. Solution First PostgreSQL …

    #PostgreSQL
  • PostgreSQL - Create UTF-8 Database
    1 min read

    PostgreSQL - Create UTF-8 Database Problem I want to create a new database ensuring encoding is set to UTF-8. Solution CREATE DATABASE "db_name" WITH ENCODING = 'UTF8'; Related PostgreSQL - Create Role for a Database

    #PostgreSQL
  • PostgreSQL - psql using URI
    1 min read

    Problem I have a PostgreSQL URI for one of my applications, a URI such as postgres://username:password@hostname:port/database-name and I need to connect to the database from the command-line Solution I used to break the …

    #PostgreSQL
  • PostgreSQL - Read-Only PSQL Session
    1 min read

    Problem I want to connect to a database ensuring that I cannot perform any write operations Solution Connect to a PostgreSQL server with psql or your graphical interface. This guide has only been tested with psql. …

    #PostgreSQL