RE: Error message restarting a database

-----Original Message-----
From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
Sent: Sunday, January 27, 2019 17:38
To: Begin Daniel
Cc: pgsql-general
Subject: Re: Error message restarting a database

On 1/27/19 2:22 PM, Begin Daniel wrote:
> -----Original Message-----
> From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
> Sent: Sunday, January 27, 2019 16:52
> To: Begin Daniel
> Cc: pgsql-general
> Subject: Re: Error message restarting a database
>
> On 1/27/19 1:38 PM, Begin Daniel wrote:
>> -----Original Message-----
>> From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
>> Sent: Sunday, January 27, 2019 16:15
>> To: Begin Daniel
>> Cc: pgsql-general
>> Subject: Re: Error message restarting a database
>>
>> On 1/27/19 12:56 PM, Begin Daniel wrote:
>>
>> Not a good day, forgot to actually Cc list last time.
>>
>>> -----Original Message-----
>>> From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
>>> Sent: Sunday, January 27, 2019 15:43
>>> To: Begin Daniel
>>> Subject: Re: Error message restarting a database
>>>
>>> On 1/27/19 12:38 PM, Begin Daniel wrote:
>>> Please reply to list also
>>> Ccing list.
>>>> -----Original Message-----
>>>> From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
>>>> Sent: Sunday, January 27, 2019 15:33
>>>> To: Begin Daniel; pgsql-general(at)lists(dot)postgresql(dot)org
>>>> Subject: Re: Error message restarting a database
>>>>
>>>> On 1/27/19 12:23 PM, Begin Daniel wrote:
>>>>> I worked with a PostgreSQL database (9.3) for a couple of years on my
>>>>> PC. I shut down the PostgreSQL server about a year ago and I obviously
>>>>> did not access the database since. Now I am trying to access again to
>>>>> the database but I get the following error message...
>>>>>
>>>>> FATAL: *"pg_tblspc/113608/PG. 9.3_ 201306121/18364" is not a valid data
>>>>> directory
>>>>>
>>>>> DETAIL: File *" pg_tblspc/113608/PG. 9.3_ 201306121/18364/PG_VERSION" is
>>>>> missing.
>>>>>
>>>>> Anyone has an idea on how to solve this problem?
>>>>
>>>> What OS and version?
>>>> Best guess is your OS updated Postgres in the past year and the newer
>>>> version is trying to load an older data directory.
>>>>
>>>>>
>>>>> Daniel
>>>>>
>>>>
>>>> OS and version...
>>>> Windows 10 Pro, Version 1809 installed on 2019-01-11
>>>> OS Build 17763.253
>>>
>>> Whoops, just realized the errors are pointing at a tablespace/dir.
>>>
>>> Does the directory in the error message exist?
>>>
>>>>
>>>>
>>> I have multiple tablespaces. According to the definition of my tablespaces, the error point on the following directory: K:\pgsqlData\PG_9.3_201306121\18364
>>> The directory exists with 888 items in it, but there is no file named PG_VERSION
>>>
>>
>> Are all the tablespaces connected to a single Postgres instance?
>>
>> Are you still using Postgres 9.3?
>>
>> Does the Postgres top level data directory still exist?
>>
>>
>> Right, not a good day ;-)
>> - All tablespaces are connected to a single Postgres database instance
>> - I am still using Postgres 9.3 since I did not change anything related to PostgreSQL since I shut down the server a year ago
>> - I am not sure what you mean by top level directory.
>
> PGDATA from here:
> https://www.postgresql.org/docs/10/storage-file-layout.html
>
> I am going to say PGDATA exists given the below.
>
>
>>
>> I am using PgAdmin to access my PostgreSQL databases. I can access the default database (postgres), tablespace definitions, group roles and login roles. The other database instance I have is the one that send me error messages.
>>
>
> To be clear you are running only one instance(cluster) of Postgres and
> that instance has several databases defined in it, correct?
>
> If you go to PGDATA.pg_tblspc do you have links to the tablespaces?
>
> I only installed one instance of Postgres on my PC, which I use to manage two databases (postgres and osmdump). osmdump is the database that complains.
>
> Regarding the links to the tablespaces, I first ran the following request.
> SELECT spcname FROM pg_tablespace; and got the following list.
> "pg_default"
> "pg_global"
> "workspace"
> "datadrive1"
> "datadrive2"
> "datadrive3"
> "datadrive0"
>
> I went to E:\pgsqlData\pg_tblspc and found the links to the five last tablespaces above (I manually created them, the first two were created when I installed Postgres).
> E:\pgsqlData\pg_tblspc \113608\PG_9.3_201306121\18364 link to the 888 items mentioned previously (physically stored in K:\pgsqlData\pg_tblspc...)

Hmm, K:\pgsqlData\pg_tblspc looks suspiciously like something
masquerading as another PGDATA directory. Does a directory listing show
what is shown in?:

https://www.postgresql.org/docs/10/storage-file-layout.html

> I also found a PG_VERSION file in E:\pgsqlData. The file contains the value 9.3
>
> Daniel
>

Sorry, bad copy paste, you should have read K:\pgsqlData\PG_9.3_201306121\18364
The suggested content is found only in E:\pgsqlData, including PG_VERSION file and pg_tblspc subdirectory
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com