Re: Weird issue - schemas missing - on Windows 10 PostgreSQL 14 & 9.6

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.