PostgreSQL

Via apt-get o synaptic el trobarem. Instal·lem el paquet PostgreSql i els paquets que necessitem, per exemple php5-pgsql

Quan s'instal.la PostgreSQL es crea un usuari postgres (usuari en el sistema linux i de la base de dades). Aquest usuari ens permetrà administrar/configurar PostgreSQL.

usuari@maquina$ sudo su postgres
postgres@maquina#

Ara ja podem crear un usuari amb la seva clau d'accés (password) i una base de dades:

postgres@maquina# createuser --pwprompt NOM_DE_USUARI
postgres@maquina# createdb NOM_BASE_DE_DADES -E UTF8 -O NOM_DE_USUARI

Ja tenim creada una base de dades i un usuari amb la seva clau d'accés. Sortirem de l'usuari postgres amb Ctlr+D o escrivint la comanda exit al terminal.

Mirarem que el fitxer de configuració /etc/postgresql/8.3/main/postgresql.conf (segons versió serà 8.1, 8.2 o 8.3) no es trobi comentada la línia:

port = 5432

Si només acceptem connexions des del localhost:

listen_addresses = 'localhost'

Però si acceptem connexions de tot arreu:

listen_addresses = '*'

O d'alguna IP en concret (podem posar una llista de IPs separades per comes):

listen_addresses = 'localhost,80.35.151.105'            # what IP address(es) to listen on;

i a més a més en el fitxer /etc/postgresql/8.3/main/pg_hba.conf cal tenir una o més línies com aquestes depenent si volem només connexions localhost, de la xarxa local o també d'algunes màquines de l'exterior:

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
host    all         all         192.168.0.0 255.255.255.0 md5
host    all         all         62.57.200.0 255.255.248.0 md5

Per arrancar, aturar o rearrancar el servidor PostgreSQL ho podem fer amb les comandes (segons versió serà 8.1, 8.2 o 8.3):

usuari@maquina$ sudo /etc/init.d/postgresql-8.3 start
usuari@maquina$ sudo /etc/init.d/postgresql-8.3 stop
usuari@maquina$ sudo /etc/init.d/postgresql-8.3 restart

sudo su postgres
createuser --pwprompt nom_usuari

sudo su postgres
dropuser nom_usuari

$ sudo su postgres
$ psql
# select * from pg_shadow;

sudo su postgres
createdb nom_base_de_dades -O nom_usuari

o

$ psql
postgres=# CREATE DATABASE nom_base_de_dades OWNER nom_usuari;

sudo su postgres
dropdb nom_base_de_dades

ALTER DATABASE dbname RENAME TO dbnamenew;

pg_dump dbname > filename

Si volem només una taula:

pg_dump dbname -t res_country > res_country.sql

Es recupera amb:

psql dbname < filename

pg_dump -Fc dbname > filename

Es recupera amb:

pg_restore -d dbname filename

Cal indicar l'opció -i si la versió de pg_dump no coincideix amb la versió del servidor.

export PGPASSWORD=%s
dropdb -U %s %s_copia
createdb -U %s -T %s %s_copia -O %s
pg_dump %s -U %s > %s/backup/backup_%s.sql
export PGPASSWORD=

Per saber quins usuaris estan usant les diferents b.d.:

select * from pg_stat_activity;

psql base_dades usuari_postgres --command='select name from res_users;' > fitxer.txt

ALTER TABLE public.res_country ADD COLUMN status boolean;
ALTER TABLE distributors DROP COLUMN address RESTRICT;

Més opcions: ALTER TABLE

UPDATE account_invoice SET number = replace(number, '/', '-') where type='out_invoice';

• Manera “senzilla”

select magento_sku,count(*) from product_product group by magento_sku;

• Manera “complexa”

select o.id from res_partner o where exists ( select 'x' from res_partner i where i.vat = o.vat and i.id < o.id);

select o.id from product_product o where exists ( select 'x' from product_product i where i.magento_sku = o.magento_sku and i.id < o.id);

--
-- select duplicate rows
--
select o.id, o.a, o.b from test o
 where exists ( select 'x' 
                  from test i
                 where i.a = o.a
                   and i.b = o.b
                   and i.id < o.id
             );
--
-- delete duplicate rows
--
-- Note: PostgreSQL dosn't support aliases on
--       the table mentioned in the from clause
--       of a delete.
--
 
delete from test 
 where exists ( select 'x' 
                  from test i
                 where i.a = test.a
                   and i.b = test.b
                   and i.id < test.id
             );

Per exemple, per omplir el camp training_offer_invoicing_id de les factures que les relaciona amb els training_offer_invoicing, quan training_offer_invoicing té un camp invoice_id que apunta a les factures:

update account_invoice i set training_offer_invoicing_id = (select id from training_offer_invoicing t where t.invoice_id = i.id);

# Crea el nou usuari mydbuser_ro:

sudo su postgres
createuser --pwprompt mydbuser_ro

# Dóna permisos d'usar l'esquema públic a l'usuari només lectura des del client psql:

psql
GRANT USAGE ON SCHEMA public TO mydbuser_ro;

# Dóna permisos de select (consultar) a totes les taules i a totes les vistes de la base de dades mydb des de la línia de comandes:

psql -U postgres -qAt -c "SELECT 'GRANT SELECT ON ' || tablename || ' TO \"mydbuser_ro\";' FROM pg_tables WHERE schemaname = 'public'" mydb | psql -U postgres mydb
psql -U postgres -qAt -c "SELECT 'GRANT SELECT ON ' || viewname || ' TO \"mydbuser_ro\";' FROM pg_views WHERE schemaname = 'public'" mydb | psql -U postgres mydb

SELECT table_name
  FROM information_schema.tables
  WHERE table_type = 'BASE TABLE'
  AND table_schema NOT IN ('pg_catalog', 'information_schema')
  ORDER BY table_name ASC;

Al resultat de l'apartat anterior executar per cada taula SELECT count(*) FROM nom_taula;

O podem obtenir el nombre de registres aproximat executant:

SELECT
  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r'
ORDER BY reltuples DESC;   

Administració de bases de dades Postgres mitjançant l'aplicació web PhpPgAdmin (Nota: També es poden administrar còmodament amb l'aplicació d'escriptori PgAdminIII).

apt-get install phppgadmin

Creem un link simbòlic o un alias a l'apache per poder executar el phppgadmin amb Apache.

ln -s /usr/share/phppgadmin /var/www/

http://localhost/phppgadmin.

És normal que tinguem encara problemes per registrar-nos. Si es així, editeu el fitxer /etc/phppgadmin/config.inc.php i mirem si es troba a “false” la línia:

$conf['extra_login_security'] = false;