Changes between Version 17 and Version 18 of sysadminPostgreSQLConfiguration

Timestamp:

Jun 24, 2009, 9:21:41 AM (13 years ago)

Author:

risard

Comment:

--

sysadminPostgreSQLConfiguration

@@ -290 +290 @@
 
 For 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].
+
+----
+== References ==
+
+
+----
+== Quick Reference ==
+
+{{{
+#### INSTALL ####
+
+    sudo apt-get update
+    sudo apt-get install postgresql-8.3
+
+#change key passwords
+
+    sudo passwd postgres    #Linux user account
+    psql# ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'mypassword';  #include the quotes around mypassword
+
+#Configuring The Database Server 
+#Creating A Data Cluster on data partition
+
+    sudo mkdir /data/postgresql/8.3/main
+    sudo chown postgres:postgres /data/postgresql/8.3/main
+
+#Initializing The Database Server - using the new cluster
+    
+    su postgres
+    initdb -D /data/postgresql/8.3/main -E UNICODE --locale=C
+
+#now configure postgresql.conf as per the file snippet at the end of these instructions
+
+#start the database:
+    
+    su postgres
+    pg_ctl -D /data/postgresql/8.3/main -l /<path_to_log_file>/<my_logfile>
+
+
+
+#### CREATE THE EVERGREEN DATABASE #### 
+
+#from source
+    
+    su postgres
+    createuser -P -s evergreen
+
+    createdb -E UNICODE evergreen
+    createlang plperl evergreen
+    createlang plperlu evergreen
+    createlang plpgsql evergreen
+    psql -f /usr/share/postgresql/8.3/contrib/tablefunc.sql evergreen
+    psql -f /usr/share/postgresql/8.3/contrib/tsearch2.sql evergreen
+    psql -f /usr/share/postgresql/8.3/contrib/pgxml.sql evergreen
+
+    perl Open-ILS/src/support-scripts/eg_db_config.pl --create-schema #run on the oils server with oils running
+
+#from a dump file
+
+#stop oils
+#drop evergreen db
+
+    su postgres
+    psql# drop database evergreen;
+
+#check if evergreen user is still there
+    psql# \du
+
+#if not, create it:
+
+    createuser -P -s evergreen
+
+#create db
+        
+    createdb -E UNICODE evergreen
+
+#edit postgresql.conf checkpoint_segments (see file snippet)
+
+#start the restore
+
+    pg_restore -d evergreen /<path_to_my_dump_file>/<dump_file>
+
+#when finished clean up
+
+    su postgres
+    VACUUM
+    ANALYZE
+
+#### INSTALL JSON::XS ####
+
+    which gcc
+
+#if not installed:
+
+    apt-get update
+    apt-get install gcc
+
+    perl -MCPAN -e 'CPAN::Shell?install(“JSON::XS”)'
+
+#networking issues
+
+    [wiki:sysadminiptables iptables and network configuration][[BR]]
+    [wiki:sysadminiptables#NecessaryPorts-DatabaseServer Necessary Ports - Database Server] [[BR]]
+    [wiki:sysadminiptables#SpecialPostgresqlIssues Special Postgresql Issues] [[BR]]
  
+###############################################################
+#
+# File snippet:
+# /data/postgresql/8.3/main/postgresql.conf
+#
+###############################################################
+#
+#
+#    # Conifer settings
+#    effective_cache_size = 4GB
+#    #shared_buffers = 512MB
+#    work_mem = 256MB
+#    #max_fsm_pages = 250000
+#    max_fsm_pages = 750000
+#    maintenance_work_mem = 1GB
+#    checkpoint_segments = 64
+#    log_line_prefix = '<%u@%h %t>'
+#    log_statement = all
+#    autovacuum = on
+#    stats_row_level = on
+#    shared_buffers = 2048MB
+#    default_statistics_target = 100
+#
+#    checkpoint_segments = 128
+#
+###############################################################
+}}}