Re: Help with restoring database from old version of PostgreSQL

[Catherine Frock <frock@xxxxxxxxxxxx>]

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 PM Ron Johnson <ronljohnsonjr@xxxxxxxxx> 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 PM Catherine Frock <frock@xxxxxxxxxxxx> 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!