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

Hi Franciso, My comments below inline

-----Original Message-----
From: Francisco Olarte [mailto:folarte(at)peoplecall(dot)com]
Sent: Friday, July 08, 2016 3:07 PM
To: Prashanth Adiyodi
Cc: pgsql-general(at)postgresql(dot)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(at)celltick(dot)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 insert it 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 postgres I 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 operation here>. 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 1st time) 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.