What is PostgreSQL?
“PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. The origins of PostgreSQL date back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform.”
This powerful system comes along with various features that aid developers to manage their databases on applications, to build them and also protect the integrity of data.
It is an open source system and can be easily downloaded for free. Apart from that, it is greatly extensible, developers can define their own customised functions, use different coding languages without needing to alter your database!
In today’s blog, let us have a look at how to install PostgreSQL11 on our Ubuntu 18.04 system.The default repositories in Ubuntu contains PostgreSQL packages.
In case, they are missing or unavailable, you can add the PostgreSQL apt repository to your Ubuntu, you can use these following commands suggested on the official website.
Step 1: Install Certificates
sudo apt-get install wget ca-certificates
Step 2: Import and Add GPG Key
You will need to import the GPG key for accessing the PostgreSQL packages.
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Step 3: Add PostgreSQL repository to your system
This command will add the repository to your system.
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
Step 4: Install PostgreSQL on Ubuntu
Once the official repository is added to our Ubuntu system, we will have to update the repository list.
sudo apt-get update
After updating, the next command installs PostgreSQL on your system.
sudo apt-get install postgresql postgresql-contrib
Working with the PostgreSQL account
After installing PostgreSQL, a default account is created with the default role. We can log into that account and start using PostgreSQL. This account has a username ‘Postgres’ and role assigned as ‘Postgres’
You can switch to the postgres account by using the following command. The password will remain the same as the login password.
sudo -i -u postgres
The postgresql client also gets installed by default when we install postgresql
in the initial steps. So let’s use the psql
command to connect and start accessing our Postgre databases
psql
This command will help you interact with your databases.
You can exit and disconnect from the ProgreSQL prompt by using
postgres-# \q
To create a new role on PostgreSQL
The role we previously used was the “postgres” role. To create a new user (more precisely a new role), use the command line with the createuser command. By using the –interactive flag, you can set the name for your new role. It also allows setting superuser permissions.
As you are already logged in and are using the “postgres” role, use the following commands,
createuser --interactive
If, instead, you prefer to use sudo for each command without switching from your normal account, type:
sudo -u postgres createuser --interactive
The command prompts a few choices to select from, and a role is created based on the responses submitted by the user.
# Output
Enter name of role to add: newuser
Shall the new role be a superuser? (y/n) y
Working with PostgreSQL service
After the PostgreSQL installation is complete, it gets listed as a service by default in Ubuntu and starts by itself. Let’s check if it has started by default.
Check PostgreSQL service status
service postgresql status
Start PostgreSQL service
If it has not started by itself, let us do that instead.
Start PostgreSQL service
service postgresql start
Restart PostgreSQL service
service postgresql restart
Stop PostgreSQL service
service postgresql stop
That’s all about the few key steps to follow while installing PostgreSQL on your Ubuntu system!