View Attach Info

INSTALLAZIONE #

Installare i pacchetti postgresql postgresql-client postgresql-contrib

TUNING#

Per un tuning di base di postgres si può utilizzare questa utility: pg-tune

Per una macchina con circa 4GB una configurazione "decente" di postgres potrebbe essere questa: 

maintenance_work_mem = 288MB # pgtune wizard 2010-02-24
checkpoint_completion_target = 0.7 # pgtune wizard 2010-02-24
effective_cache_size = 3584MB # pgtune wizard 2010-02-24
work_mem = 24MB # pgtune wizard 2010-02-24
wal_buffers = 4MB # pgtune wizard 2010-02-24
checkpoint_segments = 8 # pgtune wizard 2010-02-24
shared_buffers = 960MB # pgtune wizard 2010-02-24
max_connections = 200 # pgtune wizard 2010-02-24

Se si aumentano le connessioni e lo shared_buffers di postgres è possibile/probabile che serva aumentare anche la shared memory di linux questo è possibile attraverso questi comandi: 

cp /etc/sysctl.conf /etc/sysctl.conf-orig
vi /etc/sysctl.conf

Aggiungere la riga con il valore appropriato: 

kernel.shmmax = 104857600

Salvare il file e far ricaricare i parametri al kernel con: 

sysctl -p

PROBLEMONE SU ENCODING / LOCALE#

Attenzione alla creazione del database con ENCODING ISO-8859-1 per caratteri strani:
il problema nasce dal fatto che postgres 8.3 non mi fa cambiare il locale del server se non creando un nuovo cluster con il comando: 
# backup configurazione originale
mv /var/lib/postgresql/8.3/main /var/lib/postgresql/8.3/main_original
# mi sposto nella directory ...
cd /usr/lib/postgresql/8.3/bin/
#creo cartella main
mkdir main
#permessi
chown postgres:postgres main -R
#creo nuova configurazione 
./initdb --locale=it_IT.ISO-8859-1 -E=LATIN1 -D /var/lib/postgresql/8.3/main
#ricreo link simbolici certificati in /var/lib/postgresql/8.3/main
ln -s /etc/postgresql-common/root.crt root.crt
ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem server.crt
ln -s /etc/ssl/private/ssl-cert-snakeoil.key server.key

#modifico file postgresql.conf
vi /etc/postgresql/8.3/main/postgresql.conf
#Sostituisco UTF8 con ISO-8859-1
....
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'it_IT.ISO-8859-1'                        # locale for system error message
                                        # strings
lc_monetary = 'it_IT.ISO-8859-1'                        # locale for monetary formatting
lc_numeric = 'it_IT.ISO-8859-1'                 # locale for number formatting
lc_time = 'it_IT.ISO-8859-1'                            # locale for time formatting
....

riavvio Postgres.

CONFIGURAZIONE BASE#

shell di postgress:
su postgres psql postgres
(in ubuntu: sudo -u postgres psql postgres)

#creare super utente (da valutare)
postgres= create user root with superuser login;

#creare utente
postgres= create user sqlgrey with password 'iehQ6Ffshvta93eP';

#creare database
postgres= create database sqlgrey owner sqlgrey;
eventualmente per settare encoding diverso (default è UTF-8) usare\\
ENCODING = encoding (ad esempio 'ISO8859-1')

#creare schema
postgres= create schema nomeschema;

#abilitare connessioni in entrata
editare /etc/postgresql/8.3/main/postgresql.conf
listen_addresses = '*' 
password_encryption = on

#editare /etc/postgresql/8.3/main/pg_hba.conf (client authentication)
# TYPE     DATABASE  USER    CIDR-ADDRESS          METHOD
local      database  user  auth-method  [auth-option]
host       database  user  CIDR-address  auth-method  [auth-option]
hostssl    database  user  CIDR-address  auth-method  [auth-option]
hostnossl  database  user  CIDR-address  auth-method  [auth-option]
host       database  user  IP-address  IP-mask  auth-method  [auth-option]
hostssl    database  user  IP-address  IP-mask  auth-method  [auth-option]
hostnossl  database  user  IP-address  IP-mask  auth-method  [auth-option]

#auth-method
trust 		accetta connessioni senza pwd
reject 		non accetta connessioni
md5 		MD5-encrypted password for authentication
password 	pwd scambiata in chiaro
krb5 		usa kerberos 5 per autenticare il client
ident		ottiene il nome utente del sistema operativo dal client e controla se è abilitato alla connessione 
ldap 		autentica usando server LDAP
pam		Pluggable Authentication Modules

Comandi base#

settare uno schema(TUOSCHEMA): 
SET search_path TO TUOSCHEMA,public;
Connettere a DB: 
psql -U nomeutente nomedb
Fare dump di intero sistema: 
pg_dumpall > allDb.sql

Comandi per backup db#

Dump del db con pg_dump 
//singola tabella
pg_dump -c -U DBUSER DBNAME --table SCHEMA.TABLE -Fc > dbtable.dump

//intero db di uno schema
pg_dump -c -U DBUSER DBNAME --schema=SCHEMA -Fc > dbschema.dump

//intero db (tutti gli schemi)
pg_dump -c -U DBUSER DBNAME -Fc > dbname.dump
L'opzione -c indica a pg_dump di creare anche le istruzioni per il drop delle tabelle.

L'opzione --table serve per indicare di quale tabella fare il backup senza viene fatto il backup dell'intero DB.

L'opzione -F serve per il formato output del file (c custom, t tar).

L'opzione -U serve a specificare l'utente con il quale ci si collega al DB.

Il dump senza -F crea il salvataggio in text plain.

NB Per il restore del db da plainText utilizzare psql non pg_restore che funziona solo per formati custom o tar.

Restore del db con pg_restore

Solo per formati custom o tar (opzione -Fc o -Ft sul dump)

//restore singola tabella
pg_restore -c -U DBUSER -d DBNAME -t TABLE -v -Fc db.dump
ATTENZIONE: sulla singola tabella sembra che non ricrei gli indici (e quindi nemmeno le primary key)
//restore singolo schema
pg_restore -c -U DBUSER -d DBNAME --schema=SCHEMA -v -Fc db.dump
//restore db completo
pg_restore -c -U DBUSER -d DBNAME -Fc db.dump
L'opzione -c indica a pg_restore di eliminare le tabelle (drop) e ricrearla.

L'opzione -v è per il verbose.

L'opzione -F serve per il formato input del file (c custom, t tar).

L'opzione -d serve per specificare il dbname.

L'opzione -v = verbose.

L'opzione --data-only è per importare i soli dati e non ricrea table.

Caso Formato text plain : 

 psql -U DBUSER DBNAME -f dbname.dump

Import/export di dati #

export da pslq
\f ',' 
\a 
\t 
\o outputfile.csv 
select ..... (your sql select statement) 
\o 

import da psql
copy SCHEMA.TABLE from 'TABLE.vsc' using delimiters ',' CSV;

se in fase di import (copy) da comando di linea (psql) ottieni un errore tipo
ERROR: must be superuser to COPY to or from a file

usare da linea di comando linux
cat TUOFILE.csv | psql -U DBUSER -d DBNAME -c "copy SCHEMA.TABLE from stdin delimiter ',' CSV";

Gestione utenti#

loggandosi come utente pgsql postgres faccio alter user: 
alter user

Add new attachment

Only authorized users are allowed to upload new attachments.
« This page (revision-52) was last changed on 10-Sep-2010 17:52 by Maianti Alberto