Thread: Synchronizing Data?
Is there a way to synchronize data between postgresql on a local machine and postgresql on a remote web server? Maybe use the TCP/IP Connection with SSH somehow? Anyone done this or have any good ideas? Thanks a lot, Joe
Check this out: http://www.erserver.com/ I don't know the status of this project, but I'm certainly looking forward to try it. Poul L. Christiansen joe@jwebmedia.com wrote: > > Is there a way to synchronize data between postgresql on a local machine > and postgresql on a remote web server? Maybe use the TCP/IP Connection > with SSH somehow? Anyone done this or have any good ideas? Thanks a lot, > > Joe
Anything that can be done without waiting for erserver? I need to find a solution asap. Any ideas at all would be great. I don't even know where to really begin. Thanks, Joe
This might be the stupidest idea ever, but it seems logical to me, so tell me what you think: What if I use the TCP/IP connection to connect the two databases - Then I SELECT * INTO a temporary table on the remote server. Once that is completed (and any relational tables), I drop the table on the remote server, and rename the temp one to the name of the one I dropped (make sense?). I'm sure there are some major flaws with my theory (I'm fairly new to postgresql)...please let me know. Thanks, Joe
> Anything that can be done without waiting for erserver? I need to find a > solution asap. Any ideas at all would be great. I don't even know where > to really begin. Thanks, Database synchronization is a tricky thing to begin with. I think that if you can give us more details of exactly what you need, we can probably offer a little better help. : ) steve
> Database synchronization is a tricky thing to begin with. I think that if > you can give us more details of exactly what you need, we can probably offer > a little better help. : ) > > steve I'd be glad to. Here's the deal: I have a client who has a Linux (RedHat 6.2) server that he uses to add/modify/delete records for his e-comm site. He is on a metered ISDN line (only thing availble in the area for what he wanted to spend...) and does not what to be 'online' (it's so screwed up...) to add all of his records. Anyway...so he's adding/modifying/deleting records on his local server and we need to get that data up to the web server. In addition to putting his changes up, we need to remove any ordered items (from the web site) from the database, once he has synchronized it with his most recent data. Then, obviously, it needs to go back down to his local machine so he has the most current records to work with. It's really a big mess. Any ideas? Thanks, Joe
> I'd be glad to. Here's the deal: > > I have a client who has a Linux (RedHat 6.2) server that he uses to > add/modify/delete records for his e-comm site. He is on a metered ISDN line > (only thing availble in the area for what he wanted to spend...) and does not > what to be 'online' (it's so screwed up...) to add all of his records. > Anyway...so he's adding/modifying/deleting records on his local server and we > need to get that data up to the web server. In addition to putting his changes > up, we need to remove any ordered items (from the web site) from the database, > once he has synchronized it with his most recent data. Then, obviously, it > needs to go back down to his local machine so he has the most current records > to work with. It's really a big mess. Any ideas? Thanks, It seems like it's not something that needs to be done in real-time. Am I correct? If so, then soing a pg_dumpall, gzip it up, and restore on the other server is probably the way to go. steve
How would you go about mirroring a database server such that if all updates to the main database are also made to the backup? Mark
> How would you go about mirroring a database server such that if all updates > to the main database are also made to the backup? Try usogres. It works pretty well for me (PostgreSQL 7.0.2). -- Tatsuo Ishii >Subject: [ANNOUNCE] usogres-0.0.5 released >From: Hosokawa Tetsuichi <hosokawa@good-day.co.jp> >To: PostgreSQL Announce <pgsql-announce@postgresql.org> >Date: Sat, 28 Oct 2000 12:48:36 +0900 >X-Mailer: Sylpheed version 0.4.1 (GTK+ 1.2.8; Linux 2.2.17-0vl10; i686) > >Hi! > >Realtime Backup Utility Usogres Version 0.0.5 is released. > >Please check http://usogres.good-day.net/ > >Thank you! >-- >* Tetsuichi Hosokawa >* hosokawa@good-day.co.jp
On Mon, 6 Nov 2000, Steve Wolfe wrote:
> It seems like it's not something that needs to be done in real-time. Am
> I correct? If so, then soing a pg_dumpall, gzip it up, and restore on the
> other server is probably the way to go.
I developed a realy not time critical Web-Site (small project, but will
grow later on). If I change something in the web database on my client
behind the firewall I build a Debian package containing the pg_dump and
some additional information (mainly changes file) and upload the
Debian file to the server outside the firewall. There I only have to
apt-get update; apt-get upgrade
and the new database is installed. Should work also for RPMs. Not
the best solution but works in my (not time critical) case.
Kind regards
Andreas.
My only concern with this is the time the web database would be down. I assume if I wanted to load the pg_dumpall file, I'd have to delete everything from the database (at least the affected tables) first. Correct? I'd hate for someone to hit the web just after I had deleted everything and find a broken site, even if for just a second. However, if there's no way around it... Joe Steve Wolfe wrote: > It seems like it's not something that needs to be done in real-time. Am > I correct? If so, then soing a pg_dumpall, gzip it up, and restore on the > other server is probably the way to go. > > steve
On Tue, 7 Nov 2000 joe@jwebmedia.com wrote:
> My only concern with this is the time the web database would be down. I assume
> if I wanted to load the pg_dumpall file, I'd have to delete everything from the
> database (at least the affected tables) first. Correct? I'd hate for someone to
> hit the web just after I had deleted everything and find a broken site, even if
> for just a second. However, if there's no way around it...
That's why I went the way with the Debian package which avoids manual
work and is as fast as possible (including restarting apache if necessary)
and avoids errors in manual administration. If you can't cope with
some seconds downtime this is no solution for you. For my application
is is enough.
Kind regards
Andreas.
On Mon, Nov 06, 2000 at 12:59:14PM -0600, joe@jwebmedia.com wrote: > Is there a way to synchronize data between postgresql on a local machine > and postgresql on a remote web server? Maybe use the TCP/IP Connection > with SSH somehow? Anyone done this or have any good ideas? Thanks a lot, > As far as I understood in the running discussion you only need to update a few tables. I think, the easiest solution is a small perl-script running on one of this two machines and building up a connection to both machines. (I woulds ssh-tunnel the connection to the remote-machine). Then you can run for it and select, insert and update the things you want. Cause synchronizing always is a hard thing (what if both data has changed !?) you need to implement special rules and maybe background-tables (containing the change-date of each entry) anyway. peter -- mag. peter pilsl phone: +43 676 3574035 fax : +43 676 3546512 email: pilsl@goldfisch.at sms : pilsl@max.mail.at pgp-key available