Thursday, April 3, 2014

PostgreSQL getting started

PostGreSQL site

http://www.postgresql.org/

Download and Run 

postgresql-9.3.4-1-osx.app

  • Installation Directory: /Library/PostgreSQL/9.3
  • Data Directory: /Library/PostgreSQL/9.3/data
  • Set superuser password
  • set port e.g. 5432
  • set locale e.g en_US.UTF-8
  • Launch Stack Builder



Stack Builder 3.1.1

  • Database Drivers
    - pgJDBC v9.3-1100-1
  • Spacial Extensions
    - PostGIS 2.1
  • Select download dir e.g. /Applications/DB/PostGre/Extensions
  • finish installations



Check Database Size



$ cd /Library/PostgreSQL/9.3

$ ls -alt | grep data
drwx------  22 postgres  daemon    748 Apr  3 14:31 data


4) PostgreSQL client (UI)
  • pgAdmin3
http://www.postgresql.org/ftp/pgadmin3/release/v1.18.1/osx/




Set PATH to PostgreSQL


$ edit ~/.profile
# PostGreSQL
export POSTGRE_HOME=/Library/PostgreSQL/9.3
export PATH=${PATH}:${POSTGRE_HOME}/bin



Sandbox Directory

create yourself a sandbox directory to play in
$ cd /Applications/DB/PostGre/sandbox 





Users

Adding and checking users


@ sandbox $ psql --user=postgres template1 -c '\du'
                          

List of roles

Role name  |                   Attributes                   | Member of 
-----------+------------------------------------------------+----
 postgres  | Superuser, Create role, Create DB, Replication | {}

 uki       | Password valid until infinity                  | {}




Create Dump

creating postGreSQL database backup
@ sandbox $ pg_dump -Fp --user=postgres --dbname=gps_points --format=plain --clean --create  --file=gps_points.sql 
@ sandbox $ ls
gps_points.sql



Terminal (Command Line)


@ 9.3 $ sudo su
Password:
@ 9.3 $ su - postgres

uki:~ postgres$


Check Database Size


SELECT pg_size_pretty(pg_database_size('polygons'))
SELECT pg_size_pretty(pg_relation_size('gps_points'))