
PostgreSQL Basics by Example - darthdeus
http://darthdeus.github.io/blog/2013/08/19/postgresql-basics-by-example/
======
fphilipe
Craig Kerstiens from Heroku periodically posts great articles about PostreSQL
[1]. They're all really worth a read, especially [2] which is in the nature of
the original link.

[1]
[http://www.craigkerstiens.com/content/](http://www.craigkerstiens.com/content/)

[2] [http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-
Pos...](http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-Postgres/)

~~~
craigkerstiens
First, thanks for the callout. Happy to shamelessly accept plugs, and to add
to it there's a guide I curate and a weekly newsletter with interesting
articles as well:

[http://www.postgresguide.com](http://www.postgresguide.com)

[http://www.postgresweekly.com](http://www.postgresweekly.com)

------
workhere-io
Also worth noting for those who are starting out with PostgreSQL: The easiest
way to install it on your Mac is using Postgres.app
([http://postgresapp.com](http://postgresapp.com)).

~~~
chourobin
As much I like mattt's work, I dont understand, why is this needed. brew
install postgres is just as easy and in my experience Postgres.app is not well
maintained and uses an older version.

~~~
southpolesteve
I've personally helped 4 people setup Postgres on a recent model macbook with
OSX Mountain Lion for Rails dev in the last 6 months. Every one has been
terrible. Issue with sockets, issues with file permissions, issues with
previous attempted installs, issues with setting up database users.
Postgres.app just works and it is awesome.

~~~
chourobin
Thats interesting, I've never had an issue with the homebrew installation. The
readme details 1 or 2 lines to get everything set up for the first time.

I suppose osx built-in installation may cause conflicts.

~~~
darthdeus
To be honest I've spent about 4 hours last week trying to install homebrew
PostgreSQL on a friends MacBook ... it took me forever to figure out why it
wasn't connecting to the right socket, just because he had some things out of
date :\

While it is really easy to install most of the time, I'd say the Postgres.app
works well for people who aren't developers but need to use PostgreSQL.

~~~
chourobin
Did you see the post installation instructions to initialize a db?

initdb /usr/local/var/postgres -E utf8

Also if you are working with rails, add host: localhost in your database.yml
file and remove the user password. That should be all you need.

------
lukes386
On a somewhat related note, I'd love to see a more intermediate resource on
how to really leverage PostgreSQL in your applications.

As someone who primarily works with Rails, I feel like I use about 1% of
PostgreSQL's power.

~~~
jacques_chester
Which is how Rails wants you to use PostgreSQL. Their opinion is that logic
belongs in the app, not the database.

~~~
steveklabnik
In general this is true, but with Rails 4, we added a lot of Postgres specific
features to ActiveRecord, so you can actually take advantage of all the
awesomeness Postgres has to offer.

Check out this blog post: [http://blog.remarkablelabs.com/2012/12/a-love-
affair-with-po...](http://blog.remarkablelabs.com/2012/12/a-love-affair-with-
postgresql-rails-4-countdown-to-2013)

~~~
jacques_chester
Has the actual philosophy changed though?

When I first got to Rails it seemed that databases were considered as a
bothersome but unavoidable necessity; flat files with a funny accent, instead
of an essential and powerful ally in the fight against entropy and error.

~~~
integraton
In the past several years Postgres and NoSQL data stores have gained in
popularity and become mainstream, and Rails now supports them well. The Rails
ecosystem historically focused on MySQL, but that's not the case anymore. For
example, Engine Yard now uses Postgres by default, and Heroku always has.

~~~
jacques_chester
I basically remember head-scratching about the worthiness of exotic features
like foreign keys.

Some poking around in the current documentation seems to suggest that FKs have
been absorbed into ActiveRecord.

------
pjungwir
Nice intro. One great trick if you want to learn more about the system tables
is `psql -e`. This will show the queries used internally by all the \d, \u,
etc. commands.

------
adwf
Whilst not perfect, pgadmin can also be helpful. I find it particularly useful
for keeping an overall view of multiple databases/servers.

[http://pgadmin.org/](http://pgadmin.org/)

~~~
nucleardog
As someone who has spent the last few days trying to become familiar with
pgsql: yes.

Don't be intimidated by all the options it offers. It makes things really easy
and - more importantly imho - shows you the SQL it runs for every command.

So far it seems a great way to learn pgsql coming with some background in SQL,
if you want it to be.

------
Oculus
Bookmarked. Definitely could've used this post when I was just starting with
PostgreSQL!

~~~
darthdeus
I'm glad to hear that :) I've had problems with this for so long that I
finally decided to put all of the information together.

I'm planning to cover more things about PostgreSQL, mostly using pg_dump,
pg_restore, pg_upgrade, initdb etc., just the regular things you should know
when using it on your own VPS.

~~~
pavanred
I would definitely follow this then. I was just about to say that I hope this
develops into part 1 of a queue of topics covered in a similar way. I just
started with PostgreSQL and this is very helpful.

------
Choronzon
As far as mac installs are concerned while i am fond of OSX for day to day
usage you are way better putting your postgres install in a linux VM,it will
reflect production better and be far easier to install.

------
mistermcgruff
Repeat after me: set work_mem to 'XXXGB'; set maintenance_work_mem to 'YYYGB';
swing away!

~~~
kibibu
This annoys me so much about PostgreSQL. Why not have useful defaults?!

~~~
ilikepi
The defaults are useful in that they reflect a configuration that is
appropriate to get the system running on a large variety of hardware. It's not
really the responsibility of the Pg developers to guess at the "best" settings
for any specific environment, because environments and configurations vary
greatly.

~~~
jedberg
I agree to a point, but it would be nice if there were some provided default
configs for various classes of machines.

Especially defaults for the most popular EC2 instance types.

~~~
VLM
Its two dimensional because of "various classes of applications"

