Essential PostgreSQL Environment Variables Every DBA Should Know


PostgreSQL Environment Variables

PGHOST
Defines the hostname of the server to connect to. If empty, connects using a Unix domain socket.
PGPORT
Specifies the TCP port (default is 5432) or the socket file extension.
PGDATABASE
Sets the default database name to connect to if none is specified.
PGUSER
Specifies the default PostgreSQL user (role) for authentication.
PGPASSWORD
Supplies the password used if the server requires password authentication. (Note: Avoid using in production for security reasons.)
PGREALM
Overrides the default Kerberos realm for authentication.
PGOPTIONS
Passes additional command-line options to the backend server (e.g., -c work_mem=4MB).
PGTTY
sets the file or tty on which debugging messages from the backend server are displayed
PGSERVICE
Refers to a service name defined in a .pg_service.conf file to simplify connection strings.
PGSERVICEFILE
Specifies the path to a custom .pg_service.conf file.
PGSSLMODE
Controls SSL usage. Options: disable, allow, prefer, require, verify-ca, verify-full.
PGSSLROOTCERT
Path to the root certificate file for verifying the server certificate.
PGSSLCRL
Specifies the certificate revocation list file used during SSL verification.
PGKRBSRVNAME
Kerberos service name (default is “postgres”). Used in GSSAPI authentication. 

Comments

Post a Comment

Popular posts from this blog

Azure Data Architecture Patterns for Scalable Data Solutions

Image
  In the modern data-driven world, businesses rely on Azure Data Architecture Patterns that are built for scale, speed, and flexibility. Microsoft Azure provides a wide range of powerful cloud-native services that enable data teams to design robust systems for analytics, real-time processing, and storage across hybrid and multi-cloud environments. These Azure Data Architecture Patterns empower organizations to handle diverse workloads—whether it's streaming IoT data or managing enterprise-wide reporting pipelines. 1. Lambda Architecture – Unified Batch and Real-Time Processing When to Use: Ideal for systems needing both historical and real-time data analysis. Key Azure Services: Real-Time: Azure Stream Analytics, Azure Event Hubs Batch: Azure Data Factory, Azure Data Lake Storage, Azure Synapse Analytics How It Works: Speed Layer: Handles real-time data for immediate insights. Batch Layer: Processes large volumes of historical data. Serving Layer: Merges both to present a un...
Read more

VACUUM, ANALYZE, and VACUUM FULL command in PostgreSQL DBA

Image
In PostgreSQL, VACUUM , ANALYZE , and VACUUM FULL are essential maintenance operations that help optimize performance and manage database storage. Here's a concise explanation with examples tailored for a PostgreSQL DBA: What is MVCC in PostgreSQL? MVCC stands for Multi-Version Concurrency Control. It's a core concept in PostgreSQL that allows multiple users to read and write to the database at the same time—without locking each other out. MVCC enables concurrent access to the database while maintaining data consistency and isolation as defined by the ACID properties. Instead of locking rows when a user accesses them, PostgreSQL: Keeps multiple versions of a row (called tuples ) in the database. Each row has hidden metadata:             xmin = ID of the transaction that created the row             xmax = ID of the transaction that deleted or updated the row PostgreSQL uses MVCC (Multi-Version Concurrency Control) to ...
Read more

REINDEX and REINDEX CONCURRENTLY in PostgrSQL DBA

Image
REINDEX and REINDEX CONCURRENTLY In PostgreSQL,  REINDEX  and  REINDEX CONCURRENTLY  are used to rebuild corrupt or bloated indexes. They are essential tools for database maintenance and performance tuning. REINDEX: In practice indexes can become corrupted due to software bugs or hardware failures.  REINDEX  provides a recovery method. REINDEX  provides a way to reduce the space consumption of the index by writing a new version of the index without the dead pages. REINDEX   rebuilds indexes  from scratch. It's commonly used when: Indexes are bloated (due to many  UPDATE / DELETE  operations). Index corruption is suspected. After a major PostgreSQL version upgrade (especially via  pg_upgrade ). To improve performance of certain queries. Parameters REINDEX INDEX index_name; REINDEX TABLE table_name; REINDEX DATABASE db_name; REINDEX SYSTEM db_name; REINDEX CONCURRENTLY: REINDEX CONCURRENTLY  was introduced in  Postgre...
Read more