Postgres Tips

PostgresTips.md

JDBC Driver

Name	Value
artifact	postgresql:postgresql:8.4-702.jdbc3
driver	org.postgresql.Driver
JDBC url	jdbc:postgresql://localhost:5432/testdb

Connecting Heroku Postgres

Use ssl to true and sslfactory as NonValidatingFactory

JDBC Url: jdbc:postgresql://ec2-xx.xx.xx.xx.compute-1.amazonaws.com:5432/testdb?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

Drop schema without dropping the database

drop schema public cascade;
create schema public;

Install Postgres on Mac OS server Maverics 10.9

Stop postgres

sudo su
serveradmin stop postgres

Configuration file location

vim /Library/Server/PostgreSQL/Config/org.postgresql.postgres.plist

Move old postgres data directory

mv /Library/Server/PostgreSQL/Data /Library/Server/PostgreSQL/DataOld_1

Create and Initialize new postgres data directory

sudo -u _postgres /Applications/Server.app/Contents/ServerRoot/usr/bin/initdb /Library/Server/PostgreSQL/Data -E utf8 --lc-collate=C --lc-ctype=C

Import old data into new postgres data (Optional)

sudo -u _postgres /Applications/Server.app/Contents/ServerRoot/usr/bin/pg_upgrade -b /Applications/Server.app/Contents/ServerRoot/usr/libexec/postgresql9.2 -B /Applications/Server.app/Contents/ServerRoot/usr/bin -d /Library/Server/PostgreSQL/DataOld_1 -D /Library/Server/PostgreSQL/Data

Load the service and restart the postgres service

launchctl load -w /Applications/Server.app/Contents/ServerRoot/System/Library/LaunchDaemons/org.postgresql.postgres.plist
serveradmin start postgres

Login to template database

The default user is "_postgres"

sudo psql -U _postgres -d template1

Create user and granting create database privilege

create user postgres with password 'postgres';
alter user postgres createdb;
\q

Create mido database

psql -U postgres -h localhost
create database mido;

Unload the launchtl

launchctl remove org.postgresql.postgres

Logs file directory

/Library/Logs/PostgreSQL/PostgresSQL.log

String aggregation

select t.id, count(t.id), string_agg(cast(t.id as text), ',') from ticket group by t.id having count(t.id) > 1

Staring the database

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Checking the status

pg_ctl -D /usr/local/var/postgres status