Migrating NocoDB from SQLite to PostgreSQL (Linode Managed DB)

Self-Host
1

I’m not a developer, but I manage NocoDB for our business. It has become a critical tool for us, and as our data has grown, SQLite has started to feel sluggish. I’d like to move to PostgreSQL for better performance and reliability. We won’t really benefit from the additional features of NocoDB cloud since our primary benefit is having a single place to store a large amount of related data.

The current setup is a cheap shared vps on linode:

1 CPU Core

25 GB Storage

1 GB RAM

0 Volumes

My Goal

I’d like to transition the setup from the default SQlite → Postgres whilst retaining our bases and tables. I would ideally like to use Linode’s Databases feature so that the postgres database that houses all the data is separate to the server running Nocodb. That way I have more assurances that if I break something the business wont lose the underlying data.

What I’ve tried

I started by setting up a Linode Managed PostgreSQL database to keep the data separate from my compute instance. Once that was ready, I realized my instance needed to be on the same VPC to communicate with it, so I added that connection. Then I made sure the database would actually accept connections from my instance through the access control settings.

To get my data over, I used pgloader as I saw that was recommended. After some tinkering, and a little help from AI I believe all my data transferred over successfully. It showed 84,691 rows transferred which is significantly more then the tables contain.

I then updated my docker-compose configuration to point NocoDB at the new PostgreSQL database instead of SQLite, and tried to restart the container.

Environment variable I added.

NC_DB=pg://[private-db-hostname]:17912?u=[username]&p=[password]&d=defaultdb&ssl=prefer

That’s where it broke. NocoDB won’t start and keeps throwing an error saying tables like “nc_api_tokens” already exist.

nocodb-1 | migration file “nc_001_init” failed
nocodb-1 | migration failed with error: create table “nc_api_tokens” (“id” serial primary key, “base_id” varchar(20), “db_alias” varchar(255), “description” varchar(255), “permissions” text, “token” text, “expiry” varchar(255), “enabled” boolean default ‘1’, “fk_user_id” varchar(20), “fk_workspace_id” varchar(20), “fk_sso_client_id” varchar(20), “created_at” timestamptz, “updated_at” timestamptz) - relation “nc_api_tokens” already exists

nocodb-1 | ERROR [ExceptionHandler] create table “nc_api_tokens” … - relation “nc_api_tokens” already exists
nocodb-1 | error: create table “nc_api_tokens” … - relation “nc_api_tokens” already exists

nocodb-1 exited with code 1

Any help would be greatly appreciated.

2

Hi @michaell

It sounds like you made good progress. The issue seems like it might be related to missing the “xc_knex_migrations*” tables during the move (you might see versions like v0, v1, v2, v3, all of them are needed). These tables track the database state, so if any of them are missing, the app will try to initialize again.

We also support migrating from any database to Cloud/Enterprise. If that’s something you’d be interested in, you can check the guide here: OSS to Enterprise Migration.

Thanks for choosing NocoDB and feel free to follow up if you have any questions.

1 Like