Обсуждение: newbie ?'s
First off, what a wonderful mail list! I've been listening in for a couple of days now, and it is marvelous the community you people have.
I'm new to this software, so I won't waste much of your time, but I'm curious as to the architecture of the server-side daemon, etc.
1) Do you have to specify in advance the full number of processes, or does it dynamically manage them ala Apache?
2) If your site was busy, will requests queue up civilly or will they typically bomb right away if they can't find an idle process.
3) I've been told PostgreSQL databases must be periodically "rebuilt" so to speak, ("vacuumed"). Is this easy to do on-the-fly? Rapid? Or, does the db need to go down awhile?
4) I can't find anything yet in the docs on on-the-fly backups of the db. Can you simply copy the directory, or will this not have integrity? What is the best strategy to study for backing up of databases that may be in use 7x24?
5) This seemingly full-fledged password overhead is worrisome. Is this tedious to manage in practice? Can the same (quasi-generic) user be reading the database from many simultaneous processes?
Clayton Vernon
Houston, Texas
3) I've been told PostgreSQL databases must be periodically "rebuilt" so to
speak, ("vacuumed"). Is this easy to do on-the-fly? Rapid? Or, does the db
need to go down awhile?
Not so much rebuilt, but just cleaned up. My understanding is that deleted
records (and outdated copies of UPDATEd records) are removed, and statistics
are compiled that helps the planner determine the best way to fill your
queries. It's pretty easy to do on the fly... VACUUM; or better yet VACUUM
ANALYZE; from the SQL prompt, or 'vacuumdb' from the command line.
This can be done on a running database, but I'd recommend setting up a cron
job to do it when the database is not very busy (i.e. 4AM or some other
off-hour). The operational time depends on the size of your database; our
relatively small databases (no more than 60K records in a given table) only
take a few minutes. Not quite rapid, but not terribly painful either.
4) I can't find anything yet in the docs on on-the-fly backups of the db.
Can you simply copy the directory, or will this not have integrity? What is
the best strategy to study for backing up of databases that may be in use
7x24?
You'll want to look for the pg_dump utility. There's plenty of documentation
on this (including a nice man page), so I won't go into detail. But you can
run this on a cron job as well. pg_dump should back up the entire database,
including the schema, although it has options to dump data or schema only.
It even does it in a nice text format, so you can even go in with your
favorite text editor and modify the schema or do whatever you want. When you
need to restore, just pipe the pg_dump'ed file into psql and your have your
database. Like VACUUM, you can run pg_dump at any time from the command
line.
I'll let those wiser and more knowledgable answer the other questions...
Greg
"Clayton Vernon" <cvernon@enron.com> writes:
> 1) Do you have to specify in advance the full number of processes, or does =
> it dynamically manage them ala Apache?
You have to set an upper limit on the max number of server processes.
This is mainly to prevent Postgres from taking over your system ;-).
Hopefully you can set it high enough to not be a problem in practice.
> 2) If your site was busy, will requests queue up civilly or will they typic=
> ally bomb right away if they can't find an idle process.
Connections will be refused if the server process limit is reached.
> 5) This seemingly full-fledged password overhead is worrisome. Is this tedi=
> ous to manage in practice? Can the same (quasi-generic) user be reading the=
> database from many simultaneous processes?
There are several different options for authentication methods ---
probably you can find one that matches your combination of security and
simplicity concerns. Yes, the same userid can be used for multiple
connections at once.
regards, tom lane