Archivio
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
Default port value for postgresql is 5432
# 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
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 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
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
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/pgbckpsql < 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:
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 ;
SQL – PostgreSQL
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:
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 ;
SQL – PostgreSQL
Downgrade postgresql 8.4 to postgresql 8.1 – Centos 5
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:
