Upgrades and Error Messages

Hi,

~$ cat upgrade.sh
#!/bin/bash

/usr/lib/postgresql/9.3/bin/pg_upgrade \ -b /usr/lib/postgresql/9.1/bin/  \ -B /usr/lib/postgresql/9.3/bin/  \ -d
/var/lib/postgresql/9.1/main \ -D /var/lib/postgresql/9.3/main  \ -o ' -c
config_file=/etc/postgresql/9.1/main/postgresql.conf'\ -O ' -c config_file=/etc/postgresql/9.3/main/postgresql.conf'
 

$ ./upgrade.sh
...
Adding support functions to new cluster                     ok
Restoring database schemas in the new cluster postgres
*failure*

Consult the last few lines...

$ tail pg_upgrade_dump_11955.log
pg_restore: setting owner and privileges for ACL plperlu
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1955; 0 0 ACL plperlu postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  language
"plperlu" is not trusted
HINT:  Only superusers can use untrusted languages.   Command was: REVOKE ALL ON LANGUAGE "plperlu" FROM PUBLIC;
REVOKE ALL ON LANGUAGE "plperlu" FROM "postgres";
GRANT ALL ON LANGUAGE "plper...

---

At this point, I restarted the process. Then, in both databases (9.1 and 9.3):

CREATE OR REPLACE TRUSTED LANGUAGE plperlu;
UPDATE pg_language set lanpltrusted = true where lanname='plperlu';
REVOKE ALL ON LANGUAGE "plperlu" FROM PUBLIC;

These worked as expected.

When I re-ran the upgrade script, the same error appeared.

--

Note: It would be nice if any databases and users that were created
during the upgrade process were deleted on error. (I keep forgetting
to delete them from the 9.3 database, which results in another error.)

--

Speaking of errors, most of the errors returned from PostgreSQL are
technically accurate but provide the user with no clue as to how to
diagnose the problem. Here's a simple error message as an example:

pg_upgrade: cannot be run as root

Instead of stating what has gone wrong, state how to correct it. For
example, the pg_upgrade can easily determine the owner of the file and
can likely determine the user needed to run the program. This
information could be provided:

pg_upgrade: run as postgres (cannot be run as root)

Or even:

pg_upgrade: cannot be run as root (must be run as database owner)

Or:

pg_upgrade: ERROR 15363: Cannot be run as root
pg_upgrade: See https://postgresql.org/errors/15363/

These types of error messages exist throughout the entire code base
and make PostgreSQL a little frustrating to work with when anything
goes awry. Here's another example:

pg_restore: [archiver (db)] could not execute query: ERROR:  language
"plperlu" is not trusted

What do I need to do to make it trusted? Telling me "Only superusers
can use untrusted languages" isn't sufficient. Maybe point to a WIKI
that describes the problem in detail and offers ways to diagnose the
issue a bit deeper? For my purposes, as far as I can tell, the
languages *are* trusted in both databases, but I have no idea where to
go from here.

Meanwhile, if anyone knows how to solve/diagnose this issue, I'd
appreciate the help.

T X