Re: Help with restoring database from old version of PostgreSQL

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 PM Catherine Frock <frock(at)uwalumni(dot)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 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 version
> 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++ runtime
> 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 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: 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 <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!