Installing and Configuring PostgreSQL on Linux and Windows

Database PostgreSQL
Team CNC
19 January 2025
0
6 minutes read

PostgreSQL is a powerful open-source relational database management system (RDBMS) that can be installed and configured on various operating systems, including Linux and Windows. This guide provides step-by-step instructions for installing and configuring PostgreSQL on both platforms.

1. Installing PostgreSQL on Linux

1.1 Update System Packages

Before installing PostgreSQL, update your system packages:

sudo apt update && sudo apt upgrade -y  # For Debian/Ubuntu
sudo yum update -y                      # For CentOS/RHEL

1.2 Install PostgreSQL

Install PostgreSQL using the package manager for your distribution:

For Debian/Ubuntu:

sudo apt install postgresql postgresql-contrib -y

For CentOS/RHEL:

sudo yum install postgresql-server postgresql-contrib -y

1.3 Start and Enable PostgreSQL Service

sudo systemctl start postgresql
sudo systemctl enable postgresql

1.4 Verify Installation

Check the PostgreSQL service status:

sudo systemctl status postgresql

1.5 Access PostgreSQL CLI

Switch to the PostgreSQL user and open the PostgreSQL shell:

sudo -i -u postgres
psql

To exit the PostgreSQL shell, type:

\q

2. Installing PostgreSQL on Windows

2.1 Download PostgreSQL Installer

Download the latest version of PostgreSQL from the official website: https://www.postgresql.org/download/

2.2 Run the Installer

Launch the installer and follow the setup wizard.
Choose installation directory and components (default settings are recommended).
Set a password for the PostgreSQL superuser (postgres account).
Select the port (default: 5432).
Complete the installation process and finish the setup.

2.3 Verify Installation

After installation, open pgAdmin (a GUI management tool) or use the command line:

psql -U postgres

Enter the password you set during installation.

3. Configuring PostgreSQL

3.1 Changing Default Authentication Method (Linux Only)

By default, PostgreSQL uses peer authentication on Linux. To allow password authentication, edit the pg_hba.conf file:

sudo nano /etc/postgresql/14/main/pg_hba.conf   # Path may vary depending on version and OS

Change this line:

local   all             postgres                                peer

To:

local   all             postgres                                md5

Save and exit, then restart PostgreSQL:

sudo systemctl restart postgresql

3.2 Allow Remote Connections

Edit the postgresql.conf file to allow remote connections:

sudo nano /etc/postgresql/14/main/postgresql.conf

Uncomment and modify this line:

listen_addresses = '*'

Save and restart PostgreSQL:

sudo systemctl restart postgresql

3.3 Creating a New User and Database

CREATE USER myuser WITH PASSWORD 'mypassword';

Create a new database and assign ownership:

CREATE DATABASE mydb OWNER myuser;

Grant privileges:

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

4. Conclusion

Installing and configuring PostgreSQL is straightforward on both Linux and Windows. By following the above steps, you can set up a PostgreSQL server, manage users, and configure remote access as needed.