Re: Temporary, In-memory Postgres DB?

On 11/7/07, Gauthier, Dave <dave(dot)gauthier(at)intel(dot)com> wrote:
> Yes, I'm thinking of a small DB (could fit into mem). And the notion
> that all the data would reside in memory makes sense. But what about
> the metadata? And there is the question of the "initdb" and all the
> stuff it creates. That goes to disk, yes? no?

It goes where you tell it to.

initdb -D /mnt/ramdisk/data

et voila!

> Another question, but first my tenuous understanding of how dbs are
> created, up for critique...
>
> - "initdb" creates (on disk) all the stuff you need to have before you
> "createdb".
> - "createdb" creates a db (puts it in the place you designated with
> "initdb")

Or in a tablespace you've created since then

> - "create schema" can be used to create multiple schemas in a singel DB.
>
> - "drop schema" can be used to get rid of a schema within a db
> - "dropdb" can be used to get rid of a db that was created with
> "createdb"
> Q: How does one get rid of whatever gets created with "initdb"? Is it
> (gulp) just a "rm -r" ?

yep

shut down the db first, then clean out the directory.

> I do have access to scratch disks which, in effect, could be used as a
> temporary storage area. IOW, if something goes wrong, and I don't get
> to delete a db that was created on the scratch disk, it'll get cleaned
> up for me overnight. It's just a question of how long it'll take to
> initdb + createdb + create a db model + load.

For a small db like you're talking about only a minute or two.

If you have a "real" pg db that you don't want to scramble, then do
all your initdb under a different unprivaleged system account. Just
point it to a different port.

> What am I exposing myself to if I have dozens (maybe a hundred max) PG
> databases running on the same server? (v8.2.0)

First off, run 8.2.5 if you can, not 8.2.0. 8.2.0 has a few nasty
bugs you don't want to get bitten by.

Generally, running dozens to hundreds of instances of pgsql on one
machine is a bad idea. Unless you have a very good business case for
it, it's better to run multiple dbs inside one instance, which is no
big deal at all. I've run intranet pg machines, under pgsql 7.2 with
well over 100 individual databases with no problems at all.