Обсуждение: Postgresql takes more time to update

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

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.

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.

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

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.

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

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

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

Scott, Thanks for your response.

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

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

Peter, Thanks for your response.

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.

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

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