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

On Fri, 8 Jul 2016, 8:06 p.m. Prashanth Adiyodi, <Prashantha(at)celltick(dot)com>
wrote:

> Hi Sameer, Please see comments inline
>
>
>
>
>
> *Prashanth Adiyodi *
>
> *Technical Account Manager*
>
> *Skype: prashanth.adiyodi*
>
> *Mob: +91-9819488395*
>
> [image: celltick]
>
>
>
>
>
>
>
> *From:* Sameer Kumar [mailto:sameer(dot)kumar(at)ashnik(dot)com]
> *Sent:* Friday, July 08, 2016 3:18 PM
> *To:* Francisco Olarte; Prashanth Adiyodi
> *Cc:* pgsql-general(at)postgresql(dot)org
> *Subject:* Re: [GENERAL] [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?
>
> 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.
>

So it is more like a DR server which always lags behind the master by a day
or is at mid night time of previous day.

Above you have mentioned this target db server (which I assume serves the
purpose of DR) is not read-only(?). What kind of writes will you be doing
on this servers?

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