How to Install and Configure PostgreSQL on Ubuntu

Edited 3 weeks ago by ExtremeHow Editorial Team

PostgreSQLDatabaseUbuntuServerLinuxInstallationConfigurationOperating SystemsSystemSetup

PostgreSQL is a powerful, open-source relational database system that uses the SQL language and combines it with a number of features that securely store and scale the most complex data workloads. In this guide, we will walk through the steps to install and configure PostgreSQL on Ubuntu. We will make sure to cover each part in detail so that even if you are new to databases or Ubuntu, you should be able to follow along.

Step 1: Update your system

The first step to setup PostgreSQL on Ubuntu is to make sure your package list is up-to-date. Open your terminal and type the following command:

sudo apt update sudo apt upgrade -y

sudo apt update command updates your local package index with the latest changes made to the package repositories, while sudo apt upgrade -y will install the latest version of all packages currently installed on your Ubuntu system for which updates are available.

Step 2: Install PostgreSQL

Ubuntu includes PostgreSQL by default. You can install it by executing the following command:

sudo apt install postgresql postgresql-contrib -y

This command installs both the PostgreSQL and postgresql-contrib packages, which provide additional functionality for PostgreSQL.

Step 3: Start and enable PostgreSQL

PostgreSQL should start automatically after installation. To ensure that the PostgreSQL service is running and will start on boot, use the following command:

sudo systemctl start postgresql sudo systemctl enable postgresql

The first command starts the PostgreSQL service, and the second command ensures that it starts automatically when the system reboots.

Step 4: Check PostgreSQL status

To check if PostgreSQL is running correctly, you can use the following command:

sudo systemctl status postgresql

You should see output that indicates that the PostgreSQL service is active and running. If there are any problems, the status command should provide some hints that can help with troubleshooting.

Step 5: Adjusting PostgreSQL user authentication

By default, PostgreSQL installs a role called postgres which is the default superuser. First, we need to switch to the postgres account and access an interactive PostgreSQL session.

sudo -i -u postgres psql

This command changes the account to postgres and opens the PostgreSQL command line interface. You can exit a PostgreSQL session at any time:

\q

Step 6: Create a new role

You may want to create new roles for users other than the default postgres. To create a new role, use the following command in a PostgreSQL session:

CREATE ROLE myuser WITH LOGIN PASSWORD 'mypassword';

This command creates a new role named myuser with the password mypassword.

Step 7: Creating the new database

Once you have the new user, you can create a new database using the following command. If myuser is different, replace it with the role you created.

CREATE DATABASE mydatabase OWNER myuser;

This command creates a new database named mydatabase with myuser as the owner.

Step 8: Configure remote access

By default, PostgreSQL does not allow remote connections. You can configure PostgreSQL to allow this if necessary. Open the postgresql.conf file, usually located at /etc/postgresql/<version>/main/postgresql.conf, and adjust the listen addresses setting:

listen_addresses = '*'

Then, modify the pg_hba.conf file, which is usually in the same directory, to include an entry for your IP address and database:

host all all 0.0.0.0/0 md5

This line allows all users to connect from all IP addresses using md5 password encryption. Be sure to adjust the IP and security settings to suit your needs.

Step 9: Restart PostgreSQL

After making changes to the configuration files, you must restart the PostgreSQL service to apply the changes:

sudo systemctl restart postgresql

Step 10: Connecting to your database

Now that PostgreSQL is set up and connected, you can connect to your new database using the following psql command:

psql -U myuser -d mydatabase -h 127.0.0.1 -W

After executing this command, you will be asked to enter the password for myuser. If everything goes well, you will be connected to mydatabase database.

Conclusion

Installing and configuring PostgreSQL on Ubuntu is a straightforward process that can greatly enhance your system's ability to handle relational databases. This tutorial covers the basic installation, configuration, and initial setup required to start using PostgreSQL on your Ubuntu machine. Remember to keep your database secure and back up your data regularly.

  Share This Article

If you find anything wrong with the article content, you can request an update