Introductory PostgreSQL Administration Tasks
The Purpose of template1While poking around the pgsql data directory, you will notice a directory (or database) named template1. Template1 is used when users or developers do a CREATE DATABASE sql command, or when you do a createdb from the command line. Put simply, when either of the above commands are executed, a directory will be created named after the database being built; the contents of template1 will be copied to the new directory.
To see this process in action, change to the template1 directory and create a file from the command line (say, using cat). While still at the command line and still su'ed to the postgres superuser, type the following:
You should see the contents of test.txt spill out across your terminal.
$pwd/var/lib/pgsql/data/base/template1 (note: your data directory may be different)$cat > test.txtsample text<ctrl-d>$createdb test1CREATE DATABASE$cd ../test1$cat test.txt
Another good use for the template would be in the ASP space. Let's say, for example, you have a sales contact management system you are selling to remote users as a service. How do you separate client data from one client to another? One way would be to create fields in each record that hold the ID of the client in question. Although this approach may work, it's better to separate clients physically from one another. To do this, you would import the base contact management schema into template1. When you sign on a new customer, all you have to do is fire off a createdb command, and in a few seconds you have a brand new database for the new client.
Monitor Server Resource UsageThere will come a time during your administrative duties when you will have to assess the impact of adding more users, data, or applications to the database server. When the word performance comes up, you have to be aware of the context in which it is being used. Performance can be measured objectively or subjectively. A performance rating falls within one of two broad categories: user response time and processing time.
Processing time measures how long a series of instructions takes to execute from start to completion. Batch processing is typically measured using this term. It's a basic concept and won't be discussed much further than this.
User response time is a measure of time taken to perform an action initiated by the user at the keyboard. Response times in this category are very subjective--what might be slow to one user may be fine with another. This measure is a critical one, but fortunately it's easy to detect: Your users come to you crying that Step A takes 100 years to complete. When you start hearing comments like this, it's time to take a peek at the server to see if there is anything you can do to help your poor users out.
Unfortunately, no tool is packaged with the pgsql source distribution to help you nail performance problems. This isn't a major deal, in that the host operating system will most likely have at least basic tools to help you out. Under Unix-based systems, you will most likely have a tool like top or ps, which will start to give you clues. Items to look for are memory use (both physical and virtual), disk usage, and number of users hitting the machine at any given time.
The two most important factors when looking at pgsql performance are disk and RAM. Pgsql will serve data as fast as it can read it from the disk, but only if sufficient RAM is available. So the first thing you should consider is the speed of your disk and the location of the actual data. If pgsql is installed on the slowest disk you have on the system, then you should not be surprised if performance suffers. Is your primary swap file on the same partition as the data? This will cause drive head contentions as the system decides to either write to the database or to the swap file. I'm sure you realize which of the two would lose in that kind of contest. Are you sharing the data partition with any other disk-intensive tasks, such as Samba-based file sharing? What about print queues? All of these services take away from the primary task of answering requests for data.
The second item to look at is the amount of RAM you have installed on the machine. Each connection to pgsql requires a new postmaster task to drive the connection between the user and the backend data. These tasks require memory, and the amount of memory required will depend on the type of work you are performing during the connection. As a query is processed, pgsql will start to eat RAM. This isn't a memory leak: pgsql has to keep track of what it has done concerning the query you sent. As RAM slowly disappears, pgsql will start to eat swap space. This isn't a limitation of pgsql--it's a basic tenet of computer life.
Pgsql runs on a variety of systems that can generally be classified as either Windows NT or a Unix-like OS. Under Unix-based systems, there are a number of tools that come with the base system to allow the admin to take a peek at what is happening under the hood. Notable commands are: top and ps.
Page 2 of 4