installare Postgresql 9.6 su Centos 7.4 64 bit

  go to:  PostgreSQL nel blog 

Default postgresql server in Centos 7 x86 64 is version 9.2.x

to install postgresql 9.6.x use   Postgresl yum repository.

https://www.postgresql.org/download/linux/redhat/

su -

Download and install the yum rpm PostgreSQL Repository

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

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

yum list postgresql*

you have to see many postgresql96 packages

Install, inizialize and start postgresql 9.6.

install the client

yum install postgresql96

Install th server postgresql 9.6 with contrib package.

yum install postgresql96-server postgresql94-contrib

Than we initialize and start postgresql-9.6

#/usr/pgsql-9.6/bin/postgresql96-setup initdb
Initializing database ... OK

# systemctl enable postgresql-9.6
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-9.6.service to /usr/lib/systemd/system/postgresql-9.6.service.

# systemctl start postgresql-9.6

postgresql-9.6 /bin /lib /doc /share are in directory

/usr/pgsql-9.6

postgresql-9.6 data are in directory

/var/lib/pgsql/9.6

check that everything is OK

su - postgres  
pslq -l

we have to see a list of 3 databases

Firewall

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

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

servicectl  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 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.6

echo ‘PGPORT=5433’ >> /etc/sysconfig/pgsql/postgresql-9.6

echo ‘export PGPORT’ >> /etc/sysconfig/pgsql/postgresql-9.6

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

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

netstat -nlp | grep PGSQL
unix 2 [ ACC ] STREAM LISTENING 263035 /var/run/postgresql/.s.PGSQL.5432
unix 2 [ ACC ] STREAM LISTENING 263037 /tmp/.s.PGSQL.5432

and

# netstat -nlp | grep 5432

netstat -nlp | grep 5432
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 28083/postmaster 
tcp6 0 0 :::5432 :::* LISTEN 28083/postmaster 
unix 2 [ ACC ] STREAM LISTENING 263035 28083/postmaster /var/run/postgresql/.s.PGSQL.5432
unix 2 [ ACC ] STREAM LISTENING 263037 28083/postmaster /tmp/.s.PGSQL.5432

Postgres blog’s post:

• PostgreSQL nel blog
• PostgreSQL Comandi Utili
• Script per il backup dei database di postgresql
• Postgresql running in parallel postgresql 9.1 and 8.4 on Centos 6.3 64 bit
• 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
• Upgrade postgresql 8.1 to postgresql 8.4 – Centos 5

Annunci

installare Postgresql 9.4 su Centos 6.7 64 bit

  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:

• PostgreSQL nel blog
• PostgreSQL Comandi Utili
• Script per il backup dei database di postgresql
• Postgresql running in parallel postgresql 9.1 and 8.4 on Centos 6.3 64 bit
• 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
• Upgrade postgresql 8.1 to postgresql 8.4 – Centos 5

Cambiare l’owner di tutte le funzioni in un database postgresql

Talvolta può presentarsi la necessità di modificare l’owner degli oggetti presenti in uno schema del database postresql.
Di seguito un metodo per cambiare l’owner superuser postgres delle funzioni presenti in uno schema di un database postgresql utilizzando la console psql e il salvataggio  su file esterno .

Per elencare le funzioni presenti nello schema public si può utilizzare la query

SELECT p.proname, n.nspname,
       pg_catalog.pg_get_function_identity_arguments(p.oid) args
 FROM pg_catalog.pg_namespace n
 JOIN pg_catalog.pg_proc p
 ON p.pronamespace = n.oid
 WHERE n.nspname = 'public'

Posizionarsi in una directory in cui lo user postgres ha il permesso di scrittura oppure creare una directory ad hoc.

$ mkdir /home/postgres 
$ sudo su 
$ chown postgres:postgres
$ cd /home/postgres
$ su postgres

Avviare la console di postgresql e selezionare il database con lo funzioni il cui owner va modificato

$ psql
selezionare il database con le tabella da cambiare 
postgres=# \c eth2010

Per generare i comandi per la modifica dell’owner delle tabelle si può usare la query:

eth2010=# select 'alter function '||n.nspname||'.'||p.proname||'('||pg_get_function_identity_arguments(p.oid)||') owner to NUOVO_USER ;' from pg_proc p join pg_namespace n ON p.pronamespace = n.oid where n.nspname = 'public';

Il risultato della query sara del tipo

?column? 
 
 
-----------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------
 alter function public.chkpass_in(cstring) owner to NUOVO_USER;
 alter function public.chkpass_out(chkpass) owner to NUOVO_USER;
 alter function public._getsingleproductid(integer) owner to NUOVO_USER;
 alter function public._getmenu() owner to NUOVO_USER;
 alter function public.__getsetoflinks(integer, integer) owner to NUOVO_USER;
 alter function public._bblock_global(character varying) owner to NUOVO_USER;
 alter function public._catalog() owner to NUOVO_USER;
 alter function public._deletemenu(integer) owner to NUOVO_USER;
...
...
...
(217 rows)

salviamo la query nel file esterno alterfunctions_own ridirigendo l’output da terminale con

eth2010=# \o alterfunctions_own

rieseguire la query sostituendo NUOVO_USER con lo user desiderato.
Il risultato verrà salvato sul file esterno alterfunctions_own che conterrà a questo punto  un elenco di comandi alter function 

eth2010=# select 'alter function '||n.nspname||'.'||p.proname||'('||pg_get_function_identity_arguments(p.oid)||') owner to NUOVO_USER ;' from pg_proc p join pg_namespace n ON p.pronamespace = n.oid where n.nspname = 'public';

Per eseguire la lista di comandi alter function importare il file con

eth2010=# \i alterfunctions_own

psql:alterfx1_own.sql:3: ERROR: syntax error at or near "?"
LINE 1: ?column? ...
 ^
psql:alterfx1_own.sql:637: ERROR: syntax error at or near "317"
LINE 1: (217 rows)

Il file generato contiene in testa e in coda caratteri che non rappresentano comandi validi e che generano alcuni sintax error tuttavia  in genere non vale la pena di pulire il file.

Modificare l’owner delle funzioni di un database con una query plpgsql

La query cambia l’owner di tutte le funzioni del database in cui viene eseguita. Consente di precisare lo schema su cui agire in genere public  definito da:

• in_schema = primo parametro
• new_owner = secondo parametro

-- FUNCTION: public.__chown_functions(character varying, character varying)

-- DROP FUNCTION public.__chown_functions(character varying, character varying);

CREATE OR REPLACE FUNCTION public.__chown_functions(
	in_schema character varying,
	new_owner character varying)
    RETURNS void
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    -- ROWS 0
AS $BODY$

DECLARE
  object_types VARCHAR[];
  object_classes VARCHAR[];
  object_type record;
 
  r record;
BEGIN


  FOR r IN 
    SELECT  p.proname, n.nspname,
       pg_catalog.pg_get_function_identity_arguments(p.oid) args
    FROM    pg_catalog.pg_namespace n
    JOIN    pg_catalog.pg_proc p
    ON      p.pronamespace = n.oid
    WHERE   n.nspname = in_schema
  LOOP
    raise notice 'Changing ownership of function %.%(%) to %', 
                 r.nspname, r.proname, r.args, new_owner;
    EXECUTE 
       'alter function ' || r.nspname || '."' || r.proname ||
       '"(' || r.args || ') owner to ' || new_owner;
  END LOOP;


END;

$BODY$;

ALTER FUNCTION public.__chown_functions(character varying, character varying)
    OWNER TO postgres;

 

Risorse:

Plpgsql 

• 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 

• 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 show tables, views, index, sequences ;
• Postresql varie ;
• pg_class to check if table exists ;
• Postgresql Comandi utili ;
• Postgresql reset sequence ;

SQL  – PostgreSQL

• SQL Joincast su ;
• SQL Cast ;
• SQL Union Query ;
• SQL Count ;
• SQL Delete ;
• Postgresql : SQL UPDATE query ;
• postgresql : SQL SELECT query ;
• postgresql : Eseguire una SQL UPDATE query di test ;
• PostgreSQL Total, Subtotal an alternative ;

• PostgreSQL | maurizio siagri ;
• MySQL | maurizio siagri  ;

• PostgreSQL – novice – Check if table exists | Threaded View
• PostgreSQL blog resourses

Postgresql cancella record duplicati

Creare eliminare database e tabelle

Upgrade postgresql 8.4.13 to postrgesl 9.2 centos 5

  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 nel blog
• PostgreSQL Comandi Utili
• Postgresql running in parallel postgresql 9.1 and 8.4 on Centos 6.3 64 bit
• 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
• Upgrade postgresql 8.1 to postgresql 8.4 – Centos 5

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:

• Postgresql server 
• PostgreSQL Comandi Utili
• PostgreSQL nel blog 

Downgrade postgresql 9.1.4 to postgresql 8.4.12 – Centos 5

aggiornamento 2012 maggio

  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:

• Postgresql running in parallel postgresql 9.1 and 8.4 on Centos 6.3 64 bit
• 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
• Upgrade postgresql 8.1 to postgresql 8.4 – Centos 5

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 

• 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 

• 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 show tables, views, index, sequences ;
• Postresql varie ;
• pg_class to check if table exists ;
• Postgresql Comandi utili ;
• Postgresql reset sequence ;

SQL  – PostgreSQL

• SQL Joincast su ;
• SQL Cast ;
• SQL Union Query ;
• SQL Count ;
• SQL Delete ;
• Postgresql : SQL UPDATE query ;
• postgresql : SQL SELECT query ;
• postgresql : Eseguire una SQL UPDATE query di test ;
• PostgreSQL Total, Subtotal an alternative ;

• PostgreSQL | maurizio siagri ;
• MySQL | maurizio siagri  ;

• PostgreSQL – novice – Check if table exists | Threaded View
• PostgreSQL blog resourses

Creare eliminare database e tabelle