Watch this space.
- How to get a PostgreSQL installation to play with (Docker)
- Using
.pgpass
andPGHOST
information_schema
- JSON columns
- Lateral joins
- Window functions
- Underused aggregates such as array_agg
- Constructing JSON API replies in the database
- Common Table Expressions (WITH statement)
With Docker:
# Install PostgreSQL locally (just for the client programs, we won't start the server)
brew install postgresql
# Start the server in background with Docker
docker run --rm -d -n postgres -p 5432:5432 -v postgres-data:/var/lib/postgresql postgres
# See the section on PGHOST below.
export PGHOST=127.0.0.1
# Create a user and database for yourself
createuser -U postgres $USER
createdb -U postgres -O $USER $USER
# Now you should be able to connect to your database this easily:
psql
You probably want to brew install postgresql
just for the client programs even if you intend to run the PostgreSQL server in Docker. It's also possible to use docker run --rm -it postgres psql
to run the client via Docker but it's relatively cumbersome.
Our playground PostgreSQL does not require a password to connect. However, when using Amazon RDS etc. you always need a password or some other means of authentication.
The .pgpass
file in your home directory lets you save passwords. See the documentation on it. TL;DR
server:port:database:username:password
Eg.
foodb:5432:foodb:foouser:foopassword
Use *
to represent "any":
foodb:*:*:foouser:foopassword
Please don't use an asterisk in the first column (server hostname).
Please be smart about where you leave .pgpass
files lying around :)
Normally you'd set the server to connect via -h foodb
. But this is cumbersome if you do a lot of stuff with external databases (eg. Amazon RDS). You can set the server to connect to with an environment variable:
export PGHOST=foodb
If PGHOST
and -h
are unset, the default is to connect via UNIX file sockets. If you used the above recipe to run a PostgreSQL server in Docker, you need to explicitly use TCP/IP to connect to your database instead:
export PGHOST=127.0.0.1