On Wed, Mar 30, 2011 at 3:56 PM, Mike Orr <sluggoster@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
