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.
