RE: Error message restarting a database - Mailing list pgsql-general

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.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.klaver@aklaver.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.klaver@aklaver.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.klaver@aklaver.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.klaver@aklaver.com]
>>>> Sent: Sunday, January 27, 2019 15:33
>>>> To: Begin Daniel; pgsql-general@lists.postgresql.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
yearago
 
>> - 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).
osmdumpis 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
firsttwo 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
inK:\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.klaver@aklaver.com