Re: [SQL] Lost my tablespace

On 05/30/2017 01:36 PM, tel medola wrote:
> EXACT !!!!!
> 
> When I did the truncate, it erased all the files that referenced the 
> table and created a new one (empty). That's why when I returned the 
> physical files to the drives, it does not find the old reference and it 
> is empty.
> 
> I'll search how to redo the link for the correct filenode.
> Thanks very much for your help!!!
> 

The thing to remember is:

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

"When a table or index exceeds 1 GB, it is divided into gigabyte-sized 
segments. The first segment's file name is the same as the filenode; 
subsequent segments are named filenode.1, filenode.2, etc. This 
arrangement avoids problems on platforms that have file size 
limitations. (Actually, 1 GB is just the default segment size. The 
segment size can be adjusted using the configuration option 
--with-segsize when building PostgreSQL.) In principle, free space map 
and visibility map forks could require multiple segments as well, though 
this is unlikely to happen in practice."

and:

"A table that has columns with potentially large entries will have an 
associated TOAST table, which is used for out-of-line storage of field 
values that are too large to keep in the table rows proper. 
pg_class.reltoastrelid links from a table to its TOAST table, if any. 
See Section 58.2 for more information."





-- 
Adrian Klaver
adrian.klaver@aklaver.com