From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Weird issue - schemas missing - on Windows 10 PostgreSQL 14 & 9.6
Date: Tue, 20 Sep 2022 17:17:01 -0500
Message-ID: <5273c007-0977-2939-d522-974c19dda959@gmail.com> (raw)
In-Reply-To: <CAMCvLjXj27aG2-mQ2h4JP4+P3QNLNf8K2xiM_3LbeMyf7Vih5A@mail.gmail.com>
References: <CAMCvLjXj27aG2-mQ2h4JP4+P3QNLNf8K2xiM_3LbeMyf7Vih5A@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Weird issue - schemas missing - on Windows 10 PostgreSQL 14 & 9.6
In-Reply-To: <5273c007-0977-2939-d522-974c19dda959@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox