Question 245052 in Debian Question posted in
The oficial documentation can be found here.

Debian – How to create tables in posgresql in helm

I have deployed postgresql instance in k8s using helm and creating a database using values.yaml. Now How to create table.

global:
  postgresql:
    auth:
      # Added below credentials for testing
      postgresPassword: "postgresqladmin"
      username: "admin"
      password: "admin"
      database: "postgresqlDB"
containerPorts:
  postgresql: 5432
image:
  registry: docker.io
  repository: bitnami/postgresql
  tag: 15.4.0-debian-11-r10
  digest: ""
  pullPolicy: IfNotPresent
  debug: false

Here is the command to install:

helm upgrade --install postgresql -f values.yaml bitnami/postgresql

And here is the command to access db(I am using suggested command after install)

kubectl run postgresql-client --rm --tty -i --restart='Never' --image bitnami/postgresql --env="admin" --command -- psql --host postgresql -U admin -d postgresqlDB -p 5432
Tags:

3

Answers


  1. 0

    You have two options.

    1. Access container

    You can access to your database after it has been mounted like this, and then execute your sql script.

    kubectl run -it –rm –image=bitnami/postgresql:15.4.0-debian-11-r10 –restart=Never postgresql-client — psql -h postgresql -U admin -d postgresqlDB

    2. Include a script to your deployment :

    You create an SQL script that create your table myscript.sql :

    CREATE TABLE my_table (
    id serial PRIMARY KEY,
    field VARCHAR(255)
);

    Create a ConfigMap, a YAML file (e.g., configmap-create-my-table.yaml) with the following content:

    apiVersion: v1
kind: ConfigMap
metadata:
  name: create-my-table-configmap
data:
  create_table.sql: |
    -- create_table.sql
    CREATE TABLE my_table (
       id serial PRIMARY KEY,
       field VARCHAR(255)
    );

    Apply this to your cluster
    kubectl apply -f configmap-create-table.yaml

    Adapt your helm chart :

    postgresql:
  extraVolumeMounts:
    - name: create-my-table-configmap
      mountPath: /docker-entrypoint-initdb.d
      readOnly: true
  extraVolumes:
    - name: create-my-table-configmap
      configMap:
        name: create-my-table-configmap

    And then upgrade it :

    helm upgrade --install postgresql -f values.yaml bitnami/postgresql

    Login or Signup to reply.
  2. 0

    In order to create table after you have created database using values.yaml, try these steps;

    First you have to connect to postgreSQL using client:

    kubectl run -it --rm --image=bitnami/postgresql:15.4.0-debian-11-r10 --namespace=YOUR_NAMESPACE postgresql-client -- bash

    Now connect to your database inside the postgreSQL client pod;

    psql -h postgresql -U admin -d postgresqlDB

    NOw using simple SQL Commands, you can create tables;

    CREATE TABLE my_table (
    id serial PRIMARY KEY,
    name VARCHAR (255),
    age INT
);

    After successfully creating the tables, you can exit the PostgreSQL client using;
    ostgreSQL

    q

    Hope it works:)

    Login or Signup to reply.
  3. 0

    once you start the database using –

    kubectl run postgresql-client --rm --tty -i --restart='Never' --image bitnami/postgresql -- bash

    You’d need to connect to the instance via –

    psql -h postgresql -U <your-username> -d <your-database>

    You will be prompted for your password, which you specified in your values.yaml file (in your case, it’s "postgresqladmin").

    After this, you’ll see the cursor change to postgresql / the username you specified, this indicates that you can now access the database using query language.

    To create a table from here on, you can simply type in

    CREATE TABLE example (
    col1 serial PRIMARY KEY,
    col2 INT,
    col3 BOOLEAN
);

    and it will create these columns with the specified data types and the primary key. You can exit the client using q and exit the container futher using exit.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search