Changes between Version 4 and Version 5 of sysadminPostgreSQLConfiguration
- Timestamp:
- Jun 23, 2009, 5:04:54 PM (14 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
sysadminPostgreSQLConfiguration
v4 v5 12 12 13 13 Postgres is a common default install on most distros and might already be installed. As root (using either sudo or su), run apt to see: 14 14 15 {{{ 15 16 sudo apt-get update … … 20 21 21 22 During installation,a postgres user will be added to the local system (ie the computer). This is a standard linux user account and it's permissions are created during setup. '''Do not change the permissions of this user! ''' This user runs the postgres processes and should have only the permissions necessary to do that. Also created during installation is a db role called postgres. This is internal to postgres and has nothing to do with the operating system. The postgres role provides complete access to all databases created on that db server. It's important to change the passwords for both the postgres account and postgres role for security purposes. 23 22 24 {{{ 23 $sudo passwd postgres25 sudo passwd postgres 24 26 }}} 25 27 26 28 You will be prompted to change postgres's password. After doing so, you can check that it worked by trying: 29 27 30 {{{ 28 $su postgres31 su postgres 29 32 }}} 30 33 … … 32 35 33 36 You change the db role via psql. Switch users to postgres: 37 34 38 {{{ 35 $su postgres39 su postgres 36 40 }}} 37 41 38 42 and log into psql: 43 39 44 {{{ 40 $psql45 psql 41 46 }}} 42 47 43 48 Run the following: 49 44 50 {{{ 45 51 ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'mypassword'; #include the quotes around mypassword … … 47 53 48 54 ---- 49 == Initializing The Database Server == 55 == Configuring The Database Server == 56 57 === Creating a data Cluster === 50 58 51 59 Postgres uses the term "database cluster" to describe the directory it uses to store data. During the apt install, a default directory is created, usually at: /var/lib/postgresql/8.3/main. … … 54 62 55 63 You will need to determine the appropriate partition to install the cluster on. Usually, this is /data. To be sure, you can use the disk free command to see: 64 56 65 {{{ 57 66 df -h … … 59 68 60 69 should result in something like: 70 61 71 {{{ 62 72 Filesystem Size Used Avail Use% Mounted on … … 73 83 74 84 Looking at the above, clearly the /data patition is the largest (84G) compared to the others. 75 ''' NOTE: ''' 85 ''' NOTE: ''' Having located the largest partition, you will need to create directory for the data cluster, and give the postgres user full access to it. In the spirit of keeping things standardized, we have been using the /postgresql/<version>/main format. 76 86 77 Having located the largest partition, you will need to create directory for the data cluster, and give the postgres user full access to it. In the spirit of keeping things standardized, we have been using the /postgresql/<version>/main format.78 87 {{{ 79 88 sudo mkdir /data/postgresql/8.3/main … … 81 90 }}} 82 91 92 === Initializing The Database Server === 93 83 94 Now you need to initialize the data cluster. ''' Remember to include the -D switch to point it at the new directory you created above and not the default one! ''' 95 {{{ 96 su postgres 97 initdb -D /data/postgresql/8.3/main -E UNICODE --locale=C 98 }}} 99 the -E UNICODE options tells Postgres to encode the db with Unicode, and the --locale=C option tells postgres to use ISO C for localization support. 100 101 Now some configuration is required to ensure the best possible performace parameters for Evergreen. In: 102 {{{ 103 /data/postgresql/8.3/main/postgresql.conf 104 }}} 105 106 set the following parameters: 107 {{{ 108 # Conifer settings 109 effective_cache_size = 4GB 110 #shared_buffers = 512MB 111 work_mem = 256MB 112 #max_fsm_pages = 250000 113 max_fsm_pages = 750000 114 maintenance_work_mem = 1GB 115 checkpoint_segments = 64 116 log_line_prefix = '<%u@%h %t>' 117 log_statement = all 118 autovacuum = on 119 stats_row_level = on 120 shared_buffers = 2048MB 121 default_statistics_target = 100 122 }}} 123 124 === Starting the Database === 125 126 To start the database: 127 128 {{{ 129 su postgres 130 pg_ctl -D /data/postgresql/8.3/main -l /<path_to_log_file>/<my_logfile> 131 }}} 132 133 Notice the -D option is again set and aimed at the right data cluster. You must use the -D option every time you start or restart the server. The log file can be anywhere the postgres user can write to. If the file doesn't exist, it will be created automatically. pg_ctl lives in /usr/lib/postgresql/8.3/bin by default. You can add it to PATH by adding the following to your shell start-up file, likely ~/.bash_profile 134 135 {{{ 136 PATH=/usr/local/pgsql/bin:$PATH 137 export PATH 138 }}} 139 140 141 142 143 144