Changes between Version 4 and Version 5 of sysadminPostgreSQLConfiguration


Ignore:
Timestamp:
Jun 23, 2009, 5:04:54 PM (13 years ago)
Author:
risard
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • sysadminPostgreSQLConfiguration

    v4 v5  
    1212
    1313Postgres 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
    1415{{{
    1516    sudo apt-get update
    … …  
    2021
    2122During 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
    2224{{{
    23     $sudo passwd postgres
     25    sudo passwd postgres
    2426}}}
    2527
    2628You will be prompted to change postgres's password.  After doing so, you can check that it worked by trying:
     29
    2730{{{
    28     $su postgres
     31    su postgres
    2932}}}
    3033
    … …  
    3235
    3336You change the db role via psql.  Switch users to postgres:
     37
    3438{{{
    35     $su postgres
     39    su postgres
    3640}}}
    3741
    3842and log into psql:
     43
    3944{{{
    40     $psql
     45    psql
    4146}}}
    4247
    4348Run the following:
     49
    4450{{{
    4551    ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'mypassword';  #include the quotes around mypassword
    … …  
    4753
    4854----
    49 == Initializing The Database Server ==
     55== Configuring The Database Server ==
     56
     57=== Creating a data Cluster ===
    5058
    5159Postgres 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.
    … …  
    5462
    5563You 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
    5665{{{
    5766    df -h
    … …  
    5968
    6069should result in something like:
     70
    6171{{{
    6272    Filesystem        Size  Used Avail Use% Mounted on
    … …  
    7383
    7484Looking 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.
    7686
    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.
    7887{{{
    7988    sudo mkdir /data/postgresql/8.3/main
    … …  
    8190}}}
    8291
     92=== Initializing The Database Server ===
     93
    8394Now 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}}}
     99the -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
     101Now 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
     106set 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
     126To 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
     133Notice 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