Hi Franciso, My comments below inline -----Original Message----- From: Francisco Olarte [mailto:folarte@xxxxxxxxxxxxxx] Sent: Friday, July 08, 2016 3:07 PM To: Prashanth Adiyodi Cc: pgsql-general@xxxxxxxxxxxxxx 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@xxxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
