CODEWITHSUNDEEP
ruby-on-railspostgresql
Justin D.
Justin D.

Reputation: 4976

How to turn a sqlite3 table to a postgresql table?

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!

Upvotes: 0

Views: 121

Answers (1)

Chris Barretto
Chris Barretto

Reputation: 9529

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.

Upvotes: 1

Related Questions