On Fri, Apr 1, 2011 at 2:39 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: > 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). I'm tentatively going with a psql script: """ \set quiet 1 \timing off BEGIN; TRUNCATE incidents, entries, locator, events; \i /tmp/sync-pg/hotline.sql \i /tmp/sync-pg/locator.sql \i /tmp/sync-pg/events.sql COMMIT; ANALYZE; """ And a shell script that can do the saving, transfer, and loading in discrete parts. (So I can have the same script on both hosts, and do the parts individually for testing or together for production): """ usage: daily-pg [-hdtlk] Synchronize Postgres data to the backup server. Options: -d: Dump the data to /tmp/sync-pg -t HOSTNAME: Rsync the data to the specified host -l: Load the data from /tmp/sync-pg -k: Keep the dump directory after loading (otherwise delete it) -h: Print this help message and exit If no options, do nothing. """ Dumping also compresses the files, and loading uncompresses them if they're compressed. -- Mike Orr <sluggoster@xxxxxxxxx> -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
