Re: pg_restore

On Wed, Mar 30, 2011 at 3:56 PM, Mike Orr <sluggoster@gmail.com> wrote:
> I'm converting a MySQL webapp to PostgreSQL. I have a backup server
> which is refreshed twice daily with mysqldump/mysql and has a
> continuously-running copy of the webapp. I want to replicate this with
> pg_dump/pg_restore.  Ideally I'd like to restore just a few tables,
> without stopping the webapp and closing/reopening its connections. Is
> this possible with pg_restore?
>
> 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.
>
> The tables to be updated do have 1:many relationships. How would I
> restore all of them at once? List them all as '-t' args to the same
> pg_restore command?

pg_dump and pg_restore can do it.  pg_dump -Fc is just like regular
dump but you can pull out specific tables by name.  or you can just
take are regular backup and just pg_dump out specific tables with the
proper options set in a small script.

because pg is transactional, you can do this any time of the day
without blocking  users (although there may be a small performance
hit).

hs/sr is also something to look at.

merlin