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

[Prashanth Adiyodi]

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

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

[Prashanth Adiyodi]

Hi Sameer, Please see comments inline


Prashanth Adiyodi
Technical Account Manager
Skype: prashanth.adiyodi
Mob: +91-9819488395
[celltick]



From: Sameer Kumar [mailto:sameer.ku...@ashnik.com]
Sent: Friday, July 08, 2016 3:18 PM
To: Francisco Olarte; Prashanth Adiyodi
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] [BUGS] Where clause in pg_dump: need help


On Fri, Jul 8, 2016 at 5:38 PM Francisco Olarte 
<fola...@peoplecall.com<mailto:fola...@peoplecall.com>> wrote:
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<mailto: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).

Both databases are PostgreSQL (?). What version?
Yes, Both are postgres SQL, ver 9.3.4

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

Does this need to be done for one table or multiple tables?
Multiple tables

This backup will be then transferred to the backup DB server and will be 
inserted into that DB.

What will you be doing on the target database? Is it a read-only database?
It is not a read only database

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,

May be you can use
psql -c "COPY (SELECT .. WHERE..) TO stdout" | psql -c "COPY (mytable) FROM 
stdin"

OR
Setup replication and have a scheduled script to set recovery_target_time and 
puase_at_recovery_target to effectively replicate changes from one DB to other 
DB and maintaining a gap. But then the targetDB would be a read only replica 
and needs to be exactly same at the main DB/sourceDB

You need to explain more on version of the database, what exactly you aim at 
doing with the target DB.
Hi, the requirement is this, I have multiple tables where there may not be a 
timestamp column. I need to run a script which will execute post-midnight say 
at 2 AM and create a dump file (say data.sql), which will have records for all 
the previous day. I will then transfer this file to the target server and dump 
this data there, the idea is to create two copies of the data in case of a 
disaster on the original database server.

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 . But
if you just have insertions, copy is easy to do.

Francisco Olarte.


--
Sent via pgsql-general mailing list 
(pgsql-general@postgresql.org<mailto:pgsql-general@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
--
Best Regards
Sameer Kumar | DB Solution Architect
ASHNIK PTE. LTD.

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com<http://www.ashnik.com>