Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

PostgreSQL 9.3 (italiano)

Il database (open source) più avanzato al mondo
by

Gabriele Bartolini

on 16 April 2014

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of PostgreSQL 9.3 (italiano)

PostgreSQL 9.3
Gabriele Bartolini
Data architect
2ndQuadrant Italia
(Devise.IT)

Il più avanzato database (open source) al mondo
Contatti
@_GBartolini_
gabriele.bartolini@2ndquadrant.it
@2ndquadrant_it
Postgres
Si scrive
PostgreSQL
, si legge
Postgres
Voi e Postgres ...
Azienda fondata in UK da Simon Riggs (code committer)
Uffici in Europa, America, Oceania, Asia
Sponsor di platino di PostgreSQL
Modello di business open source
Leader HA, Disaster Recovery, Scalabilità e Performance su PostgreSQL
Supporto 24/7 ("follow the sun")
PostgreSQL
100% Open Source
Tutte le funzionalità del core di PostgreSQL sono disponibili, con lo stesso modello di licenza e senza acquisto di pacchetti extra
PostgreSQL Database Management System
(formerly known as Postgres, then as Postgres95)

Portions Copyright (c) 1996-2013, PostgreSQL Global Development Group

Portions Copyright (c) 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its
documentation for any purpose, without fee, and without a written agreement
is hereby granted, provided that the above copyright notice and this
paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR
DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS
DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY
AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS
ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO
PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
23 linee
No Monopolio
No acquisizioni
1986 - Postgres (Stonebraker)
1995 - Postgres95
1996 - PostgreSQL
Date e numeri
major release dal 1996
major release negli ultimi 6 anni
22
6
5
anni di supporto di una major release da parte della comunità
Architettura
Architettura Client/Server
Architettura Multi-processo
Multi-piattaforma
Multi-utente
Concorrenza (MVCC)
Scritto in linguaggio ANSI C
Funzionalità chiave
Integrità referenziale
Transazioni ACID
Viste
Funzioni server
Trigger
Tablespace
Schemi
Partizionamento*
Hot Backup
Point In Time Recovery
Replica Master-Slave
Replica sincrona
Replica in streaming
Per gli sviluppatori
SQL Standard
Supporto XML
Unicode (UTF8) e TEXT
Array
Common Table expression
Window Function
Tabelle esterne
Tabelle unlogged
Estensioni
...
9.3 per Ops
9.3 per Dev
include_dir (opzione di configurazione)
Puoi adesso inserire nel file postgresql.conf:
Background worker
Background worker = sottoprocesso di "postmaster"
Esegue codice scritto dall'utente (in linguaggio C)
Start/stop/monitoring di demoni:
audit
logging
monitoring
alerting
pg_isready
Utility per controllare lo stato di un server PostgreSQL:
0 = tutto OK
1 = il server non può accettare connessioni (startup)
2 = il server non risponde
3 = nessun tentativo di connessione effettuato (e.g. errore nei parametri di accesso)
Checksum delle pagine
Ogni pagina di dati contiene una checksum (16 bit)
Rileva eventuali corruzioni di dato (a livello di I/O)

Fast failover per replica in streaming
Resa più veloce la promozione di uno standby in primario
Shared memory
Da SysV a Posix
mmap() per la gestione della memoria
pg_dump parallelo
Permette di utilizzare più processi per eseguire il backup di un database, tramite le seguenti opzioni di pg_dump:
-j NUMERO_PROCESSI
-Fd (formato directory)
-f DIRECTORY
JSON
Il supporto JSON è stato introdotto in PostgreSQL 9.2:
tipo di dato JSON
Event trigger
Writable Foreign Data Wrapper
Foreign Data Wrapper (FDW) introdotti in 9.1
Accesso a dati esterni a PostgreSQL tramite SQL
SQL/MED
Tabelle esterne (FOREIGN TABLE)
COPY
LATERAL
Nuova parola chiave
Standard SQL
Si può applicare a una subquery:
LATERAL ( SELECT ... FROM ... ) AS foo
La sottoquery è correlata
è ricalcolata per ogni riga a sinistra
Può anche essere applicata a JOIN
include_dir 'conf.d'
PostgreSQL caricherà tutti i file con estensione *.conf
Se usi configuration manager come Puppet o Chef, ne apprezzerai sicuramente la rilevanza
Hacker
pg_dump -j4 -Fd -f /tmp/acdc.dump acdc
MASTER
pg1.2ndquadrant.it
Slave 1
pg2.2ndquadrant.it
Slave 2
pg3.2ndquadrant.it
Slave 3
pg4.2ndquadrant.it
Remastering
Caso d'uso: CLUSTER con almeno due repliche in streaming

In caso di switchover/failover del master su uno degli slave, gli altri server in replica sono in grado di sincronizzarsi automaticamente con il nuovo master
NOTA: funziona anche con replica in cascata (9.2)
99.999% uptime (disponibilità "carrier grade")
Mai più "impazzire" con SHMMAX e SHMALL nel kernel!
Viste
Aggiornabili
Materializzate
Ricorsive
Lo standard SQL definisce le viste aggiornabili per query che rispettano questi requisiti:
operano su una sola tabella
non contengono clausole WITH, DISTINCT, GROUP BY, HAVING, LIMIT o OFFSET
non contengono clausole UNION, INTERSECT, EXCEPT
non contengono espressioni (e.g. aggregati)
le colonne escluse dalla vista e facenti parte della tabella sottostante devono permettere valore NULL oppure avere un valore di DEFAULT
PostgreSQL 9.3 aggiunge due funzioni di sistema:
pg_view_is_insertable(regclass)
pg_view_is_updatable(regclass)
CREATE MATERIALIZED VIEW

datamart_2013_10

AS
SELECT EXTRACT('YEAR' FROM ts) AS
yy
,
EXTRACT('MONTH' FROM ts) AS
mm
,
EXTRACT('DAY' FROM ts) AS
dd
,
EXTRACT('HOUR' FROM ts) AS
hh
,
website,
count(*) AS
accessi
FROM
weblogs
WHERE
ts
>= '2013-10-01' AND
ts
< '2013-11-01'
AND NOT
is_spider
GROUP BY 1, 2, 3, 4, 5;

-- Puoi creare indici
CREATE INDEX ON

datamart_2013_10
(website);

-- Refresh totale
REFRESH MATERIALIZED VIEW

datamart_2013_10
;
Una
vista materializzata
è una particolare vista che rende persistente il risultato ottenuto sotto forma di tabella ('
cache
').
Postgres 9.3 supporta soltanto il
REFRESH manuale completo
:
no incrementale
no automatico
Appositi
indici
possono essere creati su viste materializzate.
CREATE RECURSIVE VIEW
permette di definire in modo più semplice viste che utilizzano una
Common Table Expression
(CTE)
ricorsiva
.
Le CTE sono anche dette
query WITH
.
WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100
)
SELECT * FROM t;
CREATE RECURSIVE VIEW t(n) AS
VALUES (1)
UNION ALL
SELECT n+1 FROM t WHERE n < 100;
Esempio di CTE:
Equivalente con RECURSIVE VIEW:
Fonte: http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3
SELECT city.name,
sum(foo.population) AS population,
array_agg(foo.name) AS villages
FROM city,

LATERAL
(

SELECT *
FROM village
ORDER BY loc <-> city.loc
LIMIT 3
)
AS foo
GROUP BY city.name;
Data una lista di città e paesi, calcola - per ogni città - il totale delle popolazione dei 3 paesi più vicini (<->).
Fonte: Gianni Ciolli, "PostgreSQL Advanced SQL Tips"
ERROR: function expression in FROM cannot refer to other relations of same query level
Avete mai visto questo messaggio di errore in PostgreSQL < 9.3?
Si possono creare trigger su istruzioni DDL come:
ALTER
CREATE
DROP
Si possono filtrare eventi sulla base del comando (TAG)
CREATE EVENT TRIGGER
name
ON
event
EXECUTE PROCEDURE
function_name
()
ddl_command_start
,
ddl_command_end
e
sql_drop
FREEZE
Operazioni di bulk load le cui righe inserite hanno xmin impostato a "frozen"
non onora MVCC
le righe sono immediatamente visibili dalle altre transazioni
migliori prestazioni
PROGRAM
COPY FROM PROGRAM:
INPUT = standard output del programma
COPY TO PROGRAM:
OUTPUT = standard input del programma
L'implementazione di FDW in 9.3 aggiunge
accesso in scrittura
(
Writable FDW
). Uno dei driver che supporta anche la scrittura
è quello per PostgreSQL (
postgres_fdw
).
CREATE DATABASE master;
\c master
CREATE EXTENSION postgres_fdw;
CREATE SERVER part01
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'part01');
CREATE USER MAPPING FOR ...
SERVER part01 OPTIONS (...);
CREATE FOREIGN TABLE messages_01
(msg TEXT, ...)
SERVER part01
OPTIONS (table_name 'messages');
CREATE DATABASE part01;
\c part01
CREATE TABLE messages (msg TEXT,
...);
INSERT INTO messages_01
VALUES('Sto ascoltando "Thunderstruck"');
\c part01
SELECT msg FROM messages;
Sto ascoltando "Thunderstruck"
CREATE TABLE
messaggi
(

id
SERIAL PRIMARY KEY,

utente_creazione
TEXT NOT NULL DEFAULT current_user,

orario_creazione
TIMESTAMP NOT NULL DEFAULT now(),

messaggio
TEXT NOT NULL
);

-- REVOKE ...

CREATE VIEW
v_messaggi
AS
SELECT
id, messaggio
FROM
messaggi
;

-- GRANT ...

INSERT

INTO

v_messaggi
(
messaggio
)
VALUES ('Fantastica vista di Wineglass Bay, Tasmania!');
DROP SCHEMA angus CASCADE;

\timing
BEGIN;

CREATE SCHEMA angus;
SET search_path TO angus;

CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(16) UNIQUE
);

CREATE TABLE pictures (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
title TEXT,
metadata
JSON
,
thumbnail BYTEA,
picture BYTEA,
created_at TIMESTAMP DEFAULT now(),
updated_at TIMESTAMP DEFAULT now()
);

INSERT INTO users VALUES (100, 'gabriele');

INSERT INTO pictures
(user_id, title, metadata)
VALUES (100,
'A picture of my Stratocaster',

'{"latitude":43.883,"longitude":11.1,"width":2240,"height":1680}'
);

COMMIT;
ANALYSE;
WITH
m
AS (

SELECT 43.883 AS latitude,
11.1 AS longitude,
2240 AS width,
1680 AS height
)
INSERT INTO pictures (user_id, title, metadata)
SELECT 100, 'Another picture of my Stratocaster',
to_json(m)
FROM m
;
Alternativa con CTE
-- Restituisce un oggetto JSON
WITH p AS (
SELECT title, metadata, created_at
FROM pictures WHERE id = 1
)
SELECT
to_json(p)
AS json FROM p;
-- Restituisce un array di JSON
SELECT id, title, metadata, created_at
FROM pictures WHERE user_id = 100;
WITH p AS (
SELECT id, title, metadata, created_at
FROM pictures WHERE user_id = 100
)
SELECT json
_agg(p) AS json
FROM p;
-- Accede a un valore nell'oggetto JSON
SELECT id, (
metadata->'longitude'
,

metadata->'latitude'
) AS coords
FROM pictures
WHERE user_id = 100;
{"title":"A picture of my Stratocaster","metadata":{"latitude":43.883,"longitude":11.1,"width":2240,"height":1680},"created_at":"2013-10-01 14:52:22.163447"}
[{"id":1,"title":"A picture of my Stratocaster","metadata":{"latitude":43.883,"longitude":11.1,"width":2240,"height":1680},"created_at":"2013-10-01 14:52:22.163447"}, +
{"id":2,"title":"Another picture of my Stratocaster","metadata":{"latitude":43.883,"longitude":11.1,"width":2240,"height":1680},"created_at":"2013-10-01 14:52:22.163447"}]
id | coords
----+---------------
1 | (11.1,43.883)
2 | (11.1,43.883)
PostgreSQL 9.3 aggiunge:
Generatori
Operatori
integrazione con hstore (estensione dizionario key->value)
JavaScript Object Notation
Postgre
No
SQL
Perché usare PostgreSQL
Comunità Open Source
Licenza
TCO (Total Cost of Ownership)
Ciclo di vita e di sviluppo
Qualità del codice e documentazione
Continuità operativa (Business Continuity)
Sicurezza e protezione dati
Ecosistema
Prestazioni e scalabilità
Versatilità e adattabilità
Prossimi appuntamenti
PGDay italiano (ITPUG)
Prato, 25 ottobre (www.pgday.it)
Conferenza PostgreSQL Europea
Dublino, 29 ottobre - 1 novembre (www.pgconf.eu)
Grazie
http://creativecommons.org/licenses/by-nc-sa/3.0/deed.it
Copyright 2013, 2ndQuadrant Italia

Attribuzione - Non commerciale - Condividi allo stesso modo 3.0 Unported (CC BY-NC-SA 3.0)

http://corso-python.develer.com/
Corso Python e PostgreSQL!
Full transcript