Setting up a virtual host in a different country

[bill]

Please go easy on me. I'm not much of an Apache admin. Beyond editing some .htaccess files I've got little experience with the guts of this server. I was hoping some of you could help me along here. Forgive me if I'm not too clear.

Background:
I have a web host in the US running Apache that contains my website. We have a company in Japan that runs a database server for our Japanese site. We would like to use the database server in Japan to power the website hosted in the US as well.

Quandary:
I was told that it should be possible to configure Apache (the server in the US) to use the server in Japan as some sort of virtual host. Essentially we want the site to remain hosted in the US, but to be able to pull data from the Japan database.

First, I'd like to know if this is possible. If so, what would I need on the Apache server?

Second, are there security implications in doing this? This is not an e-commerce site. It's essentially a big product catalog. No sensitive data would be passed along. Will I run into problems with hosting companies allowing me to do this even with a dedicated server?

I really need some prodding in the right direction so that I can ask my host the right questions and see whether this scheme is feasible.

[Angelis]

If it is just the database you need to access then you should be able to set the access for mysql so that you can access the database from anywhere rather than just localhost.

All you need to do is set the username you use to be able to access with a wildcard (%) symbol. That works on my server anyway as I use my home machine as a testing server and run database queries from the server to it.

Sounds similar to what you need to do... You may need to login as root to change the value however on my server you can just add it in the MySql control panel (Not phpMyAdmin)...

My server uses CPanel, if yours has it then I can tell you how to do it...

[bill]

Actually it's a PostgreSQL database. I was told that the CMS system they are using requires localhost access. I'll certainly ask whether the database can be manipulated without having to rewrite the CMS. Thanks for that!

[Angelis]

Wildcard is just an option you can use, it would be better for security reasons to use the IP or web address of your site if possible to avoid anyone else accessing the sql server directly.

Im playing with postGre at the moment to see if you can setup remote access, will tell you in a bit (when its finished installing)...

[bill]

Wow. I truly appreciate that Angelis! It seems that's not an angle that they considered. I'll be interested to hear what you find.

[bill]

I'd still be interested to see whether this type of virtual host would be possible via Apache.

[jatar_k]

to be totally honest bill, I don't see what they're getting at with the whole virtual host thing

as Angelis mentioned, it's just a db, the site is actually on the US server

I see a couple of options

1. connect remotely to the db in japan
strictly looking from a php perspective as I have no idea what you are using on the US site

from
[php.net...]

The connection_string can be empty to use all default parameters, or it can contain one or more parameter settings separated by whitespace. Each parameter setting is in the form keyword = value. Spaces around the equal sign are optional. To write an empty value or a value containing spaces, surround it with single quotes, e.g., keyword = 'a value'. Single quotes and backslashes within the value must be escaped with a backslash, i.e., \' and \\.

The currently recognized parameter keywords are: host, hostaddr, port, dbname, user, password, connect_timeout, options, tty (ignored), sslmode, requiressl (deprecated in favor of sslmode), and service. Which of these arguments exist depends on your PostgreSQL version.

essentially you can call this postgresql connection function and give it all the host data to have it connect wherever. Shouldn't really matter where the db is physically located, localhost is just a host address like any other.

2. keep the db in japan and export it into a local db on the US server

I don't see why you can't do this, have a cron or scheduled task that grabs the data from japan server and brings it over and dumps it in the local db. There are 2 sides to this so you could have the japan server send it over on a schedule and the us server grab that file and dump it into the db.

whatever but it would maybe be a better option as remote db servers sometimes make things a little slow. You would have to figure out a schedule that made sense for keeping the site up to date and also not having it take too long. Could be every hour/day/week or or every 15 mins if you really wanted but that could be a little too short.

If you ever needed to make an immediate change you just connect straight to the US server to make the change, making sure you have already made the change on the JP server so the next update doesn't get messed up.

[bill]

Thanks. Option #2 of having the server mirrored in the US is the long term plan. They didn't want to go through the setup and testing now as the site is under a tight deadline.

I'm going to run the pg_connect by them now and see what possibilities there are with that. Thanks for the ammo.

[jatar_k]

dedicated server you should be able to do whatever you want and writing a php/shell/both script to do the export/transport/import should be pretty straight forward for someone who works with postgresql. I can't say that I have, but we do this all the time with mysql and a couple times with oracle.

If they can set up postgresql on the local this might be a pretty easy fix.

there are some examples on that page for the remote connect

$conn_string = "host=sheep port=5432 dbname=test user=lamb password=bar";
$dbconn4 = pg_connect($conn_string);
//connect to a database named "test" on the host "sheep" with a username and password

[bill]

If they can set up postgresql on the local this might be a pretty easy fix.

Could you clarify that a bit? I was thinking that if they used pg_connect in the PHP then the database could still be remote in Japan. Would I need PostgreSQL on the US side as well (where the web pages would be)?

[jatar_k]

no you wouldn't if it works remotely, sorry, I am confusing the issue

I am just worried that remote be a tad slow

[bill]

OK that makes sense then.

We are somewhat resigned to the performance hit at this point as it's only for a portion of the site.

It does seem that pg_connect will need some open ports so I'll be moving this site to a dedicated server shortly.

[bill]

Just an update for those of you following along...

There were permission problems with the Japanese hosting company. They complained that using pg_connect in this way would open them to security concerns. That plan fell through.

Next we were looking at the mod_proxy module in Apache. That looked promising for a bit, but there were speed and testing concerns. We finally settled on rebuilding the entire system on the US server, PostgreSQL database and all...which I'd been telling them all along would be the best option.

[jdMorgan]

> ...which I'd been telling them all along...

:) Happens all the time...

Jim