In this blog post we will be setting up a postgres database on docker using Dockerfile.
Dockerfile are quick way to create custom docker images. These might be helpful to setup an initial setup for development.
In this example we will be setting a postgres database using Dockerfile and then setup a initial database and data to get started.
- Create a working directory for your project.
After step3, you should have 3 files in you directory.
┌─(.venv)[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ ls -l
-rw-r--r-- 1 Zubair AHMED 197611 686 Mar 21 17:54 connect_to_postgres.py
-rw-r--r-- 1 Zubair AHMED 197611 95 Mar 21 17:49 Dockerfile
-rwxr-xr-x 1 Zubair AHMED 197611 943 Mar 21 17:44 init.db.sh
- Create a
Dockerfile.
We will be using an exsisting postgres image and adding few extra parameters to it, including the init script.
FROM postgres:9.6.21
ENV POSTGRES_PASSWORD=ahmedpostgres
EXPOSE 5432
COPY init.db.sh /tmp/
- Create a shell script called
init.db.shin the same directory.
#!/bin/bash
psql -U postgres <<-EOSQL
CREATE USER docker WITH PASSWORD 'docker';
CREATE DATABASE pythontest_db;
GRANT ALL PRIVILEGES ON DATABASE pythontest_db TO docker;
\connect pythontest_db docker
BEGIN;
CREATE TABLE DOCUMENT_TEMPLATE(
ID INTEGER NOT NULL,
NAME TEXT,
SHORT_DESCRIPTION TEXT,
AUTHOR TEXT,
DESCRIPTION TEXT,
CONTENT TEXT,
LAST_UPDATED DATE,
CREATED DATE
);
INSERT INTO DOCUMENT_TEMPLATE(id,name, short_description, author, description,content, last_updated,created)
SELECT id, 'name', md5(random()::text), 'name2'
,md5(random()::text),md5(random()::text)
,NOW() - '1 day'::INTERVAL * (RANDOM()::int * 100)
,NOW() - '1 day'::INTERVAL * (RANDOM()::int * 100 + 100)
FROM generate_series(1,100) id;
COMMIT;
select * from document_template limit 3;
EOSQL
- Create a python file
connect_to_postgres.pywhich we will be using later on.
import psycopg2
try:
conn = psycopg2.connect(host="localhost", database="pythontest_db",
user="docker", password="docker")
except Exception as e:
print(e)
exit(0)
if conn is not None:
print('Connection established to PostgreSQL.')
# Creating a cursor
cur = conn.cursor()
# Getting a query ready.
cur.execute('SELECT * from DOCUMENT_TEMPLATE limit 3;')
# we are fetching all the data from the query above.
get_all_data = cur.fetchall()
# Print all data
print(get_all_data)
# Close connection
conn.close()
else:
print('Connection not established to PostgreSQL.')
- Creating a custom
dockerimage using the below command.
docker build -t py_postgres .
┌─(.venv)[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ docker build -t py_postgres .
Sending build context to Docker daemon 17.15MB
Step 1/4 : FROM postgres:9.6.21
---> 1ceb7d7a7c95
Step 2/4 : ENV POSTGRES_PASSWORD=ahmedpostgres
---> Using cache
---> f9bf24df2b54
Step 3/4 : EXPOSE 5432
---> Using cache
---> 68596f036e98
Step 4/4 : COPY init.db.sh /tmp/
---> Using cache
---> 71e79ece7101
Successfully built 71e79ece7101
Successfully tagged py_postgres:latest
SECURITY WARNING: You are building a Docker image from Windows against a non-Windows Docker host. All files and directories added to build context will have '-rwxr-xr-x' permissions. It is recommended to double check and reset permissions for sensitive files and directories.
┌─(.venv)[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ docker image list
REPOSITORY TAG IMAGE ID CREATED SIZE
py_postgres latest 71e79ece7101 2 minutes ago 200MB
- Run the image.
This will create a container and expose port 5432.
docker run -d -p 5432:5432 --name ahmedzbyr_postgres py_postgres
┌─(.venv)[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ docker run -d -p 5432:5432 --name ahmedzbyr_postgres py_postgres
2317fb026ad59389c5aed893633e3669df8f064c6ebb03dc41fdf28263adc42b
- Run the init script on the container
docker exec -it ahmedzbyr_postgres bash -c "sh /tmp/init.db.sh"
┌─(.venv)[Zubair AHMED][AHMEDZBYR-WRK-HORSE][/d/GIT_REPOS/docker-playground]
└─▪ docker exec -it ahmedzbyr_postgres bash -c "sh /tmp/init.db.sh"
CREATE ROLE
CREATE DATABASE
GRANT
You are now connected to database "pythontest_db" as user "docker".
BEGIN
CREATE TABLE
INSERT 0 100
COMMIT
id | name | short_description | author | description | content | last_updated |
created
----+------+----------------------------------+--------+----------------------------------+----------------------------------+--------------+------------
1 | name | 2082a744765045d6506a9fbc1fe4f617 | name2 | 6e6e9bfd2ea6f02c7faeaad7a8aa55f4 | 20aa5c19269295cd1c2e2dd8f1540e47 | 2021-03-21 | 2020-12-11
2 | name | 04642aa0b3813ccd9a932290a33dd386 | name2 | d9a28d5d787474c7722bb3f193a67332 | c445fc1af82103f77d0143efacd2e0ff | 2021-03-21 | 2020-09-02
3 | name | 3fec7cd305e794853789424d2244a1e0 | name2 | 354e4460f2a8d628c5a0236632c90301 | f74a0c00b00cf8eabfbf0301cbae1735 | 2021-03-21 | 2020-12-11
(3 rows)
- Execute the python command which gives the output.
python connect_to_postgres.py
Connection established to PostgreSQL.
[
(1, 'name', '2082a744765045d6506a9fbc1fe4f617', 'name2', '6e6e9bfd2ea6f02c7faeaad7a8aa55f4', '20aa5c19269295cd1c2e2dd8f1540e47', datetime.date(2021, 3, 21), datetime.date(2020, 12, 11)),
(2, 'name', '04642aa0b3813ccd9a932290a33dd386', 'name2', 'd9a28d5d787474c7722bb3f193a67332', 'c445fc1af82103f77d0143efacd2e0ff', datetime.date(2021, 3, 21), datetime.date(2020, 9, 2)),
(3, 'name', '3fec7cd305e794853789424d2244a1e0', 'name2', '354e4460f2a8d628c5a0236632c90301', 'f74a0c00b00cf8eabfbf0301cbae1735', datetime.date(2021, 3, 21), datetime.date(2020, 12, 11))
]