====== PostgreSQL i PHPPgAdmin ======

===== PostgreSQL =====

Servidor de base de dades relacionals.

<WRAP important>
La informació d'aquesta pàgina s'ha d'actualitzar
</WRAP>

Actualment el servidor disposa de dos servidors de bases de dades postgres (versió 7.4 i 8.1).

==== Instal·lació i configuració ====

El comportament de PostgreSQL al nostre sistema es pot controlar amb tres fitxers de configuració:
   * pg_hba.conf: Aquest fitxer s'utilitza per definir els diferents tipus d'accessos que té un usuari (com, on i des de quin lloc).
   * pg_ident.conf: Aquest fitxer s'utilitza per definir la informació necessària en cas que utilitzem un accés del tipus ident a //pg_hba.conf//.
   * postgresql.conf: En aquest fitxer podem canviar tots els paràmetres de configuració que afecten al funcionament i comportament de PostgreSQL a la nostra màquina.

=== pg_hba.conf ===

<file>
  # Database administrative login by UNIX sockets
  # local    DATABASE    USER                         METHOD     [OPTION]
    local    all         postgres                     trust      # ident sameuser
  
  # TYPE     DATABASE    USER        CIDR-ADDRESS     METHOD     [OPTION]
    local    all         all                          trust      # ident sameuser
    host     all         all         127.0.0.1/32     md5
    host     all         all         ::1/128          md5
</file>

=== postgres.conf ===

Les directives principals ha modificar són:
<file>
  #---------------------------------------------------------------------------
  # CONNECTIONS AND AUTHENTICATION
  #---------------------------------------------------------------------------
  port = 5432
  max_connections = 100
  ssl = true
</file>

En el cas de dos servidors postgres, com es el nostre cas, s'ha de canvia el número de port d'un d'ells.

==== Enllaços ====

  * [[http://www.postgresql.org/ | Web oficial de PostgreSQL]]
  * [[http://www.postgresql.org/docs/manuals/ | Manuals de PostgreSQL]]
    * [[http://wiki.postgresql.org/wiki/Detailed_installation_guides | Detailed installation guides]]
  * [[http://www.commandprompt.com/ppbook/ | Practical PostgreSQL,]] by John Worsley and Joshua Drake, Command Prompt, Inc. Copyright © 2001-2002 by Commandprompt, Inc





===== PHPpgadmin =====

És una interficie web en PHP per gestionar les bases de dades PostgreSQL. És accessible a,
  * [[http://cie-58-175.uab.cat | phppgadmin]]

==== Instal·lació i configuració ====

El primer de tot ens hem d'assegurar que el nostre servidor soporta [[..:marta:php | PHP]]. 

Per començar istalem el paquet ''phppgadmin'' al nostres servidor.

Per tindre accés a l'aplicació, es crea un enllaç simbolic a la carpeta del servidor que apunti a la carpeta on es troba el programa.

Ara toca editar la nostra configuració que es troba al fitxer /etc/phppgadmin/config.inc.php i ha de quedar amb el següent contigut:
<file>
  <?php
  // ------------------------Servidor PG 7.4-----------------------------------------
    $conf['servers'][0]['desc'] = 'PostgreSQL 7.4';
    $conf['servers'][0]['host'] = 'localhost';
    $conf['servers'][0]['port'] = 5432;
    $conf['servers'][0]['defaultdb'] = 'template1';
    $conf['servers'][0]['pg_dump_path'] = '/usr/bin/pg_dump';
    $conf['servers'][0]['pg_dumpall_path'] = '/usr/bin/pg_dumpall';
    $conf['servers'][0]['slony_support'] = false;
    $conf['servers'][0]['slony_sql'] = '/usr/share/postgresql';
    
  // ------------------------Servidor PG 8.1-----------------------------------------
    $conf['servers'][1]['desc'] = 'PostgreSQL 8.1';
    $conf['servers'][1]['host'] = 'localhost';
    $conf['servers'][1]['port'] = 5433;
    $conf['servers'][1]['defaultdb'] = 'template1';
    $conf['servers'][1]['pg_dump_path'] = '/usr/bin/pg_dump';
    $conf['servers'][1]['pg_dumpall_path'] = '/usr/bin/pg_dumpall';
    $conf['servers'][1]['slony_support'] = false;
    $conf['servers'][1]['slony_sql'] = '/usr/share/postgresql';
    
  // -----------------------General configuration------------------------------------
    $conf['default_lang'] = 'auto';
    $conf['extra_login_security'] = true;
    $conf['owned_only'] = false;
    $conf['show_comments'] = true;
    $conf['show_advanced'] = false;
    $conf['show_system'] = false;
    $conf['show_reports'] = true;
    $conf['owned_reports_only'] = true;
    $conf['min_password_length'] = 1;
    $conf['left_width'] = 200;
    $conf['theme'] = 'default';
    $conf['show_oids'] = false;
    $conf['max_rows'] = 30;
    $conf['max_chars'] = 50;
    $conf['use_xhtml'] = false;
    
    /* ----------------Don't modify anything below this line-------------------- */
    $conf['version'] = 15;
  ?>
</file>

En el nostre cas, hi ha dos servidors als quals podem accedir des de l'aplicació. S'ha d'anar en compte amb l'índex del servidor i el número de por que hem assignat a la configuració del servidor.

Si volem que qualsevol usuari que te permisos per utilitzar una base de dades la pugui veure des de l'aplicació, hem de vigilar que el paràmetre ''owned_only'' sigui <color blue>fals</color>.

També cal incloure a les directives a la configuració de l'apache (apache.conf): ''Allow from localhost''

==== Enllaços ====

[[http://www.phppgadmin.org/ | web oficial de PHPpgadmin ]]

===== Altres eines per PostgreSQL =====

  * [[http://www.pgadmin.org/ | pgAdmin]] is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world. pgAdmin is designed to answer the needs of all users, from writing simple SQL queries to developing complex databases. The graphical interface supports all PostgreSQL features and makes administration easy. The application also includes a syntax highlighting SQL editor, a server-side code editor, an SQL/batch/shell job scheduling agent, support for the Slony-I replication engine and much more. Server connection may be made using TCP/IP or Unix Domain Sockets (on *nix platforms), and may be SSL encrypted for security. No additional drivers are required to communicate with the database server.
  * [[http://www.dbvis.com/ | DbVisualizer DbVisualizer]] is the universal database tool for developers and database administrators. It is the perfect solution since the same tool can be used on all major operating systems accessing a wide range of databases. DbVisualizer is the universal database tool for developers and database administrators. It is the perfect solution since the same tool can be used on all major operating systems accessing a wide range of databases.
  * [[http://www.teampostgresql.com/ | TeamPostgreSQL]] allows database administrators to manage their PostgreSQL databases from anywhere.
  * [[http://www.navicat.com/ | Navicat]] for PostgreSQL is a powerful Database administration and development tool for PostgreSQL. It works with any PostgreSQL Database Server from version 7.3 or above, and supports most of the latest PostgreSQL features including Trigger, Function, View, and Manage User, etc. Features in Navicat are sophisticated enough to provide professional developers for all their specific needs, yet easy to learn for users who are new to PostgreSQL.
  * [[http://www.sqlmanager.net/ | EMS SQL Management Studio]] is a complete solution for database administration and development. SQL Studio unites all must-have components that focus on the most critical database management tasks in one powerful and easy-to-use environment that can work round the clock. Whether you are a database or application developer, DBA or business analyst, EMS SQL Management Studio offers all essential tools to make you more productive than ever before!