Re: pg_restore

On Wed, Mar 30, 2011 at 2:36 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:
> On 03/30/11 1:56 PM, Mike Orr wrote:
>>
>> MySQL simply locks the tables, drops/recreates them, loads the data,
>> and unlocks the tables. Other connections have to wait but they don't
>> have to be closed/reopened. The PostgreSQL manual recommends restoring
>> into an empty database using template0, which would require first
>> closing the other connections and then dropping the database. It would
>> also take unnecessary time to recreate the database and tables that
>> aren't changing. So I'm wondering if there's a less obtrusive way to
>> refresh the data.
>
> its a backup server, right?   so noone is accessing it, are they?

Somebody may be accessing it. I could take the site down for the
duration, but it would be easier not to.

> rather than using pg_dump -Fc |pg_restore, you can use pg_dump | psql ...
> and you can tell pg_dump in this mode to only dump specified tables.

That might be a better solution. I was hoping to use the same pgdump
file for this that I also use for routine offline backup, but maybe
this is such a special case that a separate dump file would be better.

> however, you might look at PITR and/or WAL log shipping rather than
> dump/restore.  this would only update new data, and when you playback the
> WAL log on the backup server bring it up to whatever point in time you want.

I looked at PITR and WAL, but it looks like I can't have the backup
database running and answering queries while it's WAL'ing. I'd have to
log in and switch it to normal mode and start the webapp, and that's
what I may not be able to do if the backup server were needed.

The backup server exists in case there's something like an earthquake
at the main data center. Something that could cause a regional
communication blackout and prevent the sysadmins from accessing the
backup server remotely. The site is a data-sharing tool for emergency
responders across the country. They may be in another region working
on an unrelated incident, and need the website. So the backup site has
to be already running and loaded with data -- we can't depend on a
sysadmin being able to log in remotely to turn it on.

--
Mike Orr <sluggoster(at)gmail(dot)com>