Create YB DB and Schemas via a Script file

General
#1

Hello,

I am able to pull the YB image and run a container, able to do YSQLSH and execute Create DB, Create Schema and query tables, its all good. But I do have a requirement to run all these,

  1. Create the DB
  2. Create the sample Schemas
  3. Insert the data into those sample table.
    in a script file. So that, when my developer pull my image and run a container, which should pull my script file during the container creation, it will get the DB and Schema ready for them. So that they do not have to run multiple commands in it.

I was able to login to this DB directly this way.
docker container exec -it imgyb1cont1 /home/yugabyte/bin/ysqlsh “yb_demo”
But again, to do this, the DB should exist before. which will not happen unless I issue CREATE DATABASE COMMAND.
Please help.

Also I am thinking to put together the Explore YSQL, the Yugabyte SQL API | YugabyteDB Docs the SQL Queries in a file such as ybinit.sql and want to call that when creating a container. But how can I get directly connected to ysqlsh and run the sql file?

#2

@theNewGuy

This is how you can create the db:

./bin/ysqlsh -c 'create database ybdemo;'

And then you can connect to it with:

./bin/ysqlsh -d ybdemo -f share/schema.sql
#3

Thank you Dorian. I actually found another way to do this.
Copied by ysqlscript.sql to bin folder of YB and then executed this. It actually has worked though.

docker cp ./ysqlscript.sql yb1:/home/yugabyte/bin/ysqlscript.sql
docker exec yb1 ysqlsh yugabyte -f /home/yugabyte/bin/ysqlscript.sql.

My ysqlscript.sql has the below code.
CREATE DATABASE yb_demo;
\c yb_demo;
\i share/schema.sql;
\i share/products.sql;
\i share/users.sql;
\i share/orders.sql;
\i share/reviews.sql;

However, I like your line of code as well.
But is that possible to combine both in one line as below
docker exec -it yb1 /home/yugabyte/bin/ysqlsh -c ‘create database yb_demo;’ -f share/schema.sql
When I execute, it creates the DB successfully but then when it runs the schema.sql it error out saying the relation already exists. But actually it is not.

CREATE DATABASE
ysqlsh:share/schema.sql:11: ERROR: relation “products” already exists
ysqlsh:share/schema.sql:27: ERROR: relation “users” already exists
ysqlsh:share/schema.sql:39: ERROR: relation “orders” already exists
ysqlsh:share/schema.sql:48: ERROR: relation “reviews” already exists

#4

As I explained in the other thread, it is connecting to yugabyte database by default, and you can’t switch the database like you do \c dbname inside.

You have to run a new command with -d new_database to connect to.

\c db_name basically drops the current connection and starts a new one, it doesn’t “switch/swap/change” but “exit + connect”.

#5

Ok, I understood now. I just queried the above tables are already available in yugabyte DB. I missed that piece. thank you.

#6

Or those must be created by my script when I was exploring, either way, learned I cant pass both the commands in one line. appreciate your help Dorian

#7

They were created in this command

 docker exec -it yb1 /home/yugabyte/bin/ysqlsh -c ‘create database yb_demo;’ -f share/schema.sql

Because the command above connected to yugabyte database, it’s the default value for -d.

#8

Right, thats what I was thinking, I messed that up.

#9

Dorian. I understand those schema.sql, products.sql are all by default available in there and so I can create those by calling those sql script files. But if I want to copy my own table structure, then I can put those into a SQL and use it? How do I do that?

#10

Here is my updated dockerfile

FROM yugabytedb/yugabyte:latest
RUN uname -a
RUN ls -l
ENTRYPOINT ["/home/yugabyte/bin/ysqlsh", “-c ‘create database yb_demo;’”]

This is supposed to create the yb_demo DB when I run a container. But it does not. The image gets created successfully as well, the container runs good as well, just the DB is not getting created. Please help me what I am doing wrong.

#11

And when I have the entrypoint that way mentioned above, I am getting this error

ysqlsh: could not connect to server: Connection refused
Is the server running on host “localhost” (127.0.0.1) and accepting
could not connect to server: Cannot assign requested address
Is the server running on host “localhost” (::1) and accepting
TCP/IP connections on port 5433?

I even tried EXPOSE 7000 9000 5433 9042 on my dockerfile. no luck

@dorian_yugabyte

#12

PS YB> docker run -d --name yb1 -p7000:7000 -p9000:9000 -p5433:5433 -p9042:9042 /yugabytedb/yugabyte:latest bin/yugabyted start
PS YB> docker exec -it yb1 ls /home/yugabyte/bin

bulk_load_cleanup.sh patchelf yb-check-consistency.py yb-ysck
bulk_load_helper.sh post_install.sh yb-ctl ycqlsh
configure pprof yb-generate_partitions_main ycqlsh.py
cqlsh redis-cli yb-master ysqlsh
ldb sst_dump yb-pbc-dump yugabyted
log-dump yb-admin yb-ts-cli
log_cleanup.sh yb-bulk_load yb-tserver

PS YB> docker run -it yb1
[root@02b543ea0ec2 yugabyte]# cd /home/yugabyte/bin
[root@02b543ea0ec2 bin]# ls
bulk_load_cleanup.sh configure cqlsh log_cleanup.sh patchelf pprof sst_dump yb-bulk_load yb-ctl yb-master yb-ts-cli yb-ysck ycqlsh.py ysqlsh
bulk_load_helper.sh cont.sh ldb log-dump post_install.sh redis-cli yb-admin yb-check-consistency.py yb-generate_partitions_main yb-pbc-dump yb-tserver ycqlsh ysqlscript.sql yugabyted

Please see the output above. When I just run the above, one output shows the ysqlscript.sql and cont.sh in its output, But the other one does not show that. Any reason why? Thank you

#13

Can mark this as CLOSED. I am good on this.