pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
32-bit to 64-bit migration options 3+ messages / 3 participants [nested] [flat]
* 32-bit to 64-bit migration options @ 2012-02-10 14:45 Greg Spiegelberg <gspiegelberg@gmail.com> 2012-02-10 15:14 ` Re: 32-bit to 64-bit migration options Guillaume Lelarge <guillaume@lelarge.info> 2012-02-10 15:15 ` Re: 32-bit to 64-bit migration options Kevin Grittner <Kevin.Grittner@wicourts.gov> 0 siblings, 2 replies; 3+ messages in thread From: Greg Spiegelberg @ 2012-02-10 14:45 UTC (permalink / raw) To: pgsql-admin All, I'm planning a migration for a customer with a PostgreSQL 8.4 database cluster running CentOS 4.8 32-bit. The target platform is CentOS 6.2 64-bit and will be running PostgreSQL 8.4 (our application delivers and supports 8.4, don't bother bringing up 9.x). If this were a small database cluster I wouldn't worry about it however the 8.4 database cluster is about 900 GB right now. The documented and proper way to move this data is via a dump-restore however I'm not sure my customer wants days or potentially weeks of downtime so I'm searching for options. Option 1: dump-restore I've performed a handful of these for other customers and even the 100 GB database cluster using the network transfer method "pg_dumpall | ssh target -c 'cat - | psql postgres'" can be slow as in 8+ hours. Option 2: Slony-I Is Slony-I an alternative when moving data from 32-bit to 64-bit? Option 3: pg_upgrade Is this an option? Remember, I'm going from 8.4 32-bit to 8.4 64-bit. Option 4: PITR I believe this is not a possibility because of the bit-ness change but I'm listing anyways in case I'm mistaken. Did I miss anything? TIA, Greg ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 32-bit to 64-bit migration options 2012-02-10 14:45 32-bit to 64-bit migration options Greg Spiegelberg <gspiegelberg@gmail.com> @ 2012-02-10 15:14 ` Guillaume Lelarge <guillaume@lelarge.info> 1 sibling, 0 replies; 3+ messages in thread From: Guillaume Lelarge @ 2012-02-10 15:14 UTC (permalink / raw) To: Greg Spiegelberg <gspiegelberg@gmail.com>; +Cc: pgsql-admin On Fri, 2012-02-10 at 07:45 -0700, Greg Spiegelberg wrote: > All, > > I'm planning a migration for a customer with a PostgreSQL 8.4 database > cluster running CentOS 4.8 32-bit. The target platform is CentOS 6.2 > 64-bit and will be running PostgreSQL 8.4 (our application delivers and > supports 8.4, don't bother bringing up 9.x). If this were a small database > cluster I wouldn't worry about it however the 8.4 database cluster is about > 900 GB right now. The documented and proper way to move this data is via a > dump-restore however I'm not sure my customer wants days or potentially > weeks of downtime so I'm searching for options. > > Option 1: dump-restore > I've performed a handful of these for other customers and even the 100 GB > database cluster using the network transfer method "pg_dumpall | ssh target > -c 'cat - | psql postgres'" can be slow as in 8+ hours. > Right. > Option 2: Slony-I > Is Slony-I an alternative when moving data from 32-bit to 64-bit? > Yes, absolutely. > Option 3: pg_upgrade > Is this an option? Remember, I'm going from 8.4 32-bit to 8.4 64-bit. > No because of the 32/64 bit change. > Option 4: PITR > I believe this is not a possibility because of the bit-ness change but I'm > listing anyways in case I'm mistaken. > No because of the 32/64 bit change. > Did I miss anything? > Well, you can also use Londiste, and probably Bucardo to do this. But I guess they are your only options with Slony. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 32-bit to 64-bit migration options 2012-02-10 14:45 32-bit to 64-bit migration options Greg Spiegelberg <gspiegelberg@gmail.com> @ 2012-02-10 15:15 ` Kevin Grittner <Kevin.Grittner@wicourts.gov> 1 sibling, 0 replies; 3+ messages in thread From: Kevin Grittner @ 2012-02-10 15:15 UTC (permalink / raw) To: Greg Spiegelberg <gspiegelberg@gmail.com>; +Cc: pgsql-admin Greg Spiegelberg <gspiegelberg@gmail.com> wrote: > I'm planning a migration for a customer with a PostgreSQL 8.4 > database cluster running CentOS 4.8 32-bit. The target platform > is CentOS 6.2 64-bit and will be running PostgreSQL 8.4 (our > application delivers and supports 8.4, don't bother bringing up > 9.x). Just out of curiosity, have you found an incompatibility that prevents your application from running on 9.0 or 9.1, or haven't you gotten around to trying it yet? If there is an incompatibility, what is it? (It's useful to the project to have some idea what causes pain to users, so we can minimize that when possible.) > If this were a small database cluster I wouldn't worry about it > however the 8.4 database cluster is about 900 GB right now. The > documented and proper way to move this data is via a dump-restore > however I'm not sure my customer wants days or potentially weeks > of downtime so I'm searching for options. > > Option 1: dump-restore > I've performed a handful of these for other customers and even the > 100 GB database cluster using the network transfer method > "pg_dumpall | ssh target -c 'cat - | psql postgres'" can be slow > as in 8+ hours. This is the simplest option. If you have control over the hardware environment I would recommend *not* using ssh. A few other tips: We found it to make a noticeable difference when we used a cross-wired cable for a direct connection rather than going through a switch. We found that setting up the users and databases first and doing pg_dump per database with the psql -1 option (to use a single transaction) to make a big difference. Something like: pg_dump -h host dbname | psql -1 dbname We tested conversions with different configuration files and found it ran much faster when the target had the "running with scissors" settings: fsync = off, full_page_writes = off, synchronous_commit = off, etc. Be sure to set these back afterward. You should really schedule a VACUUM FREEZE ANALYZE for sometime soon after the bulk load. When possible we do this before letting users in. When we need to minimize down time, we just run an ANALYZE on key tables first, let people in, run ANALYZE on the whole database, then run VACUUM FREEZE ANALYZE while people are working. > Option 2: Slony-I > Is Slony-I an alternative when moving data from 32-bit to 64-bit? My shop has never used it, but I've heard a lot of others talk about having done so with good results. Down time can be measured in single-digit minutes this way. > Option 3: pg_upgrade > Is this an option? Remember, I'm going from 8.4 32-bit to 8.4 > 64-bit. Not an option. The on-disk format will be different. > Option 4: PITR > I believe this is not a possibility because of the bit-ness change > but I'm listing anyways in case I'm mistaken. Not an option. The on-disk format will be different. -Kevin ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-02-10 15:15 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2012-02-10 14:45 32-bit to 64-bit migration options Greg Spiegelberg <gspiegelberg@gmail.com> 2012-02-10 15:14 ` Guillaume Lelarge <guillaume@lelarge.info> 2012-02-10 15:15 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox