Administración y manual de PostgreSQL en Linux

28/03/12

Administracin y manual de PostgreSQL en Linux

Administracin y manual de PostgreSQL en Linux Instalación de PostgreSQL en CentOS  Conexión a un servidor PostgreSQL  Permitir Perm itir conexi c onexiones ones remota remotas s a Post greSQL Modificar la contraseña del usuario postgres de PostgreSQL  Comprobar la versión de PostgreSQL instalada  Backup y restore de bases de datos en PostgreSQL  Activar el modo de compatibilidad con versiones anteriores de PostgreSQL  Instalación de PostgreSQL en un servidor con Plesk  Conexión a PostgreSQL desde PHP  Actualizar el formato/esquema de las bases de datos PostgreSQL

Instalacin de PostgreSQL en CentOS Para utilizar el servidor de bases de datos PostgreSQL en CentOS es necesario instalar los paquetes postgresql y postgresql-server: postgresql-server :

 ia ge-ee ge

El paquete postgresql-server contiene los binarios del servidor DBMS, y postgresql las ut ilidades ilidades cliente para conectarnos a un servidor, así como documentación en formato HTML y las páginas "man". El servicio no queda activado en el arranque, así que lo activaremos con la utilidad chkconfig: chkconfig:

# chcfig ge  # /ec/ii.d/ge a Iiciad a bae de da:

[

OK

]

Iiciad eici ge:

[

OK

]

Conexin a un servidor PostgreSQL Para conectarnos al servidor PostgreSQL nos cambiamos al usuario postgres y utilizamos el CLI psql, psql, ejemplo:

wiki.woop.es/PostgreSQL

1/10

28/03/12


#  - ge $  -d eae1 -U ge Wece   8.1.11, he PgeSQL ieacie eia.

Te:

\cigh f diibi e \h f he ih SQL cad \? f he ih  cad \g  eiae ih eic  eece e \  i

eae1=#

Permitir conexiones remotas a PostgreSQL Por seguridad, tras una instalación por defecto de PostgreSQL en CentOS, este aceptará únicamente conexiones locales en el puerto 5432/tcp:

# ea -a  ge LISTEN c

0

0 127.0.0.1:5432

0.0.0.0:*

LISTEN

15112/

Para modificar este comportamiento tenemos que editar el fichero /var/lib/pgsql/data/pg_hba.conf , que c ontiene la c onfiguración para la autenticac ión de c lientes y añadir el listado de las redes y/o IPs desde las que nos vamos a conectar:

# i /a/ib/g/daa/g_hba.cf h a a 192.168.0.0/24  h a a 10.10.0.1/32 

Además, tenemos que editar el fichero /var/lib/pgsql/data/postgresql.conf y modificar el parámetro listen_addresses para indicar que escuc he en las interfac es nec esarias, en est e c aso lo habilitaremos para t odas:

# i /a/ib/g/daa/ge.cf ie_addee='*' wiki.woop.es/PostgreSQL

2/10

28/03/12


El último paso es reiniciar el servicio y comprobar que los cambios se han aplicado correctamente:

# /ec/ii.d/ge ea Paad e eici

ge:

Iiciad eici ge:

[

OK

]

[

OK

]

# ea -a  ge LISTEN c

0

0 0.0.0.0:5432

0.0.0.0:*

LISTEN

15608/

En este ejemplo, permitimos el acceso a la red 192.168.0.0/24 y a la IP 10.10.0.1, quedando el servicio postmaster escuchando en todas las interfaces . Para mas información consultar el capítulo Client Authentication de la guía de Administración de Post greSQL.

Modificar la contraseña del usuario postgres de PostgreSQL Para cambiar la contraseña de un usuario de PostgreSQL tenemos que utilizar la sentencia SQL ALTER USER usuario WITH PASSWORD:

#  - ge $  -d eae1 -U ge

eae1=# ALTER USER ge WITH PASSWORD 'aQ6RRhFb'; ALTER ROLE

Comprobar la versin de PostgreSQL instalada La forma mas cómoda es ejecutando la sentencia SQL SELECT VERSION(), ejemplo:

eae1=# SELECT VERSION();

-------------------------------------------------------------------------------------------------PgeSQL 8.1.11  86_64-edha-i-g, cied b GCC gcc (GCC) 4.1.2 20070626 (Red Ha 4 wiki.woop.es/PostgreSQL

3/10

28/03/12


(1 fia)

Backup y restore de bases de datos en PostgreSQL En Post greSQL tenemos el c omando pg_dump para realizar copias de seguridad de BBDD, su uso es muy similar al mysqldump de MySQL. En este ejemplo obtenemos un listado de todas las BBDD existentes y realizamos el backup de una de ellas:

$ LANG=e_US  - Li f daabae Nae



Oe

 Ecdig

--------------+----------+---------ge

 ge  UTF8

eae0

 ge  UTF8

eae1

 ge  UTF8

_eba  ge  UTF8 (4 )

$ g_d _eba > //_eba.g

El resultado es un fichero ASCII con todas las sentencias SQL necesarias para restaurar la BBDD. Para restaurar una BBDD desde un fichero utilizaremos el comando:

$  -d _eba -f //_eba.g SET SET SET COMMENT REVOKE REVOKE GRANT GRANT

Para hacer un backup de todas las BBDD podemos utilizar el comando pg_dumpall o realizar un wiki.woop.es/PostgreSQL

4/10

28/03/12


pequeño script en BASH, ejemplo:

#!/bi/bah DIR=/a/ib/g/bac LANG=e_US LIST=$( -  a ' i $1'  ge -E '^-^Li^Naeeae[01]^\(') f db i $LIST d g_d $db  gi -c >

$DIR/$db.g

de

Activar el modo de compatibilidad con versiones anteriores de PostgreSQL Para habilitar la compatibilidad con versiones anteriores de PostgreSQL tenemos que editar el fichero /var/lib/pgsql/data/postgresql.conf y añadir las siguientes variables (mas información en el capítulo Version and Platform Compatibility ):

add_iig_f =  aa_ =  bacah_e = afe_ecdig defa_ih_id =  ecae_ig_aig =  adad_cfig_ig = ff ege_fa = adaced _iheiace = 

add_missing_from: When on, tables that are referenced by a query will be automatically added to the FROM clause if not already present. This behavior does not comply with the SQL standard and many people dislike it because it can mask mistakes (such as referencing a table where you should have referenced its alias). The default is off. This variable can be enabled for compatibility with releases of PostgreSQL prior to 8.1, where this behavior was allowed by default. array_nulls: This controls whether the array input parser recognizes unquoted NULL as specifying a null array element. By default, this is on, allowing array values containing null values to be entered. However, PostgreSQL versions before 8.2 did not support null values in arrays, and therefore would treat NULL as specifying a normal array element with the string value "NULL". For backwards compatibility with applications that require the old behavior, this variable can be turned off. wiki.woop.es/PostgreSQL

5/10

28/03/12


backslash_quote: This controls whether a quote mark can be represented by \' in a string literal. The preferred, SQL-standard way to represent a quote mark is by doubling it ('') but PostgreSQL has historically also accepted \'. However, use of \' creates security risks because in some c lient c harac ter set encodings, there are multibyte c haracters in which the last byte is numerically equivalent to ASCII \. If client-side code does escaping incorrectly then a SQLinjection attack is possible. This risk can be prevented by making the server reject queries in which a quote mark appears to be escaped by a backslash. The allowed values of backslash_quote are on (allow \' always), off (reject always), and safe_encoding (allow only if client encoding does not allow ASCII \ within a multibyte character). safe_encoding is the default setting. default_with_oids: This controls whether CREATE TABLE and CREATE TABLE AS include an OID column in newly-created tables, if neither WITH OIDS nor WITHOUT OIDS is specified. It also determines whether OIDs will be included in tables created by SELECT INTO. In PostgreSQL 8.1 default_with_oids is off by default; in prior versions of PostgreSQL, it was on by default. escape_string_warning: When on, a warning is issued if a backslash (\) appears in an ordinary string literal ('...' syntax) and standard_conforming_strings is off. The default is on. Applications that wish to use backslash as escape should be modified to use escape string syntax (E'...'), because the default behavior of ordinary strings will change in a future release for SQL compatibility. This variable can be enabled to help detect applications that will break. standard_conforming_strings : This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard. The default is currently off, causing PostgreSQL to have its historical behavior of t reating backslashes as escape c haracters. T he default will change to on in a future release to improve compatibility with the standard. Applications can check this parameter to determine how string literals will be processed. The presence of t his parameter c an also be taken as an indication that the esc ape string syntax (E'...') is supported. Escape string syntax should be used if an application desires backslashes to be treated as escape characters. regex_flavor: The regular expression "flavor" can be set to advanced, extended, or basic. The default is advanced. The extended setting might be useful for exact backwards compatibility with pre-7.4 releases of PostgreSQL. sql_inheritance: This controls the inheritance semantics. If turned off, subtables are not included by various commands by default; basically an implied ONLY key word. This was added for compatibility with releases prior to 7.1.

Podemos comprobar que el cambio de dichas variables de configuración se ha realizado correctamente con la siguiente consulta:

#  - ge wiki.woop.es/PostgreSQL

6/10

28/03/12


-bah-3.2$  -d eae1 -U ge eae1=# SELECT ae, eig FROM g_eig ;

Si al añadir y/o modificar c ualquier variable Post greSQL no arranca t endremos que revisar el fichero de log /var/lib/pgsql/pgstartup.log para enc ontrar c ualquier warning/error del t ipo:

FATAL:

ecgied cfigai aaee "aa_"

FATAL:

aaee "adad_cfi g_ig" ca be chaged

Instalacin de PostgreSQL en un servidor con Plesk NOTA: Para poder gestionar bases de datos PostgreSQL desde Plesk se necesita comprar el addon "Power Pack"!! Una vez comprado el addon, el panel de control Plesk permite la gestión de bases de datos PostgreSQL. Podemos instalar PostgreSQL en Plesk de dos formas: 1) desde el interfaz web (Home -> Updates -> PostgreSQL) y 2) desde una shell con el "autoinstaller":

# aiae --eec-eeae-ce --ia-ce ge

El siguiente paso es activarlo en el inicio del servidor, arrancar el servicio y asignar una contraseña al usuario administrador "postgres":

# chcfig ge  # /ec/ii.d/ge a #  - ge $  -d eae1 -U ge eae1=# ALTER USER ge WITH PASSWORD 'ad'; ALTER ROLE

Depués tenemos que entrar al interfaz web (Home -> Database Servers) y configurar el usuario administrador "postgres" con su contraseña, a partir de aquí podremos gestionar PostgreSQL desde el wiki.woop.es/PostgreSQL

7/10

28/03/12


Plesk. Además, se instala la herramienta de adminstración web phpPgAdmin para los clientes junto con una utilidad muy básica pg_manage que permite parar, arrancar e reinciar el servicio PostgreSQL.

Conexin a PostgreSQL desde PHP Para conectarnos a PostgreSQL desde PHP necesitamos instalar el paquete php-pgsql, que propociona las extensiones pdo_pgsql.so y pgsql.so:

#  ia h-g # /ec/ii.d/hd ea

En una instalación de PostgreSQL sobre CentOS, el método de autenticación configurado por defecto es "ident" (servicio que no está habilitado por defecto), por lo que al conectarnos desde PHP obtendremos el siguiente error:

LOG: FATAL:

cd  cec  Ide ee a adde "127.0.0.1",  113: Ceci efed Ide aheicai faied f e "ece"

La solución pasa por editar el fichero /var/lib/pgsql/data/pg_hba.conf y cambiar el método de autenticación:

# i /a/ib/g/daa/g_hba.cf (..) #h

a

a

127.0.0.1/32

ide aee

h

a

a

127.0.0.1/32

d5

Actualizar el formato/esquema de las bases de datos PostgreSQL Cuando ac tualizamos y/o c ambiamos de versión un servidor PostgreSQL tenemos que ac tualizar t ambién el formato/esquema de las bases de datos. En Post greSQL no existe una herramienta similar al mysql_upgrade de MySQL que nos automatizaría la tarea, por lo que tenemos que realizar una serie de pasos: wiki.woop.es/PostgreSQL

8/10

28/03/12


1) Backup 2) Borrar/mover el contenido de $PGDATA y volver a inicializar con initdb el nuevo formato de las bases de datos PostgreSQL 3) Restore

# /ec/ii.d/ge  #  - ge $ g_d _eba > //_eba.g $  -f $PGDATA $ iidb $  -d _eba -f //_eba.g # /ec/ii.d/ge a

Si tras actualizar una instacia de PostgreSQL no actualizamos el formato de las bases de datos al arrancar nos encontraríamos con este error:

A d ei f he daabae fa a fd. Y eed  gade he daa fa befe ig PgeSQL. See //hae/dc/ge-8.4.7/README.-di f e ifai.

Tip! Si utilizas RHEL/CentOS puedes utilizar el repositorio PowerStack para ejecutar la última versión estable de PostgreSQL.

Referencias sobre PostgreSQL Comandos básicos de administración PostgreSQL, es una traducc ión al cast ellano de 15 practical Post greSQL database Administration c ommands. Replicación y alta disponibilidad de PostgreSQL con pgpool-II y Heartbeat . Powered by Woop!


9/10

28/03/12



10/10

Administración y manual de PostgreSQL en Linux

Recommend Documents