From: | pedro noticioso <cucnews(at)yahoo(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: WAL files backup |
Date: | 2007-02-18 13:36:52 |
Message-ID: | 525241.48113.qm@web55403.mail.re4.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Thanks to all who have repplied so far
Ok so I go back and retry the complete procedure to
try to pin point what might be the problem.
1)
POSTGRES NEW INSTALL
2)
CREATE TESTING DATABASE
# createdb Test
3)
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)
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)
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)
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)
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)
CREATE recovery.conf
name = '5'
restore_command = 'cp
/home/postgres/respaldos/oficial/%f %p'
recovery_target_timeline = 'latest'
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
*****************
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
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
--- Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> pedro noticioso wrote:
> > Muchas gracia a todos los queme han respondido
> hasta
> > ahora
> > Thanks to all who have repplied so far
>
> Pedro, I don't think you're doing anyone (nor
> yourself) any favors by
> writing both in spanish and english in this list.
> This list is english
> only. If you want to write in spanish, please use
> the pgsql-es-ayuda
> list and I'll be happy to help you there. If you
> want to stick to this
> list, please use english only. I find it hard to
> follow what you're
> doing in the format you're using.
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom
> Development, 24x7 support
>
____________________________________________________________________________________
The fish are biting.
Get more visitors on your site using Yahoo! Search Marketing.
http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php
From | Date | Subject | |
---|---|---|---|
Next Message | Chad Wagner | 2007-02-18 14:57:11 | Re: WAL files backup |
Previous Message | Charley Tiggs | 2007-02-18 12:59:37 | Re: How to analyse the indexes in postgres? |