Introduction
PostgreSQL (also known as Postgres) is a powerful open-source, Object-Relational Database-Management System (ORDBMS) that is free to use, adjust, and distribute. It’s an advanced database system that packs a punch in terms of innovative features with strong emphasis on standard compliance and extensibility.
For these reasons, PostgreSQL can handle a range of workloads and allow users to create simple or multifaceted web applications without a struggle.
This tutorial will help you install PostgreSQL database system on your Ubuntu 18.04 server and perform the basic database management tasks.
Prerequisites
To install PostgreSQL on Ubuntu 18.04, you must:
- Be logged into your Ubuntu 18.04 server as a user with sudo permissions.
Step 1 Installing PostgreSQL
Ubuntu 18.04 default repository comes with PostgreSQL packages. For this reason, we’ll install the packages using the apt command system. Since we are using the apt command for the first time in this session, we’ll refresh the package index, before executing any command. Once, the package index is refreshed we’ll install PostgreSQL together with the -contrib package:
$ sudo apt update $ sudo apt install postgresql postgresql-contrib
Step 2 Verifying Postgres Installation
The PostgreSQL database server should start automatically once the installation is completed. However, we need to verify our installation. Let’s use the psql; a reliable command line utility that permits us to use the PostgreSQL database server. Run the command below to verify the installation:
$ sudo -u postgres psql -c "SELECTversion();"
This will give you an output similar to the one below:
PostgreSQL 10.5 (ubuntu 10.50.18.4) onx86_64-pc-linux-gnu, compiledbygcc (ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit
That is it! You have installed Postgres on your Ubuntu 18.04 server. Next, we’ll explore the unique quality of this database management system.
Step 3 Using Postgres Databases And Roles
By default, PostgreSQL utilizes “roles” concept to process authorization and authentication. Once, it’s installed this database system is configured to leverage the ident authentication, which associates PostgreSQL roles with the corresponding Linux/Unix system account. If a role is present within PostgreSQL, a Linux/Unix username with a matching name can easily sign in as that role.
Our installation created an account titled Postgres which is linked to the default PostgreSQL role. You can easily login into that username account to utilize PostgreSQL. There multiple ways of using this account to get permission to use Postgres.
Switching The PostgreSQL Account
Now, execute the command below to change to the default Postgres account.
$ sudo -i -u postgres
Then, run the command below to log into the PostgreSQL prompt.
$ psql
You can now use the database system management. In case, you want to exit the Postgres prompt, run the command below:
postgres=# q
This will take you back to the database management system Linux command prompt.
Accessing PostgreSQL database server Prompt without changing accounts
If you want to execute commands directly with sudo, run the command below to log into Postgres without an intermediary bash shell:
$ sudo -u postgres psql
To exit the Postgres session, run the command below:
postgres=# q
Creating New Role
By now, you have a Postgre role created within your database. You can now proceed and create another role using the creatrole command. Log in as a Postgres account and execute the command below to create another role:
postgres@server: createuser --interactive
The –interactive flag in the command prompts you to enter a name for the role created and requests you to grant or deny the role superuser permissions.
Alternatively, you can use sudo to fashion a new role:
$ sudo -u postgres createuser --interactive
This will give you an output with some questions, like the one below
Enter name of role to add: HA Shall the new role be a superuser? (y/n)
Type Y and press ENTER to grant the new user superuser privileges. Alternatively, you can type Y and press ENTER to deny the user superuser permissions.
Creating New Database
To fashion a new database follow the steps below:
- If you are logged into the PostgreSQL account as Postgres account, execute the command below to create a database:
postgre@server:$ createdb HA
- On the other hand, if you prefer to use sudo, run the command below to fashion a new database;
$ sudo -u postgres createdb HA
Using The New Role To Open The PostgreSQL prompt
PostgreSQL database management system gives the option to use ident-based authentication to log into the Postgres prompt. Here, you require a user with a name similar to the Postgres database and role.
If your Linux username doesn’t match that of the Postgres database and role, you can use adduser to create one. To achieve this, you must be logged in to your Linux server as a user with sudo privileges.
Execute the command below, to create a user:
$ sudo adduser HA
This will automatically create the new account. Once it’s created, switch over to connect to your database:
$ sudo -i -u HA $ psql
Alternatively, you can run the command below to achieve this inline:
$ sudo -u HA psql
If all the components are properly configured, you will be logged in automatically.
Additionally, you can connect to a specific database by specifying the database name in the command below:
$ psql -d postgres
Once you log in, run the command below to examine the current connection information:
HA =# conninfo
This will give you an output similar to the one below:
You are connected to database "HA"as user "HA" via socket in"/var/run/postgresql" at port "5432".
Conclusion
That’s it! You have successfully installed PostgreSQL on Ubuntu 18.04 and learned how to perform some of the most basic tasks. You can explore further PostgreSQL management tasks such as creating/deleting tables, adding/deleting columns, and more.
Check out these top 3 Linux hosting services
- Want to get top recommendations about best hosting? Just click this link!
