On Fri, 8 Jul 2016, 8:06 p.m. Prashanth Adiyodi, <prashan...@celltick.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.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> > 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> 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@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 > -- -- 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
