A never vacuumed PostgreSQL 7.0.3 database getting sluggish

I have a ~5 year old  PostgreSQL 7.0.3 installation, which I don't believe has ever had the tender lover care of a database administrator, and yet it is reasonably complex. Performance has been reported to have deteriorated over the years, partly no doubt because if the increase in size of the data set.

Before I get into looking how the schema might be rationalised by (say) spooling data off non-current data into archives, I want to look into ways to improve performance, while leaving things intact. The database can afford to have some downtime over night say.

I looked briefly at taking a dump from the database and importing it into PostgreSQL 8, but there were oodles of errors thrown up, which I'd need to pick through, so I wanted to see if I could improve matters with the existing 7.0.3.

Here are my questions:

(1) Am I reasonably safe vacuuming it now, or would I be wiser to dump and regenerate it from scratch?
(2) Am I reasonably safe setting up a daily vacuum on cron thereafter?
(3) Is there anything else I ought to consider?
LVL 17
Who is Participating?
gheistConnect With a Mentor Commented:
1) Yes, but dumping database and importing into 8.1 would be wiser, once you have time for database downtime for whole vacuum.
2) Yes, but 7.0 blocks access to db while at vacuuming, 8.1 does not
3) http://www.postgresql.org/ - A minimum is 7.3 to be somewhat supported

Please post select version(); just for statistical purposes.

I'd suggest going with 8.1 - first identifying the migration problems, then solving them.

Actually only next version imports previous, i.e 7.0->7.1->7.2->7.3->7.4->8.0->8.1
So "correct" migration will be too time-consuming.

I'd suggest dump database, recreate users in new place with 8.1 , import database, and let it work for next five years.
This leaves old database work while you are at improvement.

Post here with errors you encounter migrating.
fridomConnect With a Mentor Commented:
1) probably yes, but better do a dump neverthless. If things go wrong you have at least one backup
2) yes
3) 7.0 is not the latest in the 7.x branches. You should at least update to 7.4.7 IMHO (or why not going or the 8.0 or 8.1 versions.

Now you tell us that there are errors with migration to 8.1, but well you did not tell which ones, and you don't have told us how you dumpes the old database

rstaveleyAuthor Commented:
Thanks, fridom and gheist. I have an 8.0.7 installation that I'm tring to reinstate the dump onto now.

> Please post select version(); just for statistical purposes.

My 7.0.3 installation is: "PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.96". It is on Red Hat Linux release 7.3 (Valhalla) (with neglected up2date)

My 8.0.7 installation is: "PostgreSQL 8.0.7 on x86_64-redhat-linux-gnu, compiled by GCC x86_64-redhat-linux-gcc (GCC) 4.0.2 20051125 (Red Hat 4.0.2-8)". It is on Fedora Core release 4 (Stentz).

Reinstating hasn't been as bad as I initially feared. I fixed a bunch of user errors (changed a bunch of \connect statments to get users right) and handled some invalid UNICODE byte sequences by making the encoding of the database latin1 to match that of the original database.

I'm now left with the following:

(A) Errors about carriage returns
      ERROR:  literal carriage return found in data
      HINT:  Use "\r" to represent carriage return.
  - Do you reckon I need to do these by hand (i.e. perl script) or is there a PostgreSQL trick I should use for these?

(B) Two plpgsql errors
   (i) ERROR:  function "plpgsql_call_handler" already exists with same argument types
   (ii) ERROR:  unterminated - CONTEXT:  compile of PL/pgSQL function "uploaduser" near line 31
  - Are there known problems with upgrading plpgsql functions? I took a look at the functions with Navicat and saw no definitions. Has the formatting been chaged for these?

Sorry this question has drifted from its original subject.
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

rstaveleyAuthor Commented:
Increasing points for the follow-on.
Stop increasing points, thank you.

My points against Linux:
ext2 will lose data on power failure
ext3,jfs,xfs etc will do every write two times.

So I recommend FreeBSD, it's filesystem does commit when program asks and does not lose commited data on poweroff.

(a) use pg_dump -Fc to use binary format of dump
(b) http://www.postgresql.org/docs/8.1/interactive/sql-createlanguage.html
rstaveleyAuthor Commented:
Very interesting the points you raised about the file system, I have ext3 on both of the Linux boxes. I have a trusty old FreeBSD (4.9?) box at home, which I am very fond of, but the database is a citizen amongst other processes which would require some work to be ported.

I'll try the binary dump, but I'm surprised to hear that it is more portable than a text dump?!

With reference to (b) the language is created already. Or was I missing something in the link? Or was that a cue to RTFM ? :-)
Now FreeBSD is at 6.0 ... but still same from admins view as 4.9.

(a) strange or not - but binary dump is portable upwards. i.e if you want to downgrade it will not work.

(b) then you have to make us rewrite one simple procedure/function. Sure post refered to RTFM thing, but since you already did that, i cannot deny anymore there is a problem experts have to solve.
What is you point against using Linux. What do you think do the extended file system offer? You suggest a two fold migration
PSQL 7.x -> 8.1
and Linux -> FreeBSD in the same step

Very poor idea.

The plpsql problem is that there seems to be a
CREATE FUNCTION plpgsql_call_handler

And this thing is included with more then one table so you may have to replace it with

I would not just dump in binary format. Dump in text format also. Text format can be handeled by every tools down to an editor. If you do not need the text dump fine but what if something get's wrong with the binary dump?

rstaveleyAuthor Commented:
Yes, I'll be hanging in with Linux. FreeBSD would be too much change.

I see the text dump has this:

\connect - postgres
CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';
\connect - fred

I now see that plpgsql_call_handler is needed to provide plpgsql language support from http://www.faqs.org/docs/ppbook/c19610.htm . With these lines corrected I do not need to create the language prior to importing the dump. My Fedora system with the PostgreSQL 8.0 installation has plpgsql.so in /usr/lib64/pgsql/plpgsql.so, so I just need to change the path. Aside from being able to change users, this is a plus point of being able to keep with the text file format - though I guess I could symlink and hang in with the binary format if needs be.

Sorry - I'm still dithering.

gheist suggests the binary format to preserve the format of the plpgsql functions. This would be very handy, assuming it works. There are a hundred or so of them in this database, which I'd avoid putting in by hand.

fridom suggests dumping in the text format *also*. Is that as a fall-back in case the binary dump fails, or is it possible to reinstate a database using both dumps? If so, how would I be able to mix them?
It depends...

I'd suggest using binary and do pg_restore with -r switch - i.e when importing sort operations - create schema, constraints and import data after. maybe old pg_dump messes something up.

rstaveleyAuthor Commented:
What does the -r switch do? I can't see it at http://www.postgresql.org/docs/8.0/interactive/app-pgrestore.html or in `pg_restore --help`

Version: pg_restore (PostgreSQL) 8.0.7
rstaveleyAuthor Commented:
OK, I've reached a resolution for this now.

Vacuuming took very little time and was successful. Thanks for the reassurance.

With gheist and fridom's kind encouragement I persevered with an import into Postgres 8.0, and the problems fell away with perseverence.

For the sake of the EE PAQ database, here's the drill I now have for migrating my live 7.0.3 database to 8.0.7, when I bytes the bullet and upgrade. The database's name is morrell:

(1) Create a morrell database on my 8.0.7 installation with encoding latin1 and plpgsql support. I needed the latin1 encoding, because the original database was latin1 (I suspect that this is the 7.0.3 default) and it contains French accents in it, which throw up errors line "ERROR:  invalid byte sequence for encoding "UNICODE": 0xe27465" when รข is encountered. I created the language externally, because the create language in the 7.0.3 dump had paths specific to that installation in it (specifically the whereabouts of the .so file).

      -bash-3.00$ dropdb morrell;createdb -E latin1 morrell;createlang plpgsql morrell

(2) Take a snap-shot from the 7.0.3 database (my snap shot was prior to the vacuum, to be on the safe side).

      pg_dump morrell > morrell.database.pre-vacuum.sql

(3) Clean carriage returns replacing them with backslash followed by the carriage return, because I read that release 7.4 disallowed carriage returns unless they are escaped - see http://www.postgresql.org/docs/8.1/static/release-7-4.html - when I didn't do this I was getting messages saying ERROR:  literal carriage return found in data. HINT:  Use "\r" to represent carriage return. I could have converted to \r, but I chose to convert to \^M for maximum transparency.

      # ^M is generated at the bash prompt using Ctrl+V followed by Ctrl+M
      sed 's/^M/\\^M/' morrell.database.pre-vacuum.sql > x.sql

(3) I needed to comment out all \connect lines (because I'm using one all-powerful user for everything).

      sed 's/^\\connect/-- \\connect/' x.sql > x2.sql;mv x2.sql x.sql

(4) Comment out the dump's create lang code, because it is already created in (1) and the dumps code is system-specific.

      sed 's/CREATE FUNCTION \"plpgsql_call_handler\"/-- CREATE FUNCTION \"plpgsql_call_handler\"/' x.sql > x2.sql;mv x2.sql x.sql

(5) A hand-edit was required for one final error, in which a plpgsql function compares a string with '' to see if it is empty. I needed to change '' to \'\'. The 7.0.3 dump does not escape the single quotes. Happily there was only of of these. I didn't want to use sed/perl for this

      IF (paddleNumber<> \'\') THEN

(6) I the populated the 8.0.7 database with no reported errors

      psql morrell < x.sql 2> stderr.log > stdout.log

It is looking good, but I'm disappointed not to be able to see my plpgsql functions listed in Navicat. That may be a Navicat problem (I'm not very familiar with it yet), or it may be that the functions need to be reformatted to conform to PostgreSQL and/or Navicat conventions. I'll run some tests now to see if they work - the function code is certainly in there if I look at the data dumped from...

      pg_dump morrell > morrell.database.post-import.sql
Well I think you did it right, the steps taken sound reasonable to me. Good luck
rstaveleyAuthor Commented:
Thanks for the encouragement, Friedrich, and all your help both of you.
You can practice your procedure couple of times before altering working environment.

You can avoid most vacuuming so:

do >
vacuum full analyze verbose;
record the report
repeat after a week

Now count freed pages in second report and record doubled sum into postgresql.conf

max_fsm_pages = 80000

I am not sure about 7.0 having that setting, etc etc. so all the pressure to actualize databse version.
As far as I know for sure
7.2, 7.3 - you have to tune max_fsm_pages to avoid lengthy database blocking
7.4 - You can tune - fsm page pointer is 6 bytes , vacuum does not block
8.0+ - It will be hard to find if/when tuning is needed.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.