Hi.
I have a rather serious problem in my database. I'll try to summarize what happened and how far I've gotten with the help of friends from the pgsql-sql list.
First of all I apologize for the size of the question. But I do not see any other way to do it.
Windows 2008 R2 - 64 bits
PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit
Size of database: 2.8TB
I have several linked tables with inheritance in distinct tablespaces.
I usually use as the "main" the table that is in the c: \ drive, where it is represented by public.repositorio.
When the C: drive space is running out, I take the following steps:
CREATE TABLESPACE discoXX OWNER postgres LOCATION 'new drive';
CREATE SCHEMA "<schema>" AUTHORIZATION postgres;
CREATE TABLE "<schema>".repositorio (LIKE public.repositorio INCLUDING STORAGE INCLUDING INDEXES INCLUDING DEFAULTS INCLUDING CONSTRAINTS ) INHERITS ( public.repositorio) TABLESPACE discoXX;
COPY public.repositorio TO 'F:\data.bin' BINARY;
--where F:\data.bin is temporary file
ALTER TABLE "<schema>".repositorio NO INHERIT public.repositorio;
copy "<schema>".repositorio from 'F:\data.bin' BINARY;
After finishing the import, I check if everything is correct. I compare the two tables (public.repositorio x <schema>.repositorio). All this being done, I do the following:
truncate public.public;
ALTER TABLE "<schema>".repositorio INHERIT public.repositorio;
After that PostgreSQL is already able to find the information between the tables and the tablespaces scattered between the units, using only the select in the table repositorio.
Well. It happens that in my last operation, I ended up doing the truncate in the public.repositorio without undoing the inheritance of the other tables that were already linked.
Obviously, Postgres cleaned the information of all the units, less of the last one that I had just done, because I still had not redone the link between them.
It happens that I have the backup of the drives, but a physical backup of the drive and not a pg_dump because the base is too large and it would be impracticable to apply a backup policy in this way.
When I returned the copy of the drives, the records were no longer found. For example, if I make a "select count (*) from" 01052016 ".repository", the record amount will result to 0. But all the binaries are there, intact.
As I said above, with the help of friends from the pgsql-sql list, I managed to find the problem. When I did the truncate, the data was erased and the filenode was recreated and pointed to a zero file. Doing this query:
select pg_relation_filenode ('01052016.repository' :: regclass), it returns me: 13741352, when the correct link (before truncate) was 5214489.
Now, doing this other query:
select c. *
From pg_class c
Where c.relfilenode = 13741352
He returns me:
relname relnamespace reltype reloftype relowner relam relfilenode reltablespace relpages reltuples relallvisible reltoastrelid reltoastidxid relhasindex relisshared relpersistence relkind relnatts relchecks relhasoids relhaspkey relhasrules relhastriggers relhassubclass relispopulated relfrozenxid relminmxid relacl reloptions
repositorio 5205962 5214491 0 10 0 13741352 5205910 0 0 0 5214493 0 True False p r 7 0 False True False False False True 9360288 1
Now the question I need the help of friends ...
Is it possible to re-link to the item before truncate?
I noticed that it is not only a change of the field relfilenode, because there are more fields to be informed, such as (relpages, reltuples).
Has anyone ever had to do this and succeeded?
Every help is welcome. I have a part of the production stopped and some jobs are at risk.
Thanks
Roberto.
