Migrate SQLite dump to PostgreSQL deployed on Kubernetes

3/22/2019

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!

  1. I have seen some question regarding migrating SQLite to PostgreSQL, and it seems this command will do that: 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?
  2. How do I combine the command in question 1 to migrate it to the kubernetes deployment? I tried with adding < 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.

-- jawwe
kubernetes
postgresql
sqlite

1 Answer

3/27/2019
  1. 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

  2. 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)

-- A_Suh
Source: StackOverflow