PostgreSQL
Installazione
- Servizio
sudo apt-get update sudo apt-get install postgresql postgresql-contrib
- Client
sudo apt-get install postgresql-client pgadmin3
pgadmin3 è un client grafico.
Authentication & Authorization
Postgres ha una mappatura uno ad uno dei suoi utenti con quelli del sistema, l’utenza di deafault è “postgres”
sudo -i -u postgres
Fermare il servizio
sudo service postgresql stop
Postgres prompt
postgres@Itaca:~$ psql psql (9.3.23) Type "help" for help. postgres=#
senza fare il cambio di utenza
sudo -u postgres psql
per uscire dal dalla sessione postgres
postgres=# \q
Creare un nuovo ruolo
Autenticato come utente postgres
postgres@Itaca:~$ createuser --interactive Enter name of role to add: simondb Shall the new role be a superuser? (y/n) y
per cancellarlo
postgres@Itaca:~$ dropuser simondb
Creare un nuovo database
Si assume che per ogni utente c’è un database con lo stesso nome
postgres@Itaca:~$ createdb simondb
DROP DATABASE simondb
Accedere con il nuovo ruolo al nuovo database
Prima di tutti bisogna aggiungere l’utenza (db creato) al sistema
simon@Itaca ~/Documenti/blog: sudo adduser simondb [sudo] password for simon: Aggiunta dell'utente «simondb» ... Aggiunta del nuovo gruppo «simondb» (1001) ... Aggiunta del nuovo utente «simondb» (1001) con gruppo «simondb» ... Creazione della directory home «/home/simondb» ... Copia dei file da «/etc/skel» ... Inserire nuova password UNIX: Reinserire la nuova password UNIX: passwd: password aggiornata correttamente Modifica delle informazioni relative all'utente simondb Inserire il nuovo valore o premere INVIO per quello predefinito Nome completo []: simondb Stanza n° []: Numero telefonico di lavoro []: Numero telefonico di casa []: Altro []: Le informazioni sono corrette? [S/n] S
Eseguiamo l’autenticazione con simondb
sudo -i -u simondb
simondb@Itaca:~$ psql psql (9.3.23) Type "help" for help. simondb=# \conninfo You are connected to database "simondb" as user "simondb" via socket in "/var/run/postgresql" at port "5432". simondb=#
Creare e cancellare tabelle
CREATE TABLE playground (
equip_id serial PRIMARY KEY,
type varchar (50) NOT NULL,
color varchar (25) NOT NULL,
location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
install_date date
);
DROP TABLE playground;
Elenco delle tabelle (\dt per avere l'elenco senza sequence)
simondb=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+----------+---------
public | playground | table | simondb
public | playground_equip_id_seq | sequence | simondb
(2 rows)
simondb=#
Inserire e visualizzare i dati
INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2014-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2010-08-16');
simondb=# select * from playground;
equip_id | type | color | location | install_date
----------+-------+-------+----------+--------------
1 | slide | blue | south | 2014-04-28
(1 row)
simondb=#
**Autenticazione con url di connessione JDBC
Per eseguire l’autenticazione tramite url di connessione
jdbc:postgresql://127.0.0.1:5432/simondb?user=simondb&password=password&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory
Modificare il file pg_hba.conf:
postgres=# show hba_file ; hba_file -------------------------------------- /etc/postgresql/9.3/main/pg_hba.conf (1 row)
inserendo
host all all 127.0.0.1/32 trust
riavviare
sudo service postgresql restart
Comandi base:
\? list all the commands
\l list databases
\dn list of schema
\dt schema.* lista delle tabelle
\d schema.tabela descrizione tabella
\conninfo display information about current connection
\c [DBNAME] connect to new database, e.g., \c template1
\dt list tables
\q quit psql
\i nomefile.sql per l'esequzione da file
\d+ tablename tabl schema
Lista Sequence
SELECT c.relname FROM pg_class c WHERE c.relkind = 'S'; oppure \ds
Connessione da terminale remota
psql -h 10.152.146.13 -p 5432 -U usr_pdatbe -W -d db_pdatbe