Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tJKtu-002HN9-H5 for pgsql-general@arkaria.postgresql.org; Thu, 05 Dec 2024 23:04:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tJKtr-008z5N-08 for pgsql-general@arkaria.postgresql.org; Thu, 05 Dec 2024 23:04:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tJKtq-008z5E-Jf for pgsql-general@lists.postgresql.org; Thu, 05 Dec 2024 23:04:47 +0000 Received: from mail-ot1-x333.google.com ([2607:f8b0:4864:20::333]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tJKto-001G9D-0H for pgsql-general@postgresql.org; Thu, 05 Dec 2024 23:04:46 +0000 Received: by mail-ot1-x333.google.com with SMTP id 46e09a7af769-71d56fe8240so830925a34.0 for ; Thu, 05 Dec 2024 15:04:44 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1733439882; x=1734044682; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=xtQFQ2tMiQPNjIR8DgtNyXY3j5ez2MKlAyKhbp4Udh4=; b=StIhqED2+8uN+B75xXAi3+ZiG1d6Bidj5jm9QoNu3mbrOp74C48QvaU5gaUd018CNT I2GLYkAXL37DrbPqlUNYSZDljavNt6TQc3r8081Mb6FXyYBjobAAV0PVGVPl80CJdfZH muJtWixx4q2YCe8esv5rH9IAIj5pSHYIAKJDFxy317wAg3mZEtRU1vHKLsRVLb0cFecN ilu18E6ZyHY+tdGf8B8KQZr+Ami4cH+kq2YnMaGhtioFfA4FKZ3crGeTgAjTGAO9g2uQ oLU6ESc9rFUALTtOboVclh435J/VEzVGo3xzziYZ0Fflmdh55BFlCiV/MvI311G73OBD ELDw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1733439882; x=1734044682; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=xtQFQ2tMiQPNjIR8DgtNyXY3j5ez2MKlAyKhbp4Udh4=; b=sBiu1xET8QhicTpo34pfzFjcs534oSHEg2gKJSngoVQJe1vGmePaoVh8gerJES6lAA rdp3jneCXFPrtbr7DAme3iROSRnW+Qt7msLyS3hGmbBWKJJYwwsqEgBbt9xRYYY2KutU ZauxKChRuPeSpL1Hi9a+uEGK/a+Z5V8JWY8W+MN2U4U+ClRPhsxDe2h66G6MjAWaes7u My/vyjkkDv59jHW9nCMxDXue2C6n4oRXldVDH8TXB8LV4oP544ancAU/XoWyBO/j6TsY w9qH3AWFayLhc8sZSoyojeAUdv2cmYhXW79o+HDUtA5u1KiT+6eZzyS7CLBWm+6P1GCX 7BPQ== X-Gm-Message-State: AOJu0Yxvhnp8CISCrZayUyij3MqIeppWFstpO7L7pFqNr2PPFtue6SvD Lgzm9G6FDJf0qYQD6UlvSh5plOBT0BvtCcmDftfkEe0nehdlqWbhPFXzAFJcjmBc2FGyXAcv+Qs ajAF3+KPsRMWCcXpeJDMRu8f9AVNgMhFN X-Gm-Gg: ASbGncvxIOqvGAzGVr35K9iYL8uwwt3YvGkAzsReKUtZgjspKu88JZ0AF5g8lFneihp O+7anQDhKM7FeGx2brhG2JQYA42pBuqqObscAB1Ueez4KFTu7KgTwVIqc8iO6hMgCYA== X-Google-Smtp-Source: AGHT+IEPbJ0ToJiRp4lzEh3LwGExYtVwGA4HPMMwZF6mlAHelRW6WrXHsAFuhdZzjIKujenaonn8VQXuVa7VeNXuXtA= X-Received: by 2002:a9d:6a0a:0:b0:71d:60ec:1a80 with SMTP id 46e09a7af769-71dcf546d39mr791088a34.22.1733439881971; Thu, 05 Dec 2024 15:04:41 -0800 (PST) MIME-Version: 1.0 References: <6a6439f1-8039-44e2-8fb9-59028f7f2014@mailbox.org> <9c5ba566-27b8-4e8c-bf7d-2dc561509991@mailbox.org> <41791b6d-aaf5-4fed-9cc3-e89bc49e8637@mailbox.org> <1257068.1733025493@sss.pgh.pa.us> <06425038-e012-4bac-aec1-d9541436f893@mailbox.org> <92405f98-7f0c-4442-8252-697352daefc1@aklaver.com> <1470486.1733087658@sss.pgh.pa.us> <4ab662d8-57cc-471f-8a58-cfd71d1cea22@aklaver.com> <1475512.1733090108@sss.pgh.pa.us> <19d5f2c7-1252-4442-accb-8aa2cb289ad0@mailbox.org> In-Reply-To: <19d5f2c7-1252-4442-accb-8aa2cb289ad0@mailbox.org> From: Ron Johnson Date: Thu, 5 Dec 2024 18:04:31 -0500 Message-ID: Subject: Re: Errors when restoring backup created by pg_dumpall To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000006791af06288dec4b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006791af06288dec4b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Dec 5, 2024 at 5:32=E2=80=AFPM PopeRigby wr= ote: > On 12/1/24 13:55, Tom Lane wrote: > > Adrian Klaver writes: > >> On 12/1/24 13:14, Tom Lane wrote: > >>> It would be useful to know what is the command at line 4102 > >>> of all.sql. > >> It is here: > >> https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49 > >> CREATE TABLE public.geodata_places ( > >> id integer NOT NULL, > >> name character varying(200) NOT NULL, > >> longitude double precision NOT NULL, > >> latitude double precision NOT NULL, > >> "countryCode" character(2) NOT NULL, > >> "admin1Code" character varying(20), > >> "admin2Code" character varying(80), > >> "modificationDate" date NOT NULL, > >> "earthCoord" public.earth GENERATED ALWAYS AS > >> (public.ll_to_earth(latitude, longitude)) STORED, > >> "admin1Name" character varying, > >> "admin2Name" character varying, > >> "alternateNames" character varying > >> ); > > Ah! Then the failure occurs because we do a planning pass on the > > GENERATED expression (I don't remember exactly why that's needed > > during CREATE TABLE). So maybe messing with the dump script's > > search_path setting *would* be enough to get you past that. > > > > Having said that, the CREATE should have been seeing the new-style > > definition of ll_to_earth() if the 1.2 version of earthdistance > > was correctly installed. > > > > regards, tom lane > > So, is there anything I can do to fix this particular backup? I'm kind > of stuck here. There's also the issue with it for some reason failing to > connect to the lldap database after it literally just created it. Some > weird things going on. > Another alternative is to open the .sql file in Notepad++, then add "public." before all the unqualified "earth" and "ll_to_earth" references. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000006791af06288dec4b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Dec 5, 2024 at 5:32=E2=80=AFPM Po= peRigby <poperigby@mailbox.org<= /a>> wrote:
On 12/1/24 13:55, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 12/1/24 13:14, Tom Lane wrote:
>>> It would be useful to know what is the command at line 4102 >>> of all.sql.
>> It is here:
>> https://gist.github.com/p= operigby/fcb59eb6c22c6051800e06a0ec482b49
>> CREATE TABLE public.geodata_places (
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0id integer NOT NULL,
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0name character varying(200) NOT NULL, >>=C2=A0 =C2=A0 =C2=A0 =C2=A0longitude double precision NOT NULL,
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0latitude double precision NOT NULL,
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0"countryCode" character(2) NOT= NULL,
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0"admin1Code" character varying= (20),
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0"admin2Code" character varying= (80),
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0"modificationDate" date NOT NU= LL,
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0"earthCoord" public.earth GENE= RATED ALWAYS AS
>> (public.ll_to_earth(latitude, longitude)) STORED,
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0"admin1Name" character varying= ,
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0"admin2Name" character varying= ,
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0"alternateNames" character var= ying
>> );
> Ah!=C2=A0 Then the failure occurs because we do a planning pass on the=
> GENERATED expression (I don't remember exactly why that's need= ed
> during CREATE TABLE).=C2=A0 So maybe messing with the dump script'= s
> search_path setting *would* be enough to get you past that.
>
> Having said that, the CREATE should have been seeing the new-style
> definition of ll_to_earth() if the 1.2 version of earthdistance
> was correctly installed.
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0regards, tom lane

So, is there anything I can do to fix this particular backup? I'm kind =
of stuck here. There's also the issue with it for some reason failing t= o
connect to the lldap database after it literally just created it. Some
weird things going on.

Another alternat= ive is to open the .sql file in Notepad++, then add "public." bef= ore all the unqualified "earth" and "ll_to_earth" refer= ences.

-- =
Deat= h to <Redacted>, and butter sauce.
Don't boil me, I'm sti= ll alive.
<Redacted> lobster!
--0000000000006791af06288dec4b--