Installation and Administration Best practices
From PostgreSQL wiki
This page is under construction.
This page will contain information about the best way to install and maintain a Postgresql database, including environment variables, paths and other relevant stuff.
Fell free to add your suggestions and knowledge to make it a 'hands on' resorce.
Self compiled vs. package distributed
Before installing, consider whether to use packages distributed with your operating system or whether to roll your own. (Compiling PostgreSQL on Windows might be a difficult task. It's quite easy on Linux/Unix boxes.)
Let's compare using a pre-built package and compiling PostgreSQL yourself. (Note: This is a rather Linux-centric view. For Windows, you almost always might want to use the binary packages provided for each release.)
|Using pre-built package from distribution||Compiling yourself.|
|Very easy to install - just use your package manager.||You might need to install gcc and some development packages just for building PostgreSQL.|
|Installation is dependent on distribution (location of config files, initial tablespace).||You may install everything in one place, just where you want it.|
|Startup-scripts are included and supposed to work.||You need to provide your own system startup scripts.|
|The packages might be out of date or new minor versions might not become available frequently.||You are free to use the latest stable version and perform upgrades at your will.|
|The package management knows about the PostgreSQL installation and will update it.||Your package management doesn't know anything about the installation. Dependent libraries might get uninstalled or replaced by newer, incompatible versions. (Note: This is rather unlikely. I've never seen it happen. PostgreSQL doesn't depend on any strange or fast-evolving packages.)|
Compiling and installing in Solaris
TODO: Add a workaround for the most common issues.
Multiple Versions on the same host
If you have to install multiple PostgreSQL versions at the same host, compile from source and call configure like this:
./configure --prefix=/opt/postgresql-8.2.11 --with-pgport=8200
That way, you never need to worry what version you are talking with - you just look at the port number.
Making sure it starts up at system boot time
TODO: Provide a default init script (if there's not already one in contrib/).
Recommended values to be changed in big servers
In Linux, the SHMMAX value is setted in a historical value. So, depending on your server, the first value to be changed is SHMMAX and SHMALL.
Example of a high configuration:
fs.file-max = 32768 kernel.shmmax = 1073741824 kernel.shmall = 536870912
How to calculate? One of the equations is:
250kb + 8.2kb * shared_buffers +14.2kb * max_connections
Why this value is important? This value govern shared memory allocation with other values. If you try to assign high values in Postgresql but you don't touch this values, Postgresql may not run.
One interest link could be visited here.
Directories Location Recommended
TODO: Explain about where may be located the dirs of data.
Versioning sql scripts and configuration files
As you are doing right now (versioning the sql scripts), other best practice is to version the configuration files. Not only a simple versioning, remember that you have several envoronments (Development, Test, Production).
Other good practice, is to have versioned the DBA modifications in a separated script (SET STORAGE modifications, special indexes and rules, etc).
TODO: Paste a example.
Backup and Recovery strategies
TODO: How to perform those tasks.
TODO: How to control OS users and DB users.