• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

TinyGrab

Your Trusted Source for Tech, Finance & Brand Advice

  • Personal Finance
  • Tech & Social
  • Brands
  • Terms of Use
  • Privacy Policy
  • Get In Touch
  • About Us
Home » How to run PostgreSQL in Linux?

How to run PostgreSQL in Linux?

June 4, 2025 by TinyGrab Team Leave a Comment

Table of Contents

Toggle
  • Running PostgreSQL Like a Pro on Linux: A Deep Dive
    • Installation: Laying the Foundation
      • Using Your Distribution’s Package Manager
      • Verifying Installation
    • Initialization: Setting Up the Data Directory
      • Initializing the Database Cluster
      • Understanding the Data Directory
    • Starting the PostgreSQL Server: Firing It Up
      • Using Systemd (Recommended)
      • Alternative Methods (If Systemd is Unavailable)
    • Connecting to PostgreSQL: Making the Connection
      • Using psql (The PostgreSQL Command-Line Client)
      • Creating a New User and Database
    • Configuration: Fine-Tuning for Performance and Security
      • Key Configuration Parameters
      • Editing postgresql.conf
      • pg_hba.conf: Controlling Client Authentication
    • Frequently Asked Questions (FAQs)

Running PostgreSQL Like a Pro on Linux: A Deep Dive

So, you want to run PostgreSQL on Linux? Excellent choice! You’ve picked a rock-solid database and a versatile operating system. The process, while straightforward, benefits from a nuanced understanding to ensure optimal performance and security. In essence, running PostgreSQL on Linux involves these core steps: installation, initialization, starting the server, connecting to the database, and configuring for your specific needs. Let’s unpack each of these with the detailed insights you need.

Installation: Laying the Foundation

The most common and recommended approach is using your distribution’s package manager. This streamlines the process and ensures dependencies are handled gracefully.

Using Your Distribution’s Package Manager

  • Debian/Ubuntu (apt): Open your terminal and execute:

    sudo apt update sudo apt install postgresql postgresql-contrib 

    postgresql installs the core database server, while postgresql-contrib provides a collection of useful add-ons and utilities.

  • Red Hat/CentOS/Fedora (yum/dnf): Depending on your distribution, use either yum or dnf:

    sudo dnf install postgresql-server postgresql-contrib  # Fedora or CentOS 8+ sudo yum install postgresql-server postgresql-contrib  # CentOS 7 and older 

    postgresql-server installs the server, and again, postgresql-contrib adds extra functionality.

  • SUSE (zypper): The command is:

    sudo zypper install postgresql postgresql-server 

    Here, postgresql often includes both client and server components.

Verifying Installation

After installation, confirm that PostgreSQL is installed correctly:

psql --version 

This should display the PostgreSQL version number. If you encounter an error, double-check the installation steps and ensure your package manager is working correctly.

Initialization: Setting Up the Data Directory

Before you can start PostgreSQL, you need to initialize the data directory. This creates the necessary files and directories to store your database data.

Initializing the Database Cluster

Use the postgresql-setup command (Red Hat-based systems) or the pg_ctl command (more universal) to initialize the database cluster.

  • Red Hat/CentOS/Fedora:

    sudo postgresql-setup --initdb 

    This command initializes the default data directory (usually /var/lib/pgsql/data).

  • Debian/Ubuntu: The initialization is often handled automatically during package installation. If not, you can try:

    sudo pg_ctlcluster <version> main start  # Replace <version> with your PostgreSQL version (e.g., 14) 
  • SUSE:

    sudo systemctl start postgresql 

    SUSE usually handles initialization automatically as well.

Understanding the Data Directory

The data directory is crucial. It contains all your database data, configuration files, and transaction logs. Backing up this directory is essential for disaster recovery. By default, it’s often located at /var/lib/pgsql/<version>/data (Red Hat) or /var/lib/postgresql/<version>/main (Debian/Ubuntu).

Starting the PostgreSQL Server: Firing It Up

With the data directory initialized, you’re ready to start the PostgreSQL server.

Using Systemd (Recommended)

Most modern Linux distributions use systemd for service management. This makes starting, stopping, and enabling PostgreSQL incredibly easy.

  • Start the server:

    sudo systemctl start postgresql 
  • Stop the server:

    sudo systemctl stop postgresql 
  • Check the server status:

    sudo systemctl status postgresql 

    This command shows whether the server is running, along with recent log messages.

  • Enable the server to start on boot:

    sudo systemctl enable postgresql 

    This ensures that PostgreSQL automatically starts whenever your system boots.

Alternative Methods (If Systemd is Unavailable)

If you’re on an older system without systemd, you can use the pg_ctl command:

pg_ctl -D /path/to/your/data/directory start 

Replace /path/to/your/data/directory with the actual path to your PostgreSQL data directory.

Connecting to PostgreSQL: Making the Connection

Once the server is running, you need to connect to it to create databases and manage data.

Using psql (The PostgreSQL Command-Line Client)

psql is the primary command-line interface for interacting with PostgreSQL. It’s included with the postgresql package.

  • Connect to the default database as the postgres user:

    sudo -u postgres psql 

    This command switches to the postgres user (the default PostgreSQL administrator) and connects to the default postgres database.

  • Connect to a specific database:

    sudo -u postgres psql <database_name> 

    Replace <database_name> with the name of the database you want to connect to.

  • Connect with a specific user:

    psql -U <username> -d <database_name> -h <hostname> 

    Replace <username>, <database_name>, and <hostname> with the appropriate values. The -h option specifies the hostname (usually localhost for local connections).

Creating a New User and Database

It’s generally a good practice to create a new user and database for your applications, rather than using the postgres user directly.

  • Create a new user:

    CREATE USER <username> WITH PASSWORD '<password>'; 

    Execute this command within psql while connected as the postgres user. Replace <username> and <password> with your desired values.

  • Create a new database:

    CREATE DATABASE <database_name> OWNER <username>; 

    Execute this command within psql. Replace <database_name> with your desired database name and <username> with the user you created.

  • Grant privileges to the user:

    GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <username>; 

    This grants the user full access to the specified database.

Configuration: Fine-Tuning for Performance and Security

PostgreSQL’s configuration is primarily controlled through the postgresql.conf file, located in the data directory. This file allows you to tune various parameters, including memory allocation, connection limits, and security settings.

Key Configuration Parameters

  • listen_addresses: Specifies the IP addresses the server listens on. Setting it to '*' (not recommended for production) listens on all interfaces. For production, specify specific IP addresses or hostnames.

  • port: Specifies the port the server listens on (default is 5432).

  • max_connections: Limits the number of concurrent client connections.

  • shared_buffers: Allocates memory for shared buffers. A larger value can improve performance, but don’t allocate too much, leaving insufficient RAM for the OS.

  • work_mem: Specifies the amount of memory used by internal sort operations and hash tables.

  • wal_level: Determines the amount of information written to the write-ahead log (WAL).

  • listen_addresses: This setting determines which IP addresses the PostgreSQL server will listen on for incoming connections. The default is ‘localhost’, meaning the server only accepts connections from the local machine. To allow remote connections, you’ll need to change this to a specific IP address or ‘‘ to listen on all available interfaces. Be cautious when using ‘‘ as it can create security vulnerabilities.

Editing postgresql.conf

Use a text editor to modify the postgresql.conf file. After making changes, you need to restart the PostgreSQL server for the changes to take effect.

sudo systemctl restart postgresql 

pg_hba.conf: Controlling Client Authentication

The pg_hba.conf file controls client authentication. It specifies which clients are allowed to connect to which databases and using which authentication methods. Understanding pg_hba.conf is critical for security.

  • Local Connections: Typically, local connections are allowed using trust authentication (no password required) or peer authentication (uses the operating system’s authentication).

  • Remote Connections: For remote connections, you’ll typically use md5 (password authentication) or scram-sha-256 (a more secure password authentication method).

  • Example pg_hba.conf entry:

    host    all             all             0.0.0.0/0            scram-sha-256 

    This entry allows connections from any IP address (0.0.0.0/0) to any database as any user, using SCRAM-SHA-256 password authentication. Always restrict access based on IP address and use strong passwords.

Frequently Asked Questions (FAQs)

  1. How do I check the PostgreSQL version?

    Use the command psql --version or pg_ctl --version.

  2. How do I find the PostgreSQL data directory?

    Check the postgresql.conf file for the data_directory setting. Alternatively, examine the service definition file using systemctl cat postgresql.

  3. How do I reset the postgres user password?

    Stop the PostgreSQL server, then use pg_ctl in single-user mode to connect and reset the password using SQL commands. See the PostgreSQL documentation for precise steps.

  4. How do I back up a PostgreSQL database?

    Use the pg_dump utility: pg_dump <database_name> > backup.sql. For larger databases, consider using pg_dump -Fc (custom format) for parallel backups.

  5. How do I restore a PostgreSQL database from a backup?

    Use the psql command or the pg_restore utility (if using a custom format backup): psql -d <database_name> -f backup.sql.

  6. How do I monitor PostgreSQL performance?

    Use tools like pg_stat_statements extension, pg_top, or dedicated monitoring solutions like Prometheus with the PostgreSQL exporter.

  7. How do I enable remote connections to PostgreSQL?

    Edit the postgresql.conf file to change listen_addresses and the pg_hba.conf file to allow connections from specific IP addresses. Remember to restart the server after making changes.

  8. What are some common PostgreSQL extensions?

    pg_stat_statements (query statistics), PostGIS (geospatial data), hstore (key-value pairs), uuid-ossp (UUID generation).

  9. How do I install a PostgreSQL extension?

    First, install the extension’s package (if required), then run CREATE EXTENSION <extension_name>; within psql.

  10. How do I upgrade PostgreSQL?

    The recommended method depends on the version you’re upgrading from and to. Generally, it involves using pg_upgrade or performing a dump and restore. Always consult the official PostgreSQL documentation for the specific upgrade path.

  11. Why is PostgreSQL not starting?

    Check the PostgreSQL log files (typically located in the data directory) for error messages. Common causes include configuration errors, port conflicts, and insufficient permissions.

  12. How do I change the default port PostgreSQL listens on?

    Edit the postgresql.conf file and change the port setting. Remember to update your firewall rules and client connection strings accordingly, and restart the server.

By following these steps and understanding the underlying principles, you’ll be well on your way to running PostgreSQL effectively and efficiently on your Linux system. Remember to consult the official PostgreSQL documentation for the most up-to-date and comprehensive information. Good luck!

Filed Under: Tech & Social

Previous Post: « What is a 10/1 adjustable-rate mortgage?
Next Post: Does Starbucks have sweet tea? »

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

NICE TO MEET YOU!

Welcome to TinyGrab! We are your trusted source of information, providing frequently asked questions (FAQs), guides, and helpful tips about technology, finance, and popular US brands. Learn more.

Copyright © 2025 · Tiny Grab