Re: Help with restoring database from old version of PostgreSQL

On 11/19/24 1:47 PM, 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 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.

Go here:

https://postgis.net/development/source_code/

and look in the release notes. They will tell you what range of Postgres
versions are supported. For instance the latest 3.5.0:

https://git.osgeo.org/gitea/postgis/postgis/raw/tag/3.5.0/NEWS

"To take advantage of all postgis_sfcgal extension features SFCGAL 1.5+
is needed. PostgreSQL 12-17 required. GEOS 3.8+ required. Proj 6.1+
required. "

>
> 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/ <https://www.enterprisedb.com/> and
> pgAdmin4 version 8.12 from https://www.pgadmin.org
> <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:

1) Is db_2017 a database you had on old server?

2) What backup did you do in pgAdmin4 for the 9.6 instance?

Did you do the entire server:

https://www.pgadmin.org/docs/pgadmin4/8.13/backup_server_dialog.html

or backup just one database?:

https://www.pgadmin.org/docs/pgadmin4/8.13/backup_dialog.html

If the latter then you did not get the global objects for the Postgres
9.6 instance. This includes roles(users) and would account for the user
errors.

To get the global objects use:

https://www.pgadmin.org/docs/pgadmin4/8.13/backup_globals_dialog.html

> 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 <mailto: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> <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/
> <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
> <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>
> > <mailto: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>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
> >
>
> --
> 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