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

Known Issues

If you tried to configure jBPM or RHPAM pointing to a PostgreSQL database, you might end up with this error:

19:32:35,890 ERROR [org.dashbuilder.exception.ExceptionManager] (default task-17) Can't lookup on specified data set: jbpmProcessDefinition: org.dashbuilder.dataset.exception.DataSetLookupException: Can't lookup on specified data set: jbpmProcessDefinition

If that’s your case, probably the user configured on your datasource to access jBPM database does not have the correct rights to create/update. The following solution is not recommended for production.

If you are running locally and want to confirm the above, you can try giving your user a more powerful role:

ALTER ROLE jbpm SUPERUSER CREATEROLE;