Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eibCY-0004dD-Dv for pgsql-admin@arkaria.postgresql.org; Mon, 05 Feb 2018 07:28:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eibCW-0007jW-SP for pgsql-admin@arkaria.postgresql.org; Mon, 05 Feb 2018 07:28:28 +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_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eibCW-0007ix-LK for pgsql-admin@lists.postgresql.org; Mon, 05 Feb 2018 07:28:28 +0000 Received: from susanne.redcor.ch ([178.63.103.72]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eibCO-0003G2-8c for pgsql-admin@lists.postgresql.org; Mon, 05 Feb 2018 07:28:27 +0000 Received: from [10.146.176.99] (hermes [46.140.116.238]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) (Authenticated sender: robert@redcor.ch) by susanne.redcor.ch (Postfix) with ESMTPSA id 4A317A01D5 for ; Mon, 5 Feb 2018 08:28:19 +0100 (CET) From: robert Subject: Re: pg_restore has problems with restoring sequences. To: pgsql-admin@lists.postgresql.org References: <129cd073-60bf-96db-e129-24bd9a37319e@redcor.ch> <15766.1517758986@sss.pgh.pa.us> <18202.1517768169@sss.pgh.pa.us> Message-ID: Date: Mon, 5 Feb 2018 08:28:18 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.6.0 MIME-Version: 1.0 In-Reply-To: <18202.1517768169@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------2655B327DD62E68A1AF812E9" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------2655B327DD62E68A1AF812E9 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit thanks, On 04.02.2018 19:16, Tom Lane wrote: > robert writes: >> pg_restore: [archiver (db)] Error while PROCESSING TOC: >> pg_restore: [archiver (db)] Error from TOC entry 1068; 1259 142848 SEQUENCE >> change_email_domain_id_seq odoo >> pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or >> near "AS" >> LINE 2:     AS integer >>             ^ >>     Command was: CREATE SEQUENCE change_email_domain_id_seq >>     AS integer >>     START WITH 1 >>     INCREMENT BY 1 >>     NO MINVALUE >>     NO MAXVALU... > Hm. What this suggests is that you're using a pg_dump that is newer than > the server that you're trying to restore to. The "AS datatype" clause > in CREATE SEQUENCE is new in v10. > > The general rule for pg_dump version choice is "use the newest version you > can, but not newer than the server you're going to restore to". Otherwise > you run into problems like this of the dump containing syntax the target > server doesn't understand. > > regards, tom lane > I am using postgres 10 on the server where I create the dump and 10.1 where i try to restore. however,  the box where I try to restore uses root@lappi:/home/robert/odoo_instances# pg_restore -V pg_restore (PostgreSQL) 9.5.10 when I check with : root@lappi:/home/robert/odoo_instances# apt-cache policy postgresql-client postgresql-client:   Installed: (none)   Candidate: 10+189.pgdg17.04+1   Version table:      10+189.pgdg17.04+1 500         500 http://apt.postgresql.org/pub/repos/apt zesty-pgdg/main amd64 Packages         500 http://apt.postgresql.org/pub/repos/apt zesty-pgdg/main i386 Packages      9.6+184ubuntu1.1 500         500 http://ch.archive.ubuntu.com/ubuntu artful-updates/main amd64 Packages         500 http://ch.archive.ubuntu.com/ubuntu artful-updates/main i386 Packages         500 http://security.ubuntu.com/ubuntu artful-security/main amd64 Packages         500 http://security.ubuntu.com/ubuntu artful-security/main i386 Packages      9.6+184ubuntu1 500         500 http://ch.archive.ubuntu.com/ubuntu artful/main amd64 Packages         500 http://ch.archive.ubuntu.com/ubuntu artful/main i386 Packages and when I try to reinstall apt install postgresql-client-common i get a message, that it is already the newest version. ah, now I see something:$which pg_restore points to:  /usr/bin/pg_restore -> ../share/postgresql-common/pg_wrapper what is this pg_wrapper?? thanks --------------2655B327DD62E68A1AF812E9 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

thanks,



On 04.02.2018 19:16, Tom Lane wrote:
robert <robert@redcor.ch> writes:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1068; 1259 142848 SEQUENCE 
change_email_domain_id_seq odoo
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or 
near "AS"
LINE 2:     AS integer
             ^
     Command was: CREATE SEQUENCE change_email_domain_id_seq
     AS integer
     START WITH 1
     INCREMENT BY 1
     NO MINVALUE
     NO MAXVALU...

Hm.  What this suggests is that you're using a pg_dump that is newer than
the server that you're trying to restore to.  The "AS datatype" clause
in CREATE SEQUENCE is new in v10.

The general rule for pg_dump version choice is "use the newest version you
can, but not newer than the server you're going to restore to".  Otherwise
you run into problems like this of the dump containing syntax the target
server doesn't understand.

			regards, tom lane
I am using postgres 10 on the server where I create the dump
and 10.1 where i try to restore.
however,  the box where I try to restore uses
root@lappi:/home/robert/odoo_instances# pg_restore -V
pg_restore (PostgreSQL) 9.5.10

when I check with :
root@lappi:/home/robert/odoo_instances# apt-cache policy postgresql-client
postgresql-client:
  Installed: (none)
  Candidate: 10+189.pgdg17.04+1
  Version table:
     10+189.pgdg17.04+1 500
        500 http://apt.postgresql.org/pub/repos/apt zesty-pgdg/main amd64 Packages
        500 http://apt.postgresql.org/pub/repos/apt zesty-pgdg/main i386 Packages
     9.6+184ubuntu1.1 500
        500 http://ch.archive.ubuntu.com/ubuntu artful-updates/main amd64 Packages
        500 http://ch.archive.ubuntu.com/ubuntu artful-updates/main i386 Packages
        500 http://security.ubuntu.com/ubuntu artful-security/main amd64 Packages
        500 http://security.ubuntu.com/ubuntu artful-security/main i386 Packages
     9.6+184ubuntu1 500
        500 http://ch.archive.ubuntu.com/ubuntu artful/main amd64 Packages
        500 http://ch.archive.ubuntu.com/ubuntu artful/main i386 Packages

and when I try to reinstall
apt install postgresql-client-common
i get a message, that it is already the newest version.

ah, now I see something:$which pg_restore points to:
 /usr/bin/pg_restore -> ../share/postgresql-common/pg_wrapper

what is this pg_wrapper??

thanks


--------------2655B327DD62E68A1AF812E9--