PostgreSQL is a fully featured database management system (DBMS) with a strong emphasis on extensibility and SQL compliance. It is backed by 20 years of open-source development, and supports both SQL (relational) and JSON (non-relational) querying. It is one of the most popular databases in the industry that is used for various web, mobile and analytics applications. Let‘s now go through a step-by-step guide of how to install PostgreSQL on Ubuntu 20.04 VPS machine.
OS: Ubuntu 20.04
Resources: 2-core CPU & 2GB of RAM
Access: SSH connection to the server
Permissions: a user with ‘sudo’ privileges
It is always a good idea to download information about all packages available for installation from your configured sources before the actual installation.
$ sudo apt update
Then, install the Postgres package along with a -contrib package that adds some additional utilities and functionality:
$ sudo apt install postgresql postgresql-contrib
Ensure that the service is started:
$ sudo systemctl start postgresql
After the installation you may double-check that postgresql daemon is active.
$ service postgresql status
If the version included in your version of Ubuntu is not the one you want, you can use the PostgreSQL Apt Repository. This repository will integrate with your normal systems and patch management, and provide automatic updates for all supported versions of PostgreSQL throughout the support lifetime of PostgreSQL.
# Create the file repository configuration: sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' # Import the repository signing key: wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - # Update the package lists: sudo apt-get update # Install the latest version of PostgreSQL. # If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql': sudo apt-get -y install postgresql
For more information about the apt repository, including answers to frequent questions, please see the PostgreSQL Apt Repository page on the wiki.
When you install PostgreSQL on Ubuntu a default admin user “postgres” is created by the default. You must use it to log-in to your PostgreSQL database for the first time.
A “psql” command-line client tool is used to interact with the database engine. You should invoke it as a “postgres” user to start an interactive session with your local database.
The installation procedure created a user account called postgres that is associated with the default Postgres role. There are a few ways to utilize this account to access Postgres.
$ sudo -i -u postgres
Then you can access the Postgres prompt by running:
$ psql
This will log you into the PostgreSQL prompt, and from here you are free to interact with the database management system right away. To exit out of the PostgreSQL prompt, run the following:
\q
This will bring you back to the postgres Linux command prompt. To return to your regular system user, run the exit command:
exit
$ sudo -u postgres psql
This will log you directly into Postgres without the intermediary bash shell in between. After first launching psql, you may check the details of your connection by typing \conninfo into the interpreter.
You are now connected to database "postgres" as user "postgres". If you want to see a list of all the databases that are available on a server, use \l command.
And to see a list of all the users with their privileges use \du command.
Since the default “postgres” user does not have a password, you should set it yourself.
\password postgres
Again, you can exit the interactive Postgres session by running the following:
\q
You can create a new role by running the following command:
$ sudo -u postgres createuser --interactive
Either way, the script will prompt you with some choices and, based on your responses, execute the correct Postgres commands to create a user to your specifications.
Output Enter name of role to add: tommy Shall the new role be a superuser? (y/n) y
Another assumption that the Postgres authentication system makes by default is that for any role used to log in, that role will have a database with the same name which it can access.
This means that if the user you created in the last section is called tommy, that role will attempt to connect to a database which is also called “tommy” by default. You can create the appropriate database with the createdb command.
$ sudo -u postgres createdb tommy
To log in with ident based authentication, you’ll need a Linux user with the same name as your Postgres role and database.
If you don’t have a matching Linux user available, you can create one with the adduser command. You will have to do this from your non-root account with sudo privileges:
$ sudo adduser tommy
Once this new account is available, you can either switch over and connect to the database by running the following:
$ sudo -u tommy psql
This command will log you in automatically, assuming that all of the components have been properly configured.
If you want your user to connect to a different database, you can do so by specifying the database like the following:
\c tommy
Once logged in, you can get check your current connection information by running:
tommy=# \conninfo You are connected to database "tommy" as user "tommy" via socket in "/var/run/postgresql" at port "5432".
Congrats! You have successfully installed a PostgreSQL database on your Ubuntu 20.04 server. The tutorial is tested on VPS Mart, so it works fine on our Ubuntu VPS Hosting.