Re: WAL DUDAS - Mailing list pgsql-performance

From Albe Laurenz
Subject Re: WAL DUDAS
Date
Msg-id D960CB61B694CF459DCFB4B0128514C2023A9729@exadv11.host.magwien.gv.at
Whole thread Raw
In response to WAL DUDAS  (Antonio Perez <renjin25@yahoo.com>)
List pgsql-performance
Antonio Perez wrote:
[wonders why his online backup / recovery test didn't work]

> 1.    Se crea una instancia de postgreSQL
> 
> 2.    Se crea un directorio $PGDATA/walback donde se almacenararn los wal antiguos
> 
> 3.    Se exporta una variable $PGDATA2 que es la ubicacion del respaldo del contenido de $PGDATA
> 
> 4.    Se activa el wal
> 
> 5.    Se crea una BD y una tabla
> 
> 6.    En psql  se ejecuta pg_start_backup('etiqueta');
> 
> 7.    Se realiza una copia de todo lo que esta en $PGDATA hacia otro directorio ($PGDATA2)
> 
> 8.    En psql  se ejecuta pg_stop_backup();
> 
> 9.    Se actualiza el valor de un registro en la tabla que se creo
> 
> 10.    Se baja la instancia
> 
> 11.    Se copia todo el contenido de $PGDATA/pg_xlog y $PGDATA/walback en $PGDATA2/pg_xlog y $PGDATA2/walback
> 
> 12.    Se inicia la instancia con pg_ctl -D $PGDATA2 --log $PGDATA2/log.log start
> 
> 13.    Se ejecuta psql
> 
> 14.    Se consulta la tabla y no existen registro
> 
>     Si alguien sabe el porque pasa esto me avisan. Gracias

First, you are supposed to use English on this list.

What you did with your copy of the cluster files is a crash recovery, basically
the same thing that will take place if you kill -9 the postmaster and restart it.

This is not the correct way to restore, left alone to recover the database.

There are step-by-step instructions at
http://www.postgresql.org/docs/current/static/continuous-archiving.html#BACKUP-PITR-RECOVERY

The important step you missed is step number 7 in which you create a recovery.conf
file that tells the server where it should look for archived WAL files, how to restore
them and until what point in time it should recover.

Yours,
Laurenz Albe

pgsql-performance by date:

Previous
From: Daniele Varrazzo
Date:
Subject: Re: An "obvious" index not being used
Next
From: Howard Cole
Date:
Subject: Re: Tsearch2 Initial Search Speed