How To Install and Use PostgreSQL on Ubuntu

Database management systems are a core component of many websites and applications, providing a structured way to store, organize, and access information. PostgreSQL, commonly known as Postgres, is a leading open-source relational database management system. It provides a complete implementation of the SQL querying language and is well-regarded for its standards compliance and advanced features, such as reliable transactions and concurrency without read locks.

This guide demonstrates the complete process for setting up PostgreSQL on an Ubuntu server, starting with the installation of the necessary software packages. You will then learn how to interact with the Postgres authentication system, which is managed through “roles,” including creating new users and databases. Post that, we will cover the fundamentals of database management: connecting to your new database, defining a data structure by creating tables, and manipulating data using basic SQL commands. We will also explore key administrative topics, including essential configuration for performance, strategies for backup and restore, a comparison of PostgreSQL with other database engines, and a guide to troubleshooting common issues.

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

You can check the version by running the following command:

psql --version

Ensure that the service is started:

sudo systemctl start postgresql.service

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 peer 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

The createuser and createdb shell commands are convenient helpers. However, for more control, you can perform these same actions directly within PostgreSQL using SQL commands. This approach is often clearer when setting passwords or granting specific permissions from the start.

1. First, connect as the administrative postgres user:

   sudo -u postgres psql
   
   

   Once at the PostgreSQL prompt, you can use CREATE ROLE and CREATE DATABASE to set up your new user and database.

2. Create a New Role (User): While the createuser shell command is interactive, the CREATE ROLE command lets you define everything in one statement. To create a user named sammy that can log in (LOGIN) and has a password, run:

   CREATE ROLE sammy WITH LOGIN PASSWORD 'your_strong_password';
   
   

   If this user will also need to create databases, you can grant that permission at the same time:

   CREATE ROLE sammy WITH LOGIN PASSWORD 'your_strong_password' CREATEDB;
   
   

   This provides a subset of superuser permissions, specifically allowing the user to create new databases. While answering ‘yes’ to the superuser question in the interactive helper also grants this permission, SUPERUSER itself is a much broader and more dangerous role.

3. Create a New Database: Next, create the database. It’s good practice to assign ownership of the new database to the new role you just created.

   CREATE DATABASE sammydb OWNER sammy;
   
   

   To grant the sammy user permission to connect to the new database (a common next step), you can run:

   GRANT ALL PRIVILEGES ON DATABASE sammydb TO sammy;
   
   

   This grants database-level privileges like CONNECT, but not permissions to specific tables. Note that this does not automatically grant permissions to view or modify tables within the database; further GRANT commands on the schema (e.g., public) and the tables themselves are required for that.

4. Exit the postgres session: You can now exit the psql prompt for the postgres user.

   \q
   
   

Because you set a password, this new user can authenticate using methods other than ident, which is necessary for remote connections.

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 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".

This is useful if you are connecting to non-default databases or with non-default users.

Now that you know how to connect to the PostgreSQL database system, you can learn some basic Postgres management tasks.

The basic syntax for creating tables is as follows:

CREATE TABLE table_name (
    column_name1 col_type (field_length) column_constraints,
    column_name2 col_type (field_length),
    column_name3 col_type (field_length)
);

As you can see, these commands give the table a name, and then define the columns as well as the column type and the max length of the field data. You can also optionally add table constraints for each column.

To learn more about creating and deleting tables, check out our article on how to create and manage tables in Postgres.

For demonstration purposes, create the following table:

CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

This command will create a table that inventories playground equipment. The first column in the table will hold equipment ID numbers of the serial type, which is an auto-incrementing integer. This column also has the constraint of PRIMARY KEY which means that the values within it must be unique and not null.

The next two lines create columns for the equipment type and color respectively, neither of which can be empty. The line after these creates a location column as well as a constraint that requires the value to be one of eight possible values. The last line creates a date column that records the date on which you installed the equipment.

For two of the columns (equip_id and install_date), the command doesn’t specify a field length. The reason for this is that some data types don’t require a set length because the length or format is implied.

You can see your new table by typing:

\d

Output
                  List of relations
 Schema |          Name           |   Type   | Owner 
--------+-------------------------+----------+-------
 public | playground              | table    | sammy
 public | playground_equip_id_seq | sequence | sammy
(2 rows)

Your playground table is here, but there’s also something called playground_equip_id_seq that is of the type sequence. This is a representation of the serial type which you gave your equip_id column. This keeps track of the next number in the sequence and is created automatically for columns of this type.

If you want to see just the table without the sequence, you can type:

\dt

Output
          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | playground | table | sammy
(1 row)

With a table at the ready, let’s use it to practice managing data.

Now that you have a table, you can insert some data into it. As an example, add a slide and a swing by calling the table you want to add to, naming the columns and then providing data for each column, like this:

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

You should take care when entering the data to avoid a few common hangups. For one, do not wrap the column names in quotation marks, but the column values that you enter do need quotes.

Another thing to keep in mind is that you do not enter a value for the equip_id column. This is because this is automatically generated whenever you add a new row to the table.

Retrieve the information you’ve added by typing:

SELECT * FROM playground;

Output
 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        1 | slide | blue   | south     | 2017-04-28
        2 | swing | yellow | northwest | 2018-08-16
(2 rows)

Here, you can see that your equip_id has been filled in successfully and that all of your other data has been organized correctly.

If the slide on the playground breaks and you have to remove it, you can also remove the row from your table by typing:

DELETE FROM playground WHERE type = 'slide';

Query the table again:

SELECT * FROM playground;

Output
 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2018-08-16
(1 row)

Notice that the slide row is no longer a part of the table.

After creating a table, you can modify it by adding or removing columns. Add a column to show the last maintenance visit for each piece of equipment by typing:

ALTER TABLE playground ADD last_maint date;

If you view your table information again, you will see the new column has been added but no data has been entered:

SELECT * FROM playground;

Output
 equip_id | type  | color  | location  | install_date | last_maint 
----------+-------+--------+-----------+--------------+------------
        2 | swing | yellow | northwest | 2018-08-16   | 
(1 row)

If you find that your work crew uses a separate tool to keep track of maintenance history, you can delete of the column by typing:

ALTER TABLE playground DROP last_maint;

This deletes the last_maint column and any values found within it, but leaves all the other data intact.

So far, you’ve learned how to add records to a table and how to delete them, but this tutorial hasn’t yet covered how to modify existing entries.

You can update the values of an existing entry by querying for the record you want and setting the column to the value you wish to use. You can query for the swing record (this will match every swing in your table) and change its color to red. This could be useful if you gave the swing set a paint job:

UPDATE playground SET color = 'red' WHERE type = 'swing';

You can verify that the operation was successful by querying the data again:

SELECT * FROM playground;

Output
 equip_id | type  | color | location  | install_date 
----------+-------+-------+-----------+--------------
        2 | swing | red   | northwest | 2018-08-16
(1 row)

As you can see, the slide is now registered as being red.

Configuring PostgreSQL correctly is essential for a stable and fast database. Default settings are designed to be safe for any hardware, meaning they are almost never suitable for a production workload. Performance tuning involves adjusting resource allocation—primarily memory, I/O, and connection handling—to match your server’s hardware and your application’s specific query patterns.

Let’s discuss a few key configuration parameters and general strategies for improving database performance.

Key Configuration Files

Before tuning, you must know where the configuration files are. On Ubuntu, they are located in /etc/postgresql/[version]/main/:

• postgresql.conf: The main server configuration file. This is where you adjust all performance-related parameters.
• pg_hba.conf: This file controls client authentication (which users can connect from which addresses). It does not directly affect query performance but is fundamental for security.

After editing the postgresql.conf, you can apply the changes by reloading the PostgreSQL service:

sudo systemctl reload postgresql

However, some parameters (marked as postmaster context in documentation) require a full restart:

sudo systemctl restart postgresql

You can run the following command to check whether you need to restart or reload after a change:

SELECT context FROM pg_settings WHERE name = 'parameter_name';

By contrast, changes to pg_hba.conf only require a reload.

sudo systemctl reload postgresql

Memory Configuration

Memory is the most significant factor in database performance. PostgreSQL uses shared memory to cache data and separate per-connection memory for operations like sorting.

shared_buffers

This is the single most important memory setting. It defines the amount of memory PostgreSQL dedicates to its data cache. A larger cache means more of your working data set can be read from fast memory instead of slow disk.

• Guideline: Start by setting shared_buffers to 25% of your system’s total RAM.

• Example: On a server with 16 GB of RAM, a good starting point is 4GB.

• Configuration (postgresql.conf):

  # Default is 128MB, which is too low
  shared_buffers = 4GB
  

work_mem

This setting defines the amount of memory each individual database operation (like a sort, hash join, or bitmap) can use before it spills to temporary disk files. Complex queries may have multiple operations, each using this amount of memory.

Setting this too high can lead to memory exhaustion if many complex queries run at once. Setting it too low causes frequent and slow disk-based sorting.

• Guideline: Start with the default (4MB). Analyze your slow queries using EXPLAIN ANALYZE. If you see “Sort Method: external merge Disk” or “HashAgg Disk”, it means work_mem is too small. Increase it gradually (e.g., 16MB, 32MB) for your session to test, and only set it globally if you are sure.

maintenance_work_mem

This setting reserves memory for internal maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.

• Guideline: Set this higher than work_mem (e.g., 128MB or 256MB). Because these operations are not run as frequently as user queries, you can afford to be more generous to speed up maintenance.

Checkpoint and WAL Configuration

Checkpoints are processes where PostgreSQL writes all “dirty” data (changes held in memory) to the permanent data files on disk. This is a very I/O-intensive operation. The Write-Ahead Log (WAL) records every transaction. Tuning these helps balance I/O load and recovery time.

Connection Handling

Every connection to PostgreSQL uses memory. A high number of connections can exhaust server resources.

• max_connections: The maximum number of concurrent connections. The default is 100. While it is tempting to increase this, each connection consumes memory.
• Use a Connection Pooler: For applications that open and close many connections (like most web applications), do not increase max_connections. Instead, use an external connection pooler like PgBouncer or Pgpool-II. A pooler maintains a small set of persistent connections to the database and serves many concurrent application connections, which is far more efficient.

Query Planning and Analysis

No amount of server tuning can fix an inefficient query.

• EXPLAIN ANALYZE: This is your primary tool for query tuning. Run this command on your slow queries to see the actual execution plan and time spent.

  EXPLAIN ANALYZE SELECT * FROM users WHERE last_name = 'Smith';
  

  Look for “Sequential Scan” (Seq Scan) on large, indexed tables. This often means you are missing an index or the query is not selective enough.

• Create Indexes: If you frequently filter or join on specific columns (e.g., user_id, email, created_at), they are good candidates for an index.

  CREATE INDEX idx_users_on_email ON users (email);
  

• Run VACUUM ANALYZE: PostgreSQL uses a query planner that relies on statistics about your table data. These statistics are updated by ANALYZE. While autovacuum handles this automatically, it is good practice to run a manual VACUUM ANALYZE; after large data loads or if you notice query plans are poor.

Protecting your data is a primary responsibility of database administration. Regular, verified backups are the most effective defense against data corruption, hardware failure, or human error. PostgreSQL provides a set of robust, command-line tools for creating logical backups and restores.

There are two main categories of backups:

1. Logical Backups (SQL Dumps): These backups consist of SQL commands that, when executed, recreate the database objects (like tables and indexes) and data. They are created with the pg_dump and pg_dumpall utilities. This method is flexible and portable across different PostgreSQL versions.
2. Physical Backups (Filesystem-level): This method involves copying the physical data files from PostgreSQL’s data directory. This is typically used for Point-in-Time Recovery (PITR), an advanced setup discussed later in this section.

Creating Logical Backups with pg_dump

The pg_dump utility backs up a single database. It will not back up global objects like roles or tablespaces. It is highly flexible and can be run on a live database without blocking readers or writers.

Common pg_dump Options

You can control the output of pg_dump with several command-line flags:

• -F c (Format: custom): This is the recommended format for most backups. It produces a compressed, binary archive that is not human-readable but allows for parallel restores and reordering or excluding objects during the restore.
• -F p (Format: plain): This is the default. It outputs a large .sql file. Its main downside is that it must be restored all at once, and it does not support parallel restores.
• -s (Schema-only): Backs up only the database structure (tables, views, indexes) without any data.
• -a (Data-only): Backs up only the data, assuming the schema already exists in the target.
• --exclude-table=TABLE_NAME: Excludes a specific table from the backup. This is useful for skipping large, temporary, or non-critical tables.
• -f FILENAME: Specifies the output file.
• -U USER: Specifies the PostgreSQL user to connect as.

This is the recommended command for most backup tasks. It connects to the mydatabase database as the postgres user and creates a compressed file named mydatabase.dump.

pg_dump -U postgres -F c -f mydatabase.dump mydatabase

Restoring Logical Backups

The tool you use for restoring depends on the format of your backup file.

Restoring from a Custom Archive (.dump)

Use the pg_restore utility. This tool offers significant flexibility, including the ability to restore in parallel and run pre-flight checks.

Instead of manually creating the database first, the best practice is to use the --create flag. This flag tells pg_restore to issue the CREATE DATABASE command from the archive. To do this, you must connect to a different existing database, such as the default postgres database.

The following command connects to the postgres database, drops and recreates mydatabase (because of --clean and --create), and uses 4 parallel jobs to restore it.

pg_restore -U postgres -d postgres --create --clean -j 4 mydatabase.dump

• -d postgres: Connects to the postgres database to issue the create command.
• --create: Instructs pg_restore to create the target database (e.g., mydatabase) before restoring to it.
• --clean: When used with --create, this flag will drop and recreate the entire target database. When used alone (restoring into an existing database), it drops existing database objects before recreating them.
• -j 4: Uses 4 parallel jobs for the restore.

We connect to the postgres database because pg_restore needs a connection to an existing database on the server to issue the CREATE DATABASE command for our new target.

Restoring from a Plain Text File (.sql)

Use the standard psql client. This method requires the target database to exist first.

1. Create the database:

   createdb -U postgres mydatabase_new
   
   

2. Pipe the .sql file into psql:

   psql -U postgres -d mydatabase_new < mydatabase.sql
   
   

Backing Up All Databases (pg_dumpall)

To back up all databases on the server, plus all global objects (roles, users, and tablespaces), use the pg_dumpall utility. This tool only produces a plain text .sql file.

# Run as the postgres superuser
pg_dumpall -U postgres > all_databases.sql

To restore from a pg_dumpall file, you pipe it into psql. You must connect as a superuser (like postgres) to a default database (like postgres itself). The script file contains all necessary CREATE ROLE and CREATE DATABASE commands and will execute them from there.

# Run as the postgres superuser
psql -U postgres -d postgres -f all_databases.sql

Backing Up Roles and Globals Only

A common and useful pattern is to back up only global objects. This is done with the --globals-only flag and is an effective way to replicate user permissions across servers.

pg_dumpall -U postgres --globals-only > roles.sql

Backup and Restore Strategy Summary

The following table summarizes the backup and restore strategy:

Automation and Backup Best Practices

A backup plan is not complete until it is automated, verified, and secure.

• Automate Your Backups: Use a standard Linux tool like cron to schedule your pg_dump or pg_dumpall commands to run nightly.
• Handle Passwords Securely: To run backups in a non-interactive script, do not hardcode passwords. Use the .pgpass file. Create a file at ~/.pgpass in the user’s home directory (e.g., /var/lib/postgresql/.pgpass). The file must have strict 0600 permissions, or it will be ignored by PostgreSQL. The format is hostname:port:database:user:password. You can use * as a wildcard for any field, e.g., localhost:5432:*:postgres:your_password.
• Test Your Restores: A backup is only useful if it can be restored. Regularly practice restoring your backups to a separate, non-production server to verify their integrity.
• Verify Your Backups: After a backup is created, add a script step to check that the file size is non-zero. For custom formats, you can run pg_restore -l mydatabase.dump &> /dev/null (which lists the contents to /dev/null) to confirm the archive is not corrupt by checking the command’s exit code.
• Store Backups Remotely: Do not store your backups on the same server as your database. A complete server failure would lose both. Copy backups to a remote location such as DigitalOcean Spaces or another server as the final step of your script.

Ubuntu’s package repositories provide access to a wide variety of database engines. The best choice depends entirely on your application’s requirements, such as its data model, scalability needs, and consistency guarantees.

• PostgreSQL: This object-relational database is known for its high level of standards compliance, data integrity, and robust feature set. It is fully ACID compliant and supports advanced data types and complex queries well, making it a common choice for a primary “system of record” database.
• MySQL: As part of the original LAMP stack, MySQL is one of the most popular open-source databases. It is known for its ease of use, historically strong read performance, and strong community. It is also fully ACID compliant (with the default InnoDB engine) and favored for straightforward web applications.
• MariaDB: A community-driven fork of MySQL. While it maintains high compatibility (especially with older versions), it has diverged and introduces its own features, so it may not be a complete drop-in replacement for applications built on modern MySQL 8.0+ features.
• SQLite: This is not a client-server database. It is a C library that embeds a full database engine into an application, storing the entire database in a single file. It handles multiple concurrent readers well but is not ideal for applications with frequent concurrent writes or networked multi-user scenarios.
• MongoDB: A leading NoSQL database, MongoDB stores data in flexible, JSON-like documents. This allows for a flexible schema and is built for high availability and horizontal scaling. While it is known for flexibility, MongoDB has also supported multi-document ACID transactions since version 4.0, making it a viable choice for applications requiring strong consistency.
• Redis: This is an in-memory data structure store, meaning it keeps all its data in RAM for exceptional speed. It is not a general-purpose replacement for a disk-based database but is almost always used alongside one (like PostgreSQL) to act as a high-speed cache or session store.

Here is a comparison of PostgreSQL with other popular database engines available on Ubuntu.

Database Engine Comparison

Running PostgreSQL on Ubuntu can present specific challenges, especially for users new to its architecture on Debian-based systems. Most errors fall into predictable categories: installation conflicts, authentication rules, file permissions, and service management.

Before troubleshooting, your most important tool is the PostgreSQL log file. On standard Ubuntu installations, you can find it at /var/log/postgresql/postgresql-[version]-main.log. Always check this log first, as it provides specific error messages that will guide your diagnosis.

Installation Issues

These problems typically occur during the initial apt install process or when initdb first attempts to create a new database cluster.

Package Conflicts or Version Mismatches

This issue occurs when your system tries to install PostgreSQL packages from two different sources, such as Ubuntu’s default repository and the official PostgreSQL Global Development Group (PGDG) repository. Ubuntu’s repositories often contain older, stable versions, while the PGDG repo provides the latest releases. Mixing them can lead to broken dependencies.

Troubleshooting:

• Check your sources: Use apt-cache policy postgresql to see which versions are available and from which repository.
• Choose one source: It is highly recommended to use the PGDG repository for the most up-to-date version. If you add the PGDG repository, you may need to use apt pinning to prioritize its packages over the default Ubuntu packages.
• Fix broken installs: If you are already in a conflicted state, sudo apt-get -f install may resolve it. If not, you may need to completely purge all PostgreSQL packages (sudo apt-get purge "postgresql-*") and reinstall from a single, clean source.

Port 5432 Already in Use

This error means another program is already listening on the default PostgreSQL port (5432). This is common if an old, uninstalled PostgreSQL instance failed to shut down or if you have multiple PostgreSQL clusters running (a feature of Ubuntu’s pg_ctlcluster system).

Troubleshooting:

• Identify the process: Use netstat to find what is using the port.

  sudo netstat -tulpn | grep 5432
  
  

• Check for other clusters: If the process is postgres, use the pg_lsclusters command to see all configured clusters and their statuses.

  pg_lsclusters
  
  

  Output might show one ‘online’ and one ‘down’.

• Stop the unwanted cluster: If you find an old or unintended cluster running, stop it using its version and name.

  sudo pg_ctlcluster 13 main stop
  
  

• Change the port: If another service is correctly using port 5432, you must configure your new PostgreSQL cluster to use a different port by editing its postgresql.conf file.

Locale and Encoding Errors During initdb

Errors like “invalid locale name” or “could not determine default locale” during the initial cluster setup (initdb) mean your Ubuntu system’s locale settings are missing or misconfigured. PostgreSQL inherits these settings from the operating system to determine sort order (collation), character classification, and formatting.

Troubleshooting:

• Check your system locales: Run locale -a to see all available locales.

• Generate missing locales: If your desired locale (e.g., en_US.UTF-8) is not listed, you must generate it.

  sudo locale-gen en_US.UTF-8
  sudo update-locale
  
  

• Re-initialize the cluster: After configuring the system locales, you may need to drop and re-create the PostgreSQL cluster. The pg_createcluster command is the standard Ubuntu method for this.

  Warning: This destroys any data in the cluster

  sudo pg_dropcluster [version] main
  sudo pg_createcluster [version] main
  
  

Authentication Problems

These are the most frequent issues users face after a successful installation.

Peer Authentication Failed

This is the most common error. It means you are trying to connect locally, and your currently logged-in Ubuntu username does not match the PostgreSQL database role name you are trying to use.

By default, PostgreSQL on Ubuntu uses peer authentication for local connections. This method asks the operating system, “What is the username of the process connecting to me?” and allows the connection if that username matches the requested database role.

For example, if you are logged in as myuser and run psql -U postgres, it fails because the operating system reports your username as myuser, not postgres.

Solution:

The correct way to connect as the postgres superuser is to first assume the identity of the postgres system user using sudo.

sudo -u postgres psql

If you wish to change this behavior, you must set a password for the postgres role and then edit the pg_hba.conf file to use md5 or scram-sha-256 instead of peer.

pg_hba.conf Misconfiguration

Errors in the pg_hba.conf file (Host-Based Authentication) are the primary cause of connection failures. This file, located at /etc/postgresql/[version]/main/pg_hba.conf, is a ruleset that controls who can connect from where to which database using what method. A single syntax error or incorrect rule order can lock you out.

Troubleshooting:

• Rule order matters: PostgreSQL reads this file from top to bottom and uses the first matching rule. More specific rules must always be placed before more general ones.

• Reload after changes: After editing pg_hba.conf, you must reload the PostgreSQL service for changes to take effect:

  sudo systemctl reload postgresql
  
  

Here are common configuration mistakes:

Permission Issues

These errors often appear as “Permission denied” in logs or when trying to connect.

Cannot Access Socket File

Errors like “could not connect to server: No such file or directory” or “Permission denied” when referencing /var/run/postgresql/.s.PGSQL.5432 usually mean one of two things:

1. The service is not running: The socket file is removed when the service stops. Check the service status with sudo systemctl status postgresql.
2. User permissions: The socket directory is owned by postgres and accessible by the postgres group. If your system user is not in the postgres group, you cannot use the socket.

Solution:

Add your user to the postgres group. You must log out and log back in for this change to take effect.

sudo usermod -a -G postgres $USER

Data Directory Permissions

If the PostgreSQL service fails to start and logs mention “Permission denied” related to the data directory (e.g., /var/lib/postgresql/[version]/main), the directory’s ownership or file permissions are incorrect.

PostgreSQL requires its data directory to be owned exclusively by the postgres system user and have 0700 permissions (only the owner can read, write, and execute). This is a security measure. This issue often happens after restoring a backup or moving the data directory manually.

Solution:

Recursively reset the ownership and permissions.

# Correct the ownership (replace 18 with your version)
sudo chown -R postgres:postgres /var/lib/postgresql/18/main

# Correct the permissions
sudo chmod 0700 /var/lib/postgresql/18/main

Service Management

These issues relate to starting, stopping, and managing the postgresql service itself.

Service Fails to Start After Configuration Changes

If the service fails to start immediately after you edit postgresql.conf or pg_hba.conf, you likely have introduced a syntax error.

Troubleshooting:

1. Check the logs: This is the fastest way to find the error. sudo journalctl -u postgresql or the specific log at /var/log/postgresql/postgresql-[version]-main.log will often print the exact line number of the invalid setting.
2. Common Errors in postgresql.conf:
   • Unquoted string values (e.g., log_destination = stderr is correct; log_destination = /var/log/my.log is wrong, it should be 'var/log/my.log').
   • Invalid values for settings (e.g., shared_buffers = 10GB when you only have 8GB of RAM).

Confusion Over Multiple Clusters

Ubuntu’s postgresql-common package includes a set of wrapper scripts (pg_lsclusters, pg_ctlcluster, pg_createcluster) that allow you to run multiple, separate PostgreSQL instances (clusters) on the same machine, often of different versions.

This is a powerful feature but can be confusing. You might edit the config file for version 18 but be connecting to version 13, leading you to believe your “changes are not applying.”

Troubleshooting:

• List all clusters: Always start here to see what is running, on which port, and the location of its configuration files.

  pg_lsclusters
  # Ver Cluster Port Status Owner    Data directory                       Log file
  # 13  main    5432 down   postgres /var/lib/postgresql/13/main         ...
  # 18  main    5433 online postgres /var/lib/postgresql/18/main         ...
  

• Manage specific clusters: When starting, stopping, or reloading, always specify the cluster you intend to change:

  sudo pg_ctlcluster 18 main reload
  
  

Resource Limits

These errors occur when PostgreSQL requests more resources (like memory) than the operating system is configured to provide.

Shared Memory Errors

Errors on startup like “could not create shared memory segment: Invalid argument” or “No space left on device” often mean PostgreSQL’s shared_buffers setting is larger than your operating system’s kernel limit for shared memory (SHMMAX).

PostgreSQL uses a large block of shared memory for caching and inter-process communication. If it requests more than the OS allows, the OS denies the request, and PostgreSQL fails to start. This is common in memory-constrained environments or containers.

Troubleshooting:

1. Check OS limits:

   cat /proc/sys/kernel/shmmax
   
   

2. Check postgresql.conf: Look at your shared_buffers setting.

3. Solution 1 (Recommended): Lower the shared_buffers value in postgresql.conf to be safely under the shmmax limit.

4. Solution 2 (Advanced): Increase the kernel’s shmmax limit. Edit /etc/sysctl.conf and add a line like kernel.shmmax = 134217728 (for 128MB). Apply it with sudo sysctl -p. This is generally only needed for high-performance tuning.

1. What is PostgreSQL and why should I use it on Ubuntu?

PostgreSQL, often called Postgres, is a powerful, open-source object-relational database system. It has a strong reputation for reliability, feature robustness, and data integrity, having been in active development for over 30 years.

You should consider using it for your applications because it fully supports ACID (Atomicity, Consistency, Isolation, Durability) compliance, handles high-concurrency workloads well, and supports a wide range of advanced data types and extensions, such as the PostGIS extension for geospatial data.

Using PostgreSQL on Ubuntu is a common choice because Ubuntu is a popular and stable Linux distribution for servers. PostgreSQL is included in Ubuntu’s default package repositories, which makes installation and receiving timely security updates a straightforward process managed by the system’s apt package manager.

2. How do I install PostgreSQL on Ubuntu?

You can install PostgreSQL directly from Ubuntu’s default repositories using the apt command.

First, refresh your server’s local package index to make sure you have the latest package lists:

sudo apt update

Next, install the main PostgreSQL package and the contrib package, which includes additional utilities and extensions:

sudo apt install postgresql postgresql-contrib

3. How do I start and enable the PostgreSQL service?

Upon a successful installation, the PostgreSQL service should start automatically. You can verify its status using systemctl.

sudo systemctl status postgresql

You may see output like this:

Output
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Wed 2025-10-29 10:30:01 IST; 1min 12s ago
   Main PID: 1234 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 4662)
...

If the service is not running, you can start it manually:

sudo systemctl start postgresql

To ensure the database starts automatically every time the server boots, you must enable the service:

sudo systemctl enable postgresql

4. How can I create a new database in PostgreSQL?

The simplest method is to use the createdb command-line utility. This command must be run by a PostgreSQL role that has permission to create databases.

By default, the postgres Linux user account is configured to connect as the postgres database superuser. You can switch to this user and run the command:

sudo -i -u postgres
createdb my_database

Alternatively, you can log in to the PostgreSQL interactive prompt (psql) and use the SQL command CREATE DATABASE.

First, access the prompt as the postgres user:

sudo -i -u postgres
psql

Then, from within the psql prompt (which looks like postgres=# because postgres is a superuser), run the SQL command. Remember to end SQL commands with a semicolon.

CREATE DATABASE my_database;

You can exit the psql prompt by typing \q and then type exit to return to your regular user.

5. How do I create and manage PostgreSQL users and roles?

In PostgreSQL, user management is handled through roles. A role can be a database user or a group of users, but a role with the LOGIN privilege is considered a user.

1. Creating a User

You can use the createuser command-line utility. It is best to run this as the postgres user.

sudo -i -u postgres

A recommended method is to use the --interactive flag to be prompted for settings and the --pwprompt flag to set a password immediately.

createuser --interactive --pwprompt

This will ask for the role name, a password, and whether the role should be a superuser or have other high-level permissions.

Output
Enter name of role to add: sammy
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

You can also create a user with a single SQL command from the psql prompt. This creates a minimal user that can only log in.

CREATE ROLE sammy WITH LOGIN PASSWORD 'secure_password';

2. Granting Privileges

Creating a user does not automatically give them access to a database. You must grant privileges explicitly. This process has several steps:

1. Grant Connect Access: First, grant the new role permission to connect to your database.

   GRANT CONNECT ON DATABASE my_database TO sammy;
   
   

   Common Pitfall: Using GRANT ALL PRIVILEGES ON DATABASE my_database TO sammy; is misleading. This command only grants CONNECT, CREATE, and TEMP permissions on the database itself, not on the tables within it.

2. Grant Schema Usage: Next, you must grant permission to access the schema inside the database. Without this, the user can connect but cannot see any tables.

   GRANT USAGE ON SCHEMA public TO sammy;
   
   

3. Grant Table Permissions: Now, you can grant the specific permissions the user needs on the objects inside the schema. Connect to the database as a superuser to do this:

   \c my_database
   
   

   You are now connected to my_database.

4. Grant Permissions on Existing Objects: To give the sammy role read/write access to all existing tables, run:

   GRANT ALL ON ALL TABLES IN SCHEMA public TO sammy;
   GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO sammy;
   
   

   If you only want to grant read-only access, you would use GRANT SELECT instead of GRANT ALL.

5. (Optional) Grant Permissions for Future Objects: The commands above only affect existing tables. To automatically grant permissions for any new tables created in the future (e.g., by an admin), you must alter the default privileges:

   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO sammy;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO sammy;
   
   

6. What are the basic PostgreSQL commands for beginners?

The most important tool to learn is the psql interactive terminal. Once you are logged in (e.g., by running sudo -i -u postgres and then psql), you can use meta-commands (which start with a backslash \) to navigate and inspect your database.

Here are some of the most common psql commands:

7. How do I connect to a PostgreSQL database from the terminal?

How you connect depends on the authentication method configured in the pg_hba.conf file. (This file is typically located at /etc/postgresql/<version>/main/pg_hba.conf). By default, PostgreSQL on Ubuntu uses peer authentication for local connections.

peer authentication works by obtaining the client’s operating system username from the kernel and using it as the allowed database username. This only works for local connections made via a Unix socket.

1. Connecting as the postgres admin (Local peer auth)

This is the simplest way to connect locally. You use sudo to run commands as the postgres Linux user, which peer authentication automatically maps to the postgres database superuser.

sudo -i -u postgres
psql

This connects you via a local Unix socket to the default database named postgres.

2. Connecting as a specific user (Local peer vs. TCP/IP password auth)

Let’s say you created a PostgreSQL role named sammy but you are logged into your server as a different Linux user (e.g., ubuntu).

• Attempt 1: Fails (Peer Auth Mismatch): If you run psql -U sammy -d my_database, it will likely fail. This command attempts to use the local Unix socket, so peer authentication tries to match your OS user (ubuntu) with the database user (sammy). Since they don’t match, the connection is refused.

• Attempt 2: Success (Password Auth): To bypass peer authentication, you can force a TCP/IP (network) connection using -h localhost. By default, Ubuntu’s pg_hba.conf file is configured to use md5 or scram-sha-256 (password) authentication for local TCP/IP connections.

  psql -U sammy -d my_database -h localhost
  
  

  The terminal will then prompt you for the password you set for the sammy role.

  Tip: To avoid entering a password for every connection, you can create a .pgpass file in your home directory. This file securely stores your connection passwords. Make sure to set its file permissions to 0600 (read/write for your user only).

• Attempt 3: Success (Peer Auth Match): If you also have a Linux system user named sammy, you could use sudo to run psql as that user. peer authentication would then succeed.

  sudo -i -u sammy
  psql -d my_database
  
  

This guide provided a complete walkthrough for installing and managing PostgreSQL on an Ubuntu server. You began with the initial software installation, configured user authentication by creating roles, and established new databases. We then covered the fundamental SQL commands for data manipulation and explored essential administrative tasks, including performance configuration, backup and restore procedures, and troubleshooting common issues.

You now have a functional database server and the foundational skills required to maintain it securely and efficiently. The ability to manage data, handle user access, and ensure your database’s integrity prepares you to support complex applications.

If you’d like to learn more about Postgres and how to use it, we encourage you to check out the following guides:

• SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems
• Practice running queries with SQL