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

On Fri, Jul 8, 2016 at 5:38 PM Francisco Olarte <folarte(at)peoplecall(dot)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
> <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).

Both databases are PostgreSQL (?). What version?

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

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

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

> 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.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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