Richard's Diary

Friday, December 12, 2008

More PostgreSQL Notes Database organization hierarchy:
  1. server
  2. cluster (made up of physical tablespaces)
  3. database
  4. schema
  5. object (table, function, index, views, rules, triggers)
To drop a database and create fresh one
  1. psql -c "drop database pickyricky_production" -U pickyricky -W -d pickyricky_test
  2. psql -c "CREATE DATABASE pickyricky_production" -U pickyricky -W -d pickyricky_test

To create and restore backup, but this is not as favorable in production as warm standbys
#file:///usr/share/doc/packages/postgresql/html/app-psql.html (-1 says to do it in a transaction)
  1. pg_dump -o -U pickyricky pickyricky_development > testdumpfile
  2. psql -1 -U pickyricky pickyricky_production <>
  3. vacuumdb -z -U pickyricky -W -d pickyricky_production
  4. Speed hints #file:///usr/share/doc/packages/postgresql/html/populate.html
    1. disable archive_mode while restoring since it's writing out a journal which you presumably already have since you're restoring a dump (ie, get the journal from the server that created the dump)
    2. Set appropriate (i.e., larger than normal) values for maintenance_work_mem and checkpoint_segments


Keep in mind the following operational structure and hence all the available configuration options in postgresql.conf when PostgreSQL writes to WAL log and database files
  1. synchronycity between the WAL and database files
  2. delay (caching) of writing of to hard disk
    1. OS cache (handled by postgres)
    2. hard drive controller cache (get a controller with a backup battery)
    3. hard drive cache (hdparm command)
  3. enabling log archiving
  4. optimizing log archiving
    1. checkpoints
    2. recycling

© 2010 Picky Ricky, Inc. originalblog