Re: 8.4 to 9.4 migration - recommended practices

Leonid Rozenblyum wrote:
> I'm working on migrating a single database from cluster of postgresql
> 8.4 db to 9.4.
>
> I'm using pg_dump & pg_restore from 9.4 version.
>
> I'm using a limited account to dump/restore the data (NOT postgres user)
>
> During pg_restore I get 2 kinds of errors:
>
> 1)
> Lack of permissions:
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 1070; 2612 37119
> PROCEDURAL LANGUAGE plpgsql postgres
> pg_restore: [archiver (db)] could not execute query: ERROR: must be
> owner of language plpgsql
> Command was: CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;

That is no problem.

PL/pgSQL is now automatically installed during CREATE DATABASE and
belongs to the superuser.

> 2) Absense of a function:
>
> pg_restore: [archiver (db)] could not execute query: ERROR: function
> public.plpgsql_call_handler() does not exist
> Command was: ALTER FUNCTION public.plpgsql_call_handler() OWNER TO postgres;

Again, that's part of PL/pgSQL:

test=> SELECT * FROM pg_pltemplate WHERE tmplname = 'plpgsql';
-[ RECORD 1 ]-+-----------------------
tmplname | plpgsql
tmpltrusted | t
tmpldbacreate | t
tmplhandler | plpgsql_call_handler
tmplinline | plpgsql_inline_handler
tmplvalidator | plpgsql_validator
tmpllibrary | $libdir/plpgsql
tmplacl |

It looks like the functions were installed in schema "public" in the
8.4 database.

What I wonder is why there is no "CREATE FUNCTION public.plpgsql_call_handler()"
in the database dump.

> Are both kinds of errors worth to worry about?

I would assume that your new database is ok.

At worse, you'll have a few old and obsolete functions sitting around
in schema "public".

Testing is never a mistake though.

Yours,
Laurenz Albe