Richard's Diary

Friday, December 12, 2008

More PostgreSQL Notes Database organization hierarchy:

server
cluster (made up of physical tablespaces)
database
schema
object (table, function, index, views, rules, triggers)

To drop a database and create fresh one

psql -c "drop database pickyricky_production" -U pickyricky -W -d pickyricky_test
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)

pg_dump -o -U pickyricky pickyricky_development > testdumpfile
psql -1 -U pickyricky pickyricky_production <>
vacuumdb -z -U pickyricky -W -d pickyricky_production
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

architecture
file:///usr/share/doc/packages/postgresql/html/high-availability.html
file:///usr/share/doc/packages/postgresql/html/continuous-archiving.html
file:///usr/share/doc/packages/postgresql/html/runtime-config.html
file:///usr/share/doc/packages/postgresql/html/ddl-partitioning.html

reference
file:///usr/share/doc/packages/postgresql/html/view-pg-locks.html
file:///usr/share/doc/packages/postgresql/html/catalogs.html
file:///usr/share/doc/packages/postgresql/html/transaction-iso.html

---------------------
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

synchronycity between the WAL and database files
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)
enabling log archiving
optimizing log archiving
1. checkpoints
2. recycling