Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iBF7e-00012D-GY for pgsql-admin@arkaria.postgresql.org; Fri, 20 Sep 2019 09:22:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iBF7d-0007S8-Cg for pgsql-admin@arkaria.postgresql.org; Fri, 20 Sep 2019 09:22:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iBF7c-0007Rg-Ub for pgsql-admin@lists.postgresql.org; Fri, 20 Sep 2019 09:22:37 +0000 Received: from mail-wm1-x342.google.com ([2a00:1450:4864:20::342]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iBF7U-0003r0-3o for pgsql-admin@lists.postgresql.org; Fri, 20 Sep 2019 09:22:33 +0000 Received: by mail-wm1-x342.google.com with SMTP id 5so1639813wmg.0 for ; Fri, 20 Sep 2019 02:22:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=zunibal-com.20150623.gappssmtp.com; s=20150623; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-transfer-encoding:content-language; bh=RLnBVwp65sg7GoqGPSjgANKeh/bhGmY3o2C0TzCN72U=; b=rOFp2obGLfk2OhlXgw0+D9ykiB7esE1L1Ix8Jqr7tLwoxZVVrwOe52jWwjY1eJU0rJ nxqcqg3uCoHOLLptYy7+O0IAqUpSqDpIJN9pzZjEy5C0YewTZqtkWm0tF7+TQujMONRO y+cDuhqQzJ72jR52qo1j1Vls5LyhuY41UIqpP/WayrVaXaaF9ssgkDkpAWj6i8/RBtAW xf1+KA6KuXLk1wz+uUlZOXLqKRhVJVK0ptv0q6n7ykC9hRtlw6J8znQGvhbm0+VrcU4r VaOFkyDF6wkrLLTfkL/5R5kqlYstJSipQKtINR09O0XMzU5hksi0wMcfuTF9S1ab63H5 5h2A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-transfer-encoding :content-language; bh=RLnBVwp65sg7GoqGPSjgANKeh/bhGmY3o2C0TzCN72U=; b=ewyNbOPGwWtfoOJVnZAbvJexZA73pBHQEyqPcslCSUAv5oaCjMoTHanssXB38etm5g vg+aW8dq+LQpmD2TVf6Xzn70MYLr5r5b580Fkh+sC2v4WaAYO+OmgVqCJ3hv81L1H+ol cbEPwPeFPJgDC7s7NfYlCRpoE+nqoB7Ci9WIR/SIx0/+wI2oNJsGAhEh5TpJUau33vDG eYFw3figH0wDwMD9eT7VRTb8T3kCo7d9R7rH0VgAF7/n7g6wnNmeB28tYDLQ/wQ6/cg8 VrjeomKiLgrn6QGha3oRY5RUVFuf/pKo/JNQ0MEIQVxeLRwxytErqvHe4z9TrxKDTFHd UMEA== X-Gm-Message-State: APjAAAX95023vx/qucEaSFwhNCDN2Gf0ASY+Ka/Y48ZDWZ7T/dxGozD5 0MXEisQFNGjTIYLRcj7IY0Oc69b/Qrw= X-Google-Smtp-Source: APXvYqyRDMA9jHkNJ5rIsizhQoWeTu+QMlOIXYwHwTRdHTTHsXO1kw8y3XcmPgT91CqO/hwWFyXRDQ== X-Received: by 2002:a1c:98c8:: with SMTP id a191mr2580963wme.17.1568971346745; Fri, 20 Sep 2019 02:22:26 -0700 (PDT) Received: from [192.168.3.150] ([176.222.91.85]) by smtp.gmail.com with ESMTPSA id j1sm2003252wrg.24.2019.09.20.02.22.25 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 20 Sep 2019 02:22:26 -0700 (PDT) Subject: Re: Pg_upgrade error could not load library from 7.14/8.4 to 10.10 version To: pgsql-admin@lists.postgresql.org References: <4519fdad-d747-cd39-f6a2-33e7db1f3324@matrix.gatewaynet.com> <22046.1568901429@sss.pgh.pa.us> From: Ekaterina Amez Message-ID: Date: Fri, 20 Sep 2019 11:22:25 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.8.0 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Hi Achilleas, El 20/9/19 a las 9:26, Achilleas Mantzios escribió: > Just tested in my 11 and 10 : > dynacom=# select proname,probin,pronamespace from pg_proc where > proname='plpgsql_call_handler'; >        proname        |             probin              | pronamespace > ----------------------+---------------------------------+-------------- >  plpgsql_call_handler | $libdir/plpgsql |           11 >  plpgsql_call_handler | /usr/local/pgsql/lib/plpgsql.so | 2200 > (2 rows) > > with 2200 being the public namespace/schema. > > I my case, in every postgresql installation since 2001, always libdir > was /usr/local/pgsql/lib/ , so maybe this problem could not be > manifested. Did you check to see if you have the plpgsql_call_handler > defined in two schemas as well? > > -- > Achilleas Mantzios > IT DEV Lead > IT DEPT > Dynacom Tankers Mgmt I've restored template1 in v8.4, and created again the db where I will be restoring v7.14 backup newdb=# select proname,probin,pronamespace from pg_proc where proname='plpgsql_call_handler';  proname | probin | pronamespace ---------+--------+-------------- (0 filas) And in v10.10 (which is still an empty installation) template1=# select proname,probin,pronamespace from pg_proc where proname='plpgsql_call_handler';        proname        |     probin      | pronamespace ----------------------+-----------------+--------------  plpgsql_call_handler | $libdir/plpgsql |           11 (1 row) After restoring backup in v8.4: newdb=# select proname,probin,pronamespace from pg_proc where proname='plpgsql_call_handler';        proname        |                 probin                 | pronamespace ----------------------+----------------------------------------+--------------  plpgsql_call_handler | $libdir/plpgsql |           11  plpgsql_call_handler | /usr/lib/postgresql/8.4/lib/plpgsql.so |         2200 (2 filas) As you said, 2200 is public namespace/schema and 11 is pg_catalog. At the beginning of the backup file I can find these sentences: CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler     LANGUAGE c     AS '/usr/lib/postgresql/8.4/lib/plpgsql.so', 'plpgsql_call_handler'; <-- I've changed this line to use the right path to plpgsql.so library CREATE PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; I guess these are the ones causing all of this. **What should be the best way to handle this situation?** Remove these lines and create the language explicitly when creating database? Or replace them with a create language sentence? Maybe something else? My final goal is migrate from 7.14 server to 8.4 server and after that (if I have an OK from the boss) upgrade 8.4 to the latest version that I can use. Server uses CentOS, and probably I won't be able to upgrade to v10 but I hope at least 9.5/9.6 will be available.