- PostgreSQL is a database management system, similar to MySQL in many respects but with some key differences. Like MySQL, it's commonly hosted on Linux. In this guide, we'll show how to run a PostgreSQL server on Ubuntu 20.04 Focal Fossa, as well as installing the client version in case you just need to connect to an external PostgreSQL database.
- Classic aarch64 Official postgresql10-10.18-alt1.aarch64.rpm: PostgreSQL client programs and libraries: postgresql11-11.13-alt1.aarch64.rpm: PostgreSQL client programs and libraries.
In this tutorial, you'll learn how to install and use the open source database PostgreSQL on Ubuntu Linux.
Jul 11, 2021 Then, install the postgresql package along with a postgresql-contrib package.The addition of the -contrib package ensures that you get some extra utilities and features. So, to install the default supported PostgreSQL version provided by Ubuntu's repository, run.
PostgreSQL (or Postgres) is a powerful, free and open-source relational database management system (RDBMS) that has a strong reputation for reliability, feature robustness, and performance. It is designed to handle various tasks, of any size. It is cross-platform, and the default database for macOS Server.
PostgreSQL might just be the right tool for you if you're a fan of a simple to use SQL database manager. It supports SQL standards and offers additional features, while also being heavily extendable by the user as the user can add data types, functions, and do many more things.
Earlier I discussed installing MySQL on Ubuntu. In this article, I'll show you how to install and configure PostgreSQL, so that you are ready to use it to suit whatever your needs may be.
Installing PostgreSQL on Ubuntu
PostgreSQL is available in Ubuntu main repository. However, like many other development tools, it may not be the latest version.
First check the PostgreSQL version available in Ubuntu repositories using this apt command in the terminal:
In my Ubuntu 18.04, it showed that the available version of PostgreSQL is version 10 (10+190 means version 10) whereas PostgreSQL version 11 is already released.
Based on this information, you can make your mind whether you want to install the version available from Ubuntu or you want to get the latest released version of PostgreSQL.
I'll show both methods to you.
Method 1: Install PostgreSQL from Ubuntu repositories
In the terminal, use the following command to install PostgreSQL
Enter your password when asked and you should have it installed in a few seconds/minutes depending on your internet speed. Speaking of that, feel free to check various network bandwidth in Ubuntu.
The postgresql-contrib or the contrib package consists some additional utilities and functionalities that are not part of the core PostgreSQL package. In most cases, it's good to have the contrib package installed along with the PostgreSQL core.
Method 2: Installing the latest version 11 of PostgreSQL in Ubuntu
To install PostgreSQL 11, you need to add the official PostgreSQL repository in your sources.list, add its certificate and then install it from there.
Don't worry, it's not complicated. Just follow these steps.
Add the GPG key first:
Now add the repository with the below command. If you are using Linux Mint, you'll have to manually replace the `lsb_release -cs` the Ubuntu version your Mint release is based on.
Everything is ready now. Install PostgreSQL with the following commands:
You may also install a GUI application (pgAdmin) for managing PostgreSQL databases:
sudo apt install pgadmin4
Configuring PostgreSQL
You can check if PostgreSQL is running by executing:
Via the service command you can also start, stop or restartpostgresql. Typing in service postgresql and pressing Enter should output all options. Now, onto the users.
To install PostgreSQL 11, you need to add the official PostgreSQL repository in your sources.list, add its certificate and then install it from there.
Don't worry, it's not complicated. Just follow these steps.
Add the GPG key first:
Now add the repository with the below command. If you are using Linux Mint, you'll have to manually replace the `lsb_release -cs` the Ubuntu version your Mint release is based on.
Everything is ready now. Install PostgreSQL with the following commands:
You may also install a GUI application (pgAdmin) for managing PostgreSQL databases:
sudo apt install pgadmin4
Configuring PostgreSQL
You can check if PostgreSQL is running by executing:
Via the service command you can also start, stop or restartpostgresql. Typing in service postgresql and pressing Enter should output all options. Now, onto the users.
By default, PostgreSQL creates a special user postgres that has all rights. To actually use PostgreSQL, you must first log in to that account:
Your prompt should change to something similar to:
Now, run the PostgreSQL Shell with the utility psql:
You should be prompted with:
You can type in q to quit and ? for help.
To see all existing tables, enter:
The output will look similar to this (Hit the key q to exit this view):
With du you can display the PostgreSQL users:
You can change the password of any user (including postgres) with:
Note:Replace postgres with the name of the user and my_password with the wanted password. Also, don't forget the ; (semicolumn) after every statement.
It is recommended that you create another user (it is bad practice to use the default postgres user). To do so, use the command:
If you run du, you will see, however, that my_user has no attributes yet. Let's add Superuser to it:
You can remove users with:
To log in as another user, quit the prompt (q) and then use the command:
You can connect directly to a database with the -d flag:
You should call the PostgreSQL user the same as another existing user. For example, my use is ubuntu. To log in, from the terminal I use:
Note:You must specify a database (by default it will try connecting you to the database named the same as the user you are logged in as).
If you have a the error:
Make sure you are logging as the correct user and edit /etc/postgresql/11/main/pg_hba.conf with administrator rights:
Note:Replace 11 with your version (e.g. 10).
Here, replace the line:
Postgresql Client Ubuntu Free
With:
Then restart PostgreSQL:
Using PostgreSQL is the same as using any other SQL type database. I won't go into the specific commands, since this article is about getting you started with a working setup. However, here is a very useful gist to reference! Also, the man page (man psql) and the documentation are very helpful.
Wrapping Up
Reading this article has hopefully guided you through the process of installing and preparing PostgreSQL on an Ubuntu system. If you are new to SQL, you should read this article to know the basic SQL commands:
If you have any issues or questions, please feel free to ask in the comment section.
In this article, we will see how to uninstall PostgreSQL from Ubuntu.
Important Note: I suggest you to take backup of your important PostgreSQL databases. Once we uninstall, all your PostgreSQL data will be lost.
1. Stop PostgreSQL server using below command.
2. Lets verify PostgreSQL packages by using dpkg.
3. Remove all PostgreSQL related packages completely using below command.
Press Y to continue.
Use tab and select Yes then press Enter.
4. Use the rm command with the -rf options to recursively remove all of the directories and data for the postgresql packages:
Install Postgresql Ubuntu 20
5. After complete rm commands, execute the dpkg -l | grep postgres command one more time to verify that all of the packages have been removed.
So, we have successfully uninstalled PostgreSQL from Ubuntu Linux machine.
Related Posts:
