Re: pg_restore

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?

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.

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.