Installing a PostgreSQL database
Overview
This guide walks through setting up a sample PostgreSQL database that can be used for testing purposes.
Guide
This example uses scripts provided by Bitnami.
The following initdb.sql is provided as an example to setup a database with mock data.
CREATE DATABASE finance;
\c finance
CREATE TABLE stocks (
id int,
sym varchar(10),
market varchar(10),
name varchar(255),
cap varchar(20)
);
INSERT INTO stocks VALUES (1, 'AAPL', 'NASDAQ', 'Apple Inc.', '$2.47T');
INSERT INTO stocks VALUES (2, 'MSFT', 'NASDAQ', 'Microsoft', '$2.32T');
To launch the PostgreSQL database in Docker, add a PostgreSQL service to a docker-compose.yaml
version: "3.3"
services:
psql:
image: docker.io/bitnami/postgres:latest
ports:
- 5432:5432
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: iamsecure
restart: always
volumes:
- ./initdb.sql:/docker-entrypoint-initdb.d/initdb.sql
Finally, run docker-compose up to launch the database.
Being by installing a PostgreSQL database into the current Kubernetes cluster.
helm install postgresql bitnami/postgresql
This should result in a postgresql image being launched in the current Kubernetes context.
kubectl get pods | grep postgresql
NAME READY STATUS RESTARTS AGE
postgresql-postgresql-0 1/1 Running 0 4h16m
The PostgreSQL image will generate a random password. Extract the password from the cluster and store it in
and environment variable called PGPASSWORD for later use.
export PGPASSWORD=$(kubectl get secret postgresql -o jsonpath="{.data.postgresql-password}" | base64 --decode)
Adding mock data
Now that the database is running, add some mock data by interacting with a PostgreSQL client. Launch an interactive client by running the following.
kubectl run postgresql-client --rm --tty -i --restart='Never' \
--image docker.io/bitnami/postgresql:latest \
--env="PGPASSWORD=$PGPASSWORD" \
--command -- psql --host postgresql -U postgres -d postgres -p 5432
To add a mock stocks table with some mock data, run each of the following commands in the client shell from above.
CREATE DATABASE testdb;
CREATE USER testdbuser WITH PASSWORD 'testpass';
GRANT ALL PRIVILEGES ON DATABASE testdb TO testdbuser;
CREATE TABLE stocks (id int, sym varchar(10), market varchar(10), name varchar(255), cap varchar(20));
INSERT INTO stocks VALUES (1, 'AAPL', 'NASDAQ', 'Apple Inc.', '$2.47T');
INSERT INTO stocks VALUES (2, 'MSFT', 'NASDAQ', 'Microsoft', '$2.32T');
The end reult should have a table with the mock data added
SELECT * FROM stocks
id | sym | market | name | cap
----+------+--------+------------+--------
1 | AAPL | NASDAQ | Apple Inc. | $2.47T
2 | MSFT | NASDAQ | Microsoft | $2.32T
For next steps, see the guide on issuing a PostgreSQL query from the Stream Processor, continue to the querying guide.
Data examples
Another example using car sample data follows:
CREATE TABLE cars (id int, Name varchar(250), "Miles_per_Gallon" smallint, "Cylinders" smallint, "Displacement" smallint, "Horsepower" smallint, "Weight_in_lbs" smallint NOT NULL, "Acceleration" smallint, "Year" date NOT NULL, "Origin" character varying(60));
INSERT INTO cars VALUES (1, 'chevrolet chevelle malibu', 18, 8, 307, 130, 3504, 12, '1970-01-01', 'USA');
INSERT INTO cars VALUES (2, 'volkswagen 1131 deluxe sedan', 26, 4, 97, 46, 1835, 21, '1970-01-01', 'Europe');
The final table data should be as follows:
SELECT * FROM cars
id | name | Miles_per_Gallon | Cylinders | Displacement | Horsepower | Weight_in_lbs | Acceleration | Year | Origin
----+------------------------------+------------------+-----------+--------------+------------+---------------+--------------+------------+--------
1 | chevrolet chevelle malibu | 18 | 8 | 307 | 130 | 3504 | 12 | 1970-01-01 | USA
2 | volkswagen 1131 deluxe sedan | 26 | 4 | 97 | 46 | 1835 | 21 | 1970-01-01 | Europe