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

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

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');
2) :( help!

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

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

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?

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

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

Are you trying to recover a table by copying in a table from somewhere else? Yes because I can't modify the original file

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

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

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

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

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

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

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

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

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

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

But pg_dirty_read only runs in Linux. It doesnt run in windows.

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

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