agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@aklaver.com>
To: tel medola <tel.medola@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Lost my tablespace
Date: Tue, 30 May 2017 12:35:34 -0700
Message-ID: <88a21207-a3c1-3e5e-88e6-a2403a0ea66b@aklaver.com> (raw)
In-Reply-To: <CANRMYmjzn9RcB9W-nCnvt24cKE9hgmXB05gXkVb5PFC4sqCX6Q@mail.gmail.com>
References: <CANRMYmgu3ddSU62-=a3QCnsbGYuMVJXZ-ydTm7vd_2TQLp-Oqg@mail.gmail.com>
<CANRMYmjbg6MFhB=3wo6tcueUL8mqmqRfysSYrgRFHUu04Mxrwg@mail.gmail.com>
<97bd2743-46c3-32c4-7b3f-62def38aea65@aklaver.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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (25+ messages) latest in thread
Message-ID: <88a21207-a3c1-3e5e-88e6-a2403a0ea66b@aklaver.com>
Permalink: ../88a21207-a3c1-3e5e-88e6-a2403a0ea66b@aklaver.com/
Also on: postgresql.org/message-id/88a21207-a3c1-3e5e-88e6-a2403a0ea66b@aklaver.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.klaver@aklaver.com, tel.medola@gmail.com
Subject: Re: Lost my tablespace
In-Reply-To: <88a21207-a3c1-3e5e-88e6-a2403a0ea66b@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox