====== PostgreSQL ======

==== User management ====

  CREATE USER tom WITH PASSWORD 'myPassword';
  CREATE DATABASE jerry;
  GRANT ALL PRIVILEGES ON DATABASE jerry to tom;

===== CentOS Repo =====

http://yum.pgsqlrpms.org/reporpms/repoview/pgdg-centos.html

===== pg_hda.conf =====

  # IPv4 local connections:
  host    all         all         127.0.0.1/32          trust

Connection:

  * local
  * host
  * hostnossl
  * hostssl

Auth:

  * reject
  * trust => bad idea
  * ident => unix ident
  * crypt => bad
  * md5   => good
  * password => plain text


===== postgresql.conf =====

  listen_addresses = 'localhost' ou '*'r

====== Locale UTF8 ======

  export PGROOT="/var/lib/postgres"
  mkdir -p $PGROOT/data && chown postgres.postgres $PGROOT/data
  su - postgres -c "/usr/bin/initdb -E utf8 --locale=en_US.UTF-8 $PGROOT/data"

  $ locale
  LANG="en_US.UTF-8"
  LC_COLLATE="en_US.UTF-8"
  LC_CTYPE="en_US.UTF-8"
  LC_MESSAGES="en_US.UTF-8"
  LC_MONETARY="en_US.UTF-8"
  LC_NUMERIC="en_US.UTF-8"
  LC_TIME="en_US.UTF-8"
  LC_ALL=

If you see a UTF-8 in the LC_COLLATE parameter, then you will not have any sorting problems with your PostgreSQL data.

If you do not see a UTF-8 value for the LC_COLLATE parameter, then you should reinitialize the database and include the --locale parameter with a valid UTF-8 locale for your system.

  $ locale -a
  $ sudo -u postgres initdb -E utf8 --locale=pt_BR.UTF-8 /Library/PostgreSQL8/data

You may want to also verify that initidb put the correct values into postgresql.conf.

  $ nano /Library/PostgreSQL8/data/postgresql.conf

Find the following lines and verify that they all say “UTF-8”. If they do not, don’t edit them directly. Use initidb to reinitialize the database with the correct ones.

  # These settings are initialized by initdb
  lc_messages = 'en_US.UTF-8'
  lc_monetary = 'en_US.UTF-8'
  lc_numeric = 'en_US.UTF-8'
  lc_time = 'en_US.UTF-8'




====== PostGIS ======

  $ psql template1

  create database template_postgis with template = template1;
  UPDATE pg_database SET datistemplate = TRUE where datname = 'template_postgis';
  
  \c template_postgis

  CREATE LANGUAGE plpgsql;

Centos:
  \i /usr/share/pgsql/contrib/lwpostgis-64.sql
  \i /usr/share/pgsql/contrib/spatial_ref_sys.sql 

Arch:
  \i /opt/local/share/postgis/lwpostgis.sql
  \i /opt/local/share/postgis/spatial_ref_sys.sql

  GRANT ALL ON geometry_columns TO PUBLIC;
  GRANT ALL ON spatial_ref_sys TO PUBLIC;
  
  VACUUM FREEZE;

===== template_postgis =====

  createdb -O nofxx -T template_postgis project_development



====== Leopard OS X ======

Postgis fix:

  $ vim /opt/local/var/macports/sources/rsync.macports.org/release/ports/databases/postgis/Portfile

  build.args "ICONV_LDFLAGS=\"-L$/usr/lib -liconv\""

  sudo port install postgresql83 postgresql83-server postgis

  sudo mkdir -p /opt/local/var/db/postgresql83/defaultdb
  sudo chown postgres:postgres /opt/local/var/db/postgresql82/defaultdb
  sudo su postgres -c '/opt/local/lib/postgresql83/bin/initdb -E utf8 --locale=en_US.UTF-8 /opt/local/var/db/postgresql83/defaultdb'

To tweak your DBMS, consider increasing kern.sysv.shmmax by adding an increased kern.sysv.shmmax .. to /etc/sysctl.conf


Daemon

  sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql83-server.plist

Manual

    /opt/local/lib/postgresql82/bin/postgres -D /opt/local/var/db/postgresql82/defaultdb

    /opt/local/lib/postgresql82/bin/pg_ctl -D /opt/local/var/db/postgresql82/defaultdb -l logfile start

===== .bashrc =====

export PATH=/opt/local/lib/postgresql83/bin:$PATH

===== GUI =====

pgAdmin3 GUI 




==== postgre Ruby Gem ====

  sudo env ARCHFLAGS="-arch i386" \
  gem install postgres -- \
  --with-pgsql-lib=/opt/local/lib/postgresql83 \
  --with-pgsql-include=/opt/local/include/postgresql83



==== Dump and Restore ====

  (vc falou cu?)
  pg_dump -vC -F c -U postgres rtrac_development -f rdev.sql

  pg_restore -C -d template1 file.sql



==== Alter Table ====

  ALTER TABLE bla ALTER COLUMN foo TYPE varchar(10);

===== monkeypatch rails =====


  find ./vendor/rails/ -type f -iname databases.rake | xargs -i cp {}   ./lib/tasks/databases.rake.orig
  sed 's/^.*`createdb .* -U "#{.*}"/& -T template_postgis /' ./lib/tasks/databases.rake.orig > ./lib/tasks/databases.rake
  rm ./lib/tasks/databases.rake.orig

  $ ruby script/generate geo_scaffold Hotspot name:string description:text location:point



  /var/lib/postgres/data/

Users

  select * from pg_shadow;


Linux

.bashrc

  export PGPORT='22222'

====== Links ======

http://shifteleven.com/articles/2008/03/21/installing-postgresql-on-leopard-using-macports

http://www2.russbrooks.com:8080/2007/11/4/install-postgresql-on-mac-os-x-10-5-leopard

http://www.davidpashley.com/articles/postgresql-user-administration.html

http://www.lincolnritter.com/blog/2007/12/04/installing-postgresql-postgis-and-more-on-os-x-leopard/

http://www.cyberciti.biz/faq/howto-add-postgresql-user-account/