Postgresql Comandi utili
Avviare Fermare postgresql
/etc/init.d/postgresql {start|stop|status|restart|condrestart|condstop|reload|force-reload|initdb}
es: /etc/init.d/postgresql status
service postgresql {start|stop|status|restart|condrestart|
condstop|reload|force-reload|initdb}
es:
service postgresql status
service postgresql restart
Postgresql running
per verificare che postgresql sia attivo
ps axf | grep postgres
per verificare che il server stia correttamente ascoltando sulla porta prescelta si può utilizzare il comando
netstat -nlp | grep 5432tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 10723/postgres tcp6 0 0 :::5432 :::* LISTEN 10723/postgres
Accedere alla console interattiva
psql test postgres psql postgres postgres psql -U postgres -W postgres -h 10.0.2.15 o su - postgres psql exit per uscire
Help
\h help comandi SQL \h SELECT \h CREATE INDEX \? help comandi psql propri della console interattiva \q exit console \g o punto e virgola per eseguire la query
Mostra la versione di postgres
db=# select version();
Elenca databases
db=# \l
oppure
$ psql -l
Elenca tabelle di un database
db=# \d
Cambia database
db=# \c eurotech ;
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
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 tabella e di colonna
es. GRANT ALL PRIVILEGES ON DATABASE eurotechgroup TO ethgrp;
Mostra gli utenti di postgresql
db=#SELECT * from pg_user;Mostra gli utenti connessi a postgresql
db=#SELECT usesysid, usename FROM pg_stat_activity;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
BACKUP / RESTORE
Backup di un database
pg_dump -U {user} {db_dacopiare} -f {dumpfile.sql}
pg_dump eurotechgroup_ud -t sitepage -Fc -a -U postgres -W > miofile.sql
* -t = tabella
* -Fc formato compressa
* -a solo dati
Restore di un dataabse
psql -U {user} -d {db_target}-f {dumpfile.sql}
o
su - postgres
psql -d biblio2 -f biblio2_20030303.sql
Backup database in un server e ripristino in un altro server
pg_dump dbname | psql -h hostname dbname pg_dump -h host1 dbname -t mytable | psql -h host2 dbname pg_dump -h host1 -U username -W dbname -t mytable | psql -h host2 dbname per evitare username e password portarsi ad esempio sull'utente postgres
Backup di una tabella
# pg_dump --table catalogs -U tmpuser eurotechdb -f catalogs.sql
Ripristino di una tabella
psql -f catalogs.sql catalogs
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
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);
Conta il numero di records in un tabella
select count(*) from catalogs;
Conta il numero di records in un tabella data una spcifica colonna con valore diverso da null
select count(productline) from catalogs;
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 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
Risorse:
- plpgsql how to loop record
- PostgreSQL SQL use CASE when … then … else ..END in SELECT
- plpgsql functions nexted cycles FOR LOOP multiple IF THEN
- Postgresql plpgsql function example IF … THEN … ELSE … END IF
- plpgsql Postgres FOR LOOP END LOOP
- Postgresql reset sequence
- postgresql check if table exist
- How escape single quote ‘ in postgresql
- postgresql Elencare i campi di una tabella
- PostgreSQL CREATE TEMPORARY TABLE
- Postgresql Comandi utili
- SQL Joincast su
- SQL Cast
- SQL Union Query
- SQL Count
- SQL Delete
- Postgresql cancella record duplicati
- Postgresql : SQL UPDATE query
- postgresql : SQL SELECT query
- postgresql : Eseguire una SQL UPDATE query di test
- PostgreSQL Total, Subtotal an alternative
- SQL Concatenare due o più campi – String Concatenation
- SQL Delete
..
