Re: 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(dot)klaver(at)aklaver(dot)com