When upgrading between major releases (e.g. between 9.1.x and 9.2.x) it is necessary to create a new version of the database data directory, as PostgreSQL cannot run using the data files from another major version. Traditionally this was done by dumping and reloading all data, however as of PostgreSQL 9.0, the pg_upgrade utility enables binary upgrades of the data directory without the need to dump and restore.
Users of semi-automated binary upgrade processes (e.g. RPM or apt in Linux) should be particularly aware of this issue as they may attempt to automatically upgrade data.
Affects: PostgreSQL <= 7.4
By definition the superuser postgres requires access to all databases in a PostgreSQL cluster. However, each database may have a variety of schemas (see here for an introduction to PostgreSQL schemas) which are not automatically in the postgres user's search path.
This is particularly problematic when the default public schema has been removed and one of the external utilities such as createlang is used. If the schema is not in the postgres user's search path, createlang fails like this:
user@pc:~> createlang -U postgres plpgsql test createlang: language installation failed: ERROR: no schema has been selected to create in
It is not currently possible to specifiy a schema to createlang. The obvious workaround would be to change postgres' search path with ALTER USER:
test=# ALTER USER postgres SET search_path TO testschema; ALTER USER
However this may cause problems if other applications are expecting postgres to have a particular search path, especially one containing the default public schema. Unfortunately it is not possible to set a user's search path to include schemas not in the current database.
A safer workaround is to grab the SQL statements which createlang would execute (use the -e option), set the search path in a psql session and execute the relevant DDL (data definition language) statements there. The output with the -e option looks like this:
user@pc:~> createlang -e -U postgres plpgsql test SELECT oid FROM pg_language WHERE lanname = 'plpgsql'; SELECT oid FROM pg_proc WHERE proname = 'plpgsql_call_handler' AND prorettype = (SELECT oid FROM pg_type WHERE typname = 'language_handler') AND pronargs = 0; CREATE FUNCTION "plpgsql_call_handler" () RETURNS language_handler AS '$libdir/p lpgsql' LANGUAGE C; CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler";
and the CREATE statements should be executed like this:
user@pc:~> psql -U postgres test Welcome to psql 7.4.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit eg=# SET search_path TO testschema; SET eg=# CREATE FUNCTION "plpgsql_call_handler" () RETURNS language_handler AS '$lib dir/plpgsql' LANGUAGE C; CREATE FUNCTION eg=# CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler"; CREATE LANGUAGE eg=# \q user@pc:~>
Affects: PostgreSQL <= 7.4
Prior to version 8.0 pg_dump was not always able to produce database dumps which could be restored without manual intervention. This was particularly the case with complex foreign-key relationships where dependencies cannot be satisfied during sequential data restoration.
Beginning with PostgreSQL 8.0 all known issues have been fixed.