Help with restoring database from old version of PostgreSQL
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 through the installation of PostgreSQL 16.3, I opened up pgAdmin 4 to 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 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 How can I restore my database to have access to it again? Thanks.
Re: Help with restoring database from old version of PostgreSQL
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 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 > 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 , and butter sauce. > Don't boil me, I'm still alive. > lobster! >
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 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
Re: Help with restoring database from old version of PostgreSQL
Adrian: Yes, db_2017 is the name I originally gave the database in the PostgreSQL 9.6 server. I can't remember if I used pg_dump or pg_dumpall. Ron: Okay, I think it finally worked! Thank you both for your help. -- Arden On Tue, Nov 19, 2024 at 6:00 PM Adrian Klaver wrote: > On 11/19/24 14:13, Catherine Frock 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. > > > > You need to take a step back and take stock of where you started and > where you want to be. > > To that end answer the following: > > 1) What are the Postgres and PostGIS versions you started with? > > 2) What database or databases do you want to move forward? > > 3) Is there a hard requirement for what currently supported version of > Postgres you want to use? > > 4) What do you want to do with the new install? > > 5) What versions of Postgres, pgAdmin4 and PostGIS do you currently have > installed? > > 6) You are looking at jumping a minimum of 4 major Postgres versions up > to a maximum of 8 versions. You are also jumping some number of PostGIS > versions as well. In either case you should at some point go over the > release notes for each major version jump to see if anything is going to > cause issues. > > > -- > > Arden > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
