pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRestore database
10+ messages / 9 participants
[nested] [flat]
* Restore database
@ 2006-07-11 12:55 Alexander Burbello <alexander@cresoltec.com.br>
0 siblings, 0 replies; 10+ messages in thread
From: Alexander Burbello @ 2006-07-11 12:55 UTC (permalink / raw)
To: pgsql-admin
I need to test and create a procedure to restore databases.
I followed the steps based on the site, but I couldn't finish succesfully.
I did:
1. Put the database on Backup Mode and copy datafiles.
/pg/bin/psql cresoldev -c "SELECT pg_start_backup('/pg/backup/');"
tar -cvf /pg/backup/bk_base.tar /pg/data/base/*
/pg/bin/psql cresoldev -c "SELECT pg_stop_backup();"
File .conf: archive_command = 'cp -i %p /pg/backup/xlog/%f </dev/null'
2. Created a new table and populated with data, to simulate the recovery:
create table test (
aa integer,
bb varchar(50)
);
insert into test values (1,'aaa');
...
insert into test values (5,'aaa');
Data inserted successfully!!!
3. Shutdown on database;
Last log transactions copied to the directory archived log;
cp /pg/data/pg_xlog/* /pg/backup/xlog/
4. Configuring the recovery.conf file:
restore_command = 'cp /pg/backup/xlog/%f %p'
recovery_target_time = '2006-07-06 16:33:52 BRT'
5. Simulate the lost directories, deleting... :
rm -r /pg/data/base/*
6. Recreating the directories exploding the tar file:
tar -xvf bkp_base... .tar
7. Starting the database for applying the log transactions.
Supposing recove the table "test" located on log transactions.
LOG: database system was shut down at 2006-07-06 16:47:18 BRT
LOG: starting archive recovery
LOG: restore_command = "cp /pg/backup/xlog/%f %p"
LOG: recovery_target_time = 2006-07-06 16:33:52-03
cp: cannot stat `/pg/backup/xlog/00000001.history': No such file or directory
LOG: restored log file "000000010000000000000001" from archive
LOG: record with zero length at 0/1122880
LOG: invalid primary checkpoint record
LOG: restored log file "000000010000000000000001" from archive
LOG: record with zero length at 0/1122844
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 3989) was terminated by signal 6
LOG: aborting startup due to startup process failure
8. There was an error and the table was lost.
What's the procedure that I doing mistakes????????
Thanks for helping me, in advance.
Regards
Alexander
^ permalink raw reply [nested|flat] 10+ messages in thread
* Restore Database
@ 2006-07-11 17:12 Burbello <burbello3000@yahoo.com.br>
2006-07-12 19:41 ` Re: [ADMIN] Restore Database Aaron Bono <postgresql@aranya.com>
0 siblings, 1 reply; 10+ messages in thread
From: Burbello @ 2006-07-11 17:12 UTC (permalink / raw)
To: Lista Postgres <pgadmin-support@postgresql.org>; +Cc: pgsql-admin
I need to test and create a procedure to restore
databases.
I followed the steps based on the site, but I couldn't
finish succesfully.
I did:
1. Put the database on Backup Mode and copy
datafiles.
/pg/bin/psql cresoldev -c "SELECT
pg_start_backup('/pg/backup/');"
tar -cvf /pg/backup/bk_base.tar /pg/data/base/*
/pg/bin/psql cresoldev -c "SELECT
pg_stop_backup();"
File .conf: archive_command = 'cp -i %p
/pg/backup/xlog/%f </dev/null'
2. Created a new table and populated with data, to
simulate the recovery:
create table test (
aa integer,
bb varchar(50)
);
insert into test values (1,'aaa');
...
insert into test values (5,'aaa');
Data inserted successfully!!!
3. Shutdown on database;
Last log transactions copied to the directory
archived log;
cp /pg/data/pg_xlog/* /pg/backup/xlog/
4. Configuring the recovery.conf file:
restore_command = 'cp /pg/backup/xlog/%f %p'
recovery_target_time = '2006-07-06 16:33:52 BRT'
5. Simulate the lost directories, deleting... :
rm -r /pg/data/base/*
6. Recreating the directories exploding the tar file:
tar -xvf bkp_base... .tar
7. Starting the database for applying the log
transactions.
Supposing recove the table "test" located on log
transactions.
LOG: database system was shut down at 2006-07-06
16:47:18 BRT
LOG: starting archive recovery
LOG: restore_command = "cp /pg/backup/xlog/%f %p"
LOG: recovery_target_time = 2006-07-06
16:33:52-03
cp: cannot stat
`/pg/backup/xlog/00000001.history': No such file or
directory
LOG: restored log file "000000010000000000000001"
from archive
LOG: record with zero length at 0/1122880
LOG: invalid primary checkpoint record
LOG: restored log file "000000010000000000000001"
from archive
LOG: record with zero length at 0/1122844
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 3989) was terminated by
signal 6
LOG: aborting startup due to startup process
failure
8. There was an error and the table was lost.
What's the procedure that I doing mistakes????????
Thanks for helping me, in advance.
Regards
Alexander
_______________________________________________________
Abra sua conta no Yahoo! Mail: 1GB de espaço, alertas de e-mail no celular e anti-spam realmente eficaz.
http://mail.yahoo.com.br/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [ADMIN] Restore Database
2006-07-11 17:12 Restore Database Burbello <burbello3000@yahoo.com.br>
@ 2006-07-12 19:41 ` Aaron Bono <postgresql@aranya.com>
2006-07-12 20:04 ` Re: [ADMIN] Restore Database Alexander Burbello <burbello3000@yahoo.com.br>
0 siblings, 1 reply; 10+ messages in thread
From: Aaron Bono @ 2006-07-12 19:41 UTC (permalink / raw)
To: Burbello <burbello3000@yahoo.com.br>; +Cc: Lista Postgres <pgadmin-support@postgresql.org>; pgsql-admin
On 7/11/06, Burbello <burbello3000@yahoo.com.br> wrote:
>
> I need to test and create a procedure to restore
> databases.
Why not just use pg_dump?
See http://manual.intl.indoglobal.com/ch06s07.html - it's really easy. This
is how we copy from production to testing and development and how we do
nightly backups.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [ADMIN] Restore Database
2006-07-11 17:12 Restore Database Burbello <burbello3000@yahoo.com.br>
2006-07-12 19:41 ` Re: [ADMIN] Restore Database Aaron Bono <postgresql@aranya.com>
@ 2006-07-12 20:04 ` Alexander Burbello <burbello3000@yahoo.com.br>
2006-07-13 22:45 ` Re: [ADMIN] Restore Database Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 10+ messages in thread
From: Alexander Burbello @ 2006-07-12 20:04 UTC (permalink / raw)
To: Aaron Bono <postgresql@aranya.com>; +Cc: Lista Postgres <pgadmin-support@postgresql.org>; pgsql-admin
Ok! Its a good tool, but for Production Database I think it is not
recommended.
Only using pg_dump for the second backup plain.
Suppose that you backed up at 6:00am and at 9am happened a crash on the
server.
In this case, I would lost data between that time, 3 hours of information.
For production databases, my plan is to do phisical backup including WAL.
If a crash happen, I can restore the datafiles and recover applying the
WAL logs until the last file was generated.
As Oracle does in this type of crash.
My doubt is that I am not getting apply the WAL files on recover stage.
Any other suggestion?
Thanks for your help
Aaron Bono escreveu:
> On 7/11/06, *Burbello* <burbello3000@yahoo.com.br
> <mailto:burbello3000@yahoo.com.br>> wrote:
>
> I need to test and create a procedure to restore
> databases.
>
>
> Why not just use pg_dump?
>
> See http://manual.intl.indoglobal.com/ch06s07.html
> <http://manual.intl.indoglobal.com/ch06s07.html; - it's really easy.
> This is how we copy from production to testing and development and how
> we do nightly backups.
>
>
> ==================================================================
> Aaron Bono
> Aranya Software Technologies, Inc.
> http://www.aranya.com
> ==================================================================
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [ADMIN] Restore Database
2006-07-11 17:12 Restore Database Burbello <burbello3000@yahoo.com.br>
2006-07-12 19:41 ` Re: [ADMIN] Restore Database Aaron Bono <postgresql@aranya.com>
2006-07-12 20:04 ` Re: [ADMIN] Restore Database Alexander Burbello <burbello3000@yahoo.com.br>
@ 2006-07-13 22:45 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-14 11:42 ` Re: [ADMIN] Restore Database Alexander Burbello <burbello3000@yahoo.com.br>
0 siblings, 1 reply; 10+ messages in thread
From: Scott Marlowe @ 2006-07-13 22:45 UTC (permalink / raw)
To: Alexander Burbello <burbello3000@yahoo.com.br>; +Cc: Aaron Bono <postgresql@aranya.com>; Lista Postgres <pgadmin-support@postgresql.org>; pgsql-admin
On Wed, 2006-07-12 at 15:04, Alexander Burbello wrote:
> Ok! Its a good tool, but for Production Database I think it is not
> recommended.
> Only using pg_dump for the second backup plain.
> Suppose that you backed up at 6:00am and at 9am happened a crash on the
> server.
> In this case, I would lost data between that time, 3 hours of information.
>
> For production databases, my plan is to do phisical backup including WAL.
> If a crash happen, I can restore the datafiles and recover applying the
> WAL logs until the last file was generated.
> As Oracle does in this type of crash.
>
> My doubt is that I am not getting apply the WAL files on recover stage.
>
> Any other suggestion?
You have read this section of the manual, right?
http://www.postgresql.org/docs/8.1/interactive/backup-online.html
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [ADMIN] Restore Database
2006-07-11 17:12 Restore Database Burbello <burbello3000@yahoo.com.br>
2006-07-12 19:41 ` Re: [ADMIN] Restore Database Aaron Bono <postgresql@aranya.com>
2006-07-12 20:04 ` Re: [ADMIN] Restore Database Alexander Burbello <burbello3000@yahoo.com.br>
2006-07-13 22:45 ` Re: [ADMIN] Restore Database Scott Marlowe <smarlowe@g2switchworks.com>
@ 2006-07-14 11:42 ` Alexander Burbello <burbello3000@yahoo.com.br>
0 siblings, 0 replies; 10+ messages in thread
From: Alexander Burbello @ 2006-07-14 11:42 UTC (permalink / raw)
To: Lista Postgres <pgadmin-support@postgresql.org>; pgsql-admin
Yes!
I followed exactly that page to do.
Thanks
Scott Marlowe escreveu:
>On Wed, 2006-07-12 at 15:04, Alexander Burbello wrote:
>
>
>>Ok! Its a good tool, but for Production Database I think it is not
>>recommended.
>>Only using pg_dump for the second backup plain.
>>Suppose that you backed up at 6:00am and at 9am happened a crash on the
>>server.
>>In this case, I would lost data between that time, 3 hours of information.
>>
>>For production databases, my plan is to do phisical backup including WAL.
>>If a crash happen, I can restore the datafiles and recover applying the
>>WAL logs until the last file was generated.
>>As Oracle does in this type of crash.
>>
>>My doubt is that I am not getting apply the WAL files on recover stage.
>>
>>Any other suggestion?
>>
>>
>
>You have read this section of the manual, right?
>
>http://www.postgresql.org/docs/8.1/interactive/backup-online.html
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Restore DataBase
@ 2010-06-02 03:26 ALEXANDER JOSE <aangelj@hotmail.com>
2010-06-02 06:07 ` Re: Restore DataBase Szymon Guz <mabewlun@gmail.com>
2010-06-02 07:17 ` Re: Restore DataBase Andreas Schmitz <mailinglist@longimanus.net>
2010-06-03 05:54 ` Re: Restore DataBase Eric McKeeth <eldin00@gmail.com>
0 siblings, 3 replies; 10+ messages in thread
From: ALEXANDER JOSE @ 2010-06-02 03:26 UTC (permalink / raw)
To: pgsql-admin
psql coon I'm trying to restore a database
that has 60 GB in size, where there is a table with 38 million records,
has raised 18 million records when the restore process fails on a
syntax error, the backup was made with a file. sql as the best way I can do
to restore??
Alexander Angel
Venezuela
_________________________________________________________________
Invite your mail contacts to join your friends list with Windows Live Spaces. It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us=
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Restore DataBase
2010-06-02 03:26 Restore DataBase ALEXANDER JOSE <aangelj@hotmail.com>
@ 2010-06-02 06:07 ` Szymon Guz <mabewlun@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Szymon Guz @ 2010-06-02 06:07 UTC (permalink / raw)
To: ALEXANDER JOSE <aangelj@hotmail.com>; +Cc: pgsql-admin
2010/6/2 ALEXANDER JOSE <aangelj@hotmail.com>
> psql coon I'm trying to restore a database that has 60 GB in size, where
> there is a table with 38 million records, has raised 18 million records when
> the restore process fails on a syntax error, the backup was made with a
> file. sql as the best way I can do to restore??
>
>
> Alexander Angel
> Venezuela
>
How did you make the backup?
regards
Szymon Guz
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Restore DataBase
2010-06-02 03:26 Restore DataBase ALEXANDER JOSE <aangelj@hotmail.com>
@ 2010-06-02 07:17 ` Andreas Schmitz <mailinglist@longimanus.net>
2 siblings, 0 replies; 10+ messages in thread
From: Andreas Schmitz @ 2010-06-02 07:17 UTC (permalink / raw)
To: ALEXANDER JOSE <aangelj@hotmail.com>; +Cc: pgsql-admin
hi,
sounds like a plain text backup. your problem, I guess, will be some
constraint issue. I suggest commenting out the constraints in the backup
file before running the restore.
regards
andreas
ALEXANDER JOSE wrote:
> psql coon I'm trying to restore a database that has 60 GB in size,
> where there is a table with 38 million records, has raised 18 million
> records when the restore process fails on a syntax error, the backup
> was made with a file. sql as the best way I can do to restore??
>
>
> Alexander Angel
> Venezuela
>
> ------------------------------------------------------------------------
> Invite your mail contacts to join your friends list with Windows Live
> Spaces. It's easy! Try it!
> <http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us;
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Restore DataBase
2010-06-02 03:26 Restore DataBase ALEXANDER JOSE <aangelj@hotmail.com>
@ 2010-06-03 05:54 ` Eric McKeeth <eldin00@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Eric McKeeth @ 2010-06-03 05:54 UTC (permalink / raw)
To: ALEXANDER JOSE <aangelj@hotmail.com>; +Cc: pgsql-admin
On Tue, Jun 1, 2010 at 9:26 PM, ALEXANDER JOSE <aangelj@hotmail.com> wrote:
> psql coon I'm trying to restore a database that has 60 GB in size, where
> there is a table with 38 million records, has raised 18 million records when
> the restore process fails on a syntax error, the backup was made with a
> file. sql as the best way I can do to restore??
>
>
It will be easier for people to help if you provide more info such as
postgres version, OS, how the backup was made, exact error message, etc.
But, if you're getting a syntax error, then my best guess would be that
either that you have a corrupt/incomplete backup, or that you're trying to
restore to a different version of postgres than you used to make the backup,
and the backup has syntax which would be valid for the version that made it,
but not for the version you're trying to restore to.
-Eric
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2010-06-03 05:54 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-11 12:55 Restore database Alexander Burbello <alexander@cresoltec.com.br>
2006-07-11 17:12 Restore Database Burbello <burbello3000@yahoo.com.br>
2006-07-12 19:41 ` Re: [ADMIN] Restore Database Aaron Bono <postgresql@aranya.com>
2006-07-12 20:04 ` Re: [ADMIN] Restore Database Alexander Burbello <burbello3000@yahoo.com.br>
2006-07-13 22:45 ` Re: [ADMIN] Restore Database Scott Marlowe <smarlowe@g2switchworks.com>
2006-07-14 11:42 ` Re: [ADMIN] Restore Database Alexander Burbello <burbello3000@yahoo.com.br>
2010-06-02 03:26 Restore DataBase ALEXANDER JOSE <aangelj@hotmail.com>
2010-06-02 06:07 ` Re: Restore DataBase Szymon Guz <mabewlun@gmail.com>
2010-06-02 07:17 ` Re: Restore DataBase Andreas Schmitz <mailinglist@longimanus.net>
2010-06-03 05:54 ` Re: Restore DataBase Eric McKeeth <eldin00@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox