Redo the filenode link in tablespace

From: tel medola <tel(dot)medola(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Redo the filenode link in tablespace
Date: 2017-05-31 13:05:06
Message-ID: CANRMYmj0-etcWOm432ZLXry77oxwhhcW_vnG6Fi7SF0+Axs5mw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Osahon Oduware 2017-05-31 13:24:03 Build PostgreSQL With XML Support on Linux
Previous Message Andreas Kretschmer 2017-05-31 12:52:07 Re: Slow query plan used