Hi Franciso, My comments below inline
-----Original Message-----
From: Francisco Olarte [mailto:fola...@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 <prashan...@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 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
