Re: [SQL] Lost my tablespace - Mailing list pgsql-sql

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