Postgres, also known as PostgreSQL, is a system for managing databases that uses the SQL language. It follows standard rules and offers advanced options like reliable transactions and the ability to handle multiple tasks simultaneously without causing reading delays.
The tutorial explains how to set up Postgres on an Ubuntu 20.04 server. It covers the installation process and includes instructions on creating a new user and database.
This tutorial focuses on PostgreSQL version
Step 1: Install PostgreSQL
Enable PostgreSQL APT Respository.
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update -y && sudo apt upgrade -y
We will now install PostgreSQL packages.
sudo apt install postgresql postgresql-contrib -y
Next, we will start the PostgreSQL service and enable it to start the service on reboot.
sudo systemctl start postgresql.service && sudo systemctl enable postgresql.service
Step 2: PostgreSQL Roles and Databases
Postgres, by default, employs a mechanism termed “roles” for both authentication and authorization tasks. This is somewhat akin to the typical Unix-style user and group system.
When you install Postgres, it’s configured to use ident authentication. This means it links Postgres roles to corresponding Unix/Linux system accounts. If a role is present in Postgres, a Unix/Linux user with an identical name can log in under that role.
During the installation, a user named Postgres is established, which corresponds to the primary Postgres role. There are multiple methods to access Postgres using this account. One method is to transition to the Postgres account on your server using a specific command.
sudo -i -u postgres
Now, you can access the Postgres prompt by running the below command:
This action will grant you access to the PostgreSQL interface, allowing you to immediately engage with the database management system.
To exit from the PostgreSQL interface, execute the subsequent command:
Once you have exited the PostgreSQL prompt, you can easily switch back to your logged-in user from the Postgres user.
Alternative way to connect to Postgres prompt:
sudo -i -u postgres psql
Step 3: Create A New Role in PostgreSQL
Once you are logged in as Postgres user, you can enter the below command:
Alternatively, you can also use the below command to run as sudo user.
sudo -u postgres createuser --interactive
postgres@tekspace-db-dev:~$ createuser --interactive Enter name of role to add: tekspace-db-dev 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
Step 4: Create A New Database in PostgreSQL
When Postgres sets things up, it expects that each role (or user) will have a database with the same name they can use.
So, if you made a user named “tekspace-db-dev” before, it will try to connect to a database named “tekspace-demo” automatically. To make this database, you can use the “createdb” command.
If you’re using the Postgres account, you’d type a command similar to this:
Alternatively, you can also run this with sudo user:
sudo -u postgres createdb tekspace-db-dev
Exit out of Postgres user:
Step 5: Open a Postgres Prompt with A New Role
To sign in using ident authentication, your Linux username should match your Postgres role and database name.
If you don’t have a Linux username like that, you can make one using the “adduser” command. Perform this action with an account that has the necessary privileges, not the main ‘Postgres’ account. Please log in using a different account called ‘sudo’ instead of the main ‘Postgres’ account.
sudo adduser tekspace-db-dev
rahil@tekspace-db-dev:~$ sudo adduser tekspace-db-dev [sudo] password for rahil: Adding user `tekspace-db-dev' ... Adding new group `tekspace-db-dev' (1001) ... Adding new user `tekspace-db-dev' (1001) with group `tekspace-db-dev (1001)' ... Creating home directory `/home/tekspace-db-dev' ... Copying files from `/etc/skel' ... New password: Retype new password: passwd: password updated successfully Changing the user information for tekspace-db-dev Enter the new value, or press ENTER for the default Full Name : Room Number : Work Phone : Home Phone : Other : Is the information correct? [Y/n] Y Adding new user `tekspace-db-dev' to supplemental / extra groups `users' ... Adding user `tekspace-db-dev' to group `users' ...
After a login user has been created, now you can navigate to Postgres prompt by executing the below command:
sudo -i -u tekspace-db-dev
To access the prompt in PostgreSQL as a newly created user, execute the following command after logging in:
If you want to connect to a different database, you can specify the database as shown below:
psql -d postgres
tekspace-db-dev@tekspace-db-dev:~$ psql -d postgres psql (15.4 (Ubuntu 15.4-0ubuntu0.23.04.1)) Type "help" for help.
Once you are logged-in, you can check your current connection information by executing the below command:
postgres=> \conninfo You are connected to database "postgres" as user "tekspace-db-dev" via socket in "/var/run/postgresql" at port "5432". postgres=>
That’s it! You have successfully set up a PostgreSQL server on an Ubuntu Linux server.
User & Database Management
CREATE DATABASE tekspace_db_dev; CREATE USER tekspace_db_dev_user WITH PASSWORD 'super_secret_password'; GRANT ALL PRIVILEGES ON DATABASE tekspace_db_dev to tekspace_db_dev_user;
Next connect with admin account to a newly created database:
psql -h localhost -U postgres -d tekspace_db_dev -W
This is a workaround for PostgreSQL 15.
GRANT ALL ON SCHEMA public TO tekspace_db_dev_user;
Now connect to a newly created database with its user:
psql -h localhost -U tekspace_db_dev_user -d tekspace_db_dev -W
Change Password For Postgres USER
To change the password for Postgres user follow below steps.
sudo -i -u postgres
ALTER USER postgres PASSWORD 'Super secret password';