Postgresql

Info subito utili

Una buona guida per vedere vari comandi, sintetica ed efficace qui

Client testuale

Connessione

Per visualizzare tutti i database esistenti usare l'opzione -l nel comando psql

psql -l -h localhost -U liferay

comando di accesso

psql -W -h 192.168.99.225 -U postgres 
psql -l -W -h 192.168.99.225 -U postgres

L'opzione -W forza il prompt della password dopo l'invio del comando.

Dentro il client

Visualizzare tutti i database

postgres=# \l
        List of databases
   Name    |  Owner   | Encoding 
-----------+----------+----------
 AWVL      | AW       | UTF8

Connettersi a un database
postgres=# \c AWVL
You are now connected to database "AWVL".

Visualizza le tabelle, index, sequence, or view
AWVL=# \d
 public | bookmarks          | table    | AW
 public | c_eac              | table    | AW
 public | cartelle           | table    | AW

Visualizzare la struttura di una tabella

AWVL=# \d images
                                      Table "public.images"
    Column    |          Type          |                        Modifiers                         
--------------+------------------------+----------------------------------------------------------
 pk_id        | integer                | not null default nextval('hibernate_sequence'::regclass)
 fk_mag       | integer                | 
 path         | character varying(255) | 
 nomenclature | character varying(255) | 
 ord          | integer                | 
Indexes:
    "cst_pk_images" PRIMARY KEY, btree (pk_id)
    "idx_images_fk_mag" btree (fk_mag)
Foreign-key constraints:
    "cst_fk_mag1" FOREIGN KEY (fk_mag) REFERENCES mag(pk_id)

Creare un db e un utente

Creiamo l'utente proprietario del database se non esiste già.

CREATE USER nomeutente PASSWORD 'lapwd' CREATEDB CREATEUSER;

Dopo creiamo il database

CREATE DATABASE nomedb WITH OWNER=nomeutente ENCODING='UNICODE' ;

Rimuovere un utente

DROP USER nomeutente;

Riversare un db

Caso db già creato ma senza struttura tutte le info nel file .sql

psql -W -h 192.168.1.22 -U nomeutente nomedb < nomefiledibackup.sql

Come il caso sopra ma siamo utenti postgres diventati tramite sudo postgres da root e quindi non conosciamo la password

psql nomeutente nomedb < nomefiledibackup.sql

In questo modo riversa senza errori di permessi perchè postgres è super user del db lo fa sul db locale e non fa il prompt della password.

Creare il backup di un db

Il default del comando crea un file di backup di tipo plain text

pg_dump -W -h 192.168.1.33 -U nomeutente nomedb > backupfile.sql

Opzioni più avanzate

pg_dump --format plain -E UTF-8 --no-password --no-owner --no-privileges --no-tablespaces --inserts --verbose --file nomefile.sql nome-db
pg_dump --username peppe --host 192.168.1.40 --format plain -E UNICODE --no-owner --no-privileges --no-tablespaces --inserts --verbose --file nome-db.sql nomedb

backupare in maniera compressa

$ pg_dump miodb | gzip -c > miodb.dump.out.gz

Backup automatico con pg_dump

Per fare il backup automatico del db con pg_dump tramite uno script si deve creare un file nella directory home dell'utente che esegue lo script. Il formato deve essere il seguente

cd /root
cat .pgpass 
localhost:5432:nomedb:miousername:miapassword

Deve avere permessi limitati di lettura e scrittura per ovvi motivi
Uno script con le informazioni per il backup che riesce ad accedere alle info del file sopra è
cat /root/bin/backup-db.sql 
pg_dump -U miousername -h localhost -p 5432 nomedb > /root/backup/"nome-back`date`.sql"

Lo script sopra incolla al nome la data in formato esteso con l'orario. Le opzioni con cui si lancia il pg_dump deve essere precise altrimenti non legge la password dal file sopra.
Successivamente basta aggiungere una riga in /etc/crontab
0 20 * * * root /root/bin/backup-db.sql

Modalità direttamente a linea di comando

Creazione utente

sudo -u postgres createuser -S -D -R -l -P -E usernamescelto
Enter password for new role: 
Enter it again:

Creazione db con assegnazione ad utente precedentemente creato

sudo -u postgres createdb --encoding=UTF-8 --owner=usernamescelto nomedbscelto

Visualizzazione risultati

sudo -u postgres psql -l

Installare tramite pacchetto

Modalità sconsigliabile conviene farla tramite servizio ma quando non se ne può fare a meno seguire questa guida

http://wiki.openscg.com/index.php/PostgreSQL_DEB_Installation

Postges 9.1 su centos 6 ottima guida completa di postgis

http://www.davidghedini.com/pg/entry/install_postgresql_9_on_centos

Installare postgres 9.1 su centos 6

Presa da http://ag-up.com/?p=524

$ wget http://yum.pgrpms.org/reporpms/9.1/pgdg-centos91-9.1-4.noarch.rpm
# rpm -i pgdg-centos91-9.1-4.noarch.rpm
add "exclude=postgresql* " (without quotes) to /etc/yum.repos.d/CentOS-Base.repo in the end of sections [base] and [updates].
# yum install postgresql91-server  
# service postgresql-9.1 initdb 
# service postgresql-9.1 start 
# chkconfig postgresql-9.1 on

Installare postgres 9 su centos 5.5

Ottima guida qui

http://davidghedini.blogspot.com/2010/10/install-postgresql-9-on-centos.html

Install postgres 9.0 su ubuntu

Server

Pagina di riferimento

http://www.dctrwatson.com/2010/09/installing-postgresql-9-0-on-ubuntu-10-04/

Comandi riassunti
sudo apt-get install python-software-properties
sudo add-apt-repository ppa:pitti/postgresql
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install postgresql-9.0 libpq-dev

Può essere utile anche installare il postgis, fa tutto lui.

apt-get install postgis

Client

Al momento in cui scrivo i repository non sembrano ancora disponibili quindi uso questo metodo

add-apt-repository ppa:pitti/postgresql
apt-get update
apt-get install postgresql-client

Adesso posso installare postgresql9 e anche tutti i client della versione 9

Postgres 9.1 su ubuntu 10.04

Riferimento questo http://www.papinisascha.it/2012/02/16/installare-postgresql-9-1-in-ubuntu-10-04.html

sudo apt-get install python-software-properties
sudo add-apt-repository ppa:pitti/postgresql
sudo apt-get update
sudo apt-get install postgresql-9.1 libpq-dev

Abilitare le connessioni dall'esterno

In windows entambi i file si trovano in Programmi\Postrgres\8.4\data

Editare il file postgresql.conf dentro la dir di installazione

listen_addresses = '*'
port = 5432

Inoltre editare il file pg_hba.conf
aggiungendo in coda dove ci sono già i permessi per localhost questa riga

host   all   all  0.0.0.0/0   md5

Il formato è
host      DATABASE         USER        IP/NETMASK    METHOD

un esempio

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     ident
# IPv4 local connections:
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Modificare le credenziali di accesso

Come ripetuto anche sotto liferay non ha la password settata per l'user postgresql bisogna entrare come root o come sudo e modificarla. Dopo che si è entrati definire anche altri utenti.

sudo -u postgres psql template1
ALTER USER postgres with encrypted password 'your_password';

Modificare un db nome e proprietario

ALTER DATABASE name RENAME TO newname
ALTER DATABASE name OWNER TO new_owner

Password dimenticata

Modificare il file pg_hba.conf, mettendo a trust, in modo che accetti qualsiasi password.

local   all             postgres                 localhost                    trust

riavviare il db , entrare come utente postgresql e modificare la password
postgres=# ALTER USER postgres WITH PASSWORD 'newpasswordgoeshere';
ALTER ROLE
postgres=#

ricordarsi di modificare nuovamente la policy delle password.

Installazione sintetica from linux admin guide

Dopo averlo installato il server si avvia ma non è configurato per l'accesso via tcp/ip.
Per eseguire le seguenti operazioni connettersi come utente postgreslq o super user (meglio la prima)
Editare il file

/etc/postgresql/8.4/main/postgresql.conf

Levare il commento alla riga
listen_addresses = 'localhost'
Se si vuole permettere la connessione da tutte le altre macchine usare come parametro l'ip della propria macchina

Per modificare le impostazioni di ident si deve editare il file

/etc/postgresql/8.4/main/pg_ident.conf

Ma non è il protocollo che vogliamo usare.

Invece entriamo nel db come utente postgres e modifichiamolo per settare i permessi

sudo -u postgres psql template1
ALTER USER postgres with encrypted password 'your_password';

Attenzione a non dimenticare il ; perchè altrimenti il comando non lo da. Deve visualizzare il messaggio
ALTER ROLE

Poi abilitiamo l'utente , editando il file

/etc/postgresql/8.4/main/pg_hba.conf

Inserendo la riga
local all postgres md5 sameuser

anche se con la parola sameuser mi da errore in fase di restart del server

Poi riavviamo il servizio

service /etc/init.d/postgresql-8.4 restart

verificando che non ci siano errori.
A questo punto si può usare il client grafico tramite tcp per connettersi.

Potrebbe essere utile installare la documentazione

sudo apt-get install postgresql-doc-8.4

e accedervi tramite questo html
file:///usr/share/doc/postgresql-doc-8.4/html/index.html

Rinominare un database

Non mettere i trattini - nel nome del db, accidenti a me piacciono tanto

ALTER DATABASE myolddbname_here RENAME TO mynewdbname_here;

Vedere utenti e gruppi da psql

Per vedere gli utenti si puo' usare o \du per informazioni veloci, altrimenti si puo' usare:

SELECT * FROM pg_user;
SELECT * FROM pg_group;

Se il postgres non si inizilalizza da solo con l'installazione

mi è capitato una volta con ubuntu 12.04.3 , per risolvere

sudo apt-get update
sudo apt-get install postgresql-9.1
pg_lsclusters
pg_createcluster 9.1 main --start

Guida di postgreslq

CAP 1 (versione 8.3)

1.2 FONDAMENTI DELL'ARCHITETTURA

- postmaster è il nome del processo server che gestisce il db accetta connessioni dai programmi client
- un client può essere di diversa natura un programma di console , un interfaccia grafica ecc
- se il client e il server sono su due macchine diverse comunicano con il tcp/ip
- il postmaster può gestire diverse connessioni, per ogni nuova connessione esegue una fork dal processo principale con cui non comunica più. Il postmaster è sempre running in attessa di nuove connessioni

1.3 CRARE UN DATABASE

- $ createdb nomedb lanciarlo da console
- $ dropdb nomedb per eliminarlo
- in createdb se non si mette il nome viene usato quello di default che corrisponde al nome utente. Per il drop va specificato obbligatoriamente
- il nome di un db può essere al massimo di 63 caratteri alfanumerici ma il primo carattere deve essere un numero

1.4 ACCEDERE AL DB

- Una volta che si è creato il db si può accedere ad esso tramite il programma plsql o un editor grafico PgAcces o una suite con i driver odbc
- il comando è psql nomedb
se si visualizza nomedb=# si è il superuser e non si è sottroposti a nessun controllo altrimenti è nomedb=>
select version(); stampa la versione
ogni comando deve essere seguito dal ; tranne
\h che da l'elenco dei comandi e \q che fa uscire

Salvo diversa indicazione, il contenuto di questa pagina è sotto licenza Creative Commons Attribution-ShareAlike 3.0 License