Thread: Restoring DB using Continuous Archive Backup

No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.1/1345 - Release Date: 3/26/2008 6:50 PM

nick wrote:

Hi everyone,

 

I just got PGSQL up and running yesterday. One of the features that intrigue me is the WAL. I am running tests to perform DB restores using WAL. The last attempt ended in disaster and a reinstall.

 

This may sound retarded. I can’t seem to get a clear picture from the manual. I quote (Chapter 24.3.3 Step 4):

 

Restore the database files from your backup dump. Be careful that they are restored with the right

ownership (the database system user, not root!) and with the right permissions. If you are using

tablespaces, you should verify that the symbolic links in pg_tblspc/ were correctly restored.

 

I need to know which kind of backup (as there are multiple suggested in the manual) would that (underlined) be referring to?

 

File system level backup

Is there a way to detach/attach databases (like ms-sql) and so perform restoration on only one database instead of bringing down the service?

 

As far as i think  the answer is NO

Is there a method to re-run WAL after restoring an old backup without resorting to a service stop-start?

Not clear need more info.

 

Thanks in advance,

 

Nick

 

 

No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.1/1345 - Release Date: 3/26/2008 6:50 PM

===================================================================

sms START NETCORE to 575758 to get updates on Netcore's enterprise
products and services

sms START MYTODAY to 09845398453 for more information on our mobile
consumer services or go to http://www.mytodaysms.com

===================================================================

===================================================================

sms START NETCORE to 575758 to get updates on Netcore's enterprise
products and services

sms START MYTODAY to 09845398453 for more information on our mobile
consumer services or go to http://www.mytodaysms.com

===================================================================

No virus found in this incoming message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.1/1345 - Release Date: 3/26/2008 6:50 PM

No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.1/1345 - Release Date: 3/26/2008 6:50 PM

On Mar 28, 2008, at 12:15 AM, nick wrote:

> Thanks for the reply, ashish.
>
> Sorry, should’ve been more specific.
>
> By running WAL, I mean reprocessing the WALs up to a certain point
> of time.
>
> For example:
>
> A backup is made at 0000 hours. Something happens. Restore has to be
> made at 2000.
>
> Since the backup is at 0000, 20 hours’ worth of transactions will be
> stored in WAL. To get the data up to the 2000 the WALs have to be
> reprocessed. I realize the only way for this to work is by data file
> duplication, so this mean the service has to be stopped anyway to
> allow the files to be restored.
>
> But there may be a requirement to not take the system offline so I’m
> looking for a method to do the restore and WAL reprocessing without
> stopping then starting the service like described in the manual.
>
> It would be of great help if it’s possible.
>
> Thanks in advance,
>
> Nick
>


You need to put postgres in recovery mode to restore from WAL logs; at
this time, there isn't any way to have any write or read-only access
to the service while a restore is in progress. For 20 hours' worth of
data, depending on your hardware, you're probably looking at 1-3 hours
of restore time before you're caught up.

I don't believe there's a way to have db offline and others available
for usage since that will change the timeline.

HTH,

-salman

nick wrote:

Thanks for the reply, ashish.

 

Sorry, should’ve been more specific.

 

By running WAL, I mean reprocessing the WALs up to a certain point of time.

 

For example:

 

A backup is made at 0000 hours. Something happens. Restore has to be made at 2000.

 

Since the backup is at 0000, 20 hours’ worth of transactions will be stored in WAL. To get the data up to the 2000 the WALs have to be reprocessed. I realize the only way for this to work is by data file duplication, so this mean the service has to be stopped anyway to allow the files to be restored.

 

Yes , Service has to be stopped and started to restore from the WAL and also make sure that no one connects to the database during this phase by ensuring proper pg_hba.conf.

But there may be a requirement to not take the system offline so I’m looking for a method to do the restore and WAL reprocessing without stopping then starting the service like described in the manual.

 

When something is "already happened" to database then what is it worth to connect to that database before bringing it to proper place.

Correct me if I am mis understanding you.

Other way around i think is create another database cluster and restore the database there instead on your current cluster. But I doubt on this.

It would be of great help if it’s possible.

 

With Regards
Ashish

Thanks in advance,

 

Nick

 

---

From: ashish [mailto:ashish.karalkar@netcore.co.in]
Sent: Thursday, March 27, 2008 7:40 PM
To: nick
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Restoring DB using Continuous Archive Backup

 

nick wrote:

Hi everyone,

 

I just got PGSQL up and running yesterday. One of the features that intrigue me is the WAL. I am running tests to perform DB restores using WAL. The last attempt ended in disaster and a reinstall.

 

This may sound retarded. I can’t seem to get a clear picture from the manual. I quote (Chapter 24.3.3 Step 4):

 

Restore the database files from your backup dump. Be careful that they are restored with the right

ownership (the database system user, not root!) and with the right permissions. If you are using

tablespaces, you should verify that the symbolic links in pg_tblspc/ were correctly restored.

 

I need to know which kind of backup (as there are multiple suggested in the manual) would that (underlined) be referring to?

 

File system level backup

Is there a way to detach/attach databases (like ms-sql) and so perform restoration on only one database instead of bringing down the service?

 

As far as i think  the answer is NO

Is there a method to re-run WAL after restoring an old backup without resorting to a service stop-start?

Not clear need more info.

 

Thanks in advance,

 

Nick

 

 

 

No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.1/1345 - Release Date: 3/26/2008 6:50 PM

 

===================================================================

sms START NETCORE to 575758 to get updates on Netcore's enterprise
products and services

sms START MYTODAY to 09845398453 for more information on our mobile
consumer services or go to http://www.mytodaysms.com

=================================================================== 

No virus found in this incoming message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.1/1345 - Release Date: 3/26/2008 6:50 PM

No virus found in this outgoing message.
Checked by AVG.
Version: 7.5.519 / Virus Database: 269.22.1/1345 - Release Date: 3/26/2008 6:50 PM

===================================================================

sms START NETCORE to 575758 to get updates on Netcore's enterprise
products and services

sms START MYTODAY to 09845398453 for more information on our mobile
consumer services or go to http://www.mytodaysms.com

===================================================================