Connettersi al database da remoto


Come ovviare al messaggio di errore: “psql: could not connect to server: Connection refused Is the server running on host host.domain.com and accepting TCP/IP connections on port 5432?”

Prima di tutto assicuriamoci che il server PostgreSQL remoto sia attivo

/etc/init.d/postgresql start

Verifichiamo il file /var/lib/pgsql/data/pg_hba.conf che nelle Ubuntu si trova invece sotto /etc/postgres* (il nome del percorso cambia a seconda della versione di PostgreSQL che state usando).

Ricordiamoci che questi file vanno modificati agendo con i privilegi di root. Per consentire l’accesso al database da parte di un client appartenente alla rete 192.168.100.0/24 occorre aggiungere una riga come segue

host all all 192.168.100.0 255.255.255.0 trust

In seguito occorre aggiornare il file postgresql.conf per attivare il socket TCP/IP

tcpip_socket = true

Riavviare PostgreSQL

/etc/init.d/postgresql restart

Controllare la configurazione usando il comando

psql -h indirizzo-server-postgresql -U nomeutente -d nomedatabasecuisivuoleaccedere

Ad esempio, ipotizzando che il vostro server abbia indirizzo IP 192.168.100.5 il comando sarebbe questopsql -h 192.168.1.5 -U peppino -d protocolli

Per rigirare una connessione ad un server PostgreSQL di appoggio per Zope/PAFlow tramite SSH, invece

ssh -p 12345 -L 54321:localhost:5432 root@serverscarso

Annunci

Migrare a PostgreSQL


Una migrazione da qualche altro database server a PostgreSQL, o addirittura da una versione precedente di PostgreSQL, può essere una procedura complicata, con molti particolari ai quali prestare attenzione.

Dovendo trasmettere la mia esperienza ho messo a punto una lista di controllo allo scopo di assicurarmi che si stiano valutando tutti gli aspetti della migrazione.

Inoltre, a luglio 2010 terminerà il ciclo di vita delle versioni 7.4 e 8.0 e a novembre dello stesso anno anche la 8.1. È importante, quindi iniziare a pensare ad un piano di migrazione, eventualmente appoggiandosi a chi possiede competenze specifiche su PostgreSQL che affianchi il personale interno.

Questo elenco include anche le domande da sottoporre al cliente e apprezzerò volentieri considerazioni proprie aggiuntive o suggerimenti, poiché non sempre arrivano dai colleghi di cui sopra, a conferma che “non la sanno poi così lunga” come vorrebbero far credere a gli paga lo stipendio…

Partiamo dalle questioni tecniche.

Quanti server di database ci sono?

Per ciascuno di essi occorre raccogliere le specifiche del sistema operativo, l’architettura della CPU, la memoria RAM, tipologia e geometria dei dischi, eccetera.

Particolare attenzione va posta alla tipologia di memoria di massa usata per ospitare i database esistenti, e quale si pianifica per i futuri database: direct-attached storage (SAS, SATA, eccetera), SAN (attenzione alle peculiarità del fornitore), oppure qualcosa che sia altro? Si userà qualche strumento di configuration management come Puppet o Chef?

Application server e altri tipi di accesso remoto

Quanti Application Server esistono già? Per ciascuno, qual’è la configurazione di sistema (al solito, sistema operativo, tipo di CPU e dimensione della RAM, geometria dischi, eccetera)? Configurazione della rete (si sta adottando SSL e/o VPN)? Si sta usando ODBC?

Sono coinvolti diversi centri elaborazione dati? Ci sono firewall in ingresso e/o in uscita? Anche in questo caso è importante chiedersi se si intende utilizzare Puppet, Chef o altri programmi simili.

Middleware

Viene utilizzato qualche connection pooling o sistema per il bilanciamento del carico? Quale altro sistema intermedio interagisce con il server di database?

Descrivere la base dati

Esistono dei rapporti, ad esempio, su quali dati siano acceduti meno frequentemente e cosa invece più spesso? Esistono delle statistiche sul traffico di rete e sul numero di query?

Dimensione

Quanti sono i dati da gestire? Che tipo di transazioni sono eseguite? Quante tabelle ci sono e quanto sono grandi? Quanti utenti si connettono al data base?

Copie di sicurezza

Qual’è (se esiste) la politica adottata per garantire le copie di sicurezza del database? In che modo, eventualmente, deve essere modificata dopo il passaggio a PostgreSQL?

Replicazione e bilanciamento del carico

Che tipo di sistema di ridondanza c’è attualmente oppure occorrerebbe? È usato qualche tipo di replica master-slave?

Monitoraggio

Qual’è il sistema di monitoraggio esistente? Si fa ricorso ad un team di sistemisti interni? Possono essere facilmente aggiornati al nuovo tipo di database?

Interfacce

In che lingua sono i driver per la connessione al database corrente? Ci sarà un driver compatibile PostgreSQL disponibile nella lingua scelta?

Infine un paio di considerazioni riguardanti l’aspetto gestionale.

Pianificazione

È stato stilato un calendario per la migrazione? Quanto tempo di inattività del database ci si può permettere?

Personale

Esiste almeno una risorsa interna dedicata alla gestione della base dati attuale? Questa risorsa è in grado di gestire il nuovo database server (ha esperienza PostgreSQL)?

Essere in grado di rispondere a tutte queste domande è fondamentale per la formulazione di un piano di migrazione di successo.

http://rcm.amazon.com/e/cm?lt1=_blank&bc1=FFFFFF&IS2=1&npa=1&bg1=FFFFFF&fc1=000000&lc1=0000FF&t=liin03-20&o=1&p=8&l=as1&m=amazon&f=ifr&asins=1590599780

Spostare una tablespace in PostgreSQL


Una tablespace è un raggruppamento logico interno al database cui corrisponde una locazione fisica. Introdotte a partire dalla versione 8.0 permettono di posizionare gli oggetti, come indici e tabelle, in aree specifiche del filesystem.

Mi è capitato di dover intervenire su un server sul quale erano compromessi i blocchi del disco dove insisteva ../data.

Ho risolto spostando la tablespace:

  • ho spento demone di PostgreSQL
  • ho copiato la tablespace nella nuova locazione
  • ho editato $PGDATA/pg_tblspc cancellando il link simbolico alla tablespace vecchia e ricreato quello nuovo che puntava al nuovo percorso
  • ho infine riavviato il demone di PostgreSQL

È andata bene: per questa volta i dati sono salvi!

Schedulare i backup su Windows


OK, purtroppo capita che per lavoro si debba anche maneggiare Windows. Il disagio è minore se si utilizza software Open Source possibilmente di “buona fattura” come PostgreSQL.

La domanda è la solita: “come faccio i backup?”, nel caso specifico si parla di backup da schedulare su katramzozz server duemiglia3 o affini ambienti ad alto tasso batterico.

Accendete un cero a Sant’Espedito, come segno di devozione per ringraziarlo di avervi fatto trovare questa pagina web (e accendetene un altro come penitenza per non essere ancora passati a Linux…) e procedete come segue.

Il backup su PostgreSQL viene effettuato come su Linux, grazie al comando pg_dump. Do per scontato che se vi pagano perché vi credono “sistemisti”, sappiate leggere due parole di inglese tecnico, quindi ogni ulteriore approfondimento lo trovate sul reference on line (avviabile anche da Windows, aprendo una finestra comandi e scrivendo pg_dump -?).

Un esempio di comando che tipicamente effettua il backup è questo:

pg_dump -h localhost -U nomeutente -Fc -x -f backup.del.giorno.x

dove nomeutente è il nome dell’utente che ha i privilegi di accesso al database da sottoporre a backup e backup.del.giorno.x è il nome del file che verrà prodotto.

Questo comando suppone che voi siate dinnanzi al computer perché vi chiederà di immettere la password dell’utente specificato nell’argomento -U.

Ma quando vorrete creare una procedura automatizzata per far sembrare Windows “figo” come Linux, come si fa?

Per prima cosa dobbiamo gestire l’immissione della password. Ci sono due modi. Il primo richiede che la password relativa all’utente sia memorizzata nel file pgpass.conf che si trova, di solito in:

C:\Documents and Settings\NomeUtente\Dati applicazioni\postgresql

Se il file non esistesse, createlo manualmente, seguendo lo schema appena descritto.

Dentro al file scrivete

nomecomputer:porta:nomedatabase:nomeutente:password

L’altro metodo è quello di inserire nello script di batch, la variabile set PGPASSWORD=panelle dove “panelle” è l’ipotetica password che vogliamo usare.

Adesso quello che rimane da fare è creare lo script batch, che esegua i backup contrassegnandoli con la data.

Ecco un esempio:

for /f "tokens=1,2,3,4 delims=/ " %%a in ('DATE /T') do set Date=%%c%%b%%a
pg_dump -h localhost -U nome_utente -Fc -x -c -f dovetengoibackup\%Date%.backup nome_database

  • nomeutente, sappiamo cosa significa
  • dovetengoibackup è il percorso della directory dove collocherete i backup (del tipo c:\copiedisicurezza)
  • nome_database è il nome del database da copiare

Mi fermo qui, ho già sforato il tempo massimo che in un anno posso dedicare alla risoluzione (gratuita) di problemi per Windows… ma penso che, ECDL o non ECDL, saprete come “gestire” la schedulazione di un batch su Windows… quindi non avrete altro da fare che sottoporre a scheduler questo script e tanti saluti.

Adeguare PostgreSQL alla “privacy”


Il Garante della Privacy con il suo provvedimento del 27 novembre 2008 ha emesso una serie di Misure e accorgimenti prescritti ai titolari dei trattamenti effettuati con strumenti elettronici relativamente alle attribuzioni delle funzioni di amministratore di sistema. Federico Campoli ha pubblicato sul sito PostgreSQL Italia tutti i dettagli di configurazione per adeguare PostgreSQL.

PostgreSQL tuning


PostgreSQL, nato oltre venti anni fa presso l’università di Berkeley, si è evoluto fino a diventare il migliore database open source, con alle spalle una comunità internazionale (www.postgresql.org), altrettanto attiva quella italiana (www.postgresqlit.org).

Le principali caratteristiche: multipiattaforma (Windows, Linux, Unix), scritto in C, supporta le transazioni, ACID, utilizza WAL (write ahead log). Row Level Locking e gestione eccellente dei Table Spaces.

Supporta triggers e funzioni, linguaggi procedurali e tipi di dati personalizzabili e il point in time recovery.

Infine i “limiti” di PostgreSQL:

  • dimensione massima di una tabella: 32 TeraByte
  • Massima dimensione di una riga: 1,6 TeraByte
  • Massima dimensione di un campo: 1 GigaByte
  • Illimitato numero di righe e tablle, illimitato numero di indici

Il file di riferimento è postgresql.conf, contiene circa 200 parametri raggruppati in dieci categorie. Partiamo dalle informazioni sul numero di utenti che possono connettersi al database. Questi parametri sono regolati dalle dichiarazioni permax_connections e superuser_reserved_connections.

La prima variabile imposta il numero massimo di connessioni che PostgreSQL accetterà, mentre la seconda si riferisce al numero di connessioni riservate all’utente di amministrazione (superuser). Di solito queste due variabili vengono configurate all’atto dell’installazione da initdb sulla base di informazioni esposte dal kernel in uso.

In linea generale, all’aumentare del numero di connessioni aumenta anche la richiesta di risorse del sistema. PostgreSQL offre la possibilità di creare un “pool di connessioni” se il numero di richieste dovesse essere elevato (ad esempio oltre 1000). Altri parametri sui quali agire, comunque, per migliorare le prestazioni di PostgreSQL sono l’ottimizzazione dell’uso e dell’accesso alla memoria e ai dischi, oltre che una buona “ingegnerizzazione” degli indici e delle “query”.

Quando si lavora con il file postgresql.conf è bene tenere presente le differenti unità di misura secondo che si agisca sui registri della CPU, sulla cache o sulla RAM oppure sui dischi.

PostgreSQL non agisce direttamente sul disco, ma scrive le informazioni nello shared buffers, quindi è opportuno ottimizzare l’uso della memoria RAM in modo che lo shared_buffers abbia la giusta dimensione per consentire agli altri programmi di girare senza intoppi: uno valore troppo grande causerebbe swapping sul disco quindi un deterioramente delle performance complessive del sistema.

Il valore predefinito di shared_buffers è 32 Mb, come si legge nei commenti del file postgresql.conf, deve essere almeno 128 Kb e almeno 16Kb per ogni connessione dichiarata nella variabile max_connections.

Se si possiede una CPU a 64bit, questo valore può anche superare i 2Gb. Sovente occorre agire sulle impostazioni del kernel (sia che sia abbia una CPU a 32bit che a 64bit), per poter ampliare questi valori predefiniti. Le variabili del kernel sulle quali agire sono SHMMAX e SHMALL.

La variabile work_mem interessa sempre la memoria RAM “non shared”, cioè quella che viene usata per le operazioni interne di ordinamento. Il valore di default è 1Mb e viene allocata per multipli di potenze di 2.

Esiste una regola approssimativa per calcolare il giusto valore: iniziare con il 25% del totale della RAM disponibile sul server assegnandola a shared_buffers e il 4% della RAM assegnata a work_mem. Se il sistema non swappa, aumentare i valori. Viceversa diminuirli se il sistema swappa. Il valore di temp_buffers agisce invece sul numero massimo di buffers temporanei usati per ogni sessione di PostgreSQL.

Se l’applicazione che si appoggia al databse fa uso intensivo di tabelle temporanee, conviene aumentare questo valore, che di default è impostato a 8Mb.

Quando si effettuano operazioni come VACUUM, CREATE INDEX oppure ALTER TABLE (per aggiungere una FOREIGN KEY), conviene avere un valore di maintenance_work_mem superiore a quello di 16Mb di default.

PostgreSQL: creare un utente


Solita domanda: “Come posso creare un utente e dargli i permessi per accedere al database del protocollo?”

Accedere alla consolle di gestione di PostgreSQL

psql template1

oppure

psql -d template1 -U postgres

ed impartire il comando per aggiungere, ad esempio, un utente chiamato “peppino”

CREATE USER peppino WITH PASSWORD 'coppoladiziovincenzo';

Adesso creiamo il database “protocolli”

CREATE DATABASE protocolli;

assegniamo i privilegi utente al database

GRANT ALL PRIVILEGES ON DATABASE protocolli TO peppino;

Digitiamo \q, al solito, per chiudere la consolle di gestione di PostgreSQL. Proviamo ad accedere al database con l’utente appena configurato

psql -d protocolli -U peppino

Se tutto andrà bene, dovremmo ritrovare visualizzato “peppino” nel prompt dei comandi una volta acceduti al database protocolli.

Un’altra via potrebbe essere questa:

create_db cicciodb -O eusebio

che crea un database con l’encoding di default e assegna lo schema all’utente eusebio