How to turn a sqlite3 table to a postgresql table?

Question

I started a project with Rails without knowing Heroku required Postgresql. Now I would like to convert my sqlite3 table to a postgresql table. How can I do it?

I found a way to get SQL Inserts :

BEGIN TRANSACTION;
insert into places ("id", "name", "content", "tags", "created_at", "updated_at") values ('0', 'bac à papier', NULL, 'bac de recyclage', '13309980782012-03-06 03:57:21.219233', '13309980782012-03-06 03:57:21.219233');
insert into places ("id", "name", "content", "tags", "created_at", "updated_at") values ('1', 'bac PVM', 'plastique/verre/métal', NULL, '13310076572012-03-06 03:57:21.219233', '13310076572012-03-06 03:57:21.219233');
insert into places ("id", "name", "content", "tags", "created_at", "updated_at") values ('2', 'bac de déchets', NULL, 'poubelle', '13310076572012-03-06 03:57:21.219233', '13310076572012-03-06 03:57:21.219233');
insert into places ("id", "name", "content", "tags", "created_at", "updated_at") values ('3', 'cafétéria', '', '', '2012-03-06 03:57:21.219233', '2012-03-06 03:57:21.219233');
insert into places ("id", "name", "content", "tags", "created_at", "updated_at") values ('4', 'compost', NULL, NULL, '2012-03-06 03:57:21.219233', '2012-03-06 03:57:21.219233');
insert into places ("id", "name", "content", "tags", "created_at", "updated_at") values ('5', 'consigne', NULL, NULL, '2012-03-06 03:57:21.219233', '2012-03-06 03:57:21.219233');
insert into places ("id", "name", "content", "tags", "created_at", "updated_at") values ('6', 'CRD', NULL, NULL, '2012-03-06 03:57:21.219233', '2012-03-06 03:57:21.219233');
insert into places ("id", "name", "content", "tags", "created_at", "updated_at") values ('7', 'ressources matérielles', NULL, NULL, '2012-03-06 03:57:21.219233', '2012-03-06 03:57:21.219233');
insert into places ("id", "name", "content", "tags", "created_at", "updated_at") values ('8', 'sécurité', NULL, NULL, '2012-03-06 03:57:21.219233', '2012-03-06 03:57:21.219233');
insert into places ("id", "name", "content", "tags", "created_at", "updated_at") values ('10', 'technicien chimie', NULL, NULL, '2012-03-06 03:57:21.219233', '2012-03-06 03:57:21.219233');
insert into places ("id", "name", "content", "tags", "created_at", "updated_at") values ('11', 'technicien biologie', NULL, NULL, '2012-03-06 03:57:21.219233', '2012-03-06 03:57:21.219233');
insert into places ("id", "name", "content", "tags", "created_at", "updated_at") values ('12', 'service informatique', NULL, NULL, '2012-03-06 03:57:21.219233', '2012-03-06 03:57:21.219233');
insert into places ("id", "name", "content", "tags", "created_at", "updated_at") values ('13', 'îlot multi-récupération', 'face au B-227', NULL, '2012-03-06 03:57:21.219233', '2012-03-06 03:57:21.219233');
insert into places ("id", "name", "content", "tags", "created_at", "updated_at") values ('14', 'récupérateurs extérieurs', NULL, NULL, '2012-03-06 03:57:21.219233', '2012-03-06 03:57:21.219233');
COMMIT;

SOLUTION Here is how I solved the problem :

I used Chris's solution to tell rails that I want to use PostgreSQL. Then, I ran db:migrate.

To convert my SQLite tables to PostgreSQL tables, I used sliteman, a Linux program to manage SQLite databases. With this utility, I was able to convert the tables to SQL inserts. I logged in to PostgreSQL and applied the inserts.

Voila!

Chris Barretto · Accepted Answer · 2012-03-07 16:25:43Z

1

If you just want your local db to run on postgres to match heroku, just change your database.yml to look something like this:

development:
  adapter: postgresql
  encoding: utf8
  database: development
  pool: 5
  username: postgres
  password:

test: &TEST
  adapter: postgresql
  encoding: utf8
  database: test
  pool: 5
  username: postgres
  password:

production:
  adapter: postgresql
  encoding: utf8
  database: production
  pool: 5
  username: postgres
  password:

cucumber:
  <<: *TEST

This is under the assumption you have a postgres role with the name 'postgres'. If you are on a mac, your username should by default be whatever your user shortname is.

answered Mar 7, 2012 at 16:25

Chris Barretto

9,5694 gold badges45 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to turn a sqlite3 table to a postgresql table?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related