Upload data from .sql file to serverless

Hey, can someone give me a hint here, what am I doing wrong?
Trying to upload data from .sql file, following the serverless course and the movr one, but I am not able to upload. I got errors with the cert file.

juliobar@LAPTOP-P33HG2PG:/mnt/c/Users/julio/Downloads$ cat vehicles_data.sql | cockroach sql --url postgresql://********:**********@free-tier7.aws-eu-west-1.cockroachlabs.cloud:26257/movr?sslmode=verify-full&sslrootcert=$HOME/.postgresql/root.crt&options=–cluster%3Duniversity-cluster-1435
[1] 376
[2] 377
juliobar@LAPTOP-P33HG2PG:/mnt/c/Users/julio/Downloads$ ERROR: cannot load certificates.
Check your certificate settings, set --certs-dir, or use --insecure for insecure clusters.

problem using security settings: no certificates found; does certs dir exist?
Failed running “sql”

[1]- Exit 1 cat vehicles_data.sql | cockroach sql --url postgresql://***:@free-tier7.aws-eu-west-1.cockroachlabs.cloud:26257/movr?sslmode=verify-full
[2]+ Done sslrootcert=$HOME/.postgresql/root.crt
juliobar@LAPTOP-P33HG2PG:/mnt/c/Users/julio/Downloads$

I downloaded the crt file
juliobar@LAPTOP-P33HG2PG:/mnt/c/Users/julio/Downloads$ ls -l $HOME/.postgresql/
total 4
-rw-r–r-- 1 juliobar juliobar 2728 Jan 6 10:48 root.crt
juliobar@LAPTOP-P33HG2PG:/mnt/c/Users/julio/Downloads$

Thanks in advance.

Hi @juliobar,

Welcome to the community. This error looks strange. Are you able to connect to the cluster using just cockroach sql --url or do you face the same error?

Hey, thank you very much!
I am able to connect perfectly, let me show you:
juliobar@LAPTOP-P33HG2PG:/mnt/c/Users/julio/Downloads$ cockroach sql --url “postgresql://:*@free-tier7.aws-eu-west-1.cockroachlabs.cloud:2
6257/movr?sslmode=verify-full&sslrootcert=$HOME/.postgresql/root.crt&options=–cluster%3Duniversity-cluster-1435”

Welcome to the CockroachDB SQL shell.

All statements must be terminated by a semicolon.

To exit, type: \q.

Client version: CockroachDB CCL v21.2.3 (x86_64-unknown-linux-gnu, built 2021/12/14 15:23:22, go1.16.6)

Server version: CockroachDB CCL v21.2.0-beta.4-20-g3061813685 (x86_64-unknown-linux-gnu, built 2021/10/15 07:02:45, go1.16.6)

warning: server version older than client! proceed with caution; some features may not be available.

Cluster ID: 861aaae4-058d-4347-8829-62b860dd4922

No entry for terminal type “xterm-256color”;
using dumb terminal settings.

Enter ? for a brief introduction.

juliobarbagallo@free-tier7.aws-eu-west-1.cockroachlabs.cloud:26257/movr> SHOW databases;
database_name | owner | primary_region | regions | survival_goal
----------------±----------------±---------------±--------±---------------
defaultdb | root | NULL | {} | NULL
movr | juliobarbagallo | NULL | {} | NULL
postgres | root | NULL | {} | NULL
system | node | NULL | {} | NULL
(4 rows)

Time: 231ms total (execution 3ms / network 229ms)

juliobarbagallo@free-tier7.aws-eu-west-1.cockroachlabs.cloud:26257/movr>

It also works on Windows with PowerShell:
juliobarbagallo@free-tier7.aws-eu-west-1.cockroachlabs.cloud:26257/movr> SHOW databases;
database_name | owner | primary_region | regions | survival_goal
----------------±----------------±---------------±--------±---------------
defaultdb | root | NULL | {} | NULL
movr | juliobarbagallo | NULL | {} | NULL
postgres | root | NULL | {} | NULL
system | node | NULL | {} | NULL
(4 rows)

Time: 309ms total (execution 2ms / network 307ms)

juliobarbagallo@free-tier7.aws-eu-west-1.cockroachlabs.cloud:26257/movr>

Hi,

Thanks! Since you are able to connect to the db otherwise, I suspect the problem might be in the way the .sql file is being imported. Please could you try using the cockroach import command instead of the | operator to load the data? You can refer to this documentation on how to use the cockroach import command. You can specify the cluster you want to load the data into by providing the connection string as a part of the --url flag to the cockroach import command.

Hi Rima, I appreciate your help, but its still not working, I am doing something wring I think, but can’t realize what…

juliobar@LAPTOP-P33HG2PG:/mnt/c/Users/julio/Downloads$ cockroach import db mysqldump /mnt/c/Users/julio/Downloads/vehicles_data.sql --url postgresql://*****:*******@free-tier7.aws-eu-west-1.cockroachlabs.cloud:26257/movr?sslmode=verify-full&sslrootcert=/home/juliobar/.postgresql/root.crt&options=–cluster%3Duniversi
ty-cluster-1435 --certs-dir=/home/juliobar/.postgresql/
[1] 579
[2] 580
-bash: --certs-dir=/home/juliobar/.postgresql/: No such file or directory
[2]+ Done sslrootcert=/home/juliobar/.postgresql/root.crt
juliobar@LAPTOP-P33HG2PG:/mnt/c/Users/julio/Downloads$ E220110 14:32:33.681640 1 1@cli/clierror/check.go:35 [-] 1 problem using security settings: no certificates found; does certs dir exist?
ERROR: problem using security settings: no certificates found; does certs dir exist?
Failed running “import db”

[1]+ Exit 1 cockroach import db mysqldump /mnt/c/Users/julio/Downloads/vehicles_data.sql --url postgresql://:*******@free-tier7.aws-eu-west-1.cockroachlabs.cloud:26257/movr?sslmode=verify-full
juliobar@LAPTOP-P33HG2PG:/mnt/c/Users/julio/Downloads$ cockroach import db mysqldump /mnt/c/Users/julio/Downloads/vehicles_data.sql --url postgresql://
:*******@free-tier7.aws-eu-west-1.cockroachlabs.cloud:26257/movr?sslmode=verify-full&sslrootcert=/home/juliobar/.postgresql/root.crt&options=–cluster%3Duniversity-cluster-1435
[1] 592
[2] 593
juliobar@LAPTOP-P33HG2PG:/mnt/c/Users/julio/Downloads$ E220110 14:33:15.047580 1 1@cli/clierror/check.go:35 [-] 1 problem using security settings: no certificates found; does certs dir exist?
ERROR: problem using security settings: no certificates found; does certs dir exist?
Failed running “import db”

[1]- Exit 1 cockroach import db mysqldump /mnt/c/Users/julio/Downloads/vehicles_data.sql --url postgresql://*****:*******@free-tier7.aws-eu-west-1.cockroachlabs.cloud:26257/movr?sslmode=verify-full
[2]+ Done sslrootcert=/home/juliobar/.postgresql/root.crt
juliobar@LAPTOP-P33HG2PG:/mnt/c/Users/julio/Downloads$

Its like it doesn’t see the crt file, but its there, I was able to downloaded it.
juliobar@LAPTOP-P33HG2PG:/mnt/c/Users/julio/Downloads$ ls -l /home/juliobar/.postgresql/
total 4
-rw-r–r-- 1 juliobar juliobar 2728 Jan 6 10:48 root.crt
juliobar@LAPTOP-P33HG2PG:/mnt/c/Users/julio/Downloads$ wc -l /home/juliobar/.postgresql/root.crt
44 /home/juliobar/.postgresql/root.crt
juliobar@LAPTOP-P33HG2PG:/mnt/c/Users/julio/Downloads$

Do you see something wrong on my line?

Thank you. Julio.