DEV Community

Cover image for PostgreSQL Installation and Setup on a Linux Server
Lagat Josiah
Lagat Josiah

Posted on • Edited on

PostgreSQL Installation and Setup on a Linux Server

PostgreSQL is a powerful, open-source relational database system that has earned a strong reputation for reliability, feature robustness, and performance. In this guide, I'll walk you through installing and setting up PostgreSQL on a Linux server (works for Ubuntu, Debian, CentOS, and similar distributions).

Why Choose PostgreSQL?

Before we dive into installation, let's quickly review why you might choose PostgreSQL:

  • Fully ACID compliant
  • Extensive SQL compliance
  • Cross-platform (Linux, Windows, macOS)
  • Highly extensible (you can add custom functions, data types, etc.)
  • Strong community and commercial support
  • Excellent performance characteristics

Step 1: Installation

For Ubuntu/Debian Systems

bash
# Update your package lists
sudo apt update

# Install PostgreSQL and its contrib package (additional utilities)
sudo apt install postgresql postgresql-contrib
Enter fullscreen mode Exit fullscreen mode

For CentOS/RHEL Systems

bash
# Enable the PostgreSQL repository
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL
sudo dnf install -y postgresql15-server
Enter fullscreen mode Exit fullscreen mode

After installation, the PostgreSQL service will start automatically. You can verify this with:

bash
sudo systemctl status postgresql
Enter fullscreen mode Exit fullscreen mode

Step 2: Basic Configuration

Initialize the Database (CentOS/RHEL only)

On CentOS/RHEL, you need to initialize the database:

bash
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15
Enter fullscreen mode Exit fullscreen mode

Set a Password for the PostgreSQL User
By default, PostgreSQL creates a user named postgres. Let's set a password for this account:

bash
# Switch to the postgres user
sudo -i -u postgres

# Access the PostgreSQL prompt
psql

# In the psql prompt, set the password
\password postgres

# Exit psql
\q

# Return to your regular user
exit
Enter fullscreen mode Exit fullscreen mode

Enable Remote Access (Optional)

If you need to access PostgreSQL from other machines:

  1. Edit the PostgreSQL configuration file (location varies by distro):
  • Ubuntu/Debian: /etc/postgresql/15/main/postgresql.conf
  • CentOS/RHEL: /var/lib/pgsql/15/data/postgresql.conf

Find the line with listen_addresses and change it to:

conf
listen_addresses = '*'
Enter fullscreen mode Exit fullscreen mode
  1. Edit the client authentication file (usually pg_hba.conf in the same directory):

Add this line to allow password-based authentication from specific IPs or ranges:

conf
host    all             all             192.168.1.0/24           md5
Enter fullscreen mode Exit fullscreen mode

Or for all IPs (less secure):

conf
host    all             all             0.0.0.0/0                md5
Enter fullscreen mode Exit fullscreen mode
  1. Restart PostgreSQL: 
bash
sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

Step 3: Creating a Database and User

Let's create a dedicated database and user for your application:

bash
sudo -u postgres psql
Enter fullscreen mode Exit fullscreen mode

In the PostgreSQL prompt:

sql
-- Create a new user
CREATE USER myuser WITH PASSWORD 'mypassword';

-- Create a database
CREATE DATABASE mydb;

-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

-- Exit
\q
Enter fullscreen mode Exit fullscreen mode

Step 4: Basic Security Considerations

1. Firewall Configuration:
If you enabled remote access, configure your firewall:

bash
sudo ufw allow 5432/tcp  # For Ubuntu/Debian
sudo firewall-cmd --add-port=5432/tcp --permanent  # For CentOS/RHEL
sudo firewall-cmd --reload
Enter fullscreen mode Exit fullscreen mode

2. Regular Backups:
Set up automated backups using pg_dump:

bash
sudo -u postgres pg_dump mydb > /path/to/backup/mydb_backup.sql
Enter fullscreen mode Exit fullscreen mode

3. Updates:

Regularly update PostgreSQL to get security patches:

bash
sudo apt update && sudo apt upgrade  # Ubuntu/Debian
sudo dnf update  # CentOS/RHEL
Enter fullscreen mode Exit fullscreen mode

Step 5: Connecting to Your Database

You can now connect to your database using:

Command line:

bash
psql -h localhost -U myuser -d mydb
Enter fullscreen mode Exit fullscreen mode

From applications using connection strings:

text
postgresql://myuser:mypassword@localhost/mydb
Enter fullscreen mode Exit fullscreen mode

Troubleshooting Common Issues

  1. Connection refused errors:
  • Verify PostgreSQL is running: sudo systemctl status postgresql
  • Check listen_addresses in postgresql.conf
  • Verify pg_hba.conf has the correct permissions

2. Authentication failures:

  • Double-check username/password
  • Verify the user has privileges on the database
  • Check pg_hba.conf for correct authentication methods

*3. Performance issues:
*

  • Consider tuning shared_buffers, work_mem, and other parameters in postgresql.conf
  • Use EXPLAIN ANALYZE to analyze slow queries

Next Steps

Now that you have PostgreSQL installed and configured, consider exploring:

  • PostgreSQL extensions to add functionality
  • pgAdmin for a graphical interface
  • PostgreSQL documentation for advanced features

Conclusion

Setting up PostgreSQL on Linux is straightforward, and you now have a solid foundation for your database needs. PostgreSQL's flexibility and power make it an excellent choice for applications of all sizes.

Top comments (0)