Thread: pg_dump with select command

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.
Please let me know if it is possible as we can can specify in mysqldump
command.


Thanks

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:
> 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.
> Please let me know if it is possible as we can can specify in
> mysqldump command.
>
>
> Thanks
>

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

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 14 September 2011 11:31, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:

Any update on below issue.

What do you expect an update on? You got your answer, which included the solution.

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