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