How To Install PostgreSQL on Ubuntu

To follow along with this tutorial, you will need one Ubuntu server that has been configured by following our Initial Server Setup for Ubuntu guide. After completing this prerequisite tutorial, your server should have a non-root user with sudo permissions and a basic firewall.

To install PostgreSQL, first refresh your server’s local package index:

sudo apt update

Then, install the Postgres package along with a -contrib package that adds some additional utilities and functionality:

sudo apt install postgresql postgresql-contrib

Press Y when prompted to confirm installation. If you are prompted to restart any services, press ENTER to accept the defaults and continue.

After installation, verify that PostgreSQL is running and check its version:

sudo systemctl status postgresql

You should see output indicating that the service is active and running. To check the PostgreSQL version:

sudo -u postgres psql -c "SELECT version();"

This command will display the installed PostgreSQL version, which should be 14.x or newer on Ubuntu.

By default, Postgres uses a concept called “roles” to handle authentication and authorization. These are, in some ways, similar to regular Unix-style users and groups.

Upon installation, Postgres is set up to use ident authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account. If a role exists within Postgres, a Unix/Linux username with the same name is able to sign in as that role.

The installation procedure created a user account called postgres that is associated with the default Postgres role. There are a few ways to utilize this account to access Postgres. One way is to switch over to the postgres account on your server by running the following command:

sudo -i -u postgres

Then you can access the Postgres prompt by running:

psql

This will log you into the PostgreSQL prompt, and from here you are free to interact with the database management system right away.

To exit out of the PostgreSQL prompt, run the following:

\q

This will bring you back to the postgres Linux command prompt. To return to your regular system user, run the exit command:

exit

Another way to connect to the Postgres prompt is to run the psql command as the postgres account directly with sudo:

sudo -u postgres psql

This will log you directly into Postgres without the intermediary bash shell in between.

Again, you can exit the interactive Postgres session by running the following:

\q

If you are logged in as the postgres account, you can create a new role by running the following command:

createuser --interactive

If, instead, you prefer to use sudo for each command without switching from your normal account, run:

sudo -u postgres createuser --interactive

Either way, the script will prompt you with some choices and, based on your responses, execute the correct Postgres commands to create a user to your specifications.

Output
Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y

Another assumption that the Postgres authentication system makes by default is that for any role used to log in, that role will have a database with the same name which it can access.

This means that if the user you created in the last section is called sammy, that role will attempt to connect to a database which is also called “sammy” by default. You can create the appropriate database with the createdb command.

If you are logged in as the postgres account, you would type something like the following:

createdb sammy

If, instead, you prefer to use sudo for each command without switching from your normal account, you would run:

sudo -u postgres createdb sammy

To log in with ident based authentication, you’ll need a Linux user with the same name as your Postgres role and database.

If you don’t have a matching Linux user available, you can create one with the adduser command. You will have to do this from your non-root account with sudo privileges (meaning, not logged in as the postgres user):

sudo adduser sammy

Once this new account is available, you can either switch over and connect to the database by running the following:

sudo -i -u sammy
psql

Or, you can do this inline:

sudo -u sammy psql

This command will log you in automatically, assuming that all of the components have been properly configured.

If you want your user to connect to a different database, you can do so by specifying the database like the following:

psql -d postgres

Once logged in, you can get check your current connection information by running:

\conninfo

Output
You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

For production environments, consider these essential security configurations:

Change the postgres User Password

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'your_secure_password';"

Configure Connection Limits

Edit the PostgreSQL configuration file:

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

Look for and modify these settings:

max_connections = 100
shared_buffers = 256MB
effective_cache_size = 1GB

Restart PostgreSQL to Apply Changes

sudo systemctl restart postgresql

Issue: “psql: error: connection to server on socket failed”

Cause: PostgreSQL service is not running.

Solution:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Issue: “FATAL: role ‘username’ does not exist”

Cause: The PostgreSQL role doesn’t exist or the Linux user doesn’t match.

Solution:

sudo -u postgres createuser --interactive username
sudo adduser username

Issue: “FATAL: database ‘database_name’ does not exist”

Cause: The database hasn’t been created yet.

Solution:

sudo -u postgres createdb database_name

Issue: “FATAL: password authentication failed”

Cause: Authentication method mismatch or incorrect password.

Solution: Check the authentication method in /etc/postgresql/*/main/pg_hba.conf and ensure it matches your setup.

Issue: “could not connect to server: No such file or directory”

Cause: PostgreSQL is not running or socket path is incorrect.

Solution:

sudo systemctl status postgresql
sudo systemctl start postgresql

Understanding PostgreSQL’s performance characteristics helps you make informed decisions about hardware requirements and configuration tuning. The following benchmarks represent typical workloads on Ubuntu.

Hardware Configuration Benchmarks

Based on real-world testing with pgbench and standard TPC-B-like workloads:

Notes:

• TPS = Transactions Per Second
• Benchmarks conducted with default PostgreSQL 14 settings, then optimized
• Read/Write ratio: 70/30 (typical web application workload)
• SSD storage used for all tests

PostgreSQL Version Comparison

When choosing a PostgreSQL version for Ubuntu, it is essential to understand the improvements, support lifecycles, and feature sets relevant to performance, reliability, and long-term maintenance. Below, find a detailed comparison across the last five major releases of PostgreSQL, focusing on core features, performance enhancements, and enterprise-readiness. This comparative summary is designed to help organizations, IT professionals, and database administrators make informed decisions for both new deployments and upgrades.

Expert Implementation Guidance:
For mission-critical production deployments and environments aiming to leverage the latest performance, reliability, and enterprise features, PostgreSQL 15 and PostgreSQL 16 are highly recommended. These versions provide advanced support for parallel processing, reduced connection overhead, robust replication (including schema change propagation), and enhanced data compression. PostgreSQL 16, in particular, delivers the most optimized query execution and write performance, ideal for demanding workloads and high-concurrency scenarios.

For existing systems on PostgreSQL 14, continuing operations is still a robust and supported choice, with a mature and stable feature set. However, new installations or major upgrade cycles should target either PostgreSQL 15 or 16 to ensure access to recent enhancements and long-term maintenance support.

This comparison table is based on official PostgreSQL release notes, independent performance benchmarks, and enterprise usage patterns. Always consult the PostgreSQL Release Notes for detailed changelogs and verify upgrade paths in staging environments before production rollouts.

Authentication Methods Comparison

2025 Best Practice: Use scram-sha-256 for all production systems. Reserve ident and peer for local development only.

Configuration Impact on Performance

Testing methodology: 1000 concurrent users, mixed read/write workload (60/40), 8 CPU / 16GB RAM server.

Key Insight: Basic tuning can double performance. Connection pooling provides 15-20% improvement for high-concurrency workloads.

Memory Configuration

For servers with 4GB+ RAM, optimize these settings in /etc/postgresql/*/main/postgresql.conf:

shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 16MB
maintenance_work_mem = 256MB

Connection Pooling

Consider using PgBouncer for connection pooling in production:

sudo apt install pgbouncer

Monitoring

Enable query statistics:

sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

How do I install PostgreSQL on Ubuntu using apt?

Run:

sudo apt update
sudo apt install postgresql postgresql-contrib

This installs the default PostgreSQL version available in your Ubuntu release from the official Ubuntu repositories, suitable for most applications in 2025.

What is the recommended method to upgrade PostgreSQL to the latest supported version in 2025?

To upgrade PostgreSQL, use apt for minor updates:

sudo apt update && sudo apt upgrade

For major version upgrades (e.g., 14 to 15), install the new version and use pg_upgrade or the pg_dumpall method. Official PostgreSQL upgrade documentation and release notes are essential references.

How do I start, stop, restart, and check the status of the PostgreSQL service?

sudo systemctl start postgresql      # Start service
sudo systemctl stop postgresql       # Stop service
sudo systemctl restart postgresql    # Restart service
sudo systemctl status postgresql     # Service status and logs

For multi-version environments, manage specific clusters with pg_ctlcluster.

How can I access PostgreSQL from the command line or a remote machine?

• Local access:

  sudo -u postgres psql
  
  

• Remote access:
  Enable remote connections by editing /etc/postgresql/*/main/postgresql.conf (listen_addresses = '*') and adjust /etc/postgresql/*/main/pg_hba.conf. Then connect using:

  psql -h <server_ip> -U <username> -d <database>
  
  

What are best practices in 2025 to secure PostgreSQL on Ubuntu?

• Enforce strong passwords for all database roles, including the postgres superuser, by using password managers or enforcement policies to prevent weak or default credentials.
• Adopt SCRAM-SHA-256 authentication by setting password_encryption = scram-sha-256. This modern encryption method is far superior to MD5, providing stronger resistance against password attacks in 2025.
• Restrict network access by configuring a firewall such as ufw, limiting PostgreSQL ports to specific trusted IP addresses and blocking all unnecessary external connections to the server.
• Enable SSL/TLS encryption for all client connections by generating trusted certificates and configuring both server and client to use encrypted channels, preventing eavesdropping or data interception on the network.
• Harden configurations by practicing least-privilege access in both pg_hba.conf and postgresql.conf, only allowing necessary users, addresses, and methods to further reduce the attack surface.
• Keep PostgreSQL and the host OS fully updated by scheduling regular security updates and patching known vulnerabilities, ensuring that all security fixes are applied as soon as they’re available.
• Monitor logs and enable auditing with extensions such as pgaudit, establishing advanced logging, alerting, and regular review to detect unusual activities or potential security breaches quickly.

How do I enable AI-driven/advanced monitoring for PostgreSQL in 2025?

• Install extensions such as pg_stat_statements and pg_partman.
• Integrate with modern observability tools like Prometheus, Grafana, or cloud-native PostgreSQL monitoring services with AI anomaly detection.
• Leverage tools supporting AI-driven query optimization insights.

How do I backup and restore a PostgreSQL database?

• Backup:

  pg_dump <database_name> > backup.sql
  
  

  For full server back up:

  pg_dumpall > full_backup.sql
  
  

• Restore:

  psql <database_name> < backup.sql
  
  

  For cloud or managed instances, use built-in snapshot features where available.

How do I automate PostgreSQL administration in 2025?

• Use systemd timers or cron for automated backups and maintenance.
• Leverage Ansible, Terraform, or managed services for automated provisioning and scaling.
• Integrate AI/ML tools for predictive maintenance and automated anomaly alerts.

Can I run multiple PostgreSQL versions on one Ubuntu server safely?

Yes. Use distinct ports and data directories for each version. Ubuntu’s pg_createcluster assists in managing side-by-side installations. Ensure applications connect to the correct instance.

What are the main differences between PostgreSQL and MySQL/MariaDB for 2025 deployments?

• PostgreSQL offers advanced SQL compliance, native JSONB support, powerful indexing, richer extension ecosystem, and concurrency control (MVCC).
• MySQL/MariaDB may provide performance benefits on some read-heavy workloads or offer simplicity for beginners.
• PostgreSQL is preferred for complex data, geospatial work, analytics, and enterprise-scale workloads in 2025 due to its features and ecosystem.

How do I enable and optimize performance for high-traffic PostgreSQL workloads?

• Tune memory parameters in postgresql.conf:

  shared_buffers`, `effective_cache_size`, `work_mem`
  
  

• Set up connection pooling using PgBouncer or Pgpool-II.

• Leverage parallel query features and table partitioning.

• Enable and monitor pg_stat_statements.

• For AI-powered query optimization, use PostgreSQL 15+ extensions or external tools with AI analytics.

How can I protect PostgreSQL from common AI-generated attack vectors in 2025?

• Apply security patches promptly.
• Use AI-powered security tools for anomaly detection in SQL queries.
• Restrict superuser and administrative privileges.
• Enable SSL/TLS and require authentication for every connection.
• Monitor and alert on automated/bulk query patterns.

How do I migrate PostgreSQL data to/from cloud-based managed services?

• Use pg_dump/pg_restore or pg_dumpall for logical migrations.
• For large databases, leverage built-in managed service migration tools or streaming replication for minimal downtime.
• Ensure network security (IP whitelisting, VPN, or SSH tunneling) during migration.

What new features in PostgreSQL 15/16 are important for Ubuntu users in 2025?

• Improved performance for parallel queries and vacuuming.
• MERGE SQL command for upserts.
• Logical replication enhancements (column lists, DDL support).
• Compression improvements for large data sets.
• For full details, check the PostgreSQL release notes.

For more answers or in-depth troubleshooting tips, refer to the PostgreSQL Documentation or respected security and administration sources.

By completing this quickstart guide, you have successfully installed and configured PostgreSQL on your Ubuntu server. This setup provides you with a secure, robust foundation for managing relational databases, complete with essential security practices, troubleshooting steps, and performance enhancement recommendations to help you work confidently with your data.

As you move toward production or larger-scale deployments, make sure to strengthen your environment further—implement advanced security hardening, automate regular backups, establish monitoring and alerting, and update your server and database software frequently. These measures will help you keep your PostgreSQL installation resilient and high-performing in any scenario.

To expand your skills and deepen your understanding of PostgreSQL—whether for development, administration, or troubleshooting—explore the following comprehensive tutorials and resources:

• A comparison of relational database management systems
• Practice running queries with SQL
• How To Install and Use PostgreSQL on Ubuntu