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