First things first, we want to get an environment set up where you can experiment, learn, and start building! In this guide, we will walk through installing PostgreSQL in Ubuntu Server, configuring it with a database and a user, and connecting to it. This guide makes the assumption that you already have an instance of Ubuntu Server up and running.
Installing PostgreSQL
In debian based Linux distributions like Ubuntu, you install software from repositories using a package manager called apt. We can also use this utility to make sure that our repositories urls are up-to-date and upgrade our system.
Update the repositories:
sudo apt update
Upgrade system and software:
sudo apt upgrade
Once we have updated our repositories and updated our system and software we are ready to install PostgreSQL and its dependencies:
sudo apt install postgresql postgresql-contrib
Believe it or not, PostgreSQL is now installed and running. We can verify that it is running with the following command:
sudo service postgresql status
You can start, stop, and restart the service by issuing the same command but replacing status with the respective bolded keyword.
Accessing PostgreSQL
Now we want to set up our user so we don’t use the default postgres user which is bad security practice. For this step, you will want to know the username of your user on linux. You can find this in your terminal prompt:
your_user@computer_name:~$
Make a note of your username for future use.
To access PostgreSQL the first time, we have to use the postgres linux user it created during the installation.
Switch to the postgres user:
sudo su postgres
You should see your bash prompt change to show the new username.
Once we are logged into the postgres user, we can use the psql application to interact with the system:
psql
The prompt should now show postgres=# which means you are logged into the database system.
You can exit psql by entering the command:
\q
PostgreSQL uses linux authentication by default to authenticate users to the system. However, before you can login using your user, we need to create a role to match our linux user:
createuser --interactive *Note this command only works on the postgres user.
Enter name of role to add: your_username (the one you took note of earlier)
Shall the new role be a superuser? (y/n) y
We also need a database to use. For this tutorial, we will create one called classifieds.
createdb classifieds *Note this command only works on the postgres user.
We can logout of the linux user postgres:
exit
Finally, we can use our own user to connect to our newly created database:
psql -d classifieds
You should see classifieds=# when you are logged in and connected.
You can list the databases with the command \l and show the tables in the current database with the \d command.
When you are ready to quit psql, you can issue the \q command.