Archive

Archive for the ‘Postgresql – SQL’ Category

installare Postgresql 9.4 su Centos 6.7 64 bit

postgresql  go to:  PostgreSQL nel blog 

Default postgresql server in Centos 6.7 x86 64 is version 8.4.x

to install postgresql 9.4.4 use   Postgresl yum repository.  .

su -

Download and install the yum rpm PostgreSQL Repository

wget http://yum.pgrpms.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm

rpm -i pgdg-centos94-9.4-1.noarch.rpm

We can use yum list to check the package now available.

yum list postgresql*

you have to see many postgresql94 packages

Install, inizialize and start postgresql 9.4.

yum install postgresql94 postgresql94-server postgresql94-libs postgresql94-contrib

Than we initialize and start postgresql-9.4

service postgresql-9.4 initdb 

service postgresql-9.4 start

check that everything is OK

su - postgres  
pslq -l

we have to see a list of 3 databases

Open postgresql port 5432 in Iptables Firewall

with editord vi, cmedit nano edit file /etc/sysconfig/iptables  :
vi -w /etc/sysconfig/iptables

# add next commit row

-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

restart iptables firewall

service iptables restart

Configure postgres set superuser postgres password

# switch to postgres user

  su - postgres

# open postgresql cli as postgres and connect to the postgres database

  psql postgres

# set the password of user postgres using alter

 ALTER USER postgres WITH PASSWORD 'your-postgres-password';

Setting pg_hba.conf
pg_hba.conf say postgresql which users can access which databases and how they can do. ( centos/redhat location /var/lib/pgsql/9.4/data/pg_hab.conf)

so we change METHOD to md5 for IPv4 and IPv5 local connections
then we can add other ipv4 connection as

host    all         all         192.168.1.0/24        md5

at the end we can have something like this

# TYPE  DATABASE    USER        CIDR-ADDRESS               METHOD                    
                                                                                
# "local" is for Unix domain socket connections only                            
local   all         all                                      peer    
# IPv4 local connections:                                                       
host    all         all           127.0.0.1/32               ident
host    all         all           192.168.1.0/24             md5      
# IPv6 local connections:                                                       
host    all         all           ::1/128                    ident                     
# Allow replication connections from localhost, by a user with the              
# replication privilege.                                                        
#local   replication     postgres                            peer           
#host    replication     postgres        127.0.0.1/32        ident          
#host    replication     postgres        ::1/128             ident  

In order for the change to take effect we have to

reload pg_hba.conf
from the postgresql cli

 postgres=# select pg_reload_conf();

from the shell as postgres user ( su – postgres ) pg_ctl reload

Setting postgresq.conf
In this file we can configure remote access to postgresql
(centos/redhat location: /var/lib/pgsql/9.4/data/postgresql.conf  )

look for listen_address in connections and authentication section.
to enable remote connections uncomment and change listen_address that by default is ‘localhost’ so access is limited to local machine.
So at the and listen_addresses look like

listen_addresses = '*'

It is possible to set the listen_address to specific IPs using a comma separate list.

Restart postgresql con

service postgresql restart

Now if you want you can change post value

port=  5432
Change port value 
Default port value for postgresql is 5432
Usually to change port value (ex:5433 ) we have to uncomment and to modify
# port= 5432
to
port= 5433
in Centos 6.7 we have to do another step we have to create the file /etc/sysconfig/pgsql/postgresql-9.4 with
PGPORT=5433
export PGPORT
touch /etc/sysconfig/pgsql/postgresql-9.4
echo ‘PGPORT=5433’ >> /etc/sysconfig/pgsql/postgresql-9.4
echo ‘export PGPORT’ >> /etc/sysconfig/pgsql/postgresql-9.4

If port change you have to change iptables firewall setting and restart postgresql.

Now we configure postgresql 9.4.x service to start at boot

Check

$ chkconfig --list | grep postgresql*
postgresql-9.4 	0:off	1:off	2:off	3:off	4:off	5:off	6:off

Setting

chkconfig --level 35 postgresql-9.4 on
chkconfig --list | grep postgres*
postgresql-9.4 	0:off	1:off	2:off	3:on	4:off	5:on	6:off

we can check changes in postgesql cli :

# show listen_addresses;
# show port;

we can also do another check as root with

netstat -nxl | grep PGSQL

and

netstat -nlp | grep 5432
netstat -nlp | grep 5432
tcp        0      0 127.0.0.1:5432              0.0.0.0:*                   LISTEN      3221/postmaster     
tcp        0      0 ::1:5432                    :::*                        LISTEN      3221/postmaster     
unix  2      [ ACC ]     STREAM     LISTENING     1592992 3221/postmaster     /tmp/.s.PGSQL.5432

Postgres blog’s post:

Mysql contro Postgresql

postgresql

MySQL e PostgreSQL sono i due database relazionali  open source più  utilizzati e 

mysqllogo

conosciuti. Lo sviluppo iniziale dei due database ha avuto obiettivi diversi. Mysql ha privilegiato la velocità, l’essenzialità mentre Postgresql l’aderenza agli standard e la completezza rendendo disponibili  diverse funzionalità anche se al prezzo di performance inferiori. Ancora per postgresql si affermava che gli sviluppatori di postgresql erano animati dall’obiettivo di portare in ambito open-source un database con l’impostazione dei database di Oracle ( “open-source” Oracle) . Grazie alla velocità e all’essenzialità, che in alcuni ambiti diventà semplicità, Mysql storicamente è stata la prima scelta nelle applicazioni web appoggiate a database.  Non va trascurato poi che Mysql è entrato nella “scuderia” Oracle.

Nel tempo la situazione si è andata modificando ed oggi i due prodotti stanno in qualche modo convergendo nel senso che le prestazioni di Postgresql sono decisamente migliorate mentre Mysql si è aricchito di funzioni. MySQL ( oggi alla versione 5.6)  rimane comunque il più utilizzato e se una volta lo era per la velocità e la praticità ora lo è per l’ampio numero di progetti in cui è estrato a far parte. Infatti quasi tutte le web application si basano o prevedono la possibilità di scelta di MySQL come database. PostreSQL ( oggi alla versione 9.3.1)  dal canto suo offre comunque una protezione dei dati superiore grazie al rispetto degli standard cosa che non accade con MySQL. Per gli sviluppatori di postgresql affidabilità e integrità dei dati rimangono un obiettivo irrinunciabile.

Architettura

Per quanto riguarda il funzionamento MySQL dispone di un strato SQL mentre può utilizzare più engine dati. Ad esempio il motore di archiviazione myISAM  può essere scelto per situazioni con  maggiore concorrenza in lettura e che non richiedono proprietà ACID. InnoDB per avere il supporto ACID anche se non completo.  Invece Postgresql è un database server unificato con un unico  motore di archiviazione.

Al momento gli elementi distintivi che più contraddistinguono i due e che vanno valutati nella scelta di un database SQL opensource sono l’aderenza agli standard SQL, la conformità ACID e la necessità di usare una piattaforma LAMP. Come affermato in premessa l’aderenza alla normativa ISO è sempre stata un tratto distintivo di postgresql. Quindi se si ha la necessità di sviluppare un progetto che abbia questa caratteristica postgresql diventa la prima scelta.

Porting

Se si desidera la possibilità di trasferimento (porting) del progetto, perchè è cresciuto molto, su database Oracle. Postgresql, rispettando  gli standard ISO per SQL, rende il passaggio sufficentemente agevole. Con MySQL questo non sarà possibile poichè non é conforme agli standard ISO e non segue l’impostazione ACID  Atomicità, Coerenza, Isolamento e Durabilità sono le proprietà logiche che devono contraddistinguere le transazioni) . Nel caso di MySQL il trasferiemento può richiedere una pesante riscrittura del progetto. LAMP e Riutilizzo del Codice Molti progetti girano sul LAMP (Linux, Apache, MySQL, PHP/Pearl/Python) web applications come WordPress, Joomla, Mediawiki, …. si appoggiano preferibilmente su MySQL. Molte di queste applicazioni non prevedono la possibilità di utilizzare postgresql pertanto la scelta operata a questo livello condiziona la scelta del database. Per quanto riguarda la possibilità di riutilizzo di codice va evidenziato che esiste molto più materiale, codice a disposizione per MySQL che per postresql proprio per l’ampia dissusione dello stesso nelle web applicaiton. Risorse:

Per un confronto più articolato : MySQL vs PostgreSQL – WikiVS

Nel blog:

Risorse:

SQLExpress 8 reset sa password Windows server 2008

Può capitare di non disporre o dimenticare la password dell’utente amministratore sa di SQL Express. In questo caso per resettare la password dell’ amministrator sa è necessario avviare SQLExpress in single mode.  Utilizzando SQL Manager Studio una volta  avviato SQLExpress in single mode la procedura per il reset della password di sa risulta decisamente agevole.

Per avviare SQLExpress in single mode accedere via pannello di controllo al servizio SQL Server (SQLEXPRESS)

Fermare il servizio premere il tasto destro del mouse sul servizio e nel tab General in basso inserire nel campo Start Manager l’opzione – m e riavviare il servizio premendo Start.

 

Poichè il single mode è permessa una sola connessione attiva controllare che SQL controllare che SQL Server Agent non sia in esecuzione.

 

Utilizzando il tasto destro del mouse “Avviare SQL Manager Studio” in modalità Amministratore < Run as administrator >.

Quindi modificare la password dell’administrator sa in <Security> <Logins>

Altra possibilità per avviare in single mode SQLExpress

 

Avviare SQL Server Configuration Manager

In Microsfot SQL Server Managemente Studio menu [View]  di Selezionare  [Registered Servers]

Nella sezione Registered Server selezionare il server premere il  Tasto destro del mouse e clikkare SQL Registered Configuration Manager. Seguirà la richiesta per avviare il servizio come amministratore.  A questo livello è possibile controllare il servizio e modificare i parametri

 

 SQL Server Configuration Manager 

Per accedere alle proprietà di SQLExpress si può premere anche il tasto destro del mouse in Object Explorer di SQL Server Manager Studio

 

 

Risorse:

http://msdn.microsoft.com/en-us/library/dd207004.aspx

 

 

Installare Adminer 3.6.3 in Ubuntu 12.04 server

marzo 16, 2013 1 commento

adminer-logoRilasciata in gennaio la versione 3.6.3 di Adminer una interfaccia di amministrazione per database SQL caratterizzata da pulizia ed efficacia oltre a MySQL, supporta PostgreSQL, SQLite, MS SQL, Oracle. A differenza di phpAdmin consiste in un unico file php.

adminer login postgresql

adminer login postgresql

Disponibile anche Adminer-editor un interfaccia user-friendly e di facile utilizzo per editing dei dati dei database per l’utente finale. Adminer e Adminer editor dispongono di plugins e  skins.

Installare Adminer

Per installare adminer in Ubuntu 12.04 è necessario avere installato mysql-server, apache2 e php. Sono necessari anche i moduli php per i database con i quali si intende interagire. Ad esempio
per amministrare mysql dovrà essere installato il modulo php5-mysql, mentre per postgresql è necessario il modulo php5-pgadmin.

I moduli possono essere installati con

sudo apt-get install php5-mysql php5-pgsql

Creare la directory /usr/share/adminer

sudo  mkdir /usr/share/adminer
sudo  cd /usr/share/adminer

scaricare l’ultima versione di adminer

sudo wget "http://www.adminer.org/latest.php"

creare un link simbolico adminer.php

sudo ln -s latest.php adminer.php

Configurare apache perchè utilizzi adminer utilizzando echo e quindi riavviare apache2

echo "Alias /adminer.php /usr/share/adminer/adminer.php" > /etc/apache2/conf.d/adminer.conf

service apache2 restart

Adminer mysql

Disinstallare Adminer

Per disinstallare adminer  rimuovere la directory /adminer e il suo contenuto

sudo  rm -R /usr/share/adminer

rimuovere anche il file di configurazione apache2 e riavvare il web server

sudo rm /etc/apache2/conf.d/adminer.conf
service apache2 restart

Risorse:

 

pgScript vs pl/Pgsql postgresql

postgresql

PgScript è un linguaggio di scripting usato per maneggiare i dati del database postgresql tuttavia non è tipico di Postgresql ma di PgAdmin 3 il tool di amministrazione ad interfaccia grafica di postgresql. Pertanto opera esclusivamente a livello client.

pl/Pgsql invece è di fatto una estensione del linguaggio SQL tipica di Postgresql,  ricorda analogo linguaggio di Oracle. Ovviamente pl/Pgsql opera a livello server.

Ne consegue che le finalità sono completamente differenti .

Esempio di pgScript

 SET @N = 0 ; 
 SET @PROGR = 'pgScript' ;
 -- PRINT @PROGR + 'features' + CAST (@N AS STRING ) ;
 
 SET @N = @N +1 ; 
 PRINT @N ;
 WHILE @N < 10 
 BEGIN 
       SET @N = @N +1 ; 
      PRINT @N ;
 END

pgScript

Risorse:

query di tipo plpgsql 

Pagina riepilogativa

Query SQL  – PostgreSQL

Postgresql 

Creare eliminare database e tabelle

Postgresq Numerare le righe di una select query – SQL count row_number

In postgresql per aggiungere un contatore al risultato di una query di tipo select è possibile utilizzare la funzione row_number(). L’uso di row_number() richiede la clausola over.  Il formato generale per numerare le righe di una tabella visualizzata con una select query è :

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

es:

select row_number() OVER () as num,  sp500.* 
 FROM   sp500 WHERE date > 01-01-2010 ORDER BY date;

Se è necessario che le i records siano ordinati si può utilizzare l’espressione  OVER ( :

SELECT row_number() OVER ( ORDER BY field nulls last) as rownum, table.* 
  FROM table 
  ORDER BY field ;

es:

select row_number() OVER ( order by date nulls last ) as num,  sp500.* 
FROM sp500 WHERE date > 01-01-2010 ORDER BY date;

Se si desidera inserire anche una colonna con il numero totale si può utilizzare la funzione count (*):

select row_number() OVER( order by date nulls last ) as num,  count(*) over ()  as total; sp500.* FROM   sp500 WHERE date > 01-01-2010 ORDER BY date;

Oracle utilizza row_number

Count()

In postgresql per contare i records che soddisfano una condizione data si utilizza count utilizzando la forma Sql standard.  Ad esempio:

SELECT  count(c.itemid) AS "Num." ,  c."Country" AS codec
FROM contactsdownloads  INNER JOIN contacts AS c
ON (itemidcontacts) = c.itemid

GROUP BY codec
HAVING  count (c.itemid) > 10

Formato generale

SELECT count(* )  over () , a.*
    FROM  table a  ;

Nella query seguente  viene effettuata la somma e il count a partire non da una tabella ma da  un’altra query.
Questo consente di ottenere un count su due livelli di ragruppamento.
In pratica si effettua un primo raggruppamento dei records che ritornerà il primo conteggio quindi si elaboreranno questi dati con un secondo livello di raggruppamento. Qui si effetturà un nuovo count e si opererà la  somma dei dati del primo count.

SELECT nameprd AS "Name Product" , sum(conta) AS "files downloaded", count (itemidcontacts) AS "N.Users" 
       FROM (SELECT  nameprd , count (itemid) AS conta, itemidcontacts FROM contactsdownloads 
               WHERE createddate > '01-01-2007'
               GROUP BY nameprd, itemidcontacts
               ORDER BY conta DESC ) AS DL

     GROUP BY  nameprd
     ORDER BY sum(conta) DESC;

Pagina riepilogativa

Query SQL  – PostgreSQL

Postgresql 

query di tipo plpgsql 

Creare eliminare database e tabelle

Confronto tra i metodi: ExecuteNonQuery, ExecuteReader, ExecuteScalar

I metodi ExecuteNonQuery, ExecuteReader, ExecuteScalar sono i tre principali metodi della classe SqlCommand.Gli oggetti risultanti, una volta instanziata la classe, consentono l’esecuzioni di istruzioni SQL e Stored Procedure in un Database di un Server SQL.

Il metodo ExecuteNonQuery viene usato per eseguire una instruzione SQL e stored procedure di tipo INSERT, DELETE e UPDATE. In questo caso il valore ritornato è il numero di record che sono stati interessati dal comando SQL. Più in generale viene usato per le action queries quindi oltre che per le istruzioni SQL UPDATE, INSERT, DELETE anche per le istruzioni CREATE, ATER, DROP.  Il valore ritornato ( result type ) è di tipo int. Se l’istruzioni SQL coinvolge records il result è il numero degli stessi diversamente è -1. Esempio in  asp.net c#   database postgresql con provider dati Npgsql

...
dbcon = new NpgsqlConnection(NpgsqlConnectionString);
dbcon.Open();
NpgsqlCommand command = new NpgsqlCommand( "DELETE FROM products WHERE itemid = 56", dbcon);
int result reader = command.ExecuteNonQuery();
...

Il metodo ExecuteReader ritorna una collezione di righe e colonne ( records ) e viene pertanto utilizzato per le istruzioni di tipo SELECT. Più in generale ExecuteReader lavora sia con query di tipo SELECT ( Non-Action ) che con le query di tipo Action. Il tipo ritornato essendo una collezione di records sara di tipo DataReader. Esempio in  asp.net c#   database postgresql con provider dati Npgsql

...
dbcon = new NpgsqlConnection(NpgsqlConnectionString);
dbcon.Open();
NpgsqlCommand command = new NpgsqlCommand( "SELECT * FROM products", dbcon);
IDataReader reader = command.ExecuteReader();
...

Il metodo ExecuteScalar ritorna la prima colonna della prima riga della query che viene  lanciata in esecuzione. Le rimanenti righe e colonne sono ignorate. Comodo nel caso di query di tipo Count.  Più in generale vien usato per le query di tipo Non Action con funzioni di aggregazione. Viene ritornato un valore di tipo object.

...
dbcon = new NpgsqlConnection(NpgsqlConnectionString);
dbcon.Open();
NpgsqlCommand command = new NpgsqlCommand( "SELECT COUNT(itemid) AS "Num.", productline FROM products GROUP BY productline", dbcon);
object result = command.ExecuteScalar();
...

Risorse: