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.