Re: Postgresql takes more time to update

> 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.