pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Postgresql takes more time to update
15+ messages / 3 participants
[nested] [flat]

* Postgresql takes more time to update
@ 2007-10-04 14:49  Suresh Gupta VG <suresh.g@zensar.com>
  0 siblings, 2 replies; 15+ messages in thread

From: Suresh Gupta VG @ 2007-10-04 14:49 UTC (permalink / raw)
  To: pgsql-admin

Hi Team,

 

We are using "psql 7.4.2" version of Postgresql, these days all the
transactions on the database are taking long time to execute. We are
planning to do "ANALYZE" command on the database. Could you please
advice us, how much time it takes and what are the conditions we need to
keep on an eye. 

 

Can you please tell us whether we had any other commands are available
on postgresql to increase the performance of the database and database
tools available for Postgresql on Solaris sparc machine?

 

with thanks and regards, 

G.V. Suresh Gupta

   Sr. Software Engineer

   Batelco Phase II

Mo: +91 9890898688

Ph : +9120 66453213

 

 



DISCLAIMER:
This email may contain confidential or privileged information for the intended recipient(s) and the views expressed in the same are not necessarily the views of Zensar Technologies Ltd. If you are not the intended recipient or have received this e-mail by error, its use is strictly prohibited, please delete the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any liability for virus infected mails. 

Attachments:

image001.gif (image/gif, 2.5K) download view image
^ permalink raw reply [nested|flat] 15+ messages in thread

* Re: Postgresql takes more time to update
@ 2007-10-05 21:40  Scott Marlowe <scott.marlowe@gmail.com>
  parent: Suresh Gupta VG <suresh.g@zensar.com>
  1 sibling, 0 replies; 15+ messages in thread

From: Scott Marlowe @ 2007-10-05 21:40 UTC (permalink / raw)
  To: Suresh Gupta VG <suresh.g@zensar.com>; +Cc: pgsql-admin

On 10/4/07, Suresh Gupta VG <suresh.g@zensar.com> wrote:
>
>  Hi Team,
>
>
>
> We are using "psql 7.4.2" version of Postgresql, these days all the
> transactions on the database are taking long time to execute. We are
> planning to do "ANALYZE" command on the database. Could you please advice
> us, how much time it takes and what are the conditions we need to keep on an
> eye.
>

FIrst thing, you need to schedule an update to 7.4.18 or whatever the latest
7.4 patch release is. 7.4.2 is so far back that I'm 100% certain there are
data eating bugs in it, and you will likely get bitten if you stay on it.
Note that there is no need for dump / restore, unless something goes
horribly wrong.  Always make a backup just in case though.

Next in answer to how long an analyze will take, the answer is some time.
That's as accurate as I can be given how little I know of your database.
How much space on disk is it using?  How fast is your drive subsystem.  A
similar question would be how long does it take to paint a bridge.
Apparently, on the Golden Gate bridge, they start on one end, and when they
get to the other, the go back and start again.

> Can you please tell us whether we had any other commands are available on
> postgresql to increase the performance of the database and database tools
> available for Postgresql on Solaris sparc machine?
>

Hard to say.  The two most important things I can think of for speeding you
up right off the bat are to upgrade both your OS (to BSD or Linux on the
same hardware) and to upgrade PostgreSQL.

Barring that, we're looking at performance tuning.  I'd suggest taking a
walk over to the pgsql-perform list and telling folks all about your
hardware (CPU / Disk arrays / Memory) and what you're doing with your box:
types of queries, examples that are slow, how often you run analyze, how
often you vacuum, how large your current db is, what does vacuum verbose
tell you, those kinds of things.


with thanks and regards,
>
> *G.V. Suresh Gupta*
>
>    Sr. Software Engineer
>
>    Batelco Phase II
>
> Mo: +91 9890898688
>
> Ph : +9120 66453213
>
>
>
> DISCLAIMER:
> This email may contain confidential or privileged information for the
> intended recipient(s) and the views expressed in the same are not
> necessarily the views of Zensar Technologies Ltd. If you are not the
> intended recipient or have received this e-mail by error, its use is
> strictly prohibited, please delete the e-mail and notify the sender. Zensar
> Technologies Ltd. does not accept any liability for virus infected mails.
>

Attachments:

image001.gif (image/gif, 2.5K) download view image
^ permalink raw reply [nested|flat] 15+ messages in thread

* Re: Postgresql takes more time to update
@ 2007-10-06 17:30  Peter Koczan <pjkoczan@gmail.com>
  parent: Suresh Gupta VG <suresh.g@zensar.com>
  1 sibling, 0 replies; 15+ messages in thread

From: Peter Koczan @ 2007-10-06 17:30 UTC (permalink / raw)
  To: Suresh Gupta VG <suresh.g@zensar.com>; +Cc: pgsql-admin

>
> We are using "psql 7.4.2" version of Postgresql, these days all the
> transactions on the database are taking long time to execute. We are
> planning to do "ANALYZE" command on the database. Could you please advice
> us, how much time it takes and what are the conditions we need to keep on an
> eye.
>

As an alternative to Scott's suggestion (upgrading to the newest 7.4), you
could update your postgresql installation to 8.2, or if you can wait a few
months, 8.3. There are *huge* performance gains (I recently made a similar
switch and everything is blazing fast). Please note that this will require a
dump/restore of the data and more involved testing, so only do it if you can
devote the time, money, and energy.

As far as analyze goes, you should be running ANALYZE VERBOSE, or better
yet, VACUUM ANALYZE VERBOSE (see
http://www.postgresql.org/docs/7.4/interactive/sql-vacuum.html) so you can
interpret the output. The vacuum also helps manage disk space, and this
isn't a big performance hit because it doesn't require exclusive locks
(though a VACUUM FULL would, again, read the docs). In fact, you should be
doing this regularly, daily if possible.

For me, I have a 30 GB database cluster, and vacuum/analyze takes about 3
minutes, though YMMV. You want to look for output regarding FSM pages and
relations and adjust as necessary (otherwise you're running into index
bloat).

> Can you please tell us whether we had any other commands are available on
> postgresql to increase the performance of the database and database tools
> available for Postgresql on Solaris sparc machine?
>
I think Scott covered all of this. Alternatively, you could look to
upgrading your hardware (multi-core x86 hardware is very nice), but without
knowing your needs, usage, or budget, I can't make that determination.

Hope this helps.

Peter

^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Postgresql takes more time to update
@ 2007-10-07 07:10  Suresh Gupta VG <suresh.g@zensar.com>
  0 siblings, 1 reply; 15+ messages in thread

From: Suresh Gupta VG @ 2007-10-07 07:10 UTC (permalink / raw)
  To: Peter Koczan <pjkoczan@gmail.com>; scott.marlowe@gmail.com; +Cc: pgsql-admin

Hi Peter,

 

Thanks for your reply and to your colleague Scott. Can you pls explain
below sentence marked in red.

 

-          --------- ------------------ --------------------- 

As an alternative to Scott's suggestion (upgrading to the newest 7.4),
you could update your postgresql installation to 8.2, or if you can wait
a few months, 8.3. There are *huge* performance gains (I recently made a
similar switch and everything is blazing fast). Please note that this
will require a dump/restore of the data and more involved testing, so
only do it if you can devote the time, money, and energy.

-          --------- ---------------- ------------- -------------- 

 

Is 8.2 version is not free downloadable? What type of testing is
required? Pls advice us.

 

 

with thanks and regards, 

G.V. Suresh Gupta

   Sr. Software Engineer

   Batelco Phase II

Mo: +91 9890898688

Ph : +9120 66453213

 

________________________________

From: Peter Koczan [mailto:pjkoczan@gmail.com] 
Sent: Saturday, October 06, 2007 11:00 PM
To: Suresh Gupta VG
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgresql takes more time to update

 

	We are using "psql 7.4.2" version of Postgresql, these days all
the transactions on the database are taking long time to execute. We are
planning to do "ANALYZE " command on the database. Could you please
advice us, how much time it takes and what are the conditions we need to
keep on an eye.


As an alternative to Scott's suggestion (upgrading to the newest 7.4),
you could update your postgresql installation to 8.2, or if you can wait
a few months, 8.3. There are *huge* performance gains (I recently made a
similar switch and everything is blazing fast). Please note that this
will require a dump/restore of the data and more involved testing, so
only do it if you can devote the time, money, and energy. 

As far as analyze goes, you should be running ANALYZE VERBOSE, or better
yet, VACUUM ANALYZE VERBOSE (see 
http://www.postgresql.org/docs/7.4/interactive/sql-vacuum.html ) so you
can interpret the output. The vacuum also helps manage disk space, and
this isn't a big performance hit because it doesn't require exclusive
locks (though a VACUUM FULL would, again, read the docs). In fact, you
should be doing this regularly, daily if possible. 

For me, I have a 30 GB database cluster, and vacuum/analyze takes about
3 minutes, though YMMV. You want to look for output regarding FSM pages
and relations and adjust as necessary (otherwise you're running into
index bloat). 

	Can you please tell us whether we had any other commands are
available on postgresql to increase the performance of the database and
database tools available for Postgresql on Solaris sparc machine?

I think Scott covered all of this. Alternatively, you could look to
upgrading your hardware (multi-core x86 hardware is very nice), but
without knowing your needs, usage, or budget, I can't make that
determination. 

Hope this helps.

Peter
 

 

 



DISCLAIMER:
This email may contain confidential or privileged information for the intended recipient(s) and the views expressed in the same are not necessarily the views of Zensar Technologies Ltd. If you are not the intended recipient or have received this e-mail by error, its use is strictly prohibited, please delete the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any liability for virus infected mails. 

Attachments:

image001.gif (image/gif, 2.5K) download view image
^ permalink raw reply [nested|flat] 15+ messages in thread

* FW: Postgresql takes more time to update
@ 2007-10-08 06:41  Suresh Gupta VG <suresh.g@zensar.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Suresh Gupta VG @ 2007-10-08 06:41 UTC (permalink / raw)
  To: Peter Koczan <pjkoczan@gmail.com>; scott.marlowe@gmail.com; +Cc: pgsql-admin; pgsql-admin-owner@postgresql.org

Bounced this mail, so sending you again.

 

with thanks and regards, 

G.V. Suresh Gupta

   Sr. Software Engineer

   Batelco Phase II

Mo: +91 9890898688

Ph : +9120 66453213

 

________________________________

From: Suresh Gupta VG 
Sent: Sunday, October 07, 2007 12:40 PM
To: 'Peter Koczan'; 'scott.marlowe@gmail.com'
Cc: pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Postgresql takes more time to update

 

Hi Peter,

 

Thanks for your reply and to your colleague Scott. Can you pls explain
below sentence marked in red.

 

-          --------- ------------------ --------------------- 

As an alternative to Scott's suggestion (upgrading to the newest 7.4),
you could update your postgresql installation to 8.2, or if you can wait
a few months, 8.3. There are *huge* performance gains (I recently made a
similar switch and everything is blazing fast). Please note that this
will require a dump/restore of the data and more involved testing, so
only do it if you can devote the time, money, and energy.

-          --------- ---------------- ------------- -------------- 

 

Is 8.2 version is not free downloadable? What type of testing is
required? Pls advice us.

 

 

with thanks and regards, 

G.V. Suresh Gupta

   Sr. Software Engineer

   Batelco Phase II

Mo: +91 9890898688

Ph : +9120 66453213



________________________________

From: Peter Koczan [mailto:pjkoczan@gmail.com] 
Sent: Saturday, October 06, 2007 11:00 PM
To: Suresh Gupta VG
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgresql takes more time to update

 

	We are using "psql 7.4.2" version of Postgresql, these days all
the transactions on the database are taking long time to execute. We are
planning to do "ANALYZE " command on the database. Could you please
advice us, how much time it takes and what are the conditions we need to
keep on an eye.


As an alternative to Scott's suggestion (upgrading to the newest 7.4),
you could update your postgresql installation to 8.2, or if you can wait
a few months, 8.3. There are *huge* performance gains (I recently made a
similar switch and everything is blazing fast). Please note that this
will require a dump/restore of the data and more involved testing, so
only do it if you can devote the time, money, and energy. 

As far as analyze goes, you should be running ANALYZE VERBOSE, or better
yet, VACUUM ANALYZE VERBOSE (see 
http://www.postgresql.org/docs/7.4/interactive/sql-vacuum.html ) so you
can interpret the output. The vacuum also helps manage disk space, and
this isn't a big performance hit because it doesn't require exclusive
locks (though a VACUUM FULL would, again, read the docs). In fact, you
should be doing this regularly, daily if possible. 

For me, I have a 30 GB database cluster, and vacuum/analyze takes about
3 minutes, though YMMV. You want to look for output regarding FSM pages
and relations and adjust as necessary (otherwise you're running into
index bloat). 

	Can you please tell us whether we had any other commands are
available on postgresql to increase the performance of the database and
database tools available for Postgresql on Solaris sparc machine?

I think Scott covered all of this. Alternatively, you could look to
upgrading your hardware (multi-core x86 hardware is very nice), but
without knowing your needs, usage, or budget, I can't make that
determination. 

Hope this helps.

Peter
 

 

 



DISCLAIMER:
This email may contain confidential or privileged information for the intended recipient(s) and the views expressed in the same are not necessarily the views of Zensar Technologies Ltd. If you are not the intended recipient or have received this e-mail by error, its use is strictly prohibited, please delete the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any liability for virus infected mails. 

Attachments:

image001.gif (image/gif, 2.5K) download view image
^ permalink raw reply [nested|flat] 15+ messages in thread

* Re: Postgresql takes more time to update
@ 2007-10-08 16:31  Peter Koczan <pjkoczan@gmail.com>
  parent: Suresh Gupta VG <suresh.g@zensar.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Peter Koczan @ 2007-10-08 16:31 UTC (permalink / raw)
  To: Suresh Gupta VG <suresh.g@zensar.com>; +Cc: scott.marlowe@gmail.com; pgsql-admin

On 10/7/07, Suresh Gupta VG <suresh.g@zensar.com> wrote:
>
>  Hi Peter,
>
>
>
> Thanks for your reply and to your colleague Scott. Can you pls explain
> below sentence marked in red.
>
>
>
> -          --------- ------------------ ---------------------
>
> As an alternative to Scott's suggestion (upgrading to the newest 7.4), you
> could update your postgresql installation to 8.2, or if you can wait a few
> months, 8.3. There are *huge* performance gains (I recently made a similar
> switch and everything is blazing fast). Please note that this will require
> a dump/restore of the data and more involved testing, so only do it if you
> can devote the time, money, and energy.
>
> -          --------- ---------------- ------------- --------------
>
>
>
> Is 8.2 version is not free downloadable? What type of testing is required?
> Pls advice us.
>

Sorry about being ambiguous, 8.2 is still free, but it does have quite a few
changes from 7.4, so it will take time to update your configuration,
recompile/reinstall postgres, dump/restore your data, and test your client
applications. This will take time for the IT staff to do (and therefore
money). This is what I meant by "devoting money".

Specifically, when I upgraded, I ran into these problems:
- A primary key broke and I had to fix it before going ultimately migrating
to 8.2.
- The cidr data type is more strictly checked, I had to fix a couple rows
before migrating.
- Permissions and ownership underwent slight changes.
- User and groups were conflated into roles, which necessitated a change in
my user/group management scripts.

I tested these thoroughly before making the migration final. I found most of
these problems from a simple dump/restore. If you can, dump and restore your
databases to a test server (insofar as you can) and you should be able to
fix most migration issues.

The last thing you'll want to do is test your more critical client
applications. Postgres is very good about maintaining backwards
compatibility of SQL, so most things should "just work." Still, test.

Peter

^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Postgresql takes more time to update
@ 2007-10-18 13:31  Suresh Gupta VG <suresh.g@zensar.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Suresh Gupta VG @ 2007-10-18 13:31 UTC (permalink / raw)
  To: Peter Koczan <pjkoczan@gmail.com>; +Cc: scott.marlowe@gmail.com; pgsql-admin

Hi Peter,

 

1)       We are using "psql 7.4.2" version of Postgresql, need to create
a new schema similar to the current schema with all the objects as in
the current schema. Do we have any command to support this operation.

2)       We need to shift all the data between 2 different databases in
2 different servers. What is the best way to go either backup or copy
command? 

 

Can you please give some knowledge on this.

 

with thanks and regards, 

G.V. Suresh Gupta

   Sr. Software Engineer

   Batelco Phase II

Mo: +91 9890898688

Ph : +9120 66453213

 

________________________________

From: Peter Koczan [mailto:pjkoczan@gmail.com] 
Sent: Monday, October 08, 2007 10:01 PM
To: Suresh Gupta VG
Cc: scott.marlowe@gmail.com; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgresql takes more time to update

 

 

On 10/7/07, Suresh Gupta VG <suresh.g@zensar.com> wrote:

Hi Peter,

 

Thanks for your reply and to your colleague Scott. Can you pls explain
below sentence marked in red.

 

-          --------- ------------------ --------------------- 

As an alternative to Scott's suggestion (upgrading to the newest 7.4),
you could update your postgresql installation to 8.2, or if you can wait
a few months, 8.3. There are *huge* performance gains (I recently made a
similar switch and everything is blazing fast). Please note that this
will require a dump/restore of the data and more involved testing, so
only do it if you can devote the time, money, and energy.

-          --------- ---------------- ------------- -------------- 

 

Is 8.2 version is not free downloadable? What type of testing is
required? Pls advice us.


Sorry about being ambiguous, 8.2 is still free, but it does have quite a
few changes from 7.4, so it will take time to update your configuration,
recompile/reinstall postgres, dump/restore your data, and test your
client applications. This will take time for the IT staff to do (and
therefore money). This is what I meant by "devoting money". 

 

Specifically, when I upgraded, I ran into these problems:
- A primary key broke and I had to fix it before going ultimately
migrating to 8.2.
- The cidr data type is more strictly checked, I had to fix a couple
rows before migrating. 
- Permissions and ownership underwent slight changes.
- User and groups were conflated into roles, which necessitated a change
in my user/group management scripts.

I tested these thoroughly before making the migration final. I found
most of these problems from a simple dump/restore. If you can, dump and
restore your databases to a test server (insofar as you can) and you
should be able to fix most migration issues. 

The last thing you'll want to do is test your more critical client
applications. Postgres is very good about maintaining backwards
compatibility of SQL, so most things should "just work." Still, test. 

Peter



DISCLAIMER:
This email may contain confidential or privileged information for the intended recipient(s) and the views expressed in the same are not necessarily the views of Zensar Technologies Ltd. If you are not the intended recipient or have received this e-mail by error, its use is strictly prohibited, please delete the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any liability for virus infected mails. 

Attachments:

image001.gif (image/gif, 2.5K) download view image
^ permalink raw reply [nested|flat] 15+ messages in thread

* Re: Postgresql takes more time to update
@ 2007-10-18 13:33  Suresh Gupta VG <suresh.g@zensar.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Suresh Gupta VG @ 2007-10-18 13:33 UTC (permalink / raw)
  To: Peter Koczan <pjkoczan@gmail.com>; pgsql-admin-owner@postgresql.org; +Cc: scott.marlowe@gmail.com; pgsql-admin

 

 

with thanks and regards, 

G.V. Suresh Gupta

   Sr. Software Engineer

   Batelco Phase II

Mo: +91 9890898688

Ph : +9120 66453213

 

________________________________

From: Suresh Gupta VG 
Sent: Thursday, October 18, 2007 7:01 PM
To: 'Peter Koczan'
Cc: scott.marlowe@gmail.com; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Postgresql takes more time to update

 

Hi Peter,

 

1)       We are using "psql 7.4.2" version of Postgresql, need to create
a new schema similar to the current schema with all the objects as in
the current schema. Do we have any command to support this operation.

2)       We need to shift all the data between 2 different databases in
2 different servers. What is the best way to go either backup or copy
command? 

 

Can you please give some knowledge on this.

 

with thanks and regards, 

G.V. Suresh Gupta

   Sr. Software Engineer

   Batelco Phase II

Mo: +91 9890898688

Ph : +9120 66453213



________________________________

From: Peter Koczan [mailto:pjkoczan@gmail.com] 
Sent: Monday, October 08, 2007 10:01 PM
To: Suresh Gupta VG
Cc: scott.marlowe@gmail.com; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgresql takes more time to update

 

 

On 10/7/07, Suresh Gupta VG <suresh.g@zensar.com> wrote:

Hi Peter,

 

Thanks for your reply and to your colleague Scott. Can you pls explain
below sentence marked in red.

 

-          --------- ------------------ --------------------- 

As an alternative to Scott's suggestion (upgrading to the newest 7.4),
you could update your postgresql installation to 8.2, or if you can wait
a few months, 8.3. There are *huge* performance gains (I recently made a
similar switch and everything is blazing fast). Please note that this
will require a dump/restore of the data and more involved testing, so
only do it if you can devote the time, money, and energy.

-          --------- ---------------- ------------- -------------- 

 

Is 8.2 version is not free downloadable? What type of testing is
required? Pls advice us.


Sorry about being ambiguous, 8.2 is still free, but it does have quite a
few changes from 7.4, so it will take time to update your configuration,
recompile/reinstall postgres, dump/restore your data, and test your
client applications. This will take time for the IT staff to do (and
therefore money). This is what I meant by "devoting money". 

 

Specifically, when I upgraded, I ran into these problems:
- A primary key broke and I had to fix it before going ultimately
migrating to 8.2.
- The cidr data type is more strictly checked, I had to fix a couple
rows before migrating. 
- Permissions and ownership underwent slight changes.
- User and groups were conflated into roles, which necessitated a change
in my user/group management scripts.

I tested these thoroughly before making the migration final. I found
most of these problems from a simple dump/restore. If you can, dump and
restore your databases to a test server (insofar as you can) and you
should be able to fix most migration issues. 

The last thing you'll want to do is test your more critical client
applications. Postgres is very good about maintaining backwards
compatibility of SQL, so most things should "just work." Still, test. 

Peter



DISCLAIMER:
This email may contain confidential or privileged information for the intended recipient(s) and the views expressed in the same are not necessarily the views of Zensar Technologies Ltd. If you are not the intended recipient or have received this e-mail by error, its use is strictly prohibited, please delete the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any liability for virus infected mails. 

Attachments:

image001.gif (image/gif, 2.5K) download view image
^ permalink raw reply [nested|flat] 15+ messages in thread

* Re: Postgresql takes more time to update
@ 2007-10-23 10:11  Suresh Gupta VG <suresh.g@zensar.com>
  0 siblings, 2 replies; 15+ messages in thread

From: Suresh Gupta VG @ 2007-10-23 10:11 UTC (permalink / raw)
  To: Peter Koczan <pjkoczan@gmail.com>; +Cc: scott.marlowe@gmail.com; pgsql-admin; pgsql-admin-owner@postgresql.org

Hi Peter,

 

1)       We are using "psql 7.4.2" version of Postgresql, need to create
a new schema similar to the current schema with all the objects as in
the current schema. Do we have any command to support this operation?

2)       We need to shift all the data between 2 different databases in
2 different servers. What is the best way to go either backup or copy
command? 

3)       Any equivalent command to export and import commands in
Oracle/SQL.

 

Can you please give some knowledge on this

 

 

with thanks and regards, 

G.V. Suresh Gupta

   Sr. Software Engineer

   Batelco Phase II

Mo: +91 9890898688

Ph : +9120 66453213

 

________________________________

From: Peter Koczan [mailto:pjkoczan@gmail.com] 
Sent: Monday, October 08, 2007 10:01 PM
To: Suresh Gupta VG
Cc: scott.marlowe@gmail.com; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgresql takes more time to update

 

 

On 10/7/07, Suresh Gupta VG <suresh.g@zensar.com> wrote:

Hi Peter,

 

Thanks for your reply and to your colleague Scott. Can you pls explain
below sentence marked in red.

 

-          --------- ------------------ --------------------- 

As an alternative to Scott's suggestion (upgrading to the newest 7.4),
you could update your postgresql installation to 8.2, or if you can wait
a few months, 8.3. There are *huge* performance gains (I recently made a
similar switch and everything is blazing fast). Please note that this
will require a dump/restore of the data and more involved testing, so
only do it if you can devote the time, money, and energy.

-          --------- ---------------- ------------- -------------- 

 

Is 8.2 version is not free downloadable? What type of testing is
required? Pls advice us.


Sorry about being ambiguous, 8.2 is still free, but it does have quite a
few changes from 7.4, so it will take time to update your configuration,
recompile/reinstall postgres, dump/restore your data, and test your
client applications. This will take time for the IT staff to do (and
therefore money). This is what I meant by "devoting money". 

 

Specifically, when I upgraded, I ran into these problems:
- A primary key broke and I had to fix it before going ultimately
migrating to 8.2.
- The cidr data type is more strictly checked, I had to fix a couple
rows before migrating. 
- Permissions and ownership underwent slight changes.
- User and groups were conflated into roles, which necessitated a change
in my user/group management scripts.

I tested these thoroughly before making the migration final. I found
most of these problems from a simple dump/restore. If you can, dump and
restore your databases to a test server (insofar as you can) and you
should be able to fix most migration issues. 

The last thing you'll want to do is test your more critical client
applications. Postgres is very good about maintaining backwards
compatibility of SQL, so most things should "just work." Still, test. 

Peter



DISCLAIMER:
This email may contain confidential or privileged information for the intended recipient(s) and the views expressed in the same are not necessarily the views of Zensar Technologies Ltd. If you are not the intended recipient or have received this e-mail by error, its use is strictly prohibited, please delete the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any liability for virus infected mails. 

Attachments:

image001.gif (image/gif, 2.5K) download view image
^ permalink raw reply [nested|flat] 15+ messages in thread

* Re: Postgresql takes more time to update
@ 2007-10-23 20:47  Scott Marlowe <scott.marlowe@gmail.com>
  parent: Suresh Gupta VG <suresh.g@zensar.com>
  1 sibling, 0 replies; 15+ messages in thread

From: Scott Marlowe @ 2007-10-23 20:47 UTC (permalink / raw)
  To: Suresh Gupta VG <suresh.g@zensar.com>; +Cc: Peter Koczan <pjkoczan@gmail.com>; pgsql-admin

On 10/23/07, Suresh Gupta VG <suresh.g@zensar.com> wrote:
>
>  Hi Peter,
>
>
>
> 1)       We are using "psql 7.4.2" version of Postgresql, need to create a
> new schema similar to the current schema with all the objects as in the
> current schema. Do we have any command to support this operation?
>
> 2)       We need to shift all the data between 2 different databases in 2
> different servers. What is the best way to go either backup or copy command?
>
>
> 3)       Any equivalent command to export and import commands in
> Oracle/SQL.
>
>
>
> Can you please give some knowledge on this
>
1:  Upgrade to 7.4.18 (or whatever the latest is) as soon as you can.  Use
pg_dump with the -s switch to get just the schema and objects but no data
out.  pg_dump --help will give you a list of all the options that command
has.  To put your new schema into another database, just redirect that file
to psql:

pg_dump -s -h olddbserver olddbname > schemafile.sql
psql -h newserver newdbname < svhemafile.sql

2: pg_dump is the best way to go.  pg_dumpall can dump everything, including
user accounts from one server.  Then feed the output to psql or pg_restore.

3: pg_dump is export, psql or pg_restore are import

^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Postgresql takes more time to update
@ 2007-10-23 21:47  Peter Koczan <pjkoczan@gmail.com>
  parent: Suresh Gupta VG <suresh.g@zensar.com>
  1 sibling, 0 replies; 15+ messages in thread

From: Peter Koczan @ 2007-10-23 21:47 UTC (permalink / raw)
  To: Suresh Gupta VG <suresh.g@zensar.com>; +Cc: scott.marlowe@gmail.com; pgsql-admin; pgsql-admin-owner@postgresql.org

On 10/23/07, Suresh Gupta VG <suresh.g@zensar.com> wrote:
>
>  Hi Peter,
>
>
>
> 1)       We are using "psql 7.4.2" version of Postgresql, need to create a
> new schema similar to the current schema with all the objects as in the
> current schema. Do we have any command to support this operation?
>

If you upgrade to the newest release in the 7.4 branch, you should just have
to stop the database server and start the new version of postgres. If you
are upgrading to an 8.x version (8.0, 8.1, or 8.2), you'll have to
dump/restore using the method Scott mentioned.

2)       We need to shift all the data between 2 different databases in 2
> different servers. What is the best way to go either backup or copy command?
>

Again, it depends on if you're going with the newest 7.4 release or a new
8.x release. For the newest 7.4, you can simply rsync or do a straight copy
of the file system. If you're going with 8.x, you'll need to dump/restore
using the method Scott mentioned.

3)       Any equivalent command to export and import commands in Oracle/SQL.
>

I'm afraid I don't know enough about Oracle to answer that question.

Peter

^ permalink  raw  reply  [nested|flat] 15+ messages in thread

* Re: Postgresql takes more time to update
@ 2007-10-24 07:11  Suresh Gupta VG <suresh.g@zensar.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Suresh Gupta VG @ 2007-10-24 07:11 UTC (permalink / raw)
  To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Peter Koczan <pjkoczan@gmail.com>; pgsql-admin

Scott, Thanks for your response.

 

with thanks and regards, 

G.V. Suresh Gupta

   Sr. Software Engineer

   Batelco Phase II

Mo: +91 9890898688

Ph : +9120 66453213

 

________________________________

From: Scott Marlowe [mailto:scott.marlowe@gmail.com] 
Sent: Wednesday, October 24, 2007 2:17 AM
To: Suresh Gupta VG
Cc: Peter Koczan; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgresql takes more time to update

 

On 10/23/07, Suresh Gupta VG <suresh.g@zensar.com> wrote:

	Hi Peter,

	 

	1)       We are using "psql 7.4.2" version of Postgresql, need
to create a new schema similar to the current schema with all the
objects as in the current schema. Do we have any command to support this
operation?

	2)       We need to shift all the data between 2 different
databases in 2 different servers. What is the best way to go either
backup or copy command? 

	3)       Any equivalent command to export and import commands in
Oracle/SQL.

	 

	Can you please give some knowledge on this 

1:  Upgrade to 7.4.18 (or whatever the latest is) as soon as you can.
Use pg_dump with the -s switch to get just the schema and objects but no
data out.  pg_dump --help will give you a list of all the options that
command has.  To put your new schema into another database, just
redirect that file to psql: 

pg_dump -s -h olddbserver olddbname > schemafile.sql
psql -h newserver newdbname < svhemafile.sql

2: pg_dump is the best way to go.  pg_dumpall can dump everything,
including user accounts from one server.  Then feed the output to psql
or pg_restore. 

3: pg_dump is export, psql or pg_restore are import



DISCLAIMER:
This email may contain confidential or privileged information for the intended recipient(s) and the views expressed in the same are not necessarily the views of Zensar Technologies Ltd. If you are not the intended recipient or have received this e-mail by error, its use is strictly prohibited, please delete the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any liability for virus infected mails. 

Attachments:

image001.gif (image/gif, 2.5K) download view image
^ permalink raw reply [nested|flat] 15+ messages in thread

* Re: Postgresql takes more time to update
@ 2007-10-24 07:11  Suresh Gupta VG <suresh.g@zensar.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Suresh Gupta VG @ 2007-10-24 07:11 UTC (permalink / raw)
  To: Peter Koczan <pjkoczan@gmail.com>; +Cc: scott.marlowe@gmail.com; pgsql-admin; pgsql-admin-owner@postgresql.org

Peter, Thanks for your response.

 

with thanks and regards, 

G.V. Suresh Gupta

   Sr. Software Engineer

   Batelco Phase II

Mo: +91 9890898688

Ph : +9120 66453213

 

________________________________

From: Peter Koczan [mailto:pjkoczan@gmail.com] 
Sent: Wednesday, October 24, 2007 3:18 AM
To: Suresh Gupta VG
Cc: scott.marlowe@gmail.com; pgsql-admin@postgresql.org;
pgsql-admin-owner@postgresql.org
Subject: Re: [ADMIN] Postgresql takes more time to update

 

 

On 10/23/07, Suresh Gupta VG <suresh.g@zensar.com> wrote:

Hi Peter,

 

1)       We are using "psql 7.4.2" version of Postgresql, need to create
a new schema similar to the current schema with all the objects as in
the current schema. Do we have any command to support this operation?


If you upgrade to the newest release in the 7.4 branch, you should just
have to stop the database server and start the new version of postgres.
If you are upgrading to an 8.x version (8.0, 8.1, or 8.2), you'll have
to dump/restore using the method Scott mentioned.

	 

	2)       We need to shift all the data between 2 different
databases in 2 different servers. What is the best way to go either
backup or copy command?


Again, it depends on if you're going with the newest 7.4 release or a
new 8.x release. For the newest 7.4, you can simply rsync or do a
straight copy of the file system. If you're going with 8.x, you'll need
to dump/restore using the method Scott mentioned.

	 

	3)        Any equivalent command to export and import commands
in Oracle/SQL. 


I'm afraid I don't know enough about Oracle to answer that question.

Peter



DISCLAIMER:
This email may contain confidential or privileged information for the intended recipient(s) and the views expressed in the same are not necessarily the views of Zensar Technologies Ltd. If you are not the intended recipient or have received this e-mail by error, its use is strictly prohibited, please delete the e-mail and notify the sender. Zensar Technologies Ltd. does not accept any liability for virus infected mails. 

Attachments:

image001.gif (image/gif, 2.5K) download view image
^ permalink raw reply [nested|flat] 15+ messages in thread

* Re: Postgresql takes more time to update
@ 2007-10-29 09:05  Suresh Gupta VG <suresh.g@zensar.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Suresh Gupta VG @ 2007-10-29 09:05 UTC (permalink / raw)
  To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Peter Koczan <pjkoczan@gmail.com>; pgsql-admin

Thanks Scott and Peter,

 

Can we get a part of data from the database based on any rules like
date, field value, etc. b'cas I need the dump of data everyday in a
separate files. Can you pls suggest me on this.

 

G.V. Suresh Gupta

   Sr. Software Engineer

   Batelco Phase II

Mo: +91 9890898688

Ph : +9120 66453213

 

________________________________

From: Scott Marlowe [mailto:scott.marlowe@gmail.com] 
Sent: Wednesday, October 24, 2007 2:17 AM
To: Suresh Gupta VG
Cc: Peter Koczan; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Postgresql takes more time to update

 

On 10/23/07, Suresh Gupta VG <suresh.g@zensar.com> wrote:

	Hi Peter,

	 

	1)       We are using "psql 7.4.2" version of Postgresql, need
to create a new schema similar to the current schema with all the
objects as in the current schema. Do we have any command to support this
operation?

	2)       We need to shift all the data between 2 different
databases in 2 different servers. What is the best way to go either
backup or copy command? 

	3)       Any equivalent command to export and import commands in
Oracle/SQL.

	 

	Can you please give some knowledge on this 

1:  Upgrade to 7.4.18 (or whatever the latest is) as soon as you can.
Use pg_dump with the -s switch to get just the schema and objects but no
data out.  pg_dump --help will give you a list of all the options that
command has.  To put your new schema into another database, just
redirect that file to psql: 

pg_dump -s -h olddbserver olddbname > schemafile.sql
psql -h newserver newdbname < svhemafile.sql

2: pg_dump is the best way to go.  pg_dumpall can dump everything,
including user accounts from one server.  Then feed the output to psql
or pg_restore. 

3: pg_dump is export, psql or pg_restore are import

Attachments:

image001.gif (image/gif, 2.5K) download view image
^ permalink raw reply [nested|flat] 15+ messages in thread

* Re: Postgresql takes more time to update
@ 2007-10-29 17:39  Peter Koczan <pjkoczan@gmail.com>
  0 siblings, 0 replies; 15+ messages in thread

From: Peter Koczan @ 2007-10-29 17:39 UTC (permalink / raw)
  To: Suresh Gupta VG <suresh.g@zensar.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; pgsql-admin

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

^ permalink  raw  reply  [nested|flat] 15+ messages in thread


end of thread, other threads:[~2007-10-29 17:39 UTC | newest]

Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-04 14:49 Postgresql takes more time to update Suresh Gupta VG <suresh.g@zensar.com>
2007-10-05 21:40 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-10-06 17:30 ` Peter Koczan <pjkoczan@gmail.com>
2007-10-07 07:10 Re: Postgresql takes more time to update Suresh Gupta VG <suresh.g@zensar.com>
2007-10-08 16:31 ` Peter Koczan <pjkoczan@gmail.com>
2007-10-08 06:41 FW: Postgresql takes more time to update Suresh Gupta VG <suresh.g@zensar.com>
2007-10-18 13:31 Re: Postgresql takes more time to update Suresh Gupta VG <suresh.g@zensar.com>
2007-10-18 13:33 Re: Postgresql takes more time to update Suresh Gupta VG <suresh.g@zensar.com>
2007-10-23 10:11 Re: Postgresql takes more time to update Suresh Gupta VG <suresh.g@zensar.com>
2007-10-23 20:47 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-10-23 21:47 ` Peter Koczan <pjkoczan@gmail.com>
2007-10-24 07:11 Re: Postgresql takes more time to update Suresh Gupta VG <suresh.g@zensar.com>
2007-10-24 07:11 Re: Postgresql takes more time to update Suresh Gupta VG <suresh.g@zensar.com>
2007-10-29 09:05 Re: Postgresql takes more time to update Suresh Gupta VG <suresh.g@zensar.com>
2007-10-29 17:39 Re: Postgresql takes more time to update Peter Koczan <pjkoczan@gmail.com>

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