Feedback-ID: i76984098:Fastmail
Message-ID: <cba48d2c-0987-4233-a86f-2e693677efd3@aklaver.com>
Date: Fri, 29 Nov 2024 17:47:55 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Errors when restoring backup created by pg_dumpall
To: PopeRigby <poperigby@mailbox.org>, pgsql-general@postgresql.org
References: <6a6439f1-8039-44e2-8fb9-59028f7f2014@mailbox.org>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <6a6439f1-8039-44e2-8fb9-59028f7f2014@mailbox.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/cba48d2c-0987-4233-a86f-2e693677efd3%40aklaver.com>
Precedence: bulk

On 11/29/24 17:34, PopeRigby wrote:
> My HDD recently failed so I'm trying to restore my backup, but I'm 
> running into some errors.
> 
> I've been using a systemd service that periodically backs up my cluster 
> with pg_dumpall, and I'm using this command to restore:
> 
> sudo psql -f backup.sql postgres
> 
> I'm getting this output: 
> https://gist.github.com/poperigby/d5a0103b9de1eba95aba783007e557ea


psql:all.sql:4104: ERROR:  type "earth" does not exist
LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth

QUERY:  SELECT 
cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
CONTEXT:  SQL function "ll_to_earth" during inlining

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
);


Looks like an extension or extensions where not installed before the 
restore was done.


> 
> This is my (redacted) database dump: 
> https://gist.github.com/poperigby/fcb59eb6c22c6051800e06a0ec482b49
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com