VACUUMing sometimes increasing database size / sometimes crashing it

Nitin Verma wrote:

Were these bugs fixed by 7.3.2, if not what version should I look for?

http://archives.postgresql.org/pgsql-admin/2001-06/msg00005.php
http://archives.postgresql.org/pgsql-hackers/2000-04/msg00083.php

Ahm... 7.3.2 is *very* outdated. The current version of postgresql is
8.1.4.

The mails you linked are from the year 2001 (!), and concern 6.5 (!!) -
A lot of things have changed in postgres since then ;-)

None of the problems discussed there should trouble postgres anymore, if
you use a at least remotely recent version (Say, >= 8.0, or 7.4 *at*
*the* *very* *least*).

greetings, Florian Pflug

On Jun 15, 2006, at 1:16 PM, Florian G. Pflug wrote:

Nitin Verma wrote:

Were these bugs fixed by 7.3.2, if not what version should I look
for?
http://archives.postgresql.org/pgsql-admin/2001-06/msg00005.php
http://archives.postgresql.org/pgsql-hackers/2000-04/msg00083.php

Ahm... 7.3.2 is *very* outdated. The current version of postgresql is
8.1.4.

The mails you linked are from the year 2001 (!), and concern 6.5
(!!) - A lot of things have changed in postgres since then ;-)

None of the problems discussed there should trouble postgres
anymore, if
you use a at least remotely recent version (Say, >= 8.0, or 7.4
*at* *the* *very* *least*).

And if you are going to stick with 7.3, at least get the latest
version of it.

As for searching for bugs... http://archives.postgresql.org/pgsql-bugs/
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Will 7.3.2 Dump made up of copies using pg_dump import without any migration
to 8.0+? What I need isn't a once process and will go as a automated script,
in a way that user will not even get to know (if he isn't reading that logs)
Database version changed. Considering that even a remote problem in export
and import across versions may hit. So please let me know all the do's and
don'ts... or the pointers to those.

-----Original Message-----
From: Jim Nasby [mailto:jnasby@pervasive.com]
Sent: Friday, June 16, 2006 1:54 AM
To: Florian G.Pflug
Cc: Nitin Verma; pgsql-general@postgresql.org
Subject: Re: [GENERAL] VACUUMing sometimes increasing database size /
sometimes

On Jun 15, 2006, at 1:16 PM, Florian G. Pflug wrote:

Nitin Verma wrote:

Were these bugs fixed by 7.3.2, if not what version should I look
for?
http://archives.postgresql.org/pgsql-admin/2001-06/msg00005.php
http://archives.postgresql.org/pgsql-hackers/2000-04/msg00083.php

Ahm... 7.3.2 is *very* outdated. The current version of postgresql is
8.1.4.

The mails you linked are from the year 2001 (!), and concern 6.5
(!!) - A lot of things have changed in postgres since then ;-)

None of the problems discussed there should trouble postgres
anymore, if
you use a at least remotely recent version (Say, >= 8.0, or 7.4
*at* *the* *very* *least*).

And if you are going to stick with 7.3, at least get the latest
version of it.

As for searching for bugs... http://archives.postgresql.org/pgsql-bugs/
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

"Nitin Verma" <nitinverma@azulsystems.com> writes:

Will 7.3.2 Dump made up of copies using pg_dump import without any migration
to 8.0+? What I need isn't a once process and will go as a automated script,
in a way that user will not even get to know (if he isn't reading that logs)
Database version changed. Considering that even a remote problem in export
and import across versions may hit. So please let me know all the do's and
don'ts... or the pointers to those.

It will very likely have problems. The usual recommended procedure is
to use the version of pg_dump that comes with the PG that you're
upgrading *to* against the old database; e.g. you'd use the 8.0+
pg_dump and tell it to connect to the 7.3.2 database.

You should really upgrade from 7.3.2, at least to the latest point
release in the 7.3 series, and have a plan to go to 8.0 or 8.1,
because 7.3 won't be supported for that much longer (if it even is
right now).

-Doug

Douglas McNaught wrote:

"Nitin Verma" <nitinverma@azulsystems.com> writes:

Will 7.3.2 Dump made up of copies using pg_dump import without any migration
to 8.0+? What I need isn't a once process and will go as a automated script,
in a way that user will not even get to know (if he isn't reading that logs)
Database version changed. Considering that even a remote problem in export
and import across versions may hit. So please let me know all the do's and
don'ts... or the pointers to those.

It will very likely have problems. The usual recommended procedure is
to use the version of pg_dump that comes with the PG that you're
upgrading *to* against the old database; e.g. you'd use the 8.0+
pg_dump and tell it to connect to the 7.3.2 database.

Note that even if your 7.3 dump restores fine on 8.1 (How likely that is
depends on the complexity of your schema), you might still experience
problems, if your application depends on things that changed between 7.3
and 8.1. Postgres tends to become more strict with every release, so
there are things you got away with in 7.3 which now cause an error message.

So, you shouldn't upgrade database version "behind a users back". You'll
need to test his applikations against the new version, or at least tell
him that there might be problems.

You should really upgrade from 7.3.2, at least to the latest point
release in the 7.3 series, and have a plan to go to 8.0 or 8.1,
because 7.3 won't be supported for that much longer (if it even is
right now).

If 8.0 or 8.1 is too big a step for you, you could consider moving to
7.4. I don't know if 7.3 already supported schemas, but if it did, then
the chance of breakage is a lot smaller if you switch to 7.4 compared to
switching to 8.1. OTOH, one day 7.4 will be unsupported too, and then
you'll need to switch anyway.

greetings, Florian Pflug

if your application depends on things that changed between 7.3 and 8.1. >>

Postgres tends to become more strict with every release, so

there are things you got away with in 7.3 which now cause an error
message.

Do we have change lists where I can see all the changes between 7.3 and 8.1,
may be release by release?

-----Original Message-----
From: Florian G. Pflug [mailto:fgp@phlo.org]
Sent: Friday, June 16, 2006 4:48 PM
To: Douglas McNaught
Cc: Nitin Verma; Jim Nasby; pgsql-general@postgresql.org
Subject: Re: [GENERAL] VACUUMing sometimes increasing database size /
sometimes

Douglas McNaught wrote:

"Nitin Verma" <nitinverma@azulsystems.com> writes:

Will 7.3.2 Dump made up of copies using pg_dump import without any

migration

to 8.0+? What I need isn't a once process and will go as a automated

script,

in a way that user will not even get to know (if he isn't reading that

logs)

Database version changed. Considering that even a remote problem in export
and import across versions may hit. So please let me know all the do's and
don'ts... or the pointers to those.

It will very likely have problems. The usual recommended procedure is
to use the version of pg_dump that comes with the PG that you're
upgrading *to* against the old database; e.g. you'd use the 8.0+
pg_dump and tell it to connect to the 7.3.2 database.

Note that even if your 7.3 dump restores fine on 8.1 (How likely that is
depends on the complexity of your schema), you might still experience
problems, if your application depends on things that changed between 7.3
and 8.1. Postgres tends to become more strict with every release, so
there are things you got away with in 7.3 which now cause an error message.

So, you shouldn't upgrade database version "behind a users back". You'll
need to test his applikations against the new version, or at least tell
him that there might be problems.

You should really upgrade from 7.3.2, at least to the latest point
release in the 7.3 series, and have a plan to go to 8.0 or 8.1,
because 7.3 won't be supported for that much longer (if it even is
right now).

If 8.0 or 8.1 is too big a step for you, you could consider moving to
7.4. I don't know if 7.3 already supported schemas, but if it did, then
the chance of breakage is a lot smaller if you switch to 7.4 compared to
switching to 8.1. OTOH, one day 7.4 will be unsupported too, and then
you'll need to switch anyway.

greetings, Florian Pflug

Nitin Verma wrote:

if your application depends on things that changed between 7.3 and 8.1. >>

Postgres tends to become more strict with every release, so

there are things you got away with in 7.3 which now cause an error
message.

Do we have change lists where I can see all the changes between 7.3 and 8.1,
may be release by release?

Try the manuals where there are version-by-version details of changes in
the release-notes.

http://www.postgresql.org/docs/8.1/static/release.html

--
Richard Huxton
Archonet Ltd

Thanx so much which would really help

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Friday, June 16, 2006 6:29 PM
To: Nitin Verma
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] VACUUMing sometimes increasing database size /
sometimes

Nitin Verma wrote:

if your application depends on things that changed between 7.3 and 8.1.

Postgres tends to become more strict with every release, so

there are things you got away with in 7.3 which now cause an error
message.

Do we have change lists where I can see all the changes between 7.3 and

8.1,

may be release by release?

Try the manuals where there are version-by-version details of changes in
the release-notes.

http://www.postgresql.org/docs/8.1/static/release.html

--
Richard Huxton
Archonet Ltd

$ ls -al pgsqldb/pg_xlog
total 32816
drwx------ 2 nitinverma root 4096 Jun 16 19:53 .
drwx------ 6 nitinverma root 4096 Jun 16 19:33 ..
-rw------- 1 nitinverma root 16777216 Jun 16 20:08 0000000000000001
-rw------- 1 nitinverma root 16777216 Jun 16 19:45 0000000000000002

Looks like if a WAL file is created even vacuum can't reclaim the space. Is
that the root cause behind DB bloating with 7.3.2?

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Friday, June 16, 2006 6:29 PM
To: Nitin Verma
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] VACUUMing sometimes increasing database size /
sometimes

Nitin Verma wrote:

if your application depends on things that changed between 7.3 and 8.1.

Postgres tends to become more strict with every release, so

there are things you got away with in 7.3 which now cause an error
message.

Do we have change lists where I can see all the changes between 7.3 and

8.1,

may be release by release?

Try the manuals where there are version-by-version details of changes in
the release-notes.

http://www.postgresql.org/docs/8.1/static/release.html

--
Richard Huxton
Archonet Ltd

In response to "Nitin Verma" <nitinverma@azulsystems.com>:

$ ls -al pgsqldb/pg_xlog
total 32816
drwx------ 2 nitinverma root 4096 Jun 16 19:53 .
drwx------ 6 nitinverma root 4096 Jun 16 19:33 ..
-rw------- 1 nitinverma root 16777216 Jun 16 20:08 0000000000000001
-rw------- 1 nitinverma root 16777216 Jun 16 19:45 0000000000000002

Looks like if a WAL file is created even vacuum can't reclaim the space. Is
that the root cause behind DB bloating with 7.3.2?

All versions of Postgresql generate WAL logs. This is not bloat, this is
space required for normal operation of the database system.

I believe the defaults are to create 4 files, 16M each, and then rotate
through them. If you've only got two files so far, this must be a
fairly new installation.

http://www.postgresql.org/docs/8.1/interactive/wal-configuration.html

--
Bill Moran
Collaborative Fusion Inc.