Thread: 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/2016will take the backup of the DB for 06/07/2016). This backup will be then transferred to the backup DB server
andwill be inserted into that DB. From what I have read pg_dump is the solution (similar to export in oracle), do you
thinkof 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.
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.

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.

Francisco Olarte.

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.

Hi Adi: ( Is this correct? Adi is what your message uses to mark your replies ).

On Sun, Jul 10, 2016 at 6:53 PM, Prashanth Adiyodi
<Prashantha@celltick.com> wrote:
> Hi Franciso, My comments below inline

Got them. Only problem is your MUA does not signal quotes. It looks
like some kind of outlook by the headers it sends, so I assume it is
normal, I will try to correct it.

>> 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.

Then you NEED some kind of marker. The tables WITHOUT timestamp (
point 1 above ) are going to be difficult.

For the discussion I assume you are somehow capable of making a SELECT
query which identifies inserted data for the day, and that either you
do not care about updates/deletions ( not having any is a subset of
this condition ) or you can do a query for those too.

> 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
forthe 1st time) the where clause that should be used to achieve this result. 

Well, then your problem is on the queries. You need to be able to
identify the data inserted yesterday. There is no magic way to do it.
You NEED some kind of timestamp column. If you lack this you can use a
trigger to mark them into auxiliary tables or, if you feel
adventurous, you can try to use the xmin/xmax columns ( I would NOT
recommend even trying that, given you are having problems with simple
select queries ).

> 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;

Your where expresion is a constant of timestamp type, i.e.,
'2016-07-11 12:00:00+00', where needs a BOOLEAN.

YOU need to be able to identify the inserted rows. YOU know your data
definitions. Ar you able to query them ?

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

NOT, because I do not know the table structure. Only you can do that.

Francisco Olarte.