Archive

Posts Tagged ‘postgres’

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:

Upgrade postgresql 8.4.13 to postrgesl 9.2 centos 5

postgresql  vai a:  PostgreSQL nel blog 

Esempio di upgrade di postgresql dalla versione 8.4.13 alla versione 9.2.6 in Centos 5.8.  L’upgrade di versione necessita della fase di dump/restore oppure dell’utilizzo di pg_upgrade ( questo dalla versione 9.0) per consentire la migrazione della base dati alla nuova versione.  Per poter utilizzare il pg_update deve essere supportata l’esecuzione in parallelo di postgresql come avviene in debian o Centos 6. Centos 5 putroppo non la supporta pertanto sarà necessario utilizzare il dump/restore.

Creare una directory di backups, procedere al dump degli oggetti globali (roles e tablespaces) e di tutto il server, volendo si possono salavare in automatico i singoli db:

mkdir /home/pgsqlbck
chown postgres:postgres /home/psqlbck
su - postgres
pg_dumpall --globals-only > /home/pgsqlbck/dump-data_globals.sql 
pg_dumpall > /home/pgsqlbck/dump-data_alldb.sql
exit

I singoli db di postgresql 8.4 possono essere copiati utilizzando lo script nel link di seguito

Script per il backup dei database di postgresql

Fermiamo il servver postgresql

service postgresql stop
Visto che si tratta di upgrade di versione con cambi nella struttura dei dati possiamo conservare la directory dei dati di PostreSQL /var/lib/pgsql/data oppure al termine dell’upgrade rimuoverla

Per installare la nuova versione di postgresql utilizziamo  il repository yum per rpm. Nella pagina sotto  linkata sono disponibili le varie combinazioni di distribuzione e versione postgresql

PostgreSQL RPM Repository (with Yum)

Installiamo il repository di postgresql 9.2 per  centos 5 – i386

cd /usr/local/src
wget http://yum.pgrpms.org/9.2/redhat/rhel-5-i386/pgdg-centos92-9.2-6.noarch.rpm
rpm -ivh pgdg-centos92-9.2-6.noarch.rpm

Adesso escludiamo postgrsql dal processo standard di update dei pacchetti editando il file etc/yum.repos.d/CentOS-Base.repo aggiungendo la riga

exclude=postgresql*'

nelle sezioni [base] [updates]

ad esempio nella sezione base il risultato sarà del tipo:

[base]                                                                                         
name=CentOS-$releasever - Base 
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os 
gpgcheck=1               
...
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-5   
exclude=postgresql*

Controlliamo i pacchetti disponibili

yum list | grep ‘postgresql’

nella lista saranno presenti pacchetti  postgres84 versione installata e i pacchetti della versione 92  di postgresql

postgresql84.i386                        8.4.13-1.el5_8                installed
postgresql84-contrib.i386                8.4.13-1.el5_8                installed
.....
postgresql92.i386                        9.2.2-1PGDG.rhel5             pgdg92   
postgresql92-contrib.i386                9.2.2-1PGDG.rhel5             pgdg92   
postgresql92-debuginfo.i386              9.2.2-1PGDG.rhel5             pgdg92

adesso rimuoviamo la versione precedente postgresql84

yum remove postgresql84*

installiamo la versione postgresql92

yum install postgresql92 postgresql92-server postgresql92-contrib postgresql92-libs

inizializziamo la base dati e avviamo il server postgresql-9.2

service postgresql-9.2 initdb

service postgresql-9.2 start

le basi dati di postgresql in Cenrtos sono localizzate in /var/lib/psql

:</var/lib/pgsql>$ ls -lhG
drwx------ 4 postgres 4.0K Dec 28 12:20 9.2
drwx------ 12 postgres 4.0K Dec 28 11:21 data

/data per il postgresql base e ovviamente /9.2 per la versione che si sta installando

su - postgres
psql < /home/pgbck/dumpalldb.sql

Ripristinare le modifiche ai files di configurazione /var/lib/pgsql/data/*.conf
in particolare postgresql.conf e pg_hba.conf

il file pg_hba.conf con le vecchie impostazioni relative alla autenticazione dei client, cioè il file che controllare quale host accede è a che cosa, può essere copiato dal precedente cluster che era stato rinominato:

cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/9.2/data/pg_hba.conf

per essere in linea con le nuove caratteristiche di postgresql 9.2 appendiamo il seguente nuovo blocco commentato, presente di default nel file pg_hba.conf della versione 9.2

# 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

La modifica può essere apportata utilizzando echo con comandi riportati di seguito. Possiamo usare tail -f pg_hba.conf su altro temrinale per seguire le modifiche in real time apportate a pg_hba.conf

:
cd /var/lib/pgsql/9.2/data
su postgres
echo '# Allow replication connections from localhost, by a user with the' >> pg_hba.conf 
echo '# replication privilege.' >> pg_hba.conf
echo '#local   replication     postgres                                peer' >> pg_hba.conf
echo '#host    replication     postgres        127.0.0.1/32            ident' >> pg_hba.conf 
echo '#host    replication     postgres        ::1/128                 ident' >> pg_hba.conf

possiamo anche modificare il permesso ident in peer della condizione

:
# TYPE  DATABASE        USER            ADDRESS                 METHOD                                                               

# "local" is for Unix domain socket connections only                                                                                 
local   all             all                                     ident

in

:                                                                            
local   all             all                                     peer 

riavviamo

service postgresql-9.1 restart

controlliamo il risultato scorrendo la lista dei db importati

su - postgres

vacuumdb --all --analyze-only
psql
\l

controlliamo e Impostiamo l’avvio automatico e il runlevel per il servizio postgresql 9.2

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

ora impostiamo l’avvio automatico con

chkconfig --level 35 postgresql-9.2 on

Altri post e risorse:

postgresql abilitare accesso remoto linux ubuntu centos debian

Osservo che ritorna con una certa frequenza la domanda come abilitare l’accesso remoto in postgresql versioni 8.x e 9.x. La questione degli accesso remoto è basilare in quanto attiene anche al livello della sicurezza. In via generale l’accesso remoto in postgres è controllato dai due file postgresql.conf e pg_hba.conf.
La posizione di questi file varia a seconda delle distribuzioni ad esempio in centos/redahat fedora i due files sono posizionati nella data_directory con i postgresql in debian /ubuntu

/var/lib/pgsql/data

In debian/ubuntu si trovano si trovano in etc il percorso è del tipo
/etc/postgresql/num-versione/main

postgresql.conf    Il file postgresql tra i vari parametri che controlla Gli indirizzi ip “ascoltati” da postgresql sono rappresentati con listen_address.
Il valore  di default di listen_adress è “localhost” ed è indicato con

listen_address = 'localhost"

Questo significa che l’accesso a postgresql è consentito di default solo all’elaboratore che ha in esecuzione il server sql. Per consentire l’accesso da pc remoto a postgresql bisogna inanzitutto cambiare il valore “localhost” ad esempio è possibile utilizzare “*”per consentire l’accesso a tutti.

listen_address = '*"

E possibile indicare anche un lista di indirizzi o domini.

Una volta consentito l’ascolto su tutti gli indirizzi si utilizza il file pg_hba.conf per indicare a postgresql quali users possono accedere a quali databases e come possono farlo.

# TYPE  DATABSE     USER       CIDR-ADDRESS           METHOD

# local for domain socket only 
local   all         postgres                          ident
# IPv4 connections  
# TYPE  DATABSE     USER       CIDR-ADDRESS           METHOD

# local for domain socket only 
local   all         postgres                          ident
# IPv4 connections  
host    all         postgres                          md5 
host    all         all         127.0.0.1/32          md5 
host    all         all         192.168.0.0/24        md5
# IPv6 
host    all         all         ::1/128               md5

Due esemplificazioni  l’utente postgres su local può accedere a tutti i database senza specificare  password (metodo ident)  .
Tutti gli users degli hosts della sottorete 192.168.0.0/24 possono accedere a tutti i databases in postgresql ( a patto ovviamente che il database annoveri tra i suoi utenti l’user che accede )  con metodo di cifratura md5 e pertanto con password.

Attenzione: ricordare che postgresql analizzando il file  pg_hba quando trova la prima riga che soddisfa le condizioni della connessione non processa le righe successive.

Ovviamente sono possibili impostazioni più sofisticate di quelle dell’esempio anche considerati i diversi metodi di autenticazioni e le casistiche che si possono presentare. E’ tuttavia buona norma limitare l’accesso al database alla sola sottorete locale. Del resto è sempre possibilie creare un tunnel ssh per accedere così da agire come  si operasse in locale.

Controllare le connessioni

PostgreSQL ascolta le connessioni utilizzando il sockets unix ( in genere in /tmp ).
Pertanto si può effettuare il controllo con:

netstat -nxl | grep PGSQL

ad esempio in Ubuntu

$ netstat -nxl | grep PGSQL
unix 2 [ ACC ] STREAM LISTENING 10109 /var/run/postgresql/.s.PGSQL.5432

Altre risorse:

Downgrade postgresql 9.1.4 to postgresql 8.4.12 – Centos 5

aggiornamento 2012 maggio

postgresql  vai a:  PostgreSQL nel blog 

downgrade di postgresql dalla versione 9.1.4 alla 8.1.12 su server Centos 5 e necessario disporre di un backup ( dumpall ) del server postgresql.

mkdir /home/pgbck
chown postgres:postgres /home/pgbck
su - postgres
pg_dumpall > /home/pgbck/dumpalldb.sql
per avere solo le impostazioni globals 
pg_dumpall --globals-only > /home/pgbck/globals.sql

si può usare questo script per il backup dei singoli db

passiamo a root con su –

fermiamo il server con

service postgresql-9.1 stop

rinominiamo a scopo precauzionale il cluster della versione  9.1

mv /var/lib/pgsql/9.1 /var/lib/pgsql/9.1.old

rimuoviamo la versione 9.1

yum remove postgresql*

Adesso editiamo il file /etc/yum.repos.d/CentOS-Base.repo e commentiamo la riga  exclude=postgresql* che era stata inserita nella sezione upgrades per consentire installazione della versione postgresql 9.1

# exclude=postgresql*’

possiamo anche disinstallare il repo di postgres91 con

rpm -e  pgdg-centos92-9.1 ……

quindi svuotare e ripulire yum  con

yum clean all

quindi installiamo la versione postgresql84 per centos 5

yum install postgresql postgresql-server postgresql-contrib

inizializziamo  il server postgresql 8.4

service postgresql initdb

avviamo il server postgresql con

/etc/init.d/postgresql start

ripristinare la base dati del server sql caricare i dati dal file dumpall di backup creato

su – postgres

cd /home/pgbck

psql < dumpalldb.sql

per accedere alla console interattiva
psql

per l’elenco dei database caricati

\l

Altri post:

plpgsql Postgres FOR LOOP END LOOP

Esempio di funzione plpgsql che utilizza
cicli  FOR LOOP END LOOP 
e IF FOUND THEN END IF 

CREATE OR REPLACE FUNCTION __getcatalog_ID0(integer)
RETURNS SETOF catalog AS
$BODY$
DECLARE
_mytupla1 record;
_mytupla2 record;
_myIDC int;
BEGIN
SELECT * INTO _mytupla1 FROM catalog 
    WHERE "IDCatalogFather" = $1 ;
IF FOUND THEN
     -- visualizziamo primo livello distina
     -- ed estraiamo secondo livello distinta
     FOR _mytupla1 IN SELECT * FROM catalog
         WHERE ("IDCatalogFather") = $1
     LOOP
         return next _mytupla1;
         -- estraiamo secondo livello
        _myIDC =  _mytupla1."IDCatalog" ;
        SELECT  * INTO _mytupla2
        FROM catalog  WHERE "IDCatalogFather" = _myIDC ;
        IF FOUND THEN
             return next _mytupla2;
        END IF ;
     END LOOP;
 ELSE
    _myIDC = _mytupla."IDCatalog" ;
    FOR _mytupla in select * from catalog
        WHERE ("IDCatalog") = $1 ;
    LOOP
        return next _mytupla;
    END LOOP;
 END IF;
END ;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER

Risorse:

Plpgsql 

Postgresql 

Creare eliminare database e tabelle

Postgresql plpgsql function example IF … THEN … ELSE … END IF

Postgres Plpgsql function.

Use of :
– DECLARE
– more SELECT queries.
– FOUND to find value for use in other query.
– more IF ELSE END IF ;
– Loop return next “record” End Loop ;
– date_part(‘year’,”Data”)
– LIKE % % ;

-- Function: _getnews_y(character varying, integer)
-- DROP FUNCTION _getnews_y(character varying, integer);

CREATE OR REPLACE FUNCTION _getnews_y(character varying, integer)
  RETURNS SETOF "Newslist" AS
$BODY$

DECLARE
    _record     RECORD ;    
    _year       ALIAS FOR $2;
    _category   ALIAS FOR $1;
    _yeartemp   integer ; 
BEGIN

    IF _category = 'news' THEN     
        -- ritorna tutte le news     
	for _record in 
	SELECT * from "Newslist" 
	WHERE "AgeFlag" = 'New' AND date_part('year',"Data") = _yeartemp 

	ORDER BY "Data" DESC loop	
		return next _record;
	end loop;
	return;
    ELSE
        -- ritorna le news che hanno uno specifico tag = category   
	for _record in 
	SELECT * from "Newslist" 

	WHERE "AgeFlag" = 'New' AND date_part('year',"Data") = _yeartemp
        AND category LIKE '%' || _category || '%'

	ORDER BY "Data" DESC 
        LOOP	
		return next _record;
	END LOOP;
	return;
    END IF ;

END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
  COST 100
  ROWS 1000;
ALTER FUNCTION _getnews_y(character varying, integer) 
OWNER TO postgres;

Risorse:

Plpgsql 

Postgresql 

Postgresql cancella record duplicati

Creare eliminare database e tabelle

Downgrade postgresql 8.4 to postgresql 8.1 – Centos 5

postgresql  vai a:  PostgreSQL nel blog 

downgrade di postgresql dalla versione 8.4 alla 8.1 su server Centos 5 e necessario disporre di un backup ( dumpall ) del server postgresql

mkdir /home/pgbck
chown postgres:postgres /home/pgbck
su - postgres
pg_dumpall > /home/pgbck/dumpalldb.sql
per avere solo le impostazioni globals 
pg_dumpall --globals-only > /home/pgbck/globals.sql

si può usare questo script per il backup dei singoli db

passiamo a root con su –

fermiamo il server con

service postgresql stop

rimuoviamo la versione 8.4

yum remove postgresql*

Adesso editiamo il file /etc/yum.repos.d/CentOS-Base.repo e commentiamo le righe exclude=postgresql* che erano state inserite per poter avere disponibile la versione postgresql 8.4

# exclude=postgresql*’

quindi installiamo la versione standard di postgresql per centos 5 che è una versione minor di 8.1

yum install postgresql postgresql-server postgresql-contrib

avviamo il server postgresql con

/etc/init.d/postgresql start

l’avvio sarà preceduto dall’inizializzazione

ripristinare la base dati del server sql caricare i dati dal file dumpall di backup creato

su – postgres
cd /home/pgbck
psql < dumpalldb .sql

per accedere alla console interattiva
psql

per l’elenco dei database caricati

\l

Altri post: