PostgreSQL, aka Postgres, is an open source relational database management system (RDBMS) that’s become very popular in the IT industry, particularly in the app and web development.
Thanks to its open source background, Postgres works excellently on all platforms, including Linux, Windows, and macOS. In fact, it is the default database for macOS powered servers. It comes with a standard implementation of SQL, support for concurrency, and very reliable transactions.
This tutorial covers the process of installing PostgreSQL on Ubuntu servers.
Why Should You Consider PostgreSQL for Your Projects
The popularity of PostgreSQL is because of the following factors that come together and make it a dependable RDBMS for your projects:
- A diverse community that helps you set up and use PostgreSQL for your projects.
- Support for Stored Procedures (SQL functions) speeds up data operations
- PostgreSQL supports multiple indexing techniques, including GIN and GiST
- Simple full-text search for strings
- PostgreSQL is an excellent option for geographically distributed data stores.
Prerequisites
To install PostgreSQL on Ubuntu, you’ll need the following:
- A server running Ubuntu 22.04 (or the latest stable version)
- A non-root user with administrator (sudo) privilege
- A basic firewall on the server
As you can see, security is always an important consideration whenever you do anything related to data. If you’re setting up PostgreSQL for your active projects, you should consider more robust security measures.
Install PostgreSQL on Ubuntu
Now that you have the basic information about PostgreSQL and the prerequisites of the process, let’s dive into the details of the process.
Step 1: Update the Server Packages
Start by updating the package index of the Ubuntu server with the following command:
sudo apt update
Step 2: Install the PostgreSQL Package
Next, we’ll download and install the PostgreSQL package. This download will also include utilities that would help us better use PostgreSQL.
Use the following command to download the postgresql-contrib package:
sudo apt install postgresql postgresql-contrib
During the process, you’ll be prompted to confirm that you want to install the package and restart the service after installation.
Step 3: Confirm PostgreSQL Service is Up and Running
We recommend you check if the PostgreSQL service is running. For this check the status with the following command:
sudo service postgresql status
Access PostgreSQL Database
Now that PostgreSQL is installed and running, you can access the PostgreSQL database.
During the installation process, PostgreSQL creates a user named postgres. We’ll use this user to access the database engine. To switch over to the PostgreSQL user, use the following command:
sudo -i -u postgres
Next, access the PostgreSQL prompt with the command:
psql
This launches the PostgreSQL command line utility that allows you to access the databases and users.
Check Available Databases and Users
The first thing you should do after installing PostgreSQL is to check what databases and users are available.
First, to see the list of all databases created on the server, run the following command:
\l
Next, check the list of all the users with their privileges using the following command:
\du
You should note that the default user postgres has no password. If you want to set it up, you can use the following command:
\password postgres
You will be prompted to set up the password. Remember to press ENTER to complete the process at the second entry.
Finally, to quit the PostgreSQL prompt, run the following command:
\q
You’ll be returned to the PostgreSQL user command prompt. If you wish to return to the logged-in user, run the exit command:
exit
Add A New User
When using PostgreSQL, you might want to add a new user in addition to the default postgres user. This is often a requirement when you want to add users with different roles and access rights. PostgreSQL has a simple process of adding new users and defining their roles.
Use the following command to add a new user:
createuser --interactive
Because of the –interactive switch, the script will prompt you to answer a question. Based on your responses, the script executes the correct commands to create a user to your specifications.
Now to log in to PostgreSQL using the credentials for the redswitches user, you need to have a registered user. Fortunately, you can add a user.
Important: You need to be logged in with the non-root account with the sudo rights we mentioned in the prerequisites.
sudo adduser redswitches
Once the account has been added, you can switch over to it and connect to the database:
sudo -i -u redswitches
psql
This command will log you in automatically, assuming that all components have been appropriately configured.
Once logged in as redswitches, you can get check your current connection information by running the following command:
\conninfo
Create a New Database
Now that you have added a user, it’s time to add a new database.
Notice that we added a user named redswitches to PostgreSQL. To add a new database, you must be logged in with this user to conform to the PostgreSQL authentication processes.
We’ll now add a new database (also named redswitches) with the following command.
createdb redswitches
This will create a database with the name redswitches.
Conclusion
We hope you now have a clear idea of how to install PostgreSQL on Ubuntu server. We also covered the process of adding a new user and a database. We highly recommend using strong passwords and usernames to improve database security. If you need further help in setting up PostgreSQL on your Ubuntu server, post your question in the comments for our experts.
