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?
--
Mike Orr <sluggoster@gmail.com>