I am trying to migrate a SQLite dump to PostgreSQL deployed on Kubernetes but I am having issues connecting the dots.
I have created a SQLite dump using the following command:
sqlite3 sqlite.db .dump > dump.sql
Now, I want to migrate it to my Kubernetes deployment. I have connected to it using:
psql -h <external IP of node> -U postgresadmin --password -p <port that i got using kubectl get svc postgres>
Everything seems to work so far, it is now I run into problems that resulted in me having two questions!
psql /path/to/psql -d database -U username -W < /the/path/to/dump.sql
. Will I have to create the same tables (empty but with same columns etc.) in PostgreSQL if the dump contains several tables, or will this command do this automatically? I believe I do, based on output I share in question 2, but will it also move on automatically to the next table?< dump.sql
to the command I used connecting to it but got the following output:CREATE TABLE
INSERT 0 1
ERROR: relation "first_report" does not exist
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
So, obviously I am doing something wrong... The dump contains three tables, first_report
,second_report
and relation_table
.
you definitely don't need to create empty tables in PostgreSQL, your dump file should contains create table
statements. I'd recommend to test with dumping a schema only, without data sqlite3 sqlite.db .schema > dump.sql
In order to restore db from dump inside a container just run
kubectl exec -it postgres-pod -- psql --username admin --d testdb < dump.sql
If it won't help, please share you dump.sql file (schema only)