Re: [SQL] Lost my tablespace

On 05/30/2017 11:56 AM, tel medola wrote:


> To be clear the tablespace for public.repositorio is the default one in 
> $PGDATA on the C:\ drive, correct?
> /Yes./
> 
> So is there anything in public.repositorio now?
> /Yes, users are inserting information into the public.repositorio table/
> 

> 
> Is the data in 13042017.repositorio the data you want?
> /No. The information on this drive I have, because the link was not 
> lost. Those are the other units I need to 
> recover("01052016".repositorio, 
> "05122016".repositorio,"22082016".repositorio,"30122015".repositorio )/
> 

I think I see now. The schema names are the dates you transferred the 
data out of public.repositorio into the appropriate schema. I also think 
I see what the issue might be with the tablespaces. When you did the
TRUNCATE the table relfilenode changed:

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

"
Note that while a table's filenode often matches its OID, this is not 
necessarily the case; some operations, like TRUNCATE, REINDEX, CLUSTER 
and some forms of ALTER TABLE, can change the filenode while preserving 
the OID. Avoid assuming that filenode and table OID are the same. Also, 
for certain system catalogs including pg_class itself, 
pg_class.relfilenode contains zero. The actual filenode number of these 
catalogs is stored in a lower-level data structure, and can be obtained 
using the pg_relation_filenode() function.

In the tablespace the tables are stored by that relfilenode also:
From same link as above:

"Tablespaces make the scenario more complicated. Each user-defined 
tablespace has a symbolic link inside the PGDATA/pg_tblspc directory, 
which points to the physical tablespace directory (i.e., the location 
specified in the tablespace's CREATE TABLESPACE command). This symbolic 
link is named after the tablespace's OID. Inside the physical tablespace 
directory there is a subdirectory with a name that depends on the 
PostgreSQL server version, such as PG_9.0_201008051. (The reason for 
using this subdirectory is so that successive versions of the database 
can use the same CREATE TABLESPACE location value without conflicts.) 
Within the version-specific subdirectory, there is a subdirectory for 
each database that has elements in the tablespace, named after the 
database's OID. Tables and indexes are stored within that directory, 
using the filenode naming scheme. The pg_default tablespace is not 
accessed through pg_tblspc, but corresponds to PGDATA/base. Similarly, 
the pg_global tablespace is not accessed through pg_tblspc, but 
corresponds to PGDATA/global.
"

You used the file system backup to restore the old tablespace that that 
had the old relfilenode names for the table. The thing is that Postgres 
is looking for the new relfilnode names in the tablespace and not 
finding them. I would start by doing this:

select pg_relation_filenode('01052016.repositorio'::regclass);

and seeing if that returned number exists in the tablespoace directory 
for disco02. My guess is that it does not. I'm also going to say that is 
that is going to be the same for all the tables except 13042017.repositorio.

If that is the case then it is a matter of getting the number that is in 
the Postgres system catalog in sync with the one that is on disk. This 
is not something I have done before and I would advise you to get other 
opinions on how to do this. I would say it is now time to subscribe to 
pgsql-general and ask how to do this. It would help to give a brief 
description of what you did and then cut and paste my thoughts from above.


-- 
Adrian Klaver
adrian.klaver@aklaver.com