Обсуждение: Separating application from data

Is it possible to put the data and structure files of Postgres (tables, views, indicies, sequences and so on) on
anothermachine than that one where system files are? In other words I want to put only the system and application files
onone server and only the structure and data files on another server, is it possible? and if yes, how can I? 
Thanks.

Ago wrote:
> Is it possible to put the data and structure files of Postgres
> (tables, views, indicies, sequences and so on) on another machine
> than that one where system files are? In other words I want to put
> only the system and application files on one server and only the
> structure and data files on another server, is it possible? and if
> yes, how can I? Thanks.

Running over NFS is not recommended, but people use external storage
arrays. What exactly are you trying to do?

--
   Richard Huxton
   Archonet Ltd

Please CC: the mailing list too, other people might have something
useful to say.

NMB Webmaster wrote:
> It is for backup purpose. I thought that in this way I could backup
> only data and structure of all my databases from one machine without
> backuping the application files that would be on another machine.

The standard backup tool for PostgreSQL is pg_dump - this indeed just
backs up the data. You should not rely on copying the files of a running
database - that won't always work.

> Moreover, if the application server crashes I do not miss my data.

Ah - I think I see what you're trying to do. It's quite common to have
e.g. Apache+PHP/JBoss/etc on one machine and PostgreSQL on another. All
that's required is to turn the tcp connection option on in PostgreSQL
and change the connect string in the application.

Don't forget to update your pg_hba.conf to allow your application to
connect from the remote machine too.

HTH
--
   Richard Huxton
   Archonet Ltd

Sorry for CC to mailing list, I forgot!!


On 01/06/2004 11.35, Richard Huxton <dev@archonet.com> wrote:
>Please CC: the mailing list too, other people might have something
>
>useful to say.
>
>NMB Webmaster wrote:
>> It is for backup purpose. I thought that in this way I could backup
>> only data and structure of all my databases from one machine without
>> backuping the application files that would be on another machine.
>
>The standard backup tool for PostgreSQL is pg_dump - this indeed
>just
>backs up the data. You should not rely on copying the files of a
>running
>database - that won't always work.
>
But I stop the postgres service before to copy all the files, then the database is not running. The problem is that I
usea  backup tool that backup data from my server (MySql and Postgres databases included) in that way (I know pg_dump,
Iused it before) and afterwards it transfers the backup file (a kind of tar file) via FTP on another machine. Of course
Ican schedule it. My idea was to increment the protection of my db data. What about the external storage arrays? 

>> Moreover, if the application server crashes I do not miss my data.
>
>Ah - I think I see what you're trying to do. It's quite common to
>have
>e.g. Apache+PHP/JBoss/etc on one machine and PostgreSQL on another.
>All
>that's required is to turn the tcp connection option on in PostgreSQL
>
>and change the connect string in the application.
>
>Don't forget to update your pg_hba.conf to allow your application
>to
>connect from the remote machine too.
>
I already tried it and it works perfectly.

>HTH
>--
>   Richard Huxton
>   Archonet Ltd
>
>
>

NMB Webmaster wrote:
> Sorry for CC to mailing list, I forgot!!

No problem, I do sometimes. Then on other lists, I do it when I shouldn't.

>>
>> The standard backup tool for PostgreSQL is pg_dump - this indeed
>> just backs up the data. You should not rely on copying the files of
>> a running database - that won't always work.
>>
>
> But I stop the postgres service before to copy all the files, then
> the database is not running.

If the server is stopped, then yes, it's perfectly safe to backup the
data directory, but make sure you do the whole directory including the
xlog/clog stuff.

 > The problem is that I use a  backup tool
> that backup data from my server (MySql and Postgres databases
> included) in that way (I know pg_dump, I used it before) and
> afterwards it transfers the backup file (a kind of tar file) via FTP
> on another machine. Of course I can schedule it. My idea was to
> increment the protection of my db data.

Well, it sounds like you want point-in-time recovery (PITR). This is
being worked on at the moment, and would allow you to take a full backup
    nightly and incremental hourly backups of the WAL files. Replaying
the logs then allows you to restore your database to a set hour.

PITR is under development and may well be in the next version, but there
are no guarantees. See the archives of the hackers list for details.

 > What about the external
> storage arrays?

These are big boxes of disks with e.g. a firewire connection. Expensive,
and probably not what you're after.

If you want some insurance against failure of your database server you
could look at replication. There are a number of solutions, one
currently under development is called "slony" - googling and searching
the archives should get you details, I believe it's in testing at the
moment.

--
   Richard Huxton
   Archonet Ltd