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 1tDWfo-001JgM-Sm for pgsql-general@arkaria.postgresql.org; Tue, 19 Nov 2024 22:26:17 +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 1tDWfn-0006hJ-H8 for pgsql-general@arkaria.postgresql.org; Tue, 19 Nov 2024 22:26:15 +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 1tDWfn-0006hA-4D for pgsql-general@lists.postgresql.org; Tue, 19 Nov 2024 22:26:15 +0000 Received: from mail-oi1-x22c.google.com ([2607:f8b0:4864:20::22c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tDWfk-002oyo-HS for pgsql-general@postgresql.org; Tue, 19 Nov 2024 22:26:14 +0000 Received: by mail-oi1-x22c.google.com with SMTP id 5614622812f47-3e5fbc40239so1620359b6e.3 for ; Tue, 19 Nov 2024 14:26:12 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1732055170; x=1732659970; 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=kIygrgMHwTc/uxZJZ9U3FhS5Q6AWtJmr6NKiZC1pqD4=; b=gX+F4S9RHSEIdmg8tczyB1FJCl8DFF2Kd2d+Dg/0d9+FuRq/g3ICC+3sbc1tNPc0I2 VROna5VQOZvPacBS9DdDZsKnvUmJAblnFIL5rGwD8HwVhWwRjhwnTGUXhYlxGYAUp4Wn ynarxdVek+rijJc1pom8p5GRkCVOF4ERqxqOFWrPG2wxVyalsUi1/5hkk7V1f5L76vJt 3jw9njUj2vY2SBrjujstCTTOO+NsilYLe+PEyXbc/Dm24/9sW4zOOqYpggQst/JWA7Cz XvjQJqlMYkLcmRbw5x68cVovqFHLRk95u//+F3e/nsa01sVPomw0j6q6+QnzWnZhcl5M ItuA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732055170; x=1732659970; 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=kIygrgMHwTc/uxZJZ9U3FhS5Q6AWtJmr6NKiZC1pqD4=; b=O8Ryvx976xljiFlPWRAtL5+wrdAnGrA+ha+Bpt3JbYcbydQ2XSrQaFiFGFlvBaCDjX mC4cPTbu5UdVei5eUlu0yAZOpgMdHXw05m+vjlkRKO/xNAspDeKQgoX5MRHenT1hFzgp 3Q1i80k8e6XLp+tAf3PLH/ECTgaldCARrK+faJsJokLlRr9qtnnKsDkS2apYtMmlGw1f YGOVf99LKH5/05sYnFbxYaOFGS84/H2DkEcJsAfjn5Ed0IMqaFryqlyM4xCQ0Q8tSKC7 lodOPeRENaHrB/JAT8h8NPex+goaa8QPqHdpVwhIXi676e26IDFHqwpDHlB/Gf1YvRmV 6Ddg== X-Gm-Message-State: AOJu0YwHY8M+y9SuuAkN+9F1Q0XfbgKBgR7000P86MsZ1GVYgvo47ExD kPBlWDvaglZOgNnrWnobTOqVG1wYSUWa3hLNsBArVe51zWV3bdLJyVC8Iyfh/thMMbCo7wJLMt6 EyDcSpmnHwSqAMplCL6sM6ilthcMfP8Rz X-Google-Smtp-Source: AGHT+IEx7HU3oCq3hSkqwji2imlbOyPTga1taK5Rx1Lzbi992TBCmMlkCQQy3Ovdf7WUOJ09RtYqUebnDeptx8Afebw= X-Received: by 2002:a05:6808:1a1e:b0:3e7:63ed:c6ed with SMTP id 5614622812f47-3e7eb7d53d5mr423003b6e.41.1732055170419; Tue, 19 Nov 2024 14:26:10 -0800 (PST) MIME-Version: 1.0 References: <1fe9de96-6ebe-4e3a-9b15-ae8a11986664@aklaver.com> <11b5cfbd-cb44-4bc5-b74e-c6c682d9cfb9@aklaver.com> In-Reply-To: From: Ron Johnson Date: Tue, 19 Nov 2024 17:25:59 -0500 Message-ID: Subject: Re: Help with restoring database from old version of PostgreSQL To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000002a156706274b8539" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002a156706274b8539 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable You say that you just installed PG17. That's perfectly fine. If pgAdmin4 version 8.12 is recent, then it's fine to use that, too. The thing is that sql files are just... a bunch of SQL statements. Whatever SQL is generated by v9.6 will be easily processed by a modern version. On Tue, Nov 19, 2024 at 5:13=E2=80=AFPM Catherine Frock wrote: > Do you know what versions of PG and pgAdmin4 I need? Also, I don't know > where to get a different version of PG. > > -- > Arden > > > On Tue, Nov 19, 2024 at 5:01=E2=80=AFPM Ron Johnson > wrote: > >> Ah, so it's a plain old SQL file. That makes things much easier!! >> >> Here's the thing: PG (whether 9.6 or 16 or 17) is the database engine; n= o >> GUI, and just a basic command line interface. >> PgAdmin4 is a GUI interface to PG. Newer versions of PgAdmin4 are not >> compatible with EOL PG versions like 9.6. >> >> What you need is *both* a new PG version *and* a new PgAdmin4 version. >> >> *Then* you need to: >> CREATE USER basic_user WITH PASSWORD 'random_horse'; >> >> After that, you can run the "backup sql script". >> >> On Tue, Nov 19, 2024 at 4:47=E2=80=AFPM Catherine Frock >> wrote: >> >>> I am trying to restore a previously backed up database .sql file that >>> was created in PostgreSQL 9.6 using pgAdmin4 (I'm not sure which versio= n I >>> was using). >>> >>> In response to Ron: Do I need to use PG16 instead of pgAdmin4? And if >>> so, can you please provide a website where I can download PG16? >>> >>> In response to Adrian: How do I determine what version of PostGIS I nee= d >>> to be using? When I installed it, I was only given one option of a vers= ion >>> to install. >>> >>> Since my previous installation of pgAdmin4 was not working, I have >>> started over today, uninstalling all versions of PostgreSQL that I had = and >>> reinstalling PostgreSQL Version 17.0 from https://www.enterprisedb.com/= and >>> pgAdmin4 version 8.12 from https://www.pgadmin.org. I tried installing >>> PostgreSQL 9.6.24 also, but I received an error message when I run the >>> installation file: "An error occurred executing the Microsoft VC++ runt= ime >>> installer." I installed the latest versions of the PostGIS (3.5.0) and >>> psqlODBC drivers (I'm forgot to write that down and am not sure how to >>> check the version). My installation of PostgreSQL/PostGIS was verified >>> as successful. I am able to connect to the PostgreSQL 9.6 and 17 server= s. I >>> set the PostgreSQL Binary Path to: C:\Program Files\PostgreSQL\17\bin. >>> I have tried to restore my database using this in the command prompt: "= psql.exe >>> db_2024_9 < C:\postgis\db_2017-08-16.sql postgres" using an empty datab= ase >>> in both the PostgreSQL 9.6 and 17 servers, and this is the result: >>> >>> Password for user postgres: >>> SET >>> SET >>> SET >>> SET >>> SET >>> SET >>> SET >>> SET >>> WARNING: database "db_2017" does not exist >>> COMMENT >>> CREATE SCHEMA >>> >>> [snip] >> >>> CREATE TRIGGER >>> ALTER TABLE >>> ALTER TABLE >>> ALTER TABLE >>> ERROR: role "basic_user" does not exist >>> ERROR: role "basic_user" does not exist >>> ERROR: role "basic_user" does not exist >>> ERROR: role "basic_user" does not exist >>> SET >>> >> [snip] >> >>> SET >>> ALTER DEFAULT PRIVILEGES >>> ERROR: role "basic_user" does not exist >>> SET >>> ALTER DEFAULT PRIVILEGES >>> ERROR: role "basic_user" does not exist >>> SET >>> ALTER DEFAULT PRIVILEGES >>> ERROR: role "basic_user" does not exist >>> SET >>> ALTER DEFAULT PRIVILEGES >>> ERROR: role "basic_user" does not exist >>> >>> >> >> Death to , and butter sauce. >> Don't boil me, I'm still alive. >> lobster! >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000002a156706274b8539 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
You say that you just installed PG17.=C2= =A0 That's perfectly fine.=C2=A0 =C2=A0If pgAdmin4 version 8.12 is rece= nt, then it's fine to use that, too.

The thing= is that sql files are just... a bunch of SQL statements.=C2=A0 Whatever SQ= L is generated by v9.6 will be easily processed by a modern version.
<= br>
On Tue,= Nov 19, 2024 at 5:13=E2=80=AFPM Catherine Frock <frock@uwalumni.com> wrote:
Do you know what ve= rsions of PG and pgAdmin4 I need? Also, I don't know where to get a dif= ferent version of PG.

On Tue, Nov 19, 2024 at 5:01=E2=80=AFPM Ron Johnson <ronljohnsonjr@gma= il.com> wrote:
Ah, so it's a plain old SQL file.=C2=A0 Tha= t makes things much=C2=A0easier!!

Here's the t= hing: PG (whether 9.6 or 16 or 17) is the database engine; no GUI, and just= a basic command line interface.
PgAdmin4 is a GUI interface to P= G.=C2=A0 Newer versions of PgAdmin4 are not compatible with EOL PG versions= like 9.6.

What you need is both=C2=A0a= new PG version and=C2=A0a new PgAdmin4 version.

Then=C2=A0you need to:
=C2=A0 =C2=A0 CREATE USER bas= ic_user WITH PASSWORD 'random_horse';

Afte= r=C2=A0that, you can run the "backup sql script".

On Tue, Nov 19, = 2024 at 4:47=E2=80=AFPM Catherine Frock <frock@uwalumni.com> wrote:
I am trying to restore a previously backed up databa= se .sql file that was created in PostgreSQL 9.6 using pgAdmin4 (I'm not= sure which version I was using).

In response= to Ron: Do I need to use PG16 instead of pgAdmin4? And if so, can you plea= se provide a website where I can download PG16?

In response t= o Adrian: How do I determine what version of PostGIS I=C2=A0need to be usin= g? When I installed it, I was only given one option of a version to install= .

Since my previous installation of pgAdmin4 was not= working, I have started over today, uninstalling all versions of PostgreSQ= L that I had and reinstalling=C2=A0PostgreSQL Version 17.0 from=C2=A0https://www.enterprisedb.com/=C2= =A0and pgAdmin4 version= =C2=A08.12 from=C2=A0https://www.pgadmin.org. I tried installing PostgreSQL 9.6.24 al= so, but I received an error message when=C2=A0I run the installation file: = "An error occurred executing the Microsoft VC++ runtime installer.&quo= t; I installed the latest versions of the=C2=A0PostGIS (3.5.0) and psqlODBC drivers= (I'm forgot to write that down and am not sure how to check the versio= n). My installation of=C2=A0PostgreSQL/PostGIS was verified as successful.= I am able to connect to the PostgreSQL 9.6 and 17 servers. I set the=C2=A0= PostgreSQL Binary Path to:=C2=A0C:\Program Files\PostgreSQL\17\bin. I have tried to restore my database using this in = the command prompt: "psql.exe db_2024_9 < C:\postgis\db_2017= -08-16.sql postgres" using an empty database in both the PostgreSQL 9.= 6 and 17 servers, and this is the result:

Password for user postgres= :
SET
SET
SET
SET
SET
SET
SET
SET
WARNING: =C2= =A0database "db_2017" does not exist
COMMENT
CREATE SCHEMA<= br>
[snip]=C2=A0
CREATE TRIGGER
ALTER= TABLE
ALTER TABLE
ALTER TABLE
ERROR: =C2=A0role "basic_user&= quot; does not exist
ERROR: =C2=A0role "basic_user" does not e= xist
ERROR: =C2=A0role "basic_user" does not exist
ERROR: = =C2=A0role "basic_user" does not exist
SET
[snip]=C2=A0
SET
ALTER DEFAULT PRIVILEGES
ERROR: =C2= =A0role "basic_user" does not exist
SET
ALTER DEFAULT PRIVI= LEGES
ERROR: =C2=A0role "basic_user" does not exist
SET
= ALTER DEFAULT PRIVILEGES
ERROR: =C2=A0role "basic_user" does n= ot exist
SET
ALTER DEFAULT PRIVILEGES
ERROR: =C2=A0role "basi= c_user" does not exist

=C2= =A0

Death to <Redacted&g= t;, and butter sauce.
Don't boil me, I'm still alive.
<= div><Redacted> lobster!


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--0000000000002a156706274b8539--