PostgreSQL (Postgres) is an open-source, general-purpose object-relational database management system with numerous advanced capabilities, allowing you to build fault-tolerant systems or complex applications.
In this tutorial, you will learn how to install PostgreSQL on Ubuntu 20.04.
Update the System
Connect to your server and run system updates:
sudo apt update
Install PostgreSQL
Install the PostgreSQL package along with the -contrib package, which adds some additional utilities and functionality:
sudo apt install postgresql postgresql-contrib
Start the PostgreSQL Service
Ensure that the PostgreSQL service is running:
sudo systemctl start postgresql.service
Accessing the PostgreSQL Prompt
PostgreSQL uses "roles" to handle authorization and authentication by default. Switch to the postgres user:
sudo -i -u postgres
Then, access the PostgreSQL prompt:
psql
This will log you into the PostgreSQL prompt, where you can interact with the database management system.
Exit the PostgreSQL Prompt
To exit the PostgreSQL prompt, use:
\q
Creating a New Role
If you are logged in as the postgres account, you can create a new role by running:
createuser --interactive
You will need to enter the name of the new role and specify if the role should be a superuser.
Creating a Database
PostgreSQL assumes that each role used to log in will have a database with the same name. For example, if the new role is named test, it will attempt to connect to a database named test by default. Create the necessary database with:
createdb test
If you want to use sudo for each command without switching from your normal account:
sudo -u postgres createdb test
Creating a Linux User
To log in with ident-based authentication, you need a Linux user with the same name as your PostgreSQL role and database. If you don't have a matching Linux user, create one:
sudo adduser test
Connecting to the Database
Switch to the new account and connect to the database:
sudo -i -u test
psql
To connect to a different database, specify the database name:
psql -d postgres
Checking Connection Information
After connecting, check your current connection information:
\conninfo
