How to setup PostgreSQL on Fedora

Not only MySQL or MariaDB you can use for web development. My favourite database system is PostgreSQL. PostgreSQL is more like Oracle and has many and many features (and extensions). So how to install it on Fedora linux...

$ sudo dnf install postgresql-server postgresql-contrib

By default the postgresql server is disabled and of course turned off. You can enable its start during the boot your system following this command:

$ sudo systemctl enable postgresql

If you try to start it after installation with command...

$ sudo systemctl start postgresql

...you will probably end with an error like this:
Job for postgresql.service failed. See 'systemctl status postgresql.service' and 'journalctl -xn' for details.

It's ok because postgresql needs to be populated with initial data after instalation. It means you have to create the configuration files postgresql.conf and pg_hba.conf. You can do that by the following command:

$ sudo postgresql-setup initdb

Firewall

Of course you should enable postgresql ports on firewall:

$ firewall-cmd --add-port=5432/tcp
$ firewall-cmd --permanent --add-port=5432/tcp

Database management

It is more comfortable to use graphical interface like the web application phpPgAdmin or the desktop app PgAdmin. Both of them you can install with command:

$ sudo dnf install phpPgAdmin
$ sudo dnf install pgadmin3

Basic security configuration

It is good idea to create new user and new database. For that You should switch to postgres user and then run postgre's interactive shell:

$ sudo su
$ su - postgres
$ psql

Setup password for postgres user

postgres=# \password postgres

User and database creation

Commands for postgre's interactive shell:

postgres=# CREATE USER joe WITH PASSWORD 'superfriend';
postgres=# CREATE DATABASE sandwich OWNER joe;

Configuration

For postgres configuration there are two main config files (/var/lib/pgsql/data/postgresql.conf and /var/lib/pgsql/data/pg_hba.conf).
If you want postgres to accept network connections, you should change in the postgresql.conf:

listen_addresses = 'localhost'

to

listen_addresses = '*'

You need to configure access to your database server too. You should edit the pg_hba.conf file to:

# IPv4 local connections:
# TYPE    DATABASE        USER            ADDRESS                 METHOD
    host      all                 all                127.0.0.1/32                md5

pgAdmin 3

If you installed pgAdmin by sudo dnf install pgAdmin3 command (seen above), it will executable by command:

$ pgadmin3

PgAdmin3

phpPgAdmin

If you installed phpPgAdmin by sudo dnf install phpPgAdmin command (seen above), it will accessable via url: http://localhost/phpPgAdmin/

phpPgAdmin startpage