sql-info.de

Administration

Upgrades

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.

Superuser and Schemas

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:~>
      

pg_dump dependencies prior to 8.0

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.

pg_stat_activity and multiple statements

If a single PQexec call is used to send multiple, semi-colon separated statements to the backend, pg_stat_activity displays the entire query list in the query field, making it difficult to know which query is actually running. The pg_stat_statements extension is similarly affected.