Re: One big database or little separate ones?

From: Andreas <maps(dot)on(at)gmx(dot)net>
To: Allan Berger <alb2(at)cornell(dot)edu>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: One big database or little separate ones?
Date: 2004-01-12 03:40:22
Message-ID: 40021726.2060701@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi Allan,

> Is there any advantage to maintaining separate databases for backup
> purposes?

> Specifically, I am going to have a large number of tables with text
> info, and just a few tables that saves several gigabytes of images and
> data files. I was thinking of having the text data in one database to
> allow for frequent backups (every few hours), with the large image and
> file database backed up less frequently (once a day or less frequently).

If you do this you'll lose the benefits of all the pretty relational
integrity stuff when pic 17 in table A in database DB1 belongs to record
10 in table B in DB2. You can't connect database DB1 with DB2 in PG.
If you can afford this, why store the picutes in a database at all ?

Put all the images in a filesystem probaply on a RAID mirrord drive or
rsync or whatever.
Store the file paths in the database and you'll have fast file access to
the pics and not too much weight in the database.

If you insist in putting all in an RDBMS then you could use PG's schemes.
Use 1 big DB and some schemes within like
a) Schema "data"
b) Schema "images"
That's like having 2 sub-databases that can actually see each other e.g.
for having foreign key constraints.

Then use pg_dump to backup the database in parts as you like.

Look up the documentation of pg_dump:
--schema=schema
--table=table

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Tor Gunston 2004-01-12 04:01:04 Re: PQexec hangs, stuck on malloc_consolidate
Previous Message George Weaver 2004-01-12 03:28:47 Re: Get error during initdb