Message-ID: <5273c007-0977-2939-d522-974c19dda959@gmail.com>
Date: Tue, 20 Sep 2022 17:17:01 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.9.1
Subject: Re: Weird issue - schemas missing - on Windows 10 PostgreSQL 14 & 9.6
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: 
 <CAMCvLjXj27aG2-mQ2h4JP4+P3QNLNf8K2xiM_3LbeMyf7Vih5A@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
In-Reply-To: 
 <CAMCvLjXj27aG2-mQ2h4JP4+P3QNLNf8K2xiM_3LbeMyf7Vih5A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/5273c007-0977-2939-d522-974c19dda959%40gmail.com>
Precedence: bulk

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.