PostgreSQL, often referred to as Postgres, is a widely-used relational database management system (RDBMS) that implements the SQL querying language.
It is known for its advanced features such as reliable transactions and concurrency without read locks.
This guide will walk you through the process of setting up Postgres on an Ubuntu 22.04 server. We’ll cover everything from installation to setting up a new user and database.
Prerequisites
Before we begin, ensure that you have a Ubuntu 22.04 server configured according to the Initial Server Setup for Ubuntu 22.04 guide. A basic firewall should be in place and a non-root user having sudo permissions should be set up on your server.
Read: Best open source database software
Step 1: Installing PostgreSQL
Start by refreshing your server’s local package index with the following command:
sudo apt update
Proceed by installing the -contrib package, which enhances utilities and functionality, along with the Postgres package:
sudo apt install postgresql postgresql-contrib
When prompted to confirm installation, press Y.
Step 2: Using PostgreSQL Roles and Databases
In Postgres, the handling of authentication and authorization is managed through a mechanism known as “roles”. These roles are similar to regular Unix-style users and groups.
Upon installation, Postgres is set up to use ident authentication, which associates Postgres roles with a matching Unix/Linux system account. If a role exists within Postgres, a Unix/Linux username with the same name can sign in as that role.
The default role in Postgres is linked to a user account known as postgres, which is created during the installation process. There are several methods to leverage this account for accessing Postgres.
Read: How to Install MySQL Workbench on Ubuntu 22.04
Setting up a Postgres Database
Creating a new database in PostgreSQL is a straightforward process. Here are the steps:
Access and start PostgreSQL on Ubuntu: You access the Shell by running the command sudo -u postgres psql in your terminal.
This will log you in as the ‘postgres’ user. Note that there is no default password for postgres in Ubuntu when you login using the method above. To establish a PostgreSQL user with a password, execute the command below, substituting user_name with your chosen username and user_password with your desired password:
postgres=# CREATE USER user_name PASSWORD user_password;
Create the new database: Once you’re in the PostgreSQL shell, you can create a new database using the CREATE DATABASE command followed by the name of the database you want to create. For example, if you want to create a database named ‘my_database’, you would run the following command:
CREATE DATABASE my_database;
Read: 7 Tips to create a database for beginners
Now, to list out the available databases, simply run the command:
\l
This will show the following output :
Now you can start to use a database in Postgres.
Exit the PostgreSQL shell: You can exit the PostgreSQL shell by typing \q and then hitting enter.
And that’s it! You’ve created a new database in PostgreSQL. Remember, you need to have the necessary privileges to create a database. By default, only superusers and users with the ‘CREATEDB’ privilege can create new databases.
If you like the content, we would appreciate your support by buying us a coffee. Thank you so much for your visit and support.
