Postgres

• On windows install using installer or unpack zip and manually initialize cluster and setup service
• On OpenBSD use pkg_add postgresql ¹⁾
• When installing PostgreSQL always select UTF-8 or Latin1 as default encoding ²⁾
• each database can have its own encoding, but changing default 7-bit C_ASCII encoding for a server or existing database is a pain.

• http://www.postgresql.org/docs/8.4/interactive/index.html
• http://www.postgresql.org/docs/8.2/interactive/index.html
  • http://linux.die.net/man/1/pg_ctl
  • http://linux.die.net/man/1/initdb

• Before you can do anything you need to initialize the catalog cluster (and asign super user password):
  • initdb -D c:\postgresql\8.4\data -U postgres -W³⁾
  • make sure the postgres service user has write/modify access to the data folder
• Then add the following:
  • pg_hba.conf: host all all 0.0.0.0/0 md5
  • postgresql.conf: uncomment connection settings (listen_addresses = '*', port = 5432, max_connections = 100)

• see this blog post (at the bottom)
• Assuming that your superuser role name is 'postgres' do this
  • add the following to pg_hba.conf: local all postgres trust and restart postgres
  • login locally as psql -U postgres -d template1
  • run alter user postgres with encrypted password 'newpass';
  • remove line from pg_hba.conf and restart.
• if you don't know your superuser name add local all all trust and create new user instead.

• http://linux.die.net/man/1/pg_ctl
• pg_ctl register [ -N servicename ] [ -U username ] [ -P password ] [ -D datadir ] [ -w ] [ -o options ]
• pg_ctl unregister [ -N servicename ]

Couldn't install it with -U/P so had to do this and then change service properties and add description via registry (see below):

• pg_ctl register -N Postgres-8.4 -D C:\PostgreSQL\8.4\data

• \\quesnel\c$\PostgreSQL\8.2\data\pg_hba.conf
  • add IP authorized to connect to server/database
• Service config: see registry under HKLM\Software\PostgreSQL\

• create pgpass.conf file under profile of the user who will be running the script
  • e.g. c:\Documents and Settings\username.domain\Application Data\postgresql\pgpass.conf

  • localhost:5432:*:postgres:XXXXXXXXXXX
    

• create backup script

  • pg_dumpall.exe -U postgres -h localhost -p 5432 -c -o  | gzip > c:\backup\postgres_alldb.bak.gz
    xcopy /y c:\backup\postgres_alldb.bak.gz \\backup\db\hostname\postgres\
    

• for individual databases use pg_dump

• pg_dump.exe -i -h localhost -p 5432 -F t -b -v -f "c:\backup\dbname.backup" dbname
  gzip -f dbname.backup
  xcopy /y c:\backup\dbname.backup.gz \\backup\db\hostname\postgres\
  

• see also
  • http://www.postgresql.org/docs/8.2/static/app-pg-dumpall.html
  • http://www.postgresql.org/docs/8.2/static/app-pgdump.html

« start