Running a PostgreSQL instance ad-hoc for testing is not always as bootstraping as it should be. This blog will run a PostgreSQL instance that connects to your workstation’s network and instead of using one of the popular tools like dbeaver we shall use the client that comes with the instance. Also we shall run a bootstrap script to have some data pre-inserted.
Let’s get started by running the instance. On purpose I will use another port. On scenarios of multiple instances running in your workstation, port collisions are likely. The workaround would be to choose port 5433.
1 | docker run --rm --name test-instance -e POSTGRES_PASSWORD=password -p 5433:5432 postgres |
This will run PostgreSQL and you shall be able to connect to port 5433. On a CTRL-C the instance will be stopped and destroyed.
Now instead of using an external tool to connect let’s use the instance itself, it comes with psql pre-installed.
1 2 3 4 5 6 7 8 9 | docker exec -it test-instance /bin/bash> psql postgres postgrespostgres=# \hAvailable help: ABORT ALTER TRIGGER CREATE RULE DROP GROUP LISTEN ALTER AGGREGATE ALTER TYPE CREATE SCHEMA DROP INDEX LOAD ALTER COLLATION ALTER USER CREATE SEQUENCE DROP LANGUAGE LOCK.....postgres=# \q |
The instance works and connections from the outside are possible.
Next step would be to bootstrap a db initialization script.
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 | #!/bin/bashset -e psql -v ON_ERROR_STOP=1 --username postgres --dbname postgres <<-EOSQL create schema test_schema; create table test_schema.employee( id SERIAL PRIMARY KEY, firstname TEXT NOT NULL, lastname TEXT NOT NULL, email TEXT not null, age INT NOT NULL, salary real, unique(email) ); insert into test_schema.employee (firstname,lastname,email,age,salary) values ('John','Doe 1','john1@doe.com',18,1234.23);EOSQL |
Supposing the file with the script is called init_db.sh
Let’s run the command with the initialization schema mounted.
1 | docker run --rm --name test-instance -v /path/to/init_db.sh:/docker-entrypoint-initdb.d/init-db-script.sh -e POSTGRES_PASSWORD=password -p 5433:5432 postgres |
And let’s check the results.
1 2 3 4 5 6 7 | docker exec -it test-instance /bin/bash>psql postgres postgrespostgres=# SELECT*FROM test_schema.employee; id | firstname | lastname | email | age | salary----+-----------+----------+---------------+-----+--------- 1 | John | Doe 1 | john1@doe.com | 18 | 1234.23(1 row) |
That’s it! You created a Postgresql database through docker, you did connect to it also you added a bootstrap script with data.
Published on Java Code Geeks with permission by Emmanouil Gkatziouras, partner at our JCG program. See the original article here: Run a docker PostgreSQL instance locally for Testing Opinions expressed by Java Code Geeks contributors are their own. |
You can also copy *.SQL files to the /docker-entrypoint-initdb.d/ directory.
I’ve done it with the COPY command in a dockerfile:
COPY *.sql /docker-entrypoint-initdb.d/