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 1tDWTv-001I3F-Tb for pgsql-general@arkaria.postgresql.org; Tue, 19 Nov 2024 22:14:00 +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 1tDWTu-00HYIG-5Y for pgsql-general@arkaria.postgresql.org; Tue, 19 Nov 2024 22:13:58 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tDWTt-00HYI2-L4 for pgsql-general@lists.postgresql.org; Tue, 19 Nov 2024 22:13:57 +0000 Received: from mail-yb1-xb31.google.com ([2607:f8b0:4864:20::b31]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tDWTq-002lWV-AP for pgsql-general@lists.postgresql.org; Tue, 19 Nov 2024 22:13:56 +0000 Received: by mail-yb1-xb31.google.com with SMTP id 3f1490d57ef6-e30d212b6b1so1144400276.0 for ; Tue, 19 Nov 2024 14:13:54 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=uwalumni.com; s=google; t=1732054433; x=1732659233; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=8e+4Egic1mbguzMLtsoXj8QEEQ++tUg2K/UMXG8LrLs=; b=SZJ85I9Vf3R6guDwLSzpZzXHYS+V+zPCdwS2EQpfNsyChn66MVN0mYN+FC+5+tz8tW kLDx1OOZITDEVVbOTKODqUMqpACYcCjfBQROP7WURGgANW92+mlGSj7kFDlukGg0IPk1 d6h6KGJtJOC/e6H16iDyXfRpPDFIE1Azjspuk= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732054433; x=1732659233; h=cc: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=8e+4Egic1mbguzMLtsoXj8QEEQ++tUg2K/UMXG8LrLs=; b=S8Qn9IeClrJ073jmBN+FDlxQPBh1XvmxpohZejE5kYl8liVm3QUraq+PKSv2glTMm2 UW03L8WtnWWyvVChbRVcEkdn5qJzGF3ODbVB02kIJdML0rpbWdBYg1ek8grWYuCnSKW+ TgjYe4BPJq6LLi89WmkeyWVS+cnkmkwl+PWwVyJ0sYvAnGZSTE8Sr/xyzao1ZOFfERUB ArJLdyIpMfXeZl7eG2LEYaj3ONoXqldsia88MjolZIVgly2m8Jm7D0zdTKjhpBu9iF+o pf6droarFWM9eZ7mLLB8ew3NXk0Qda0XGTZdBu9jH2ln0jQsgfIlcw/0axdzlJBk0kSr oH3Q== X-Gm-Message-State: AOJu0Yz9mWLVllhT1pFk+QGhfiE+PM+7a2f7vLwh+zU37gOIoKq/p34x oJ6+DgH2cY+UQjvQc2SmSpGp5k+rtzwYyaTqweqE/BZweQIrtEKHt1gb0/w5Z7gfoHLCYGI+FLC VAHvyin3TMI5eaBsiXraZmBuEvSDD6m5h5p+pDAZNV1/+EzmiwsJ/ig== X-Google-Smtp-Source: AGHT+IEMUx1CyIDEy0D1ZRB4VTuEzH3gDW3+cTlKoXITvyAf2J6KtFOxduMwSf5rFrG9Zb71pLRJaGuQ7BLHhFtwvFk= X-Received: by 2002:a05:6902:118e:b0:e30:da53:f551 with SMTP id 3f1490d57ef6-e38cb566a7bmr448407276.19.1732054433388; Tue, 19 Nov 2024 14:13:53 -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: Catherine Frock Date: Tue, 19 Nov 2024 17:13:40 -0500 Message-ID: Subject: Re: Help with restoring database from old version of PostgreSQL To: Ron Johnson Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000003bef7b06274b5958" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003bef7b06274b5958 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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; no > 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 wa= s >> created in PostgreSQL 9.6 using pgAdmin4 (I'm not sure which version I w= as >> 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 need >> to be using? When I installed it, I was only given one option of a versi= on >> to install. >> >> Since my previous installation of pgAdmin4 was not working, I have >> started over today, uninstalling all versions of PostgreSQL that I had a= nd >> 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++ runti= me >> 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 servers= . 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: "psq= l.exe >> db_2024_9 < C:\postgis\db_2017-08-16.sql postgres" using an empty databa= se >> 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! > --0000000000003bef7b06274b5958 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 <ronljohnsonjr@gmail.com> wro= te:
Ah, so it's a plain old SQL file.=C2=A0 That makes things muc= h=C2=A0easier!!

Here's the thing: PG (whether = 9.6 or 16 or 17) is the database engine; no GUI, and just a basic command l= ine interface.
PgAdmin4 is a GUI interface to PG.=C2=A0 Newer ver= sions 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 basic_user WITH PASS= WORD 'random_horse';

After=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 database .sql file = that was created in PostgreSQL 9.6 using pgAdmin4 (I'm not sure which v= ersion 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=C2=A0need to be using? When I ins= talled it, I was only given one option of a version to install.
<= font face=3D"arial, sans-serif">
Since my previous installation of pgAdmin4 was not working, I h= ave started over today, uninstalling all versions of PostgreSQL that I had = and reinstalling=C2=A0Po= stgreSQL 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 also, but I recei= ved an error message when=C2=A0I run the installation file: "An error = occurred executing the Microsoft VC++ runtime installer." I installed = the latest versions of the=C2=A0PostGIS (3.5.0) and psqlODBC drivers (I'm forgo= t to write that down and am not sure how to check the version). My installa= tion of=C2=A0PostgreSQL/PostGIS was verified as successful. I am able to c= onnect to the PostgreSQL 9.6 and 17 servers. I set the=C2=A0PostgreSQL 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!
--0000000000003bef7b06274b5958--