Re: [GENERAL] Restoring 8.0 db to 8.1
Hello Vivek, Am 2007-12-21 15:59:21, schrieb Vivek Khera: On Dec 21, 2007, at 11:09 AM, Martijn van Oosterhout wrote: The usual answer is use slony. You can use it to replicate the 8.0 server onto an 8.1 server. This may take weeks/months/years/whatever to synchronise. When the slave is up to date, you pull the plug on the 8.0 server and get everyone to use the 8.1 server... No downtime... except he has large objects, which slony can't replicate. currently or will this be changed in the future? My PostgreSQL 8.2 is now a little bit more then 900 GByte without and 3800 GByte with indexes and restoring a dump take several days on my Opteron with 8 GByte of memory using 15000 RpM 300 GByte SCSI drives. Note: I am already using TableSpace and TablePartitioning to fight the enormeous size which is growing every day by arround 350 MB. ...and since I am not God or the absolute PostgreSQL guru (which should redesign my Database-Structure) I have a bunch of Large Objects in this pig which should be normaly on a fileserver or such! Thanks, Greetings and nice Day Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant -- Linux-User #280138 with the Linux Counter, http://counter.li.org/ # Debian GNU/Linux Consultant # Michelle Konzack Apt. 917 ICQ #328449886 50, rue de Soultz MSN LinuxMichi 0033/6/6192519367100 Strasbourg/France IRC #Debian (irc.icq.com) signature.pgp Description: Digital signature
Re: [GENERAL] Restoring 8.0 db to 8.1
On Mon, Jan 07, 2008 at 12:58:44PM +0100, Michelle Konzack wrote: except he has large objects, which slony can't replicate. currently or will this be changed in the future? Not likely to change in the future, no. Slony uses triggers to manage the changed rows. We can't fire triggers on large object events, so there's no way for Slony to know what happened. A ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: large objects,was: [GENERAL] Restoring 8.0 db to 8.1
On Jan 8, 2008 9:01 AM, Harald Armin Massa [EMAIL PROTECTED] wrote: Not likely to change in the future, no. Slony uses triggers to manage the changed rows. We can't fire triggers on large object events, so there's no way for Slony to know what happened. that leads me to a question I often wanted to ask: is there any reason to create NEW PostgreSQL databases using Large Objects, now that there is bytea and TOAST? (besides of legacy needs) as much as I read, they take special care in dump/restore; force the use of some special APIs on creating, do not work with Slony The primary advantage of large objects is that you can read like byte by byte, like a file. ---(end of broadcast)--- TIP 6: explain analyze is your friend
large objects,was: [GENERAL] Restoring 8.0 db to 8.1
Not likely to change in the future, no. Slony uses triggers to manage the changed rows. We can't fire triggers on large object events, so there's no way for Slony to know what happened. that leads me to a question I often wanted to ask: is there any reason to create NEW PostgreSQL databases using Large Objects, now that there is bytea and TOAST? (besides of legacy needs) as much as I read, they take special care in dump/restore; force the use of some special APIs on creating, do not work with Slony Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: large objects,was: [GENERAL] Restoring 8.0 db to 8.1
On Jan 8, 2008, at 9:13 AM, Scott Marlowe wrote: On Jan 8, 2008 9:01 AM, Harald Armin Massa [EMAIL PROTECTED] wrote: Not likely to change in the future, no. Slony uses triggers to manage the changed rows. We can't fire triggers on large object events, so there's no way for Slony to know what happened. that leads me to a question I often wanted to ask: is there any reason to create NEW PostgreSQL databases using Large Objects, now that there is bytea and TOAST? (besides of legacy needs) as much as I read, they take special care in dump/restore; force the use of some special APIs on creating, do not work with Slony The primary advantage of large objects is that you can read like byte by byte, like a file. Also, with bytea (and any other varying length data type) there is still a limit of 1G via TOASTing. Large Objects will get you up to 2G for one field. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Restoring 8.0 db to 8.1
On Dec 21, 2007 8:53 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi I am trying to upgrade my database from 8.0 to 8.1 and am looking for a little info/advice. This is a production database that we are migrating and it is in CONSTANT use, so the maintenance window must be small and hopefully mostly off-hours. We use a PITR/LVM snapshot solution for our backups and were hoping to simply restore the filesystem and startup under 8.1. Obviously this didnt work, and I know the doc says a conversion is necessary, however I havent found anything with enough detail to sway me from a better solution than pg_restore. The problem with pg_restore is that our database takes 3+ weeks to restore from a dump file. This is not an acceptable window for us. ( Approximately 3 days of this is data + lobs, and the rest indexes and constraints. If we are doing something wrong here, I am all ears as well ) Could anyone point me to the information I am missing or offer some middle ground solutions? You can try and see if the pg_migrator project helps you . http://pgfoundry.org/projects/pg-migrator/, its still in beta, but IIRC handles relatively simpler scenarios nicely. -- Usama Munir Dar http://www.linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar
[GENERAL] Restoring 8.0 db to 8.1
Hi I am trying to upgrade my database from 8.0 to 8.1 and am looking for a little info/advice. This is a production database that we are migrating and it is in CONSTANT use, so the maintenance window must be small and hopefully mostly off-hours. We use a PITR/LVM snapshot solution for our backups and were hoping to simply restore the filesystem and startup under 8.1. Obviously this didnt work, and I know the doc says a conversion is necessary, however I havent found anything with enough detail to sway me from a better solution than pg_restore. The problem with pg_restore is that our database takes 3+ weeks to restore from a dump file. This is not an acceptable window for us. ( Approximately 3 days of this is data + lobs, and the rest indexes and constraints. If we are doing something wrong here, I am all ears as well ) Could anyone point me to the information I am missing or offer some middle ground solutions? TIA Bill
Re: [GENERAL] Restoring 8.0 db to 8.1
On Fri, Dec 21, 2007 at 10:53:49AM -0500, [EMAIL PROTECTED] wrote: I am trying to upgrade my database from 8.0 to 8.1 and am looking for a little info/advice. This is a production database that we are migrating and it is in CONSTANT use, so the maintenance window must be small and hopefully mostly off-hours. The usual answer is use slony. You can use it to replicate the 8.0 server onto an 8.1 server. This may take weeks/months/years/whatever to synchronise. When the slave is up to date, you pull the plug on the 8.0 server and get everyone to use the 8.1 server... No downtime... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] Restoring 8.0 db to 8.1
[EMAIL PROTECTED] [EMAIL PROTECTED] writes: The problem with pg_restore is that our database takes 3+ weeks to restore from a dump file. This is not an acceptable window for us. ( Approximately 3 days of this is data + lobs, and the rest indexes and constraints. If we are doing something wrong here, I am all ears as well ) Uh, how big is that database exactly? One question is, if migration is so painful, why are you updating to a branch that is already obsolete? At this point you should be skipping 8.1.x and going directly to 8.2.x; or maybe planning to wait a few more weeks and go to 8.3.x. I say this not just on general principles, but mindful of the fact that there were some pretty significant improvements in sorting speed in 8.2, which would translate directly to shorter btree index build times. (Are all your indexes btrees?) Also, are you following the guidelines at http://developer.postgresql.org/pgdocs/postgres/populate.html#POPULATE-PG-DUMP ? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Restoring 8.0 db to 8.1
[EMAIL PROTECTED] wrote: Hi I am trying to upgrade my database from 8.0 to 8.1 and am looking for a little info/advice. This is a production database that we are migrating and it is in CONSTANT use, so the maintenance window must be small and hopefully mostly off-hours. We use a PITR/LVM snapshot solution for our backups and were hoping to simply restore the filesystem and startup under 8.1. Obviously this didnt work, and I know the doc says a conversion is necessary, however I havent found anything with enough detail to sway me from a better solution than pg_restore. Slony will let you replicate between different versions. Given that you can't afford any downtime, and presumably don't want to repeat this process in a hurry, I'd suggest going to 8.2, or 8.3 if you can afford to wait a few weeks. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Restoring 8.0 db to 8.1
On Dec 21, 2007, at 11:09 AM, Martijn van Oosterhout wrote: The usual answer is use slony. You can use it to replicate the 8.0 server onto an 8.1 server. This may take weeks/months/years/whatever to synchronise. When the slave is up to date, you pull the plug on the 8.0 server and get everyone to use the 8.1 server... No downtime... except he has large objects, which slony can't replicate. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
