# Migrating from sqlite to postgresql

**URL:** <https://support.prodi.gy/t/migrating-from-sqlite-to-postgresql/468>\
**Category:** Uncategorized\
**Tags:** database, solved\
**Created:** [April 4, 2018, 2:33am UTC](https://support.prodi.gy/t/migrating-from-sqlite-to-postgresql/468 "2018-04-04T02:33:39Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![cmc476](https://avatars.discourse-cdn.com/v4/letter/c/7ab992/32.png) [@cmc476](https://support.prodi.gy/u/cmc476)\
**Post date:** [April 4, 2018, 2:33am UTC](https://support.prodi.gy/t/migrating-from-sqlite-to-postgresql/468/1 "2018-04-04T02:33:39Z")

</div>

Some notes in case any one else decides to take this path -

dumped sqlite3 using -

`sqlite3 ~/.prodigy/prodigy.db .dump > prodigy_sqlite3.dump`

edit prodigy\_sqlite3.dump and remove any create statements, and begin\commit.  
regex replace —

`:s/\,X\'/\,E\'\\\\x/g`

to convert blob (bit) to bytea

```python
sudo su - postgresuser
createdb prodigy
psql prodigy
CREATE TABLE dataset (
    id integer NOT NULL,
    name character varying(255) NOT NULL,
    created integer NOT NULL,
    meta bytea NOT NULL,
    session integer NOT NULL
);
ALTER TABLE ONLY dataset
    ADD CONSTRAINT dataset_pkey PRIMARY KEY (id);
CREATE TABLE example (
    id integer NOT NULL,
    input_hash bigint NOT NULL,
    task_hash bigint NOT NULL,
    content bytea NOT NULL
);
ALTER TABLE ONLY example
    ADD CONSTRAINT example_pkey PRIMARY KEY (id);
CREATE TABLE public.link (
    id integer NOT NULL,
    example_id integer NOT NULL,
    dataset_id integer NOT NULL
);

ALTER TABLE ONLY link
    ADD CONSTRAINT link_pkey PRIMARY KEY (id);

ALTER TABLE ONLY link
    ADD CONSTRAINT link_dataset_id_fkey FOREIGN KEY (dataset_id) REFERENCES dataset(id);

ALTER TABLE ONLY link
    ADD CONSTRAINT link_example_id_fkey FOREIGN KEY (example_id) REFERENCES example(id);

\q

```

`cat prodigy_sqlite3.dump | psql prodigy`

```python
ALTER TABLE dataset ALTER COLUMN session DROP DEFAULT;
ALTER TABLE dataset ALTER session TYPE bool USING CASE WHEN session=0 THEN FALSE ELSE TRUE END;
ALTER TABLE dataset ALTER COLUMN session SET DEFAULT FALSE;

CREATE SEQUENCE dataset_id_seq;
ALTER TABLE dataset ALTER id SET DEFAULT NEXTVAL('dataset_id_seq');
CREATE SEQUENCE example_id_seq;
ALTER TABLE example ALTER id SET DEFAULT NEXTVAL('example_id_seq');
CREATE SEQUENCE link_id_seq;
ALTER TABLE link ALTER id SET DEFAULT NEXTVAL('link_id_seq');

SELECT id FROM (each table - dataset, example, link) ORDER BY id DESC limit 1;
ALTER SEQUENCE (each sequence - dataset, example, link) RESTART WITH (id from above + 1);

```

whew…prodigy started - no warranty implied - your mileage may vary.

---

<div class="post-metadata">

**Author:** ![honnibal](https://sea2.discourse-cdn.com/flex020/user_avatar/support.prodi.gy/honnibal/32/35_2.png) [@honnibal](https://support.prodi.gy/u/honnibal)\
**Post date:** [April 4, 2018, 8:36am UTC](https://support.prodi.gy/t/migrating-from-sqlite-to-postgresql/468/2 "2018-04-04T08:36:35Z")

</div>

Thanks! ❤ ❤

Database Administration is probably the skill-tree most under-represented in Ines and I as a development team, so we’re really grateful for the tips.