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 1tDWHX-001H2l-DV for pgsql-general@arkaria.postgresql.org; Tue, 19 Nov 2024 22:01:11 +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 1tDWHV-00HQ9A-LQ for pgsql-general@arkaria.postgresql.org; Tue, 19 Nov 2024 22:01:09 +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 1tDWHV-00HQ8t-5M for pgsql-general@lists.postgresql.org; Tue, 19 Nov 2024 22:01:09 +0000 Received: from mail-ot1-x329.google.com ([2607:f8b0:4864:20::329]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tDWHP-002lQs-OS for pgsql-general@lists.postgresql.org; Tue, 19 Nov 2024 22:01:07 +0000 Received: by mail-ot1-x329.google.com with SMTP id 46e09a7af769-71807ad76a8so2052631a34.0 for ; Tue, 19 Nov 2024 14:01:03 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1732053663; x=1732658463; darn=lists.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=tDs8HV2JFGxWuXBadijIbRzYSOXTayg1yWNSzqSvjpQ=; b=CAKQQC4dKRtH7PhZP2pZMzo/YyzQOSpql3OpdESJ32M2Zb+no0/TZDCs+VMxIU8LoJ nBTskiyyVZwTZy4gDC4SqZOcw1ezJKGrTiga8Av4R9lkBwqJFg6DMv2nzJFZYE4sasc9 OeMMvtzXp6R3F9cQYwibytZ3UDR4tELiQhZOKslEiSUtBfQE2vHtVOw6ayhwOfoqzn2r 67aHi1/Af15kxIy64xWYRFFiPG2PsiM19twqWb2gDjKJvRoTABDRQRogAuNRiuD8GbDC T1F3LIiKRk+oXGng9L4csr0Nw9dyplEw8UneQrZZ+1vXLbIkRI93yVq+dsgqkoNWEKM7 sxpg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1732053663; x=1732658463; 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=tDs8HV2JFGxWuXBadijIbRzYSOXTayg1yWNSzqSvjpQ=; b=terpSGoABE+eF99NWzrqAtWy/6VohO32yM/INxyeU8aVqsT5yX+2tRID8NleBpzHhK 9ajPIarXHAJwWDspE7r8wTOSB1AbaKMMvDnivuLhTo0BNOjKCIEoblZh3b2k+HIq0oDJ E+FCmdVYdTZBGu8RI494VOJujBhD1cBbUCCmfGhAE5qzQF/z21uqIS+Ddmo2p0ficzJI neCk4dV9U2dKdnfh1chcWtd7NTE1r0Zsf0o+lkH9Nfv3aQIPmOdSYEQfDCiwrbtpE71B 3F3KtHo+78vPlic/upEf0GIVim6MRWdhFGS4ApwqI+r1HVwO0znDOAn49hlIVICxUehi rDxQ== X-Gm-Message-State: AOJu0YwAY72XDmk4Yji2c4Nm3uzqKATCNR6CjPSDLYH2+c2777UOXD/b r+Scv+eCz7DtreJJdmKPNNqfBcHYu4YzoVj0R6wX3pj5bnlfUsGFSU0QMtpPm5ckG+rsYF0F2EA VcYvyrke81rNNY1spNs24lf7/Ryz7Lg== X-Google-Smtp-Source: AGHT+IEfgZIeVo4D2Qx4z9MbI7RrG+BuAJS8n+3hzNstQp9Cv9M5zkGAlLHUiTyRzZ5mCIeCYPAJsRCgmRyH4f0c3Tw= X-Received: by 2002:a05:6830:18e4:b0:717:d012:a513 with SMTP id 46e09a7af769-71ab3224188mr426047a34.19.1732053662643; Tue, 19 Nov 2024 14:01:02 -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:00:51 -0500 Message-ID: Subject: Re: Help with restoring database from old version of PostgreSQL To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000004b3edc06274b2b4a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004b3edc06274b2b4a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 was > created in PostgreSQL 9.6 using pgAdmin4 (I'm not sure which version I wa= s > 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 versio= n > to install. > > Since my previous installation of pgAdmin4 was not working, I have starte= d > over today, uninstalling all versions of PostgreSQL that I had and > reinstalling PostgreSQL Version 17.0 from https://www.enterprisedb.com/ a= nd > 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++ runtim= e > 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: "psql= .exe > db_2024_9 < C:\postgis\db_2017-08-16.sql postgres" using an empty databas= e > 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! --0000000000004b3edc06274b2b4a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Ah, so it's a plain old SQL file.=C2=A0 That make= s things much=C2=A0easier!!

Here's the thing: = PG (whether 9.6 or 16 or 17) is the database engine; no GUI, and just a bas= ic command line interface.
PgAdmin4 is a GUI interface to PG.=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 basic_us= er WITH PASSWORD '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 version 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 w= here I can download PG16?

In response to Adrian: How do I det= ermine what version of PostGIS I=C2=A0need to be using? 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 start= ed over today, uninstalling all versions of PostgreSQL that I had and reins= talling=C2=A0PostgreSQL = Version 17.0 from=C2=A0https://www.enterprisedb.com/=C2=A0and pgAdmin4 version=C2=A08.12 from=C2= =A0https://www.pgadmi= n.org. I tried installing PostgreSQL 9.6.24 also, but I received an err= or message when=C2=A0I run the installation file: "An error occurred e= xecuting the Microsoft VC++ runtime installer." 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 version). 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=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!
--0000000000004b3edc06274b2b4a--