Re: WAL files backup

Muchas gracia a todos los queme han respondido hasta
ahora
Thanks to all who have repplied so far

Ok entonces regreso y reinicio al procedimiento
completo para tratar de atinarle a lo que puede ser el
problema.
Ok so I go back and retry the complete procedure to
try to pin point what might be the problem.

1)
Postgres recien instalado - POSTGRES NEW INSTALL

2)
Creando db de Pruebas - - CREATE TESTING DATABASE
# createdb Test

3)
populamos Test - INSERT A LOT OF DATA INTO 'Test'
# insert into users values (1 ,'name','800122','2
someones name',3,1);
# insert into users values (2 ,'name','800122','2
someones name',3,1);
...cincuenta mil veces con su id unico - FIVE THOUSAND
TIMES WITH UNIQUE ID'S

4)
copiamos wal y data otro lado - COPY WAL AND DATA
FILES SOMEWHERE ELSE

# psql Test
# SELECT pg_start_backup('1');
# \q

# cd /home/postgres/backup
# tar zcf 070215-data-01.tgz /usr/local/pgsql/data
# tar zcf 070215-oficial-01.tgz oficial

# psql Test
# SELECT pg_stop_backup();
# \q

5)
populamos Test - INSERT A LOT OF DATA INTO 'Test'
# insert into users values (5001 ,'name','800122','2
someones name',3,1);
# insert into users values (5002 ,'name','800122','2
someones name',3,1);
...cincuenta mil veces con su id unico - FIVE THOUSAND
TIMES WITH UNIQUE ID'S

6)
copiamos wal y data otro lado - COPY WAL AND DATA
FILES SOMEWHERE ELSE

# psql Test
# SELECT pg_start_backup('2');
# \q

# cd /home/postgres/backup
# tar zcf 070215-data-02.tgz /usr/local/pgsql/data
# tar zcf 070215-oficial-02.tgz oficial

# psql Test
# SELECT pg_stop_backup();
# \q

7)
Recuperar al respaldo 1 y los archivos WAL del
respaldo 2 - RECOVER DATA BACKUP 1 AND WAL FILES 2

# cd /home/postgres/backup
# rm -rf /usr/local/pgsql/data/pg_xlog/*
# rm -rf oficial

# psql Test
# SELECT pg_start_backup('2');
# \q

# cd /home/postgres/backup
# tar zxf 070215-data-01.tgz
# tar zxf 070215-oficial-02.tgz

# psql Test
# SELECT pg_stop_backup();
# \q
# ls -la oficial
drwxr-sr-x 2 postgres staff 4096 2007-02-16 10:51 .
drwxr-sr-x 4 postgres staff 4096 2007-02-16 10:47 ..

-rw------- 1 postgres staff 234 2007-02-16 10:12
000000010000000000000003
-rw------- 1 postgres staff 234 2007-02-16 10:12
000000010000000000000004
-rw------- 1 postgres staff 234 2007-02-16 10:12
000000010000000000000005.0024FD70.backup

# rm -rf 000000010000000000000003
# rm -rf 000000010000000000000004

8)
crear al recovery.conf - CREATE recovery.conf

name = '5'
restore_command = 'cp
/home/postgres/respaldos/oficial/%f %p'
recovery_target_timeline = 'latest'

asi que inicio PG y me marca errores - SO I START PG
AND GET THESE ERRORS

LOG: archived transaction log file
"000000010000000000000003"
LOG: archived transaction log file
"000000010000000000000004"
ERROR: relation "foo" does not exist
ERROR: syntax error at or near "4" at character 1
ERROR: duplicate key violates unique constraint
"users_pkey"
ERROR: duplicate key violates unique constraint
"users_pkey"
ERROR: duplicate key violates unique constraint
"users_pkey"
ERROR: duplicate key violates unique constraint
"users_pkey"
ERROR: duplicate key violates unique constraint
"users_pkey"
ERROR: syntax error at or near "pwd" at character 1
FATAL: role "root" does not exist
LOG: database system was interrupted at 2007-02-16
10:10:27 CST
LOG: could not open file
"pg_xlog/000000010000000000000005" (log file 0,
segment 5): No such file or directory
LOG: invalid checkpoint record
PANIC: could not locate required checkpoint record
HINT: If you are not restoring from a backup, try
removing the file
"/usr/local/pgsql/data/backup_label".
LOG: startup process (PID 14858) was terminated by
signal 6
LOG: aborting startup due to startup process failure
LOG: database system was interrupted at 2007-02-16
10:10:27 CST
LOG: could not read from log file 0, segment 5 at
offset 2416640: No such file or directory
LOG: invalid checkpoint record

*****************
NOTA: ok asi que le entiendo, falta un archivo asi que
ahi va
NOTE: ok so I get it, there is a missing file so here
it goes
# cp oficial/000000010000000000000005.0024FD70.backup
/usr/local/pgsql/data/pg_xlog/000000010000000000000005
NOTA: ubicacion en el evento en el log no es muy
exacto pero te da la idea de que ya no hay queja sobre
el hecho de que falte un archivo WAL de pg_xlog
NOTE: placement in the event in the log is not quite
aqccurate but you get the idea that there is no
complain about the WAL file missing from pg_xlog any
more
*****************

PANIC: could not locate required checkpoint record
HINT: If you are not restoring from a backup, try
removing the file
"/usr/local/pgsql/data/backup_label".
LOG: startup process (PID 14872) was terminated by
signal 6
LOG: aborting startup due to startup process failure
LOG: database system was interrupted at 2007-02-16
10:10:27 CST
LOG: could not read from log file 0, segment 5 at
offset 2416640: No such file or directory
LOG: invalid checkpoint record
PANIC: could not locate required checkpoint record
HINT: If you are not restoring from a backup, try
removing the file
"/usr/local/pgsql/data/backup_label".
LOG: startup process (PID 14881) was terminated by
signal 6
LOG: aborting startup due to startup process failure
1

--- Chad Wagner <chad(dot)wagner(at)gmail(dot)com> wrote:

> On 2/16/07, Andy Shellam (Mailing Lists) <
> andy(dot)shellam-lists(at)mailnetwork(dot)co(dot)uk> wrote:
> >
> >
> > Why bother trying to delete WAL files older than
> the .backup file? When
> > PostgreSQL is in recovery mode it knows which WAL
> files are necessary to
> > perform the recovery.
> >
> > Also, the documentation recommends excluding the
> pg_xlog directory when
> > performing the base backup. Likely when it comes
> time to recovery the
> > online WAL files have been archived already, so it
> is a risk of confusion I
> > am sure.
> >
> >
> > If the OP is doing the same as myself, the WAL
> files are being archived
> > outside of pg_xlog (indeed outside of the PG data
> cluster) - it makes no
> > sense keeping around WAL files older than the
> .backup file because they're
> > not needed - in a day I generate ~5GB worth of WAL
> files which aren't needed
> > after the full backup runs at 2am, so it's a waste
> of resources to keep them
> > around or to worry about backing them up after
> this time.
> >
>
>
> I mentioned this in my other response, the only
> reason I ask is because of
> the potential need to recover from an earlier base
> backup up to a specific
> point -- perhaps before the most recent base backup.
> Personally I don't
> think it is a good idea for every environment to do
> this, but I can see the
> value in it and if you truly feel it is unnecessary
> then I can certainly
> respect that. Perhaps I am a bit too paranoid :),
> but I have seen people
> foolishly do a huge cleanup and then take a backup
> and realize "Oh crap, I
> did too much!" -- and if you don't have all the WAL
> files you certainly
> can't guarantee a specific PITR.
>
>
> --
> Chad
> http://www.postgresqlforums.com/
>

____________________________________________________________________________________
8:00? 8:25? 8:40? Find a flick in no time
with the Yahoo! Search movie showtime shortcut.
http://tools.search.yahoo.com/shortcuts/#news