Обсуждение: Weird issue - schemas missing - on Windows 10 PostgreSQL 14 & 9.6
Hello friends,
I had a Postgres 9.6 server with a few databases running on my Windows 10 laptop. Some months ago I installed version 14 and migrated my databases across using pg_dumpall and uninstalled 9.6 without deleting the data folders for 9.6.
I do not use all the databases always, but post upgrade I remember checking all the databases and they all looked fine. Off late, I was working on a couple of newer databases and they are all working fine. But now when I needed to access another older database, I noticed that I can only see the public schemas in them. In fact all my older databases are only showing public schema in them. My main schemas just disappeared and they are not there even in the information_schema if I query the list of schemas.
Honestly I have no idea how and when this happened. The only thing I remember doing (something perhaps not good) was I entered CTRL+C when I ran vacuumdb on all databases in version 14.
Now I have one database which is very important for me and my latest backup, unfirtunately, does not include newer data. So I stand to lose a few months' worth data if I am unable to restore the schemas back.
I installed 9.6 version again and ran it on another port to see if I can access the database from the older version. Funnily, that also shows the schemas missing. This is just not possible as I have positively used the application that runs off this database some months back.
So all my dear expert admins, is there any way I can restore these missing schemas in all these databases? I am a bit worried 😞
Please help. Thanks in advance.
Best regards,
Nitin
On 9/20/22 16:09, Nitin N wrote: > Hello friends, > > I had a Postgres 9.6 server with a few databases running on my Windows 10 > laptop. Some months ago I installed version 14 and migrated my databases > across using pg_dumpall and uninstalled 9.6 without deleting the data > folders for 9.6. > > I do not use all the databases always, but post upgrade I remember > checking all the databases and they all looked fine. Off late, I was > working on a couple of newer databases and they are all working fine. But > now when I needed to access another older database, I noticed that I can > only see the public schemas in them. In fact all my older databases are > only showing public schema in them. My main schemas just disappeared and > they are not there even in the information_schema if I query the list of > schemas. > > Honestly I have no idea how and when this happened. The only thing I > remember doing (something perhaps not good) was I entered CTRL+C when I > ran vacuumdb on all databases in version 14. > > Now I have one database which is very important for me and my latest > backup, unfirtunately, does not include newer data. So I stand to lose a > few months' worth data if I am unable to restore the schemas back. Now you know to back up data more frequently. :( > I installed 9.6 version again and ran it on another port to see if I can > access the database from the older version. Funnily, that also shows the > schemas missing. This is just not possible as I have positively used the > application that runs off this database some months back. > > So all my dear expert admins, is there any way I can restore these missing > schemas in all these databases? I am a bit worried 😞 > > Please help. Thanks in advance. Do you still have the 9.6 dumpall backup file? If so, look through it for the schema. Check the search_path. Do you remember any specific table names? "\du missing_schema.specific_table_name" might show it. Connect as user "postgres" and query information_schema.schemata. -- Angular momentum makes the world go 'round.