User:Ttomecek/draft/postgresql

Installation

TBD

Firewall

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

SELinux

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 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

User Creation and Database Creation

Soon you run into need of creating a user (and database for the user). First, you have to switch user to interact with postgres:

$ su - postgres

and then run postgre's interactive shell:

$ psql 
psql (9.3.2)
Type "help" for help.

postgres=# 

and now we just need to create the user:

postgres=# CREATE USER lenny WITH PASSWORD 'leonard';
postgres=# CREATE DATABASE carl OWNER lenny;

this could be done from shell too:

$ createuser lenny
$ createdb --owner=lenny carl

pg_hba.conf

Once your database is set up, you need to configure access to your database server. This may be done by editing file vim /var/lib/pgsql/data/pg_hba.conf. There are rules like this in there:

# TYPE    DATABASE        USER            ADDRESS                 METHOD
  host    all             all             127.0.0.1/32            md5

First field stands for connection type. It can have these values:

• local — Unix-domain socket
• host — plain or SSL-encrypted TCP/IP socket
• hostssl — is 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

When database server is authenticated client it seeks for a record with matching connection type, client address, requested database and user name. As soon as it finds it, it performs the authentication. If authentication fails, no more subsequent records are taken into account. If no record matches, client's access is denied.

When you install your database server and at first you start to "make it work", you should turn off firewall, SELinux and make postgres' authentication permissive (bear in mind this could greatly expose your server, so do it only on trusted network — preferably without network connection):

  host    all             all             127.0.0.1/32            trust

Optimisation

Default configuration of postgres is severely undertuned. It can handle simple application with not so often database access but if you require higher performance, you should configure your instance better. All the magic is happening in /var/lib/pgsql/data/postgresql.conf`. Also logging mechanism is configured not very intuitively.

Performance

max_connections = <number>

Number of clients which may be connected to PostgreSQL at the same time.

shared_buffers = <memory unit>

This is the entry point. This is telling PostgreSQL how much memory is dedicated to use 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.

effective_cache_size = <memory unit>

This value is used by query planner to know how much memory is available in the system. Query planner uses this information to figure out whether plan fits into memory or not. Setting this to 50% of total memory is common.

work_mem = <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 rump up pretty high. Therefore this is tightly bound to max_connections.

For more information about this topic I advise you to read official documentation about tuning PostgreSQL.

Logging

By default, logs are rotated every week and you don't find much information in there (one could miss log level, date, time, ...). Also for simple web applications I prefer to increase verbosity.

log_destination = 'stderr'

This is just fine. If you would like syslog to take care of your logs, change it to 'syslog', or even 'syslog,stderr' (if you go for syslog, don't forget to configure syslog itself too; for more info, see official documentation)

logging_collector = on

In case of logging to stderr, postgres will grab all the logs if you enable this option.

log_filename = 'postgresql-%a.log'

This is default option. Much preferred 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 case of simple app with a few data in 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

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"

Lets improve it to:

2013-12-30 17:51:36 CET testx@::1(50867):postgres [11213] FATAL:  password authentication failed for user "testx"
2013-12-30 17:51:36 CET testx@::1(50867):postgres [11213] DETAIL:  Connection matched pg_hba.conf line 84: "host   all             all             ::1/128                 md5 "   

You just have to alter option log_lin_prefix.

# %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 single database with single user connecting, it would make more sense to simplify the prefix to

log_line_prefix = '%t [%p] '

Final recipe

log_destination = 'stderr'
logging_collector = on
log_filename = 'postgresql-%G-%m.log'
log_truncate_on_rotation = off
log_rotation_age = 31d
client_min_messages = notice
log_min_messages = info
log_min_error_statement = notice
log_line_prefix = '%t %u@%r:%d [%p] '

Upgrade

If you can't start your instance of PostgreSQL server and can see a message in logs stating

An old version of the database format was found.

You should upgrade your database. With version 9 you can use upgrade tool. First you have to install it:

yum install postgresql-upgrade

Then just execute the upgrade:

$ 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.

This will backup your existing data and migrate your database. Don't forget to migrate your configuration (with meld for example: meld /var/lib/pgsql/data{,-old}/postgresql.conf).

You can also upgrade by dumping your database and loading it again. For more information, see official documentation.

Reference

Tuning performance

Logging configuration

Upgrading PostgreSQL