Subject: Re: Lost my tablespace
To: tel medola <tel.medola@gmail.com>
Cc: pgsql-sql@postgresql.org
References: 
 <CANRMYmgu3ddSU62-=a3QCnsbGYuMVJXZ-ydTm7vd_2TQLp-Oqg@mail.gmail.com>
 <CANRMYmhxARYp73boRCfHB5VHDFT+wM5G=kTGrCQcN0Ds=uxLWw@mail.gmail.com>
 <a9063a7d-ac70-abb8-38f4-6ef49bf401f3@aklaver.com>
 <CANRMYmjc7nc0GpxCkxApQTgAr+9YGooUvA8AEU_9SjjBJb+qqg@mail.gmail.com>
 <32bb9df2-3b4d-95cb-1f82-8da675536a9b@aklaver.com>
 <CANRMYmgw9+vreD0+43vGK+JYyshMaea=zedwc1ufYS_cBEj5VA@mail.gmail.com>
 <a18d2b5a-255f-e5b5-0c4d-686eb182e88e@aklaver.com>
 <CANRMYmjY9V-rzygyDFrDmf2GDP_-vcsEiP-ZOXd6WvNAFUEnhA@mail.gmail.com>
 <b7598d9a-eef6-1ff3-7d30-19838b3add51@aklaver.com>
 <CANRMYmjXeyEYeEaVV+8k_DZBXuNipCWZ8+wWvYCKGnSKRAAEmQ@mail.gmail.com>
 <100e137f-4510-031c-1fa5-51b2a145f39a@aklaver.com>
 <CANRMYmgKvrGEo5NOQ7ScRBJj-jgae5bS5y6oXVrwQh0csonPbw@mail.gmail.com>
 <c8865858-2ab4-12d2-8b2f-f8ef24396860@aklaver.com>
 <CANRMYmjzn9RcB9W-nCnvt24cKE9hgmXB05gXkVb5PFC4sqCX6Q@mail.gmail.com>
 <88a21207-a3c1-3e5e-88e6-a2403a0ea66b@aklaver.com>
 <CANRMYmhGJAaZEMCevzcrhVVVhEMJzgo+P_Ms_E4UiJB60ozJHg@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <48eeef4b-b04d-77c1-2b51-d59f23bdd203@aklaver.com>
Date: Tue, 30 May 2017 15:07:23 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.1.1
MIME-Version: 1.0
In-Reply-To: 
 <CANRMYmhGJAaZEMCevzcrhVVVhEMJzgo+P_Ms_E4UiJB60ozJHg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql