development · quick-guides

Run and access PostgreSQL DB in 30 seconds [4steps]

Quick and simple, as should every technology be. Here’s a quick step-by-step to bootstrap and access a local PSQL Database without needing to install it:


Pre-req: Docker


#TLDR;

Ready… Set… Go:

mkdir -p $HOME/docker/opt/db/psql
docker run --rm   --name psql-jbpm -e POSTGRES_PASSWORD=psql@123 -d -p 6543:5432 -v $HOME/docker/opt/db/psql:/var/lib/postgresql/data  postgres:9.4

That’s it, now you have a local psql up and running.

docker exec -it psql-jbpm /bin/bash
psql -h localhost -U postgres -d postgres

Instructions (With more details):

  1. Create a folder to map a volume for the container:
mkdir -p $HOME/docker/opt/db/psql

2. Run PostgreSQL 9.4, mapping the volume to the directory above, and exposing the default port 5432, with 6543 to be accessed by other services. (Like jBPM! )

docker run --rm   --name psql-jbpm -e POSTGRES_PASSWORD=psql@123 -d -p 6543:5432 -v $HOME/docker/opt/db/psql:/var/lib/postgresql/data  postgres:9.4

3. A code like this will be outputted with the container id: e8ba74e847c613e9f60ec06943f9d8cb1d4e18eb32e399b3ba30a45431b1c5ac . Grab the first two chars, and access the container:

docker exec -it e8 /bin/bash

4. Access psql DB:

psql -h localhost -U postgres -d postgres

Enjoy.

\q to quit postgres | just in case you forget 🙂

Bonus

Adding a database, with grants for new user and password (using SQL):

CREATE DATABASE mydb;
CREATE USER myusername WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myusername;

The connection URL to this database should be something like:

jdbc:postgresql://<database_host>:<port>/<database_name>
therefore:
jdbc:postgresql://localhost:6543/mydb

If you try to connect using, for example, WildFly and see the following error:

Caused by: org.postgresql.util.PSQLException: FATAL: password authentication failed for user

Make sure you are using the proper driver version for your server, and that your pg_hba.conf is properly configured. In this post we used PSQL 9.4, so the proper driver is postgresql-9.4.1212.jar

One thought on “Run and access PostgreSQL DB in 30 seconds [4steps]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s