Re: [SQL] Lost my tablespace - Mailing list pgsql-sql

On 05/30/2017 07:56 AM, tel medola wrote:

Looks to me like the above is inheriting itself, note the non-schema qualified repositorio. Pretty sure that is not good
/I realized this too, I looked in other banks (of other applications) that I have, and this information is the same: "Têm OIDs: não"/

"Têm OIDs: não" is not part of the problem. Many years ago user tables where created with a system column oid. This turned into a problem and now user tables are not created with an oid column. It is still possible to create  or alter a table to have an oid column(I would not suggest that). That is what "Têm OIDs: não" is showing.

So I assume the other repositorio tables in the other schemas are as above but pointing at different tablespaces, correct?
/Yes/

Understood. Still one of the issues is not providing information from explicit commands provided. As an example in previous post I had:
/Ok. Thanks! /
/
/
/rai=# show search_path;/
/                      search_path/
/--------------------------------------------------------/
/ 1052016, 5122016, 13042017, 22082016, 30122015, public/
/(1 registro)/

So if you do:

\d+ *.repositorio

you should see all the repositorio tables.

It is important remember is that what is obvious to you looking at the terminal is not so obvious on this end. To understand what is going on we need specific information.
/Understood/


I understand the pressure you are under. I am going to be heading out to work here shortly and will not be able to help for awhile. I am not sure where you are, but you might want to look here:
/Ok, very thanks./


As I remember 13042017.repositorio  is something you created after the TRUNCATE.
So where did the 491 GB in data come from?
Can it be used to seed the other tables?

/I always use the public.repository table for the preliminary information. After drive C: gets full, I move the data to a new tablespac on another drive and then do the truncate of the public. After that, I relink the inheritance with the new table created (in the new tablespace) so that postgres can extract the data automatically./
/The schema(13042017) was done this way (after the wrong truncate)./

To be clear the tablespace for public.repositorio is the default one in $PGDATA on the C:\ drive, correct?

So is there anything in public.repositorio now?

<NOTE>
Before I forget and for future use:

1) Truncate is transaction safe so can be done in a BEGIN/ROLLBACK or COMMIT sequence. Though if you are space constrained on C:\ I am not quite sure if truncating 400GB+ of data in an open transaction might not cause space issues also.

2) TRUNCATE has an ONLY option that restricts the action to the named table only and not its descendant(child) tables:

https://www.postgresql.org/docs/9.6/static/sql-truncate.html
<NOTE>


Is the data in 13042017.repositorio the data you want?

If so why not use that data to reseed the other child tables?

2017-05-30 11:21 GMT-03:00 Adrian Klaver <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>:

    On 05/30/2017 06:50 AM, tel medola wrote:

        That despite recovering the backup, I can not access my data. So
        I posted that I lost my tablespaces.
        /Aware, thanks. In the next email I'll be careful about that/
        /
        /


    See comments inline.

        Did you try my previous suggestions:/
        /
        /Yes, but dont list all tables, in all schemas/.
        /Bellow the main table/:

        /rai=# \d+ public.repositorio;/
        /                                          Tabela
        "public.repositorio"/
        /    Coluna     |            Tipo             |             Modificadores     | Armazenamento | Estatísticas | Descrição/
        /---------------+-----------------------------+-----------------------+---------------+--------------+-----------/
        / id_documento  | character(39)               |                               | extended      |              |/
        / documento     | bytea                       |                               | extended      |              |/
        / nomedocumento | character varying           |                               | extended      |              |/
        / id            | character(39)               | nÒo nulo                      | extended      |              |/
        / datahora      | timestamp without time zone | valor padrÒo de
        now() | plain         |              |/
        / id_itemtype   | bigint                      | nÒo nulo                      | plain         |              |/
        /═ndices:/
        /    "repositorio_pkey" PRIMARY KEY, btree (id)/
        /    "repositorio_iddocumento" btree (id_documento) WITH
        (fillfactor=100)/
        */Tabelas descendentes: "01052016".repositorio,/*
        */                      "05122016".repositorio,/*
        */                      "22082016".repositorio,/*
        */                      "30122015".repositorio,/*
        */                      repositorio/*


    Looks to me like the above is inheriting itself, note the non-schema
    qualified repositorio. Pretty sure that is not good.

        /Têm OIDs: não/


        rai=# \d+ 01052016.*
                                                   Tabela
        "01052016.repositorio"
              Coluna     |            Tipo             |             Modificadores     | Armazenamento | EstatÝsticas | DescriþÒo
        ---------------+-----------------------------+-----------------------+---------------+--------------+-----------
           id_documento  | character(39)               |                               | extended     |              |
           documento     | bytea                       |                               | extended     |              |
           nomedocumento | character varying           |                               | extended     |              |
           id            | character(39)               | nÒo nulo                      | extended     |              |
           datahora      | timestamp without time zone | valor padrÒo de
        now() | plain        |              |
           id_itemtype   | bigint                      | nÒo nulo                      | plain        |              |
        ═ndices:
              "repositorio_pkey" PRIMARY KEY, btree (id)
              "repositorio_id_documento_idx" btree (id_documento) WITH
        (fillfactor=100)
        *Heranças: public.repositorio*
        *Têm OIDs: não*
        *Tablespace: "disco02"*


                 ═ndice "01052016.repositorio_id_documento_idx"
              Coluna    |     Tipo      |  DefiniþÒo   | Armazenamento
        --------------+---------------+--------------+---------------
           id_documento | character(39) | id_documento | extended
        btree, para tabela "01052016.repositorio"
        Opþ§es: fillfactor=100


                   ═ndice "01052016.repositorio_pkey"
           Coluna |     Tipo      | DefiniþÒo | Armazenamento
        --------+---------------+-----------+---------------
           id     | character(39) | id        | extended
        chave primßria, btree, para tabela "01052016.repositorio"


    So I assume the other repositorio tables in the other schemas are as
    above but pointing at different tablespaces, correct?


        /Adrian, I see you really want to help me, thank you very much
        for that. I apologize if at any point I did not quite understand
        what you meant, it is that writing in English is not the best.


    Understood. Still one of the issues is not providing information
    from explicit commands provided. As an example in previous post I had:

    What does:

        show search_path;

    return?

    It is important remember is that what is obvious to you looking at
    the terminal is not so obvious on this end. To understand what is
    going on we need specific information.


        /
        /But I need to know where you want to get the questions, because
        the logical links in the table are all correct, but for some
        reason Postgres can not access my data and I'm practically
        losing my job because I can not deliver the information I should./


    I understand the pressure you are under. I am going to be heading
    out to work here shortly and will not be able to help for awhile. I
    am not sure where you are, but you might want to look here:

    https://www.postgresql.org/support/professional_support/
    <https://www.postgresql.org/support/professional_support/>

    for folks close by that could help.


        /Is there a way to get access to this data again?/


    One thing that I have not understood is:

    Esquema  |    Nome     |  Tipo  |   Dono   |  Tamanho   | Descrição
    ----------+-------------+--------+----------+------------+-----------
      01052016 | repositorio | tabela | postgres | 8192 bytes |
      05122016 | repositorio | tabela | postgres | 8192 bytes |
      13042017 | repositorio | tabela | postgres | 491 GB  |
      22082016 | repositorio | tabela | postgres | 8192 bytes |
      30122015 | repositorio | tabela | postgres | 8192 bytes |


    As I remember 13042017.repositorio  is something you created after
    the TRUNCATE.

    So where did the 491 GB in data come from?

    Can it be used to seed the other tables?



    --     Adrian Klaver

    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com