Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgresql takes more time to update

Postgresql takes more time to update

From
"Suresh Gupta VG"
Date:

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.

Attachment

Re: Postgresql takes more time to update

From
"Scott Marlowe"
Date:
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.


Attachment

Re: Postgresql takes more time to update

From
"Peter Koczan"
Date:

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
 


Re: Postgresql takes more time to update

From
"Suresh Gupta VG"
Date:

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.

Attachment

Re: Postgresql takes more time to update

From
"Peter Koczan"
Date:


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

Re: Postgresql takes more time to update

From
"Suresh Gupta VG"
Date:

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.

Attachment

Re: Postgresql takes more time to update

From
"Suresh Gupta VG"
Date:

 

 

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.

Attachment

Re: Postgresql takes more time to update

From
"Suresh Gupta VG"
Date:

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.

Attachment

Re: Postgresql takes more time to update

From
"Scott Marlowe"
Date:
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

Re: Postgresql takes more time to update

From
"Peter Koczan"
Date:


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

Re: Postgresql takes more time to update

From
"Suresh Gupta VG"
Date:

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.

Attachment

Re: Postgresql takes more time to update

From
"Suresh Gupta VG"
Date:

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.

Attachment

Re: Postgresql takes more time to update

From
"Suresh Gupta VG"
Date:

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

Attachment

Re: Postgresql takes more time to update

From
"Peter Koczan"
Date:

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.