Setting up PostgreSQL database on Fedora

How to do the initial set up for working with PostgreSQL on Fedora. I will cover the installation, creating a database, and binding that database to a particular non-root user so that it is easy to use. I will also show couple of basic commands so that it is easy to check users/database owners etc.

I assume that you already have a non-root user created on your Fedora system. This user should have sudo privileges. If you don't have one or don't know how to make one, consult Google.

Installing PostgreSQL is pretty straightforward, all it takes is these three commands

$ sudo dnf install postgresql postgresql-server
$ sudo postgresql-setup initdb
$ sudo systemctl start postgresql

To check if it is installed and running successfully, type this command

$ sudo systemctl status postgresql

You should see that it is active and running.

During installation PostgreSQL automatically creates a user called 'postgres' and a database with the same name 'postgres'.  This 'postgres' user is a database superuser, similar to root in Linux. It means it can create other users, give them privileges, delete users, it can create other databases, delete them and so on.

Normally you don't want to operate as this admin superuser, just like you don't want to operate as root user in Linux.

So the first thing you want to do after installation is creating another user and creating a database for that user. 

Log in as a PostgreSQL admin and create a PostgreSQL user.

$ sudo su -postgress 
$ createuser <your name>

What I always do for simplicity is that I create a new PostgreSQL user with the same name as is my UNIX username.

On this machine, my UNIX username is lukas 

[lukas@localhost~] $ 

So I create a PostgreSQL user with the same name and I also create a database with the same name. Here is how it looks like.

[lukas@localhost ~]$ sudo su - postgres
[postgres@localhost ~]$ createuser lukas
[postgres@localhost ~]$ createdb lukas --owner lukas

This created database with name lukas and lukas as the owner. To check it, type psql which is a command that opens Postgres shell and there type \l , which will list all the databases and its owners. 

postgres=# \l

Notice that the user in the prompt is 'postgres' - the admin supersuer. This is normally not the user you want to operate with. To exit the shell and get back to your UNIX user, type exit and exit.

postgres=# exit
[postgres@localhost ~]$ exit
logout
[lukas@localhost ~]$ 

The first exit will jump out of the Postgres shell the second will jump out from the Postgres admin.

Now because you have set up your database to be the same name as you UNIX user, in order to connect to that database, you can simply write psql command.

[lukas@localhost ~]$ psql
psql (11.8)
Type "help" for help.

lukas=> 

It will immediately open the Postgres shell, and connects to the lukas (default) database. You can check it out by typing \conninfo.

lukas=> \conninfo 
You are connected to database "lukas" as user "lukas" via socket in "/var/run/postgresql" at port "5432".

This is very handy and easy, because sometimes you just want to quickly open a database and work. I don't need to specify which database I want to connect, which user, password, etc. No questions asked and I am there. 

For a production database you will probably want to set up a password and make it somehow more elaborate but for simple use-case this ok.

If you want to check all the databases, type \l within the Postgres shell. 

lukas=> \l

If you want to checll the users, type \du within the Postgres shell.

lukas=> \du

In both cases, a table will be listed where you can see the details.