PostgreSQL

Jiyoung Joung, niko d, Héctor H. Louzao P Version F37 and newer Last review: 2022-12-19

Gli utenti di Fedora Server Edition possono trovare informazioni aggiuntive in Configurazione del Server Database PostgreSQL

Installazione

L’installazione e l’inizializzazione del server PostgreSQL sono un po' diverse rispetto ad altri pacchetti e ad altre distribuzioni Linux. Questo documento mira a riassumere i passaggi di installazione di base rilevanti per le recenti versioni di Fedora Linux.

$ sudo dnf install postgresql-server postgresql-contrib

Il server PostgreSQL non è in esecuzione e disabilitato per impostazione predefinita. Per impostarlo affinché si avvii all’avvio, eseguire:

$ sudo systemctl enable postgresql

Il database deve essere popolato con dati iniziali dopo l’installazione. L’inizializzazione del database può essere eseguita utilizzando il seguente comando. Crea i file di configurazione postgresql.conf e pg_hba.conf

$ sudo postgresql-setup --initdb --unit postgresql

Per avviare manualmente il server PostgreSQL, eseguire

$ sudo systemctl start postgresql

Creazione Utente e Creazione Database

Ora è necessario creare un utente e un database per l’utente. Questo deve essere eseguito da un account utente postgres sul sistema.

$ sudo -u postgres psql

Da qui è possibile creare un utente Postgres e un database. Qui, assumeremo che l’account utente del computer si chiami lenny. Nota: è possibile eseguire questo anche dalla shell con createuser lenny e createdb --owner=lenny carl.

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

Potrebbe essere una buona idea aggiungere una password per l’utente postgres mentre ci si è:

postgres=# \password postgres

Premere Ctrl + D o \q per lasciare la sessione psql in esecuzione come utente postgres. Ora è possibile accedere al nuovo database dall’account utente (lenny) e incominciare a usarlo.

$ psql my_project

Configurazione iniziale

Il server PostgreSQL utilizza due file di configurazione principali

  • /var/lib/pgsql/data/postgresql.conf

  • /var/lib/pgsql/data/pg_hba.conf

If you’re getting ident errors from your app you’ll probably need to perform the accepted solution described at https://serverfault.com/questions/406606/postgres-error-message-fatal-ident-authentication-failed-for-user?newreg=a4fdc3e21349449985cc65b82399c5b4

$ sudo gedit /var/lib/pgsql/data/pg_hba.conf

and edit host all all 127.0.0.1/32 ident to host all all 127.0.0.1/32 md5 and host all all ::1/128 ident to host all all ::1/128 md5. Once you’ve made this change, be sure to restart the postgresql service for the changes to take affect.

$ sudo systemctl restart postgresql

These changes should allow most applications to connect with username/password.

Aggiornamento

Come si può vedere dal messaggio di errore nel mio esempio, non si tratta di una nuova installazione, ma di un aggiornamento.

Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com postgresql-check-db-dir[2108]: An old version of the database format was found.
Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com postgresql-check-db-dir[2108]: Use "postgresql-setup upgrade" to upgrade to version 9.3.

Con la versione 9 è possibile utilizzare lo strumento di aggiornamento. È pacchettizzato come postgresql-upgrade:

$ sudo postgresql-setup upgrade

Reindirizzamento a /bin/systemctl stop  postgresql.service
Aggiornamento database: OK

I file di configurazione sono stati sostituiti dalla configurazione predefinita.
La configurazione precedente e i dati sono memorizzati nella cartella /var/lib/pgsql/data-old.

Vedere /var/lib/pgsql/pgupgrade.log per dettagli.

I dati si trovano in

  • /var/lib/pgsql/data

  • /var/lib/pgsql/data-old

L’aggiornamento stesso effettuerà il backup dei dati esistenti e migrerà il database. Non dimenticare di migrare la configurazione (con meld, ad esempio: meld /var/lib/pgsql/data{,-old}/postgresql.conf).

Potrebbe essere necessario passare PostgreSQL alla modalità trust prima dell’aggiornamento. Questo dovrebbe essere già risolto.

È possibile aggiornare anche scaricando il database e caricandolo di nuovo. Per ulteriori informazioni, vedere la documentazione ufficiale.

Skipped major release

In Fedora 43 the Postgres packages went from 16 to 18. Database upgrades cannot skip a major release and the above procedure will not work. Unless you have already upgraded to Postgres 17 on Fedora 42, you will have to do the upgrade from 16 to 18 in 2 steps on Fedora 43.

First you have to uninstall Postgres 18 and install Postgres 17. You can verify which packages are installed with rpm -qa |grep ^postgres Before you install Postgres 17, please backup your data directory. Even though a backup is part of the upgrade procedure, it is better to be safe than sorry. After installing the Postgres 17 packages, you can use the above upgrade procedure to do the actual upgrade from 16 to 17. Then you have to uninstall Postgres 17 and install Postgres 18 again. Please run above upgrade procedure one more time and you are good to go.

During this 2 step procedure you might encounter a few obstacles, which are explained here:

In case you see the error old cluster does not use data checksums but the new one does you can either choose to create the checksums or upgrade with checksums disabled. To create the checksums, drop into the shell of the postgres user and run the following command: pg_checksums -e -D <data-dir>. However, when you see this error, you have already updated the packages to a newer release while the database is still on the previous one. Thus it might be easier to upgrade with PGSETUP_INITDB_OPTIONS="--no-data-checksums" postgresql-upgrade <data-dir> and create the checksums afterwards. Unless you changed the Postgres data directory, <data-dir> is /var/lib/pgsql/data.

Don’t forget to adjust and consolidate your config files (*.conf) from the old and new data directories as well as to update the collation for all databases.

Update the collation (as postgres user): ALTER DATABASE <dbname> REFRESH COLLATION VERSION; \c <dbname>; REINDEX DATABASE;

Firewall

PostgreSQL operates on port 5432 (or whatever else you set in your postgresql.conf). In firewalld, to make the change persist after reboot:

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

To apply the change to the running firewall immediately:

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

Nel caso di iptables:

$ sudo iptables -A INPUT -p tcp --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT

Tieni presente che probabilmente non si vuole aprire il server database a tutto il mondo.

SELinux

Se SELinux è attivo, potresti incontrare problemi quando si tenta di fare alcune configurazioni non standard. Ad esempio, se si desidera modificare la posizione del database, è necessario aggiungere un nuovo mapping di contesto per la nuova posizione:

$ sudo semanage fcontext -a -t postgresql_db_t "/my/new/location(/.*)?"

Se la porta predefinita non funziona, potrebbe essere necessario mappare il tipo di porta di PostgreSQL sulla porta desiderata:

$ sudo semanage port -a -t postgresql_port_t -p tcp 5433

Se si installa un’app web che vuole comunicare con PostgreSQL via TCP/IP, sarà necessario dire a SELinux di permettere questo sull’host del webserver:

$ sudo setsebool -P httpd_can_network_connect_db on

Configurazione

Come menzionato sopra, il server PostgreSQL utilizza due file di configurazione principali

  • /var/lib/pgsql/data/postgresql.conf

  • /var/lib/pgsql/data/pg_hba.conf

systemd

Alcuni parametri di configurazione vengono passati al demone tramite opzioni della linea di comando. Questo comportamento potrebbe sovrascrivere le impostazioni in postgresql.conf. Ad esempio, se si desidera modificare il numero di porta del server in 5433, creare un file denominato /etc/systemd/system/postgresql.service contenente:

.include /lib/systemd/system/postgresql.service
[Service]
Environment=PGPORT=5433

Nota: modificare PGPORT o PGDATA richiederà tipicamente anche l’aggiustamento della configurazione SELinux; vedere la sezione SELinux.

Seguire la documentazione systemd 2 per ulteriori dettagli.

postgresql.conf

Se si desidera che Postgres accetti connessioni di rete, è necessario modificare

listen_addresses = 'localhost'

in

listen_addresses = '*'

pg_hba.conf

Una volta configurato il database, è necessario configurare l’accesso al server database. Questo può essere fatto modificando il file /var/lib/pgsql/data/pg_hba.conf. Ci sono regole come questa nel file:

# TYPE    DATABASE        USER            ADDRESS                 METHOD
  host    all             all             127.0.0.1/32            md5
  host    all             all             ::1/128                 md5
  local   all             postgres                                peer

Il primo campo indica il tipo di connessione. Può avere questi valori:

  • local — socket dominio Unix

  • host — socket TCP/IP semplice o crittografato SSL

  • hostssl — socket TCP/IP crittografato SSL

  • hostnossl — socket TCP/IP semplice

L’ultima colonna specifica il metodo di autenticazione che verrà utilizzato.

  • md5 — il client deve fornire una password elaborata con l’algoritmo MD5

  • ident — ottiene il nome utente del client che si connette dal sistema operativo e lo consulta con la mappa specificata

  • trust — chiunque sia in grado di connettersi al server PostgreSQL può agire come qualsiasi utente senza fornire password

  • peer — ottiene il nome utente dal sistema operativo e verifica se corrisponde al nome utente del database

Quando il server database autentica il client, cerca un record con un tipo di connessione corrispondente, indirizzo client, database richiesto e nome utente. Non appena trova queste credenziali, esegue l' autenticazione. Se l’autenticazione fallisce, non vengono presi in considerazione ulteriori record successivi. Se nessun record corrisponde, l’accesso del client viene negato.

Le impostazioni predefinite sono solitamente limitate a localhost.

Quando si installa il server database e si tenta inizialmente di "farlo funzionare", è necessario disattivare firewall, SELinux e rendere l' autenticazione postgres permissiva. Tieni presente che questo esporrà notevolmente il server, quindi fallo solo su una rete fidata — preferibilmente senza rete affatto:

host    all             all             127.0.0.1/32            trust

Non appena si è in grado di connettersi, attivare i sistemi di sicurezza uno per uno verificando che la connessione possa essere stabilita.

Per ulteriori informazioni vedere la documentazione ufficiale per file pg_hba.conf.

Ottimizzazione

La configurazione predefinita di Postgres è gravemente sottodimensionata. Può gestire applicazioni semplici senza accesso consistente al database, ma se si richiedono prestazioni superiori, è necessario riconfigurare l’istanza. Tutta la magia avviene in /var/lib/pgsql/data/postgresql.conf\`. Inoltre, il meccanismo di logging non è configurato in modo molto intuitivo.

Prestazioni

Il numero di client che possono essere connessi a PostgreSQL contemporaneamente:

max_connections = <number>

shared_buffers è il punto di ingresso. Questo indica a PostgreSQL quanta memoria è dedicata per il caching. Impostarlo al 25% della memoria totale del sistema è un buon inizio. Se non funziona, provare qualcosa tra il 15% - 40% della memoria totale.

shared_buffers = <memory unit>

Questo valore è utilizzato dal pianificatore di query per sapere quanta memoria è disponibile nel sistema. Il pianificatore di query utilizza queste informazioni per capire se il piano si adatta alla memoria o no. Impostarlo al 50% della memoria totale è una pratica comune.

effective_cache_size = <memory unit>

Quando PostgreSQL esegue operazioni di ordinamento, pianifica la sua strategia se ordinare la query su disco o in memoria. Tieni presente che questa memoria è disponibile per ogni istanza di ordinamento. Nel caso di più utenti che inviano query al server database, questo può aumentare notevolmente. Pertanto questo è strettamente legato a max_connections.

work_mem = <memory unit>

Per ulteriori informazioni su questo argomento, consiglio di leggere la documentazione ufficiale sull’ottimizzazione di PostgreSQL.

Logging

Per impostazione predefinita, i log vengono ruotati ogni settimana e potresti non trovare molte informazioni lì. Si potrebbe perdere un livello di log, data, ora, ecc. Inoltre, per applicazioni web semplici, alcuni preferiscono aumentare la verbosità.

log_destination = 'stderr'

Va bene così. Se si desidera che syslog si occupi dei log, modificare 'stderr' in 'syslog', o persino 'syslog,stderr'. Se si opta per syslog, non dimenticare di configurare anche syslog stesso; per ulteriori info, vedere documentazione ufficiale.

logging_collector = on

Nel caso di logging su stderr, postgres catturerà tutti i log se si abilita l’opzione logging_collector.

Questa è l’opzione predefinita:

log_filename = 'postgresql-%a.log'

Un metodo preferito potrebbe essere quello di nominare i file di log in base alla data in cui sono stati creati:

log_filename = 'postgresql-%G-%m.log

Rotazione. Questo dipende davvero dall’app stessa. Nel caso di un’app semplice con pochi dati nel database, tutti i log possono essere conservati persistentemente su disco senza rotazione.

log_truncate_on_rotation = off
log_rotation_age = 31d

Aumentare il numero di voci nel log:

client_min_messages = notice      # default notice
log_min_messages = info           # default warning
log_min_error_statement = notice  # default error

Se si desidera registrare query lente, sentiti libero di usare questa opzione:

log_min_duration_statement = 1000  # in ms

La voce di log predefinita non contiene molte info:

FATAL:  Ident authentication failed for user "test"
DETAIL:  Connection matched pg_hba.conf line 84: "host    all             all             ::1/128                 ident"

Miglioriamolo in:

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 "

Basta modificare l’opzione log_line_prefix.

# %t -- timestamp
# %u -- user
# %r -- client's host
# %d -- database
# %p -- PID
log_line_prefix = '%t %u@%r:%d [%p] '

Se si esegue solo un singolo database con un singolo utente che si connette, ha più senso semplificare il prefisso in

log_line_prefix = '%t [%p] '

Ricetta finale

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

Riferimenti

Documentazione completa sul packaging RPM

Ottimizzazione delle prestazioni

Logging configuration

Upgrading PostgreSQL

pg_hba.conf file

See a typo, something missing or out of date, or anything else which can be improved? Edit this document at https://forge.fedoraproject.org/docs/quick-docs.

Want to help? Learn how to contribute to Fedora Docs ›