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 come 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 cambaire la pasword de del super user 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
Cancellare 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 una 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
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
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
Per visualizzare le impostazione di postgresql
SHOW ALL ;
Query per elencare gli oggetti presenti nel database
La query sotto riportata utilizza pg_class per visualizzare gli oggetti: type, tabella, sequenza, vista, indice e special. Il campo pg_class.relkind è utilizzato per determinare il tipo di oggetto. I codici nel campo relkind utilizzati sono
- table ordinary = relkind = ‘r‘
- table TOAST = ‘t‘
- table foreign = ‘f‘
- sequence = ‘S‘
- view = ‘v‘
- index = ‘i‘
- special = ‘s‘
SELECT n.nspname as "Schema", c.relname as "Name", CASE c.relkind WHEN 'c' THEN 'type' WHEN 'r' THEN 'table' WHEN 't' THEN 'table' WHEN 'f' THEN 'table' WHEN 'S' THEN 'sequence' WHEN 'i' THEN 'index' WHEN 'v' THEN 'view' WHEN 's' THEN 'special' END as "Type", pg_catalog.pg_get_userbyid(c.relowner) as "Owner" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('c','') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1,2;
draft usando pg_class
SELECT relname FROM pg_class WHERE relname ='r' ;
Cambiare l’owner degli oggetti presenti nel database
Per cambiare l’owner degli oggetti di un database e disponibile il comando:
REASSIGN OWNED BY old_user TO new_user;
Tuttavia il comando non consente la modifica degli oggetti dello user postgres.
Per modificare l’owner di tutti gli oggetti di una database utilizzando una query fare il riferimento al post
Cambiare l’owner di tutte lefunzioni di un database utilizzando la console psql e il terminale:
Pagina riepilogativa
Alcune risorse presenti nel blog
- Installare Postgresql 9.6.5 su Centos 7.4 64 bit
- installare Postgresql 9.4 su Centos 6.7 64 bit
- Postgresql running in parallel postgresql 9.1 and 8.4 on Centos 6.3 64 bit
- Mysql contro Postgresql
- Upgrade postgresql 8.4.12 To postgresql 9.1.4 Debian
- Downgrade postgresql 9.1.4 to postgresql 8.4.12 – Centos 5
- Upgrade postgresql 8.4 to postgresql 9.1 Centos 5
- Upgrade postgresql server 8.4.3 8.4.12 – Centos 5
- Installare postgreSQL 8.4 in Centos 5
- Tunnel via ssh per gestire il server postgresql con pgAdmin
- Postgresql reset sequence
- Creare uno user di tipo read-only in un database postgresql
- Script per il backup dei database di postgresql
- Postgresql Windows 2008 server R2
- Postgresql server
Postgresql
- postgresql check if table exist ;
- How escape single quote ‘ in postgresql ;
- SQL Concatenare due o più campi – String Concatenation ;
- Postgresql cancella record duplicati ;
- postgresql Elencare i campi di una tabella ;
- PostgreSQL CREATE TEMPORARY TABLE ;
- Postgresql Comandi utili ;
- Postgresql reset sequence ;
Query SQL – PostgreSQL
- SQL Join – Joincast su ;
- SQL Cast ;
- SQL Union Query ;
- SQL Count ;
- SQL Delete ;
- Postgresql : SQL UPDATE query ;
- postgresql : SQL SELECT query ;
- PostgreSQL SQL use CASE when … then … else ..END in SELECT
- postgresql : Eseguire una SQL UPDATE query di test ;
- PostgreSQL Total, Subtotal an alternative select multipli e TEMPORARY TABLE;
- Postgresq Numerare le righe di una select query – SQL count row_number
DB
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’;
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…
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.
Con cron è possibile fermare e far ripartire il servizio postgresql-9.4 a proprio piacimento.
Tuttavia sono incuriosito dalla procedura di backup.
Infatti per il backup di postgresql non serve software specifico e non serve fermare postgresql.
Ecco il post con lo script che esegue il backup di tutti i database postgresql e senza fermare il servizio
https://mauriziosiagri.wordpress.com/2010/03/08/script-per-il-backup-dei-database-di-postgresql/