Re: Help with restoring database from old version of PostgreSQL

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
ALTER SCHEMA
COMMENT
CREATE SCHEMA
ALTER SCHEMA
COMMENT
CREATE SCHEMA
ALTER SCHEMA
COMMENT
CREATE SCHEMA
ALTER SCHEMA
COMMENT
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
SET
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
CREATE FUNCTION
ALTER FUNCTION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
COMMENT
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
COMMENT
SET
CREATE TABLE
ALTER TABLE
SET
CREATE VIEW
ALTER TABLE
COMMENT
SET
CREATE TABLE
ALTER TABLE
SET
CREATE TABLE
ALTER TABLE
COMMENT
SET
CREATE VIEW
ALTER TABLE
COMMENT
CREATE VIEW
ALTER TABLE
COMMENT
SET
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
COMMENT
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
CREATE TABLE
ALTER TABLE
COMMENT
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
SET
ALTER TABLE
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
SET
COPY 0
setval
--------
1
(1 row)

COPY 0
SET
COPY 3
COPY 7
COPY 6
COPY 3
SET
COPY 67
COPY 37649
setval
--------
38344
(1 row)

COPY 38783
COPY 38344
setval
--------
41058
(1 row)

COPY 81
COPY 81
setval
--------
654
(1 row)

SET
COPY 35
COPY 40
COPY 39
COPY 35
COPY 35
COPY 0
COPY 58
COPY 39
SET
ALTER TABLE
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
SET
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE TRIGGER
CREATE TRIGGER
CREATE TRIGGER
COMMENT
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
ERROR: role "basic_user" does not exist
ERROR: role "basic_user" does not exist
SET
ERROR: role "basic_user" does not exist
SET
ERROR: role "basic_user" does not exist
SET
ERROR: role "basic_user" does not exist
SET
ERROR: role "basic_user" does not exist
ERROR: role "basic_user" does not exist
SET
ERROR: role "basic_user" does not exist
SET
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
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

--
Arden

On Thu, Nov 14, 2024 at 12:02 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 11/14/24 07:00, Catherine Frock wrote:
> > Yes, I still have the backup file. I tried to open pgAdmin4 today to see
> > what version I'm running, and now this happened (see attachments). My
> > operating system is Windows 10 Home, version 10.0.19045 Build 19045. I
> > installed Postgres using these instructions:
> >
> https://basille.org/postgis2017/installation_instructions/install_postgresql.html
> <
> https://basille.org/postgis2017/installation_instructions/install_postgresql.html> I'm
> guessing at this point I probably need to start all over with the
> installation since pgAdmin4 is not working? But if 9.6 is no longer
> supported, can I still use it to access my database? I assumed I would need
> to migrate to the most recent version of PostgreSQL, but maybe that was a
> wrong assumption?
>
> 1) You can copy/paste the error message and provide as text.
>
> 2) Assuming you are installing Postgres 16 from the EDB package, the
> from here:
>
> https://www.postgresql.org/download/windows/
>
> PostgreSQL Version 64 Bit Windows Platforms
> 16 2022, 2019
>
> [...]
> 11 2019, 2016, 2012 R2
> 10 2016, 2012 R2 & R1, 7, 8, 10
>
> 3) From here:
>
> https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
>
> 9.6 can still be downloaded.
>
> It is not a supported version, but you can at least use it to access
> your database.
>
>
> 4) You need to provide a more detailed explanation of what you are
> trying to achieve.
>
> 5) 9.6 -> 16 is six major version jump. That means some research needs
> to be done, starting with determining what version of PostGIS you need
> to be using.
>
> >
> > Thank you for your help,
> > --
> > Arden
> >
> >
> > On Wed, Nov 13, 2024 at 3:42 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
> >
> > On 11/13/24 10:50, Catherine Frock wrote:
> > > I'm seeking help in restoring a database that was created with
> > > PostgreSQL 9.6. I backed it up successfully and have restored it
> > before,
> > > but that was when PostgreSQL 9.6 was still supported. After going
> >
> > Do you still have the Postgres 9.6 instance running?
> >
> > > through the installation of PostgreSQL 16.3, I opened up pgAdmin
> > 4 to
> >
> > What version of pgAdmin4?
> >
> > > try to verify the PostgreSQL/PostGIS installation, but when I try
> to
> > > connect to the PostgreSQL 16.3 server, I get an internal server
> > error
> > > message: 'ServerManager' object has no attribute 'user_info'.
> > >
> > > In pgAdmin4, I am able to connect to the PostgreSQL 9.6 server,
> > but when
> >
> > So the 9.6 instance is running.
> >
> > > I try to verify the install by querying: CREATE EXTENSION postgis;
> > > SELECT postgis_version();
> > > I get the following error message: ERROR: could not open
> extension
> > > control file "C:/Program
> > > Files/PostgreSQL/9.6/share/extension/postgis.control": No such
> > file or
> > > directory SQL state: 58P01
> >
> > What OS and version are you running?
> >
> > How did you install Postgres?
> >
> >
> > >
> > > How can I restore my database to have access to it again?
> > >
> > > Thanks.
> >
> > --
> > Adrian Klaver
> > adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> >
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>