The installation and initialization of the postgresql server is a little bit different in comparison to other packages and other linux distros. This document aims to summarize basic installation steps relevant to recent fedora release. In the first place, you may consider installing a newer version than is packaged for Fedora, see 1. However, this is not recommended.
$ sudo yum install postgresql-server postgresql-contrib
Or with dnf in Fedora 22 and later versions:
$ sudo dnf install postgresql-server postgresql-contrib
The postgresql server is turned off and disabled by default. You can enable its start during the boot using following command:
$ sudo systemctl enable postgresql
You can start the postgresql server only when necessary as follows.
$ sudo systemctl start postgresql Job for postgresql.service failed. See 'systemctl status postgresql.service' and 'journalctl -xn' for details.
The database needs to be populated with initial data after installation. The error log describes the problem and its solution.
$ journalctl -xn -- Logs begin at Mon 2013-11-04 14:38:33 CET, end at Thu 2013-11-14 11:45:56 CET. -- Nov 14 11:45:34 mlich-lenovo.usersys.redhat.com sudo: jmlich : TTY=pts/2 ; PWD=/home/jmlich ; USER=root ; COMMAND=/bin/systemctl status postgresql Nov 14 11:45:37 mlich-lenovo.usersys.redhat.com sudo: jmlich : TTY=pts/2 ; PWD=/home/jmlich ; USER=root ; COMMAND=/bin/systemctl status postgresql Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com sudo: jmlich : TTY=pts/2 ; PWD=/home/jmlich ; USER=root ; COMMAND=/bin/systemctl start postgresql Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com systemd: Starting PostgreSQL database server... -- Subject: Unit postgresql.service has begun with start-up -- Defined-By: systemd - Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel[http://lists.freedesktop.org/mailman/listinfo/systemd-devel] -- -- Unit postgresql.service has begun starting up. Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com postgresql-check-db-dir: An old version of the database format was found. Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com postgresql-check-db-dir: Use "postgresql-setup upgrade" to upgrade to version 9.3. Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com postgresql-check-db-dir: See /usr/share/doc/postgresql/README.rpm-dist for more information. Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com systemd: postgresql.service: control process exited, code=exited status=1 Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com systemd: Failed to start PostgreSQL database server. -- Subject: Unit postgresql.service has failed -- Defined-By: systemd -- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel[http://lists.freedesktop.org/mailman/listinfo/systemd-devel] -- Documentation: `http://www.freedesktop.org/wiki/Software/systemd/catalog/be02cf6855d2428ba40df7e9d022f03d[`http://www.freedesktop.org/wiki/Software/systemd/catalog/be02cf6855d2428ba40df7e9d022f03d] -- -- Unit postgresql.service has failed. -- -- The result is failed.
The database initialization could be done using following command. It creates the configuration files postgresql.conf and pg_hba.conf
$ sudo postgresql-setup initdb
Or on Fedora 22 and later:
$ sudo postgresql-setup --initdb --unit postgresql
As you can see from the error message in my example, it is not a fresh installation, but an ugprade.
Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com postgresql-check-db-dir: An old version of the database format was found. Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com postgresql-check-db-dir: Use "postgresql-setup upgrade" to upgrade to version 9.3.
With version 9 you can use the upgrade tool. It is packaged as
$ postgresql-setup upgrade Redirecting to /bin/systemctl stop postgresql.service Upgrading database: OK The configuration files was replaced by default configuration. The previous configuration and data are stored in folder /var/lib/pgsql/data-old. See /var/lib/pgsql/pgupgrade.log for details.
The data are located at
The upgrade itself will backup your existing data and migrate your
database. Don’t forget to migrate your configuration (with meld, for
You may need to switch postgresql to trust mode before updating. This should be fixed already.
You can also upgrade by dumping your database and loading it again. For more information, see the official documentation.
For database management, it is more user-friendly to use graphical tools such as phpPgAdmin or pgadmin3
$ sudo yum install phpPgAdmin $ sudo yum install pgadmin3
Or with dnf in Fedora 22 and later versions:
$ sudo dnf install phpPgAdmin $ sudo dnf install pgadmin3
PostgreSQL operates on port 5432 (or whatever else you set in your
postgresql.conf). In firewalld you can open it like this:
$ # make it last after reboot $ firewall-cmd --permanent --add-port=5432/tcp $ # change runtime configuration $ firewall-cmd --add-port=5432/tcp
In the case of iptables:
$ iptables -A INPUT -p tcp --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
Bear in mind that you probably don’t want to open your database server to the whole world.
If you have SELinux enforced, you may run into trouble when trying to do some non-standard configuration. For example, if you would like to change a location of your database, you have to add new context mapping for the new location:
$ semanage fcontext -a -t postgresql_db_t "/my/new/location(/.*)?"
If the default port doesn’t work for you, you may need to map postgre’s port type to your desired port:
$ semanage port -a -t postgresql_port_t -p tcp 5433
If you install a webapp that wants to communicate with PostgreSQL via TCP/IP, you will have to tell SELinux to allow this on the webserver host:
# setsebool -P httpd_can_network_connect_db on
Eventually, you need to create a user (and database for the user).
First, you have to switch the user to interact with
# su - postgres
and then run postgre’s interactive shell:
$ psql psql (9.3.2) Type "help" for help. postgres=#
From there you can run user creation commands:
postgres=# CREATE USER lenny WITH PASSWORD 'leonard'; postgres=# CREATE DATABASE carl OWNER lenny;
You can do this from the system shell as well:
$ createuser lenny $ createdb --owner=lenny carl
It might be good idea to add password for the
postgres=# \password postgres
The postgresql server is using two main configuration files
Some configuration parameters are passed to daemon via command line
options. This behaviour may override settings in
example, if you want to change the server’s port number to 5433, create
a file named
.include /lib/systemd/system/postgresql.service [Service] Environment=PGPORT=5433
Note: changing PGPORT or PGDATA will typically require adjusting SELinux configuration as well; see section selinux.
Please follow the systemd documentation 2 for more details.
If you want postgres to accept network connections, you should change
listen_addresses = 'localhost'
listen_addresses = '*'
Once your database is set up, you need to configure access to your
database server. This may be done by editing file
/var/lib/pgsql/data/pg_hba.conf. There are rules like this in the
# TYPE DATABASE USER ADDRESS METHOD host all all 127.0.0.1/32 md5 host all all ::1/128 md5 local all postgres peer
First field stands for connection type. It can have these values:
local — Unix-domain socket
host — plain or SSL-encrypted TCP/IP socket
hostssl — an SSL-encrypted TCP/IP socket
hostnossl — plain TCP/IP socket
Last column specifies which authentication method will be used.
md5 — client has to supply password processed with MD5 algorithm
ident — obtain user name of connecting client from operating system and consult it with specified map
trust — anyone who is able to connect to PostgreSQL server may act as any user without supplying password
peer — obtains user’s name from operating system and checks if it matches database user name
When the database server is authenticating the client, it seeks for a record with a matching connection type, client address, requested database, and user name. As soon as it finds these credentials, it performs the authentication. If the authentication fails, no more subsequent records are taken into account. If no record matches, the client’s access is denied.
The default settings are usually restricted to localhost.
When you install your database server and at first you try to "make it
work", you should turn off firewall, SELinux and make the
authentication permissive. Bear in mind this will greatly expose your
server, so do it only on a trusted network — preferably with no network at all:
host all all 127.0.0.1/32 trust
As soon as you are able to connect, turn on the security systems one by one while verifying the connection can be established.
For more information see official documentation for pg_hba.conf file.
The default configuration of postgres is severely undertuned. It can handle simple applications without consistent database access, but if you require higher performance, you should re-configure your instance. All the magic is happening in
/var/lib/pgsql/data/postgresql.conf\`. Also, the logging mechanism is not configured very intuitively.
The number of clients which may be connected to PostgreSQL at the same time:
max_connections = <number>
shared_buffers is the entry point. This is telling PostgreSQL how much
memory is dedicated for caching. Setting this to 25% of total memory of
your system is a good start. If it doesn’t work for you, try to go for
something between 15% - 40% of total memory.
shared_buffers = <memory unit>
This value is used by the query planner to know how much memory is available in the system. The query planner uses this information to figure out whether the plan fits into memory or not. Setting this to 50% of total memory is a common practice.
effective_cache_size = <memory unit>
When PostgreSQL performs sorting operations, it plans its strategy
whether to sort the query on disk or in memory. Bear in mind that this
memory is available for every sorting instance. In case of multiple
users submitting queries to your database server, this can ramp up
pretty high. Therefore this is tightly bound to
work_mem = <memory unit>
For more information about this topic I advise you to read the official documentation about tuning PostgreSQL.
By default, logs are rotated every week and you might not find much information in there. One could miss a log level, date, time, etc. Also, for simple web applications, some prefer to increase verbosity.
log_destination = 'stderr'
This is just fine. If you would like syslog to take care of your logs,
'syslog', or even
'syslog,stderr'. If you go for
syslog, don’t forget to configure syslog itself too; for more info, see
logging_collector = on
In case of logging to
postgres will grab all the logs if you
This is default option:
log_filename = 'postgresql-%a.log'
A preferred method could be to name log files by date when they were created:
log_filename = 'postgresql-%G-%m.log
Rotation. This really depends on the app itself. In the case of a simple app with little data in the database, all the logs may be kept persistently on disk without rotation.
log_truncate_on_rotation = off log_rotation_age = 31d
Increase number of entries in log:
client_min_messages = notice # default notice log_min_messages = info # default warning log_min_error_statement = notice # default error
If you would like to log slow queries, feel free to use this option:
log_min_duration_statement = 1000 # in ms
The default log entry doesn’t contain much info:
FATAL: Ident authentication failed for user "test" DETAIL: Connection matched pg_hba.conf line 84: "host all all ::1/128 ident"
Let’s improve it to:
2013-12-30 17:51:36 CET testx@::1(50867):postgres  FATAL: password authentication failed for user "testx" 2013-12-30 17:51:36 CET testx@::1(50867):postgres  DETAIL: Connection matched pg_hba.conf line 84: "host all all ::1/128 md5 "
You just have to alter the option
# %t -- timestamp # %u -- user # %r -- client's host # %d -- database # %p -- PID log_line_prefix = '%t %u@%r:%d [%p] '
If you are running only a single database with a single user connecting, it makes more sense to simplify the prefix to
log_line_prefix = '%t [%p] '