Thread: Read recover rows

On 12/13/2012 02:23 PM, Alejandro Carrillo wrote:
> Hi,
>
> 1) Anybody knows how to create a table using a table file? It isn't a
> fdw, is a file that compose the table in postgresql and get with the
> pg_relation_filepath function. Ex:
>
>   select pg_relation_filepath('pg_proc');

Not sure what you are asking. The above just returns a file path to the
on disk representation of the table.

Are you looking for

CREATE TABLE AS

http://www.postgresql.org/docs/9.2/interactive/sql-createtableas.html

OR

CREATE TABLE LIKE

http://www.postgresql.org/docs/9.2/interactive/sql-createtable.html

OR

Do want to create a table from a script?

>
> 2) Anybody knows a JDBC or a multiplatform code that let read the delete
> rows of a table without writing of a table file?

Not sure if that is possible, someone else may have a better answer.

>
> Thanks


--
Adrian Klaver
adrian.klaver@gmail.com

On 12/13/2012 03:14 PM, Alejandro Carrillo wrote:
> Hi,
>
> 1) Isn't a script. The file is a table file get using the function
> pg_relation_filepath:
> select pg_relation_filepath('pg_proc');

The above does NOT return a file it just returns the file path name.
Postgres stores it tables(relations) with numbers. This function just
helps which number is associated with a table.

> 2) :( help!

Still not sure what you are trying to do:)

Wild guess, pg_proc was deleted and you want to recover it?


--
Adrian Klaver
adrian.klaver@gmail.com

On 12/13/2012 03:30 PM, Alejandro Carrillo wrote:
> I have a file CALLED 11649 in the path: %PG_DATA%\base\11912
> This file is a table data in postgresql. Ex: pg_proc.
> Now I copied this file, renamed it and I want to connect this file to
> another new table with the same structure and data.
> How I can do this?

As far as I know you cannot. If you want to create a new table from an
existing table you will need to use the SQL commands I mentioned
previously. This assumes the original table exists. Are you trying to
recover a table by copying in a table from somewhere else?

>
>


--
Adrian Klaver
adrian.klaver@gmail.com

On 12/13/2012 03:41 PM, Alejandro Carrillo wrote:
> Are you trying to recover a table by copying in a table from somewhere
> else? Yes because I can't modify the original file

You will not be able to work with the disk file directly, you will need
to go through the database.

Have you tried pg_dump:

http://www.postgresql.org/docs/9.2/interactive/app-pgdump.html

Something like:

pg_dump -t some_table -f some_table.sql -U some_user  database_name

Where the dummy names are replaced with the table/database you want.

This will create a plain text file. If you need to change the name you
could do find and replace on the table name.


This assumes you are trying to move a user created table not a system
table.
Is that the case?


--
Adrian Klaver
adrian.klaver@gmail.com

Alejandro Carrillo escribió:
> Hi,
>
> 1) Anybody knows how to create a table using a table
> file? It isn't a fdw, is a file that compose the table in postgresql and
>  get with the pg_relation_filepath function. Ex:
>  
>  select pg_relation_filepath('pg_proc');

Make sure the server is down and replace a table's file with the file
you have.  You can just create a dummy empty table with exactly the same
row type as the one that had the table the file was for; you need to
recreate dropped columns as well.

> 2) Anybody knows a JDBC or a multiplatform code that let read the delete rows of a table without writing of a table
file?

You already tried pg_dirtyread, I imagine, after I suggested it to you
in the spanish list?  You can use it through JDBC.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 12/13/2012 06:51 PM, Alvaro Herrera wrote:
> Alejandro Carrillo escribió:
>> Hi,
>>
>> 1) Anybody knows how to create a table using a table
>> file? It isn't a fdw, is a file that compose the table in postgresql and
>>   get with the pg_relation_filepath function. Ex:
>>
>>   select pg_relation_filepath('pg_proc');
>
> Make sure the server is down and replace a table's file with the file
> you have.  You can just create a dummy empty table with exactly the same
> row type as the one that had the table the file was for; you need to
> recreate dropped columns as well.

So it is possible to slide a file under a table?
How exactly do you do that and keep the OID and filenode references in sync?




--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver <adrian.klaver@gmail.com> writes:
> On 12/13/2012 06:51 PM, Alvaro Herrera wrote:
>> Make sure the server is down and replace a table's file with the file
>> you have.  You can just create a dummy empty table with exactly the same
>> row type as the one that had the table the file was for; you need to
>> recreate dropped columns as well.

> So it is possible to slide a file under a table?
> How exactly do you do that and keep the OID and filenode references in sync?

The XIDs embedded in the tuples would be the big problem.

            regards, tom lane

On 12/14/2012 07:30 AM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@gmail.com> writes:
>> On 12/13/2012 06:51 PM, Alvaro Herrera wrote:
>>> Make sure the server is down and replace a table's file with the file
>>> you have.  You can just create a dummy empty table with exactly the same
>>> row type as the one that had the table the file was for; you need to
>>> recreate dropped columns as well.
>
>> So it is possible to slide a file under a table?
>> How exactly do you do that and keep the OID and filenode references in sync?
>
> The XIDs embedded in the tuples would be the big problem.

Well I always figured doing something like this would be equivalent to
rebuilding a car engine while it was running:) I am intrigued by the
possibility though. Just wondering if it has actually been done
successfully.


>
>             regards, tom lane
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

Alejandro Carrillo escribió:
> But pg_dirty_read only runs in Linux. It doesnt run in windows.

So port it.  There's no fundamental reason for it not to work.


--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services