PostgreSQL Comandi Utili

postgresql  vai a:  PostgreSQL nel blog 

Postgresql, abbreviato anche in “postgres”, è un database relazione ad oggetti molto completo.
Nel blog è anche presente una pagina riepilogativa con molte delle risorse dedicate a PostgreSQL nel blog.
Di seguito alcuni dei principali comandi che si possono lanciare da terminale per gestire e controllare postgresql.

  • Il servizio, il server,  è in genere chiamato postgresql o postgresql più il numero di versione
  • Il client per postgresql da terminale è psql
  • Il client grafico di postgresql è pgadmin3
  • L’utente base del database è l’utente postgres

Avviare Fermare postgresql

service postgresql

{start|stop|status|restart|condrestart| condstop|reload|force-reload|initdb}

es: service postgresql restart 
sudo service postgresql status 
/etc/init.d/postgresql

{start|stop|status|restart| condrestart|condstop|reload|force-reload|initdb}

es: /etc/init.d/postgresql status 

Postgresql running
per verificare che postgresql sia attivo

ps axf | grep [p]ostgres 

per verificare che il server stia correttamente ascoltando sulla porta prescelta si può utilizzare il comando

netstat -nlp | grep 5432
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 10723/postgres
tcp6 0 0 :::5432 :::* LISTEN 10723/postgres

Ricaricare pg_hba.conf aseguito di modifica

 
su – postgres
pg_ctl reload
postgres=# select pg_reload_conf();   ##utilizzando postgresql cli

Configurazione postgresql

Due sono i file che usualmente vengono utilizzati per la configurazione del server postgresql postgresql.conf( settaggio porta di ascolto … ) e  pg_hda.conf  ( quali users possono accedere a quali databases e ccome possono farlo ). Posts con dettagli a riguardo   Postgresql server , Postgresql abilitare accesso remoto linux ubuntu centos debian.

Accedere alla console interattiva

psql test postgres
psql postgres postgres
psql -U postgres -W postgres -h 10.0.2.15 -p 5432
o
su - postgres
psql
exit per uscire

Help

\h help comandi SQL
\h SELECT
\h CREATE INDEX  

\? help comandi psql propri della console interattiva
\h select
\q exit console
\g o punto e virgola per eseguire la query

Mostra la versione di postgres

db=# select version();
Elenca databases da console interattiva
db=# \l
 query
db=#  SELECT datname FROM pg_database;

oppure elenca databases da terminale

$ psql -l
Visualizza database corrente
db=# \c 
 query 
db=# SELECT current_database();
Cambia database da console interattiva
db=# \c eurotech ;
Elenca tabelle di un database da console interattiva
db=# \d
 query
db=# SELECT table_name FROM information_schema.tables 
       WHERE table_schema = 'public'
Elenca colonne di una tabella di un database da console interattiva
db=# \d tabella
 query
db=# SELECT column_name FROM information_schema.columns 
       WHERE table_name = 'tabella' ;

db=# \d+ tabella

Users

Cambiare la password di un utente ed esempio con l’utente di riferimento postgres

db=# ALTER USER user WITH PASSWORD 'userpassword';
db=# ALTER USER postgres WITH PASSWORD 'YourPostgresPassword';

Creare un nuovo utente / role

db=# CREATE USER newuser WITH password 'userpassword';

Creare un nuovo “testuser” ROLE con superuser e password:

 db=# CREATE ROLE testuser WITH SUPERUSER LOGIN PASSWORD 'test';

Creare un nuovo utente dalla shell

/usr/local/pgsql/bin/createuser newuser

Permessi
Usare GRANT consente di definire i permessi a livello di database, tabella e di colonna
es.

GRANT ALL PRIVILEGES ON DATABASE eurotechgroup TO ethgrp;
GRANT SELECT ON mytable TO guest;

Mostra gli utenti di postgresql

db=#SELECT * from pg_user;

Mostra gli utenti connessi a postgresql

db=#SELECT usesysid, usename FROM pg_stat_activity;

Creare uno user di tipo read-only in un database postgresql

Database Crea/Delete

Creare un nuovo database dal terminale interattivo

db=# CREATE DATABASE newdb WITH OWNER postgres;

Creare un nuovo database dalla shell

su – postgres
/usr/local/pgsql/bin/createdb newdb -O postgres

Cancella un database

DROP DATABASE mydb;

Cancellare un database dalla shell

su – postgres
dropdb dbname

Se si utilizzano maiuscole nel nome del database in psql usare le ” ”  es.

DROP DATABASE myDB

BACKUP / RESTORE

Formato sql  per il backup 

Se si utilizza  il formato sql per effettuare il backup si possono usare i seguetni comandi per il backup restore

backup:
pg_dump -U user -h host db_dacopiare -p port -f dumpfile.sql
 $ pg_dump -U postgres -h 10.0.1.10 -p 5432 ethgroup2008 -f ethgroup2008.sql
restore:
 $ psql -U postgres -h 192.168.1.10 -W -d ethgroup2008 -f ethgroup2008.sql

Backup database in un server e ripristino in un altro server

per evitare username e password portarsi ad esempio sull’utente postgres,  il database deve essere presente nella macchina target diversamente va creato.

su - postgres

# creare il database nella macchina target  da terminale con un comando del tipo

createdb dbname

# esempi di comando per il trasferimento

pg_dump -h host_source dbname | psql -h host_target dbname
pg_dump dbname | psql -h host_target -d dbname
pg_dump -h host1 dbname -t mytable | psql -h host2 -d dbname
pg_dump -h host1 -U username -W dbname -t mytable | psql -h host2 -d dbname

Questi comandi possono essere efficacemente utilizzati specialmente su Tunnel ssh Tunnel via ssh per gestire il server postgresql anche con pgAdmin

Va considerato che nel server target sarebbe opportuno fossero già presenti gli utenti presenti nel server sorgente.

Backup di una tabella

# pg_dump --table catalogs -U tmpuser eurotechdb -f catalogs.sql

Ripristino di una tabella

psql -f catalogs.sql catalogs

Formato backup custom

backup:
pg_dump -Fc -U postgres -W ethgroup2008 -f ethgroup2008.backup

Restore:
pg_restore -h 10.0.1.10 -U postgres -c -W -d ethgroup2008 ethgroup2008.backup
  • -Fc formato compresso
  • -c drop database object prima di ricreareli
  • -C crea il database target
  • -f file output
  • -W password
  • -t = tabella
  • -a solo dati

Backup di tutti i database

pg_dumpall > alldb.sql
su – postgres
psql < alldb.sql

Visualizza i datatypes base

SELECT typname,typlen from pg_type where typtype='b';

Transazioni

Avviare una transazione

# BEGIN

rollback o commit a transazione

# ROLLBACK
# COMMIT

QUERY

Ovviamente la console interattiva consente di eseguire query dei vari tipi: select, update, insert …
In genere pero è preferibile passare per strumenti come pgadmin3 …
Di seguito alcune query di tipo generale

\watch <n.sec>

Dalla versione 9.3 è disponibile il comando \watch che consentente di ripetere l’ultima query ogni n. secondi.

per cambiare database

\c biblioteca;

Display il piano di esecuzione di una query

EXPLAIN SELECT * FROM products;

Mostra il piano eseguendo la query sul lato server

EXPLAIN ANALYZE SELECT * FROM products;

EXPLAIN ANALYZE SELECT * FROM _getcatalog(300);

Query select per contare il numero di records in un tabella

per cambia database

\c     ethgroup2010U ;

quindi

select count(*) from catalogs;

Query di tipo count che ritorna il numero di records in un tabella data una spcifica colonna con valore diverso da null

select count(productline) from catalogs;

Aggiungere un contatore di record in una select

SELECT row_number() OVER() as rownum, table.* FROM table;

Conta il numero distinto di records data una spcifica colonna con valore diverso da null

select count(distinct productline) from catalogs;

Ottieni il minimo (MIN) o il massimo (MAX) valore di una colonna

# select MIN(ordercode) FROM products;

Ottieni il secondo

# SELECT MIN(ordercode) FROM products where ordercode > ( select MIN(ordercode) from productse );
# SELECT MAX(ordercode) FROM products where ordercode <( select MAX(ordercode) from productse );

Per inviare l’output di query select ad un file

# \o myoutput_file
# SELECT * FROM news;

ritorna l’output allo stdout
# \o

Ritorna dimensioni dei database e delle tabelle

Calcola le dimensioni di uno specifico database

SELECT pg_database_size(‘mydb’);

pg_size_pretty ritorna le dimensione in MB un formato più amichevole

SELECT pg_size_pretty(pg_database_size(‘mydb’));

pg_size_pretty ritorna le dimensione in MB un formato più amichevole

SELECT pg_size_pretty(pg_total_relation_size(‘big_table’));

Trova la tabella più grande del database selezionato

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

relpages: numero di pagine ( di default una pagina è 8Kb)
pg_class: system table, mantiene i dettagli delle relazioni

per ritornare l’ip del server postgres
SELECT * FROM inet_server_addr()

Memorizza la password dopo crittografia

SELECT crypt ( ‘erim’, gen_salt(‘md5’) );

PostgreSQL registra tutti i comandi eseguiti in un file chiamato ~/.psql_history

# cat ~/.psql_history

 

To see postgresql all settings

SHOW ALL ;

 

Pagina riepilogativa

Alcune risorse presenti nel blog

Postgresql 

query di tipo plpgsql 

Creare eliminare database e tabelle

  1. griko
    settembre 15, 2015 alle 2:19 pm

    SELECT column_name FROM information_schema.column WHERE table_name = ‘tabella‘
    dà errore perché ci va columnS e non column. Corretto:
    SELECT column_name FROM information_schema.columns WHERE table_name = ‘tabella’;

    • ottobre 17, 2015 alle 1:54 pm

      Grazie per la segnalazione. Correzione inserita nel post.
      Purtroppo gli errori di questo tipo nel blog sono sempre in agguato.
      Dovrei sempre fare un taglia incolla del comando dopo averlo lanciato per essere cento che la sintassi sia corretta…

  2. Dave
    marzo 14, 2016 alle 4:04 pm

    Buongiorno, chiedo cortesemente un aiuto.
    Dovendo backappare giornalmente il DB (del backup se ne occupa il sw Veeam), dobbiamo premunirci di stoppare, prima che il backup si avvii, il servizio di postgresql e ci è stato lasciato il comando: systemctl stop postgresql-9.4. Dopo di che, a backup avvenuto il ripristino del servizio: systemctl start postgresql-9.4. Utente: root per entrambi.
    Conoscendo gli orari di avvio del backup ed il tempo di elaborazione del backup stesso, è possibile creare un semplice file “batch” che stoppi prima e faccia ripartire dopo i servizi ?
    Se si come si fa e dove va messo ?
    Il SO è Centos 7 e per chi come noi viene da AS400 “qualche…” problemino c’è😀
    Grazie.

  1. No trackbacks yet.

Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

%d blogger cliccano Mi Piace per questo: