Changes between Version 17 and Version 18 of sysadminPostgreSQLConfiguration


Ignore:
Timestamp:
Jun 24, 2009, 9:21:41 AM (13 years ago)
Author:
risard
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • sysadminPostgreSQLConfiguration

    v17 v18  
    290290
    291291For a complete discussion of iptables see [wiki:sysadminiptables iptables and network configuration].  For the specific ports needed by the postgres server see [wiki:sysadminiptables#NecessaryPorts-DatabaseServer Necessary Ports - Database Server] for other Postgres specific networking issues see [wiki:sysadminiptables#SpecialPostgresqlIssues Special Postgresql Issues].
     292
     293----
     294== References ==
     295
     296
     297----
     298== Quick Reference ==
     299
     300{{{
     301#### INSTALL ####
     302
     303    sudo apt-get update
     304    sudo apt-get install postgresql-8.3
     305
     306#change key passwords
     307
     308    sudo passwd postgres    #Linux user account
     309    psql# ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'mypassword';  #include the quotes around mypassword
     310
     311#Configuring The Database Server
     312#Creating A Data Cluster on data partition
     313
     314    sudo mkdir /data/postgresql/8.3/main
     315    sudo chown postgres:postgres /data/postgresql/8.3/main
     316
     317#Initializing The Database Server - using the new cluster
     318   
     319    su postgres
     320    initdb -D /data/postgresql/8.3/main -E UNICODE --locale=C
     321
     322#now configure postgresql.conf as per the file snippet at the end of these instructions
     323
     324#start the database:
     325   
     326    su postgres
     327    pg_ctl -D /data/postgresql/8.3/main -l /<path_to_log_file>/<my_logfile>
     328
     329
     330
     331#### CREATE THE EVERGREEN DATABASE ####
     332
     333#from source
     334   
     335    su postgres
     336    createuser -P -s evergreen
     337
     338    createdb -E UNICODE evergreen
     339    createlang plperl evergreen
     340    createlang plperlu evergreen
     341    createlang plpgsql evergreen
     342    psql -f /usr/share/postgresql/8.3/contrib/tablefunc.sql evergreen
     343    psql -f /usr/share/postgresql/8.3/contrib/tsearch2.sql evergreen
     344    psql -f /usr/share/postgresql/8.3/contrib/pgxml.sql evergreen
     345
     346    perl Open-ILS/src/support-scripts/eg_db_config.pl --create-schema #run on the oils server with oils running
     347
     348#from a dump file
     349
     350#stop oils
     351#drop evergreen db
     352
     353    su postgres
     354    psql# drop database evergreen;
     355
     356#check if evergreen user is still there
     357    psql# \du
     358
     359#if not, create it:
     360
     361    createuser -P -s evergreen
     362
     363#create db
     364       
     365    createdb -E UNICODE evergreen
     366
     367#edit postgresql.conf checkpoint_segments (see file snippet)
     368
     369#start the restore
     370
     371    pg_restore -d evergreen /<path_to_my_dump_file>/<dump_file>
     372
     373#when finished clean up
     374
     375    su postgres
     376    VACUUM
     377    ANALYZE
     378
     379#### INSTALL JSON::XS ####
     380
     381    which gcc
     382
     383#if not installed:
     384
     385    apt-get update
     386    apt-get install gcc
     387
     388    perl -MCPAN -e 'CPAN::Shell?install(“JSON::XS”)'
     389
     390#networking issues
     391
     392    [wiki:sysadminiptables iptables and network configuration][[BR]]
     393    [wiki:sysadminiptables#NecessaryPorts-DatabaseServer Necessary Ports - Database Server] [[BR]]
     394    [wiki:sysadminiptables#SpecialPostgresqlIssues Special Postgresql Issues] [[BR]]
    292395 
     396###############################################################
     397#
     398# File snippet:
     399# /data/postgresql/8.3/main/postgresql.conf
     400#
     401###############################################################
     402#
     403#
     404#    # Conifer settings
     405#    effective_cache_size = 4GB
     406#    #shared_buffers = 512MB
     407#    work_mem = 256MB
     408#    #max_fsm_pages = 250000
     409#    max_fsm_pages = 750000
     410#    maintenance_work_mem = 1GB
     411#    checkpoint_segments = 64
     412#    log_line_prefix = '<%u@%h %t>'
     413#    log_statement = all
     414#    autovacuum = on
     415#    stats_row_level = on
     416#    shared_buffers = 2048MB
     417#    default_statistics_target = 100
     418#
     419#    checkpoint_segments = 128
     420#
     421###############################################################
     422}}}