pg_dump with select command

Hi,

On 12 September 2011 15:03, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:

Today I need some part ( subset ) of some tables to another database to a
remote server.
I need to take backup of  tables after satisfying a select query.

Is there any option to specify query in pg_dump command.I researched in the
manual but not able to find that.
Please let me know if it is possible as we can can specify in mysqldump
command.

No, pg_dump can dump full tables only. You can use psql:

psql -h <host1> ... -c 'copy (select ... from <tablename> where ...)
to stdout' | psql -h <host2> ... -c 'copy <tablename> from stdin'

(where '...' are other psql's options like user, db, ...)

--
Ondrej Ivanic
(ondrej.ivanic@gmail.com)

Any update on below issue.

Thanks

Adarsh Sharma wrote:

On 14/09/2011 10:31, Adarsh Sharma wrote:

Any update on below issue.

Thanks

Adarsh Sharma wrote:

Dear all,

Today I need some part ( subset ) of some tables to another database
to a remote server.
I need to take backup of tables after satisfying a select query.

Is there any option to specify query in pg_dump command.I researched
in the manual but not able to find that.

No, there isn't. Instead, you could use \copy from within psql (NB: very
different from the SQL command COPY).

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On Wed, Sep 14, 2011 at 5:31 AM, Adarsh Sharma <adarsh.sharma@orkash.com>wrote:

Any update on below issue.

Someone already responded to you with the answer, don't top-post.

I am sorry Sir, but Still I am not able to solve the issue.

I followed the below steps & they are :-

1. Create table from the main tables by *create table as select* command.
2. Then I take the backup of that tables and restore on the remote machine.
3. After this I have to change the table names to the original ones in
the remote server.

Where as , In mysql we have -X option to specify a query while taking
backups & then restore them. But I think Postgresql doesnot support this.

Thanks

Alban Hertroys wrote:

On Wednesday, September 14, 2011 9:48:40 pm Adarsh Sharma wrote:

I am sorry Sir, but Still I am not able to solve the issue.

I followed the below steps & they are :-

1. Create table from the main tables by *create table as select* command.
2. Then I take the backup of that tables and restore on the remote machine.
3. After this I have to change the table names to the original ones in
the remote server.

The easier way was already provided in this message:

http://archives.postgresql.org/pgsql-general/2011-09/msg00277.php

Specifically:

psql -h <host1> ... -c 'copy (select ... from <tablename> where ...)
to stdout' | psql -h <host2> ... -c 'copy <tablename> from stdin'

(where '...' are other psql's options like user, db, ...)

Where as , In mysql we have -X option to specify a query while taking
backups & then restore them. But I think Postgresql doesnot support this.

Thanks

--
Adrian Klaver
adrian.klaver@gmail.com