Re: Heavy postgres process

On Wed, Sep 17, 2008 at 1:43 PM, Guido Barosio <gbarosio(at)gmail(dot)com> wrote:
> Getting to the latest 7.4 server involves the same as going to the
> latest 8.x server, AFAIK, if we take in consideration that 7.4.8
> requires a dump & restore (meaning downtime).

7.4.7 to 7.4.8 does NOT require a complete dump and restore. Been
there, done it and got the tshirt. There's a minor fix in the
catalogs that you can get by a single SQL command.

> I would rather go after the latest 8.x server
>
> 2 cents.

Me too. But the update to 7.4.21 or whatever the latest version is
does NOT require a dump reload, and unless you need that one line sql
fix, you don't even have to do that.

I quote the 7.4.8 release notes:

A dump/restore is not required for those running 7.4.X. However, it is
one possible way of handling two significant security problems that
have been found in the initial contents of 7.4.X system catalogs. A
dump/initdb/reload sequence using 7.4.8's initdb will automatically
correct these problems.

and further down

If you wish not to do an initdb, perform the following procedures
instead. As the database superuser, do:

BEGIN;
UPDATE pg_proc SET proargtypes[3] = 'internal'::regtype
WHERE pronamespace = 11 AND pronargs = 5
AND proargtypes[2] = 'cstring'::regtype;
-- The command should report having updated 90 rows;
-- if not, rollback and investigate instead of committing!
COMMIT;

Next, if you have installed contrib/tsearch2, do:

and so on.

> (It seems that Vivek works for Infosys, and that may explain the
> reason for an Oracle migration in the future. They are prolly plenty
> of Oracle DBA's working there, cheaper and reusable for sure if you
> think that often the kind of clients they handle are already
> oracle'ized )

especially if you can piggy back on someone else's oracle server
that's already running.