Re: [BUGS] Where clause in pg_dump: need help

Hi Franciso, My comments below inline

-----Original Message-----
From: Francisco Olarte [mailto:folarte@peoplecall.com] 
Sent: Friday, July 08, 2016 3:07 PM
To: Prashanth Adiyodi
Cc: pgsql-general@postgresql.org
Subject: Re: [BUGS] Where clause in pg_dump: need help

1.- CCing to the list ( remember to hit reply-all or whatever your MUA uses for that, otherwise threads may get lost
).

2.- Try to avoid top-posting, it's not the style of the list ( or mine ).

On Fri, Jul 8, 2016 at 4:43 AM, Prashanth Adiyodi <Prashantha@celltick.com> wrote:
> Basically my requirement is, I have a live Db with certain tables and 
> a backup Db at another location (both on postgressql). I need to take 
> a backup of this live DB every night for the previous day (i.e the 
> backup script running on 07/07/2016 will take the backup of the DB for 
> 06/07/2016). This backup will be then transferred to the backup DB 
> server and will be inserted into that DB. From what I have read 
> pg_dump is the solution (similar to export in oracle), do you think of 
> any other approach to get to this objective, have you come across a 
> script or something that already does this,

Your requirement is a bit 'understated'. I assume your problem is:

1.- You have a backup with a series of tables which get inserted WITH a timestamp.
Adi-The series of tables may or may not have timestamp
2.- At the end of the day you want to transfer the inserted data, and only the inserted data, to another server and
insertit ther.
 
Adi-Exactly., somewhere post midnight I need to transfer the inserted data for the day to another DB.

If BOTH servers are postgres, you can do it easily with a series of COPY commands easily. If the target one is not
postgresI would use it too, but pass the COPY data through a perl script to generate whatever syntax the target DB
needs( I've done that to go from postgres to sql server and back using freebcp, IIRC, on the sql server side )
 

You still can have problems IF you have updates to the tables, or deletions, or <insert your favorite problematic
operationhere>. But if you just have insertions, copy is easy to do.
 
Adi-
I am OK with the copy command, however I am not able to understand (my bad, I am not used to postgres and using for the
1sttime) the where clause that should be used to achieve this result.
 

I tries using something like the below,
psql -d my_db -c 'copy (select * from mytab WHERE date_trunc('day',NOW() - interval '1 day') TO STDOUT' -o data1.copy;

but this, I am sure has some syntax errors, could you help correct this, 

Francisco Olarte.