Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id E5BE49FB870 for ; Mon, 29 Oct 2007 14:40:01 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 82712-09-2 for ; Mon, 29 Oct 2007 14:39:49 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.178]) by postgresql.org (Postfix) with ESMTP id 858519F9D96 for ; Mon, 29 Oct 2007 14:39:55 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id u77so3913471pyb for ; Mon, 29 Oct 2007 10:39:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=HJIkhfz5UHHptYaekM1mbhk8HIkFxmXs7Dkj/C1/KgM=; b=WWopnrc8+l0GLxr8TJiXrUlwPQoYyHvnXKSLz0h55sYv1UuWrNOARqoj1QjGUbAlGLcChTXcwlCqVd/4bc+3nWb8+tETb3A/0Oih7WwpkfTNLU6rqA0Wrt2BYw20DZAQqRPlYHGg0iM1lhaRh7QPPsLgqEup7OBo5dvQBchUgFw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Dcua4520KpQVOEj96ekvzQ6mcAWZyMs5rkNP8hXs+0iPBhTwCLUsT85IPbK/pnPwthoQK47pWGkmBs9EEuneRfVrNa0QmYHC3fzvVhYbGP8xspmq6LWE6LTqAOQ4cgu+Pw0a5WvB2vfozdLtAn8uL8HWakmJDhyx0LEIy+O4VNI= Received: by 10.114.13.1 with SMTP id 1mr858268wam.1193679593343; Mon, 29 Oct 2007 10:39:53 -0700 (PDT) Received: by 10.114.197.19 with HTTP; Mon, 29 Oct 2007 10:39:53 -0700 (PDT) Message-ID: <4544e0330710291039y25d05c0mf541b2ad18b042ec@mail.gmail.com> Date: Mon, 29 Oct 2007 12:39:53 -0500 From: "Peter Koczan" To: "Suresh Gupta VG" Subject: Re: Postgresql takes more time to update Cc: "Scott Marlowe" , "pgsql-admin@postgresql.org" In-Reply-To: <3D5445983859B84B92669C0D883EA6FB0ADBEE89@ZENMAILHQ1.ind.zensar.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7287_27129594.1193679593334" References: <3D5445983859B84B92669C0D883EA6FB0ADBEE89@ZENMAILHQ1.ind.zensar.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/294 X-Sequence-Number: 27208 ------=_Part_7287_27129594.1193679593334 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline > Here I need to know from you > > a) Is there any better way to do this other than the above? > That's more complicated than you need to make it. What I do is something like this (in a shell): pg_dumpall -h host1 -p 5432 | psql -h host2 -p 5432 template1 and then delete the data as necessary via psql afterward. You may need to tweak that command based on your authentication scheme or substitute 5432 for the port the server is listening on and to specify the proper hosts. > b) Do I need to stop all the applications which are accessing this > live Production database? (Should I need to take an outage of my complete > system) > For testing, no, pg_dump takes a snapshot of the database and it doesn't add a whole lot of overhead (at least not when I do it). You may notice some slowdown, but nothing that would truly affect performance. When you actually want to switch to the new version, probably. You'll have to figure out some way to make sure there are no updates on the database before dump/restoring if you're concerned about making sure data is consistent. There will be downtime in this case. You can either disable outside connections via pg_hba.conf or move the server to a different port. You should plan it out for when the time comes. > c) Are there any core area where I need to take care of these > activities? > I don't understand the question. > d) I had 10 lacs of records on "pgsql 7.4.2". We are using "SunOS > 5.9" version of Solaris machine. How much time it can take to take a dump > of all the data. > That depends greatly on your hardware and your database design. For me, dump/restoring 10 GB of data (which becomes 50 GB once all the indexes are created) to decent but modest hardware takes about 4 hours. > Please advice me to carry on this process successfully and safely with out > any conflicts. Thanks in advance. > Test, figure out how to do things, plan, manage any possible downtime. That's the process in a nutshell. ------=_Part_7287_27129594.1193679593334 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

Here I need to know from you

a)       Is there any better way to do this other than the above?

That's more complicated than you need to make it. What I do is something like this (in a shell):
pg_dumpall -h host1 -p 5432 | psql -h host2 -p 5432 template1

and then delete the data as necessary via psql afterward.

You may need to tweak that command based on your authentication scheme or substitute 5432 for the port the server is listening on and to specify the proper hosts.

b)        Do I need to stop all the applications which are accessing this live Production database? (Should I need to take an outage of my complete system)

For testing, no, pg_dump takes a snapshot of the database and it doesn't add a whole lot of overhead (at least not when I do it). You may notice some slowdown, but nothing that would truly affect performance.

When you actually want to switch to the new version, probably. You'll have to figure out some way to make sure there are no updates on the database before dump/restoring if you're concerned about making sure data is consistent. There will be downtime in this case. You can either disable outside connections via pg_hba.conf or move the server to a different port. You should plan it out for when the time comes.

c)       Are there any core area where I need to take care of these activities?

I don't understand the question.

d)        I had 10 lacs of records on "pgsql 7.4.2". We are using "SunOS 5.9" version of Solaris machine. How much time it can take to take a dump of all the data.

That depends greatly on your hardware and your database design. For me, dump/restoring 10 GB of data (which becomes 50 GB once all the indexes are created) to decent but modest hardware takes about 4 hours.

Please advice me to carry on this process successfully and safely with out any conflicts. Thanks in advance.

Test, figure out how to do things, plan, manage any possible downtime. That's the process in a nutshell.
------=_Part_7287_27129594.1193679593334--