Re: Help with restoring database from old version of PostgreSQL

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Help with restoring database from old version of PostgreSQL
Date: Tue, 19 Nov 2024 17:25:59 -0500
Message-ID: <CANzqJaAcJ=WrvQzCxuZY01hAnNQ6YGc8i1nsJ58CrFh3umxU2w@mail.gmail.com> (raw)
In-Reply-To: <CAAkgG6iq2cjJMifBr1qShb0TkNzDx3kMXyQV4djTSZMa_npTPw@mail.gmail.com>
References: <CAAkgG6hSWMwUUivGzoi_ku6JNXmTVXC2mP7XY5Xs+QPnvKjAdQ@mail.gmail.com>
	<[email protected]>
	<CAAkgG6jLBV81cN7ogJF0bHj3ymFJ87ULmbnuX=De2rndoTGNXw@mail.gmail.com>
	<[email protected]>
	<CAAkgG6iWGvmczXnarYTU2PNAWiF3o0DFa47Qzb7M6wjcasNrJQ@mail.gmail.com>
	<CANzqJaAO7-FO0Gr=q-mtp=1J360MkPZrO_jKsrWqq41d-upGyw@mail.gmail.com>
	<CAAkgG6iq2cjJMifBr1qShb0TkNzDx3kMXyQV4djTSZMa_npTPw@mail.gmail.com>

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 PM Catherine Frock <[email protected]> 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 PM Ron Johnson <[email protected]>
> 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 <[email protected]>
>> 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!
>>
>

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Help with restoring database from old version of PostgreSQL
  In-Reply-To: <CANzqJaAcJ=WrvQzCxuZY01hAnNQ6YGc8i1nsJ58CrFh3umxU2w@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox